Prisma

• ORM with typesafe client generation and UI

• works across db types including Postgres and MongoDB

Setup*

npm install prisma --save-dev
npx prisma init #create a `schema.prisma` file
#edit schema
npx prisma generate
npx prisma migrate dev --name init #for dev, generates migrations and client
npx prisma deploy #for prod wont reset stuff, w/o protection

Usage

 prisma studio #open explorer

Schema

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  role    Role     @default(USER)
  posts   Post[]
  profile Profile?
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @unique
}

model Post {
  id         Int        @id @default(autoincrement())
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
  title      String
  published  Boolean    @default(false)
  author     User       @relation(fields: [authorId], references: [id])
  authorId   Int
  categories Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

enum Role {
  USER
  ADMIN
}

Client*

src/utils/prisma.ts

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

export default prisma;

Query

// Must be unique column/id
const caseData = await prisma.case.findUnique({
  where: { id: parseInt(id as string) },
  include: { answers: true },
});

const corpus = await prisma.corpus.findMany({
  where: { caseId: Number(caseId) },
  orderBy: { createdAt: "desc" },
});

• .findFirst

• .findFirstOrThrow

• .findUniqueOrThrow

Create

const newCase = await prisma.case.create({
  data: {
    form: { connect: { id: formId } },
    answers: {
      create: answers.map(
        (a: { value: string; formQuestionId: number }) => ({
          value: a.value,
          formQuestion: { connect: { id: a.formQuestionId } },
        })
      ),
    },
  },
});

const result = await prisma.user.createMany({
  data: [
    { name: 'Bob', email: '[e-mail]' },
    { name: 'Yewande', email: '[e-mail]' },
    // ... more records
  ],
  skipDuplicates: true
})

Update

const updatedCase = await prisma.case.update({
        where: { id: parseInt(id) },
        data: {
          form: { connect: { id: formId } },
          answers: {
            deleteMany: {
              id: {
                notIn: answers
                  .map((a: { id?: number }) => a.id)
                  .filter((id): id is number => id !== undefined),
              },
            },
            upsert: answers.map(
              (a: { id?: number; value: string; formQuestionId: number }) => ({
                where: { id: a.id || -1 },
                update: { value: a.value },
                create: {
                  value: a.value,
                  formQuestion: { connect: { id: a.formQuestionId } },
                },
              })
            ),
          },
        },
      });

Advanced

pg_vector?

await prisma.$executeRaw`
  ALTER TABLE "VectorizedItem"
  ADD COLUMN "embedding_512" vector(512);
`;

Transactions

Ensure all or none of operations succeed, commited when end of transaction reached

// sequential operations
const [posts, totalPosts] = await prisma.$transaction([
  prisma.post.findMany({ where: { title: { contains: 'prisma' } } }),
  prisma.post.count(),
])

//interactive transactions
function transfer(from: string, to: string, amount: number) {
  return prisma.$transaction(async (tx) => {
    // 1. Decrement amount from the sender.
    const sender = await tx.account.update({
      data: {
        balance: {
          decrement: amount,
        },
      },
      where: {
        email: from,
      },
    })

    // 2. Verify that the sender's balance didn't go below zero.
    if (sender.balance < 0) {
      throw new Error(`${from} doesn't have enough to send ${amount}`)
    }

    // 3. Increment the recipient's balance by amount
    const recipient = await tx.account.update({
      data: {
        balance: {
          increment: amount,
        },
      },
      where: {
        email: to,
      },
    })

    return recipient
  })
}

Gotchas

hidden: {not: true} doesn't return null values, https://github.com/prisma/prisma/issues/24252, instead do below

return await prisma.priorAuth.findMany({
    where: {
       OR: [
         {
           hidden: {
              not: true,
           },
         },
         {
           hidden: null,
         },
      ],
   }
}