🎉 2023 CV is released. Read more →
🖋️ Summary Notes #blog
Prisma

Prisma #ORM #async

What?

What Is the Process?

  1. Prisma Schema prisma/schema.prisma (opens in a new tab)
    • Data model 🔗 (opens in a new tab)
    • clear schema language ⇒ express data relationship in human-readable way
    • Or schema can also be automatically inferred from any existing DB
  2. Schema ⇒ type definition
    1. allowing perform typesafe CRUD operations with own DB model
  3. Migrations as ts structure evolves
  4. Visualize data
    1. Prisma studio ⇒ npx prisma studio ⇒ view and manage all tables and rows in browser

Why?

Why Prima?

Why Shouldn't?

How?

How to Setup the Project?

How to use Prisma?

  1. Define/create schema prisma/schema.prisma (opens in a new tab)

  2. /update database structure can be handled automatically

    • npx prisma migrate
      • or npx prisma migrate dev --name init ## give the name of init
      • npm prisma migrate dev --name test2
    • migration file will interact with database and make all changes
      • /prisma/migrations/init/migration.sql

  3. Install client

    1. npx i @prisma/client

  4. interact with the DB on the server

    • npx prisma generate ## go through all the steps generate based on whatever provider we want

  5. import Prisma in Clien

    t

  6. setup package.json scripts

    • “devStart”: “nodemon script.ts” ## npm run devStart

  • READING DATA

    // npm run devStart ## to run the script.ts
// run `npx prisma generate`
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient(); // manage different db for you !! only use one instance of it
// const prisma = new PrismaClient({log:["query"]}); // log the query in the terminal
 
// everything in prisma is async
async function main() {
  // ... you will write your Prisma Client queries here
 
  // delete all users in the database
  await prisma.user.deleteMany(); // How to delete everything form the database
  /*
    const user = await prisma.user.createMany({
        where: {
            AND: [
                { email: { startsWith: "alice"} },
                { email: { endsWith: "@test.com"} },
            ]
            OR: [],
            NOR: [],
            // queries
            name: { equals: "Alice" },
            name: { not: "Alice" },
            name: { in: ["Alice", "Kyle"] },
            name: { notIn: ["Alice", "Kyle"] },
            age: { lt: 20 }, // age less than 20    
            age: { lte: 20 }, // age less than or equal 20    
            age: { gt: 20 }, // age greater than 20    
            age: { gte: 20 }, // age greater than  or equal 20    
            email: { contains: "@test.com"}, 
            email: { endsWith: "@test.com"}, 
            email: { startsWith: "alice"}, 
 
        },
        distinct: ["name", "age"], // return one single Alice
        orderBy: {
            age: "asc", // "desc"
        }
        take: 2, // pagination
        skip: 1, // skip the first user Alice, get the next two
 
    })
        - cannot use select function
    
    const user = await prisma.user.findUnique({ 
        where: {
            email: "alice@test.com",
            age_name: { // this is generated by @@unique([age, name])
                age: 24,
                name: "Alice"
            }
        }
    })
        - find by unique keys
        - only return one
        - can use select and include function 
 
		// Find the first matching 
    const user = await prisma.user.findFirst({ 
        where: {
            name: "Alice"
            }
    })
        - only return one
 
    const user = await prisma.user.findMany()   // get all users
    const user = await prisma.user.findMany({
        where: {
            writtenPosts: {
                every: {
                    title: { startsWith: "Test" },
                    createdAt: new Date(),
                },
                none: {},
                some: {},
 
            }
        }
    })
    const user = await prisma.post.findMany({
        where:{
            author:{
                is: {
                    age: 24,
                }
                isNot: {}
            }
        }
    })
 
    */
 
  const user = await prisma.user.create({
    data: {
      name: "Alice",
      email: "alice@test.com",
      age: 24,
      userPreference: {
        // create function allows create a brand new user preference
        create: {
          emailUpdates: true,
        },
      },
    },
 
    /* CAN ONLY INCLUDE OR SELECT CANNOT DO BOTH */
 
    // include the userPreference model
    include: {
      userPreference: true,
    },
    // only return the name property
    select: {
      name: true,
      userPreference: { select: { id: true } }, // only the id of the userPreference
    },
  });
 
  console.log(user);
}
 
main()
  .catch((e) => {
    console.error(e.message); // catching any errors and printing them out
  })
  .finally(async () => {
    await prisma.$disconnect(); // finally disconnected
  });

  • UPDATE DATA

    async function main() {
  // update first user
  // - can use include and select function
  const user = await prisma.user.update({
    where: {
      email: "alice@test.com", // take this
    },
    data: {
      email: "alice@test2.com", // change to this
    },
  });
 
  // updateMany: some as update but update all users
  const users = await prisma.user.updateMany({
    where: {
      name: "Alice", // take this
    },
    data: {
      name: "New Alice", // change all matching to this
    },
  });
  console.log(user);
}
 
const user = await prisma.user.update({
  where: {
    email: "alice@test.com", // take this
  },
  data: {
    email: "alice@test2.com", // change to this
    age: {
      increment: 1, // increment the int
      decrement: 1,
      multiply: 1,
      divide: 1,
    },
    // update user and give a new user preference with this information
    userPreference: {
      create: {
        emailUpdates: true,
      },
      connect: {
        id: "askdjflkasjdlfkjalskdjf",
      },
    },
  },
});

  • DELETE DATA

    async function main() {
  // simiar to find
  // must be unique field
  const user = await prisma.user.delete({
    where: {
      email: "alice@test.com",
    },
  });
  console.log(user);
 
  // deleteMany
  const users = await prisma.user.deleteMany({
    where: {
      age: { gt: 20 },
    },
  });
 
  // delete all user in the database
  const users = await prisma.user.deleteMany();
}

How to format Prisma file?

  • npx prisma format
  • Plugin (can set in setting.json [prisma])
DockerReact