RefPad

Prisma

Requires Prisma v6 / PostgreSQL (MySQL and SQLite work mostly the same)

Table of Contents

  1. Setup
  2. Schema Definition
  3. Migrations
  4. Basic Prisma Client Operations
  5. Filtering, Sorting & Pagination
  6. Relation Operations
  7. Transactions
  8. Key Commands

1. Setup

npm install prisma @prisma/client
npx prisma init

prisma init generates:

prisma/
└── schema.prisma   # schema definition file
.env                # contains DATABASE_URL

# .env
DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Managing the Prisma Client Instance

Use a singleton pattern during development to avoid creating a new instance on every hot reload.

// lib/prisma.ts
import { PrismaClient } from '@prisma/client'
 
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient }
 
export const prisma =
  globalForPrisma.prisma ?? new PrismaClient({ log: ['query'] })
 
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

2. Schema Definition

datasource / generator

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
generator client {
  provider = "prisma-client-js"
}

Model Definition

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
 
  posts     Post[]   // one-to-many relation
}
 
model Post {
  id          Int      @id @default(autoincrement())
  title       String
  content     String?
  published   Boolean  @default(false)
  createdAt   DateTime @default(now())
 
  authorId    Int
  author      User     @relation(fields: [authorId], references: [id])
 
  tags        Tag[]    @relation("PostTags") // many-to-many
}
 
model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
 
  posts Post[] @relation("PostTags")
}

Common Field Attributes

AttributeDescription
@idPrimary key
@default(autoincrement())Auto-increment
@default(cuid())Auto-generate a CUID
@default(uuid())Auto-generate a UUID
@default(now())Set creation timestamp automatically
@updatedAtSet update timestamp automatically
@uniqueUnique constraint
@relation(...)Define a relation
? (after type)Nullable

Composite Unique / Index

model UserFollow {
  followerId  Int
  followingId Int
 
  @@id([followerId, followingId])        // composite primary key
  @@unique([followerId, followingId])    // composite unique
  @@index([followingId])                 // index
}

3. Migrations

# Apply schema changes as a migration (development)
npx prisma migrate dev --name add_user_table
 
# Apply migrations to production DB (no file generation)
npx prisma migrate deploy
 
# Force-sync schema to DB (may lose data — for development prototypes)
npx prisma db push
 
# Manually regenerate Prisma Client (after direct schema edits)
npx prisma generate

migrate dev also runs prisma generate automatically, so explicit generate is usually not needed.

4. Basic Prisma Client Operations

import { prisma } from '@/lib/prisma'

Create

// Create one record
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
  },
})
 
// Create multiple records
await prisma.user.createMany({
  data: [
    { email: 'bob@example.com', name: 'Bob' },
    { email: 'carol@example.com', name: 'Carol' },
  ],
  skipDuplicates: true, // skip on conflict
})

Read

// Find one by primary key (returns null if not found)
const user = await prisma.user.findUnique({
  where: { id: 1 },
})
 
// Find first matching record
const user = await prisma.user.findFirst({
  where: { email: 'alice@example.com' },
})
 
// Find multiple records
const users = await prisma.user.findMany({
  where: { name: { not: null } },
  orderBy: { createdAt: 'desc' },
  take: 10,
  skip: 0,
})
 
// Count records
const count = await prisma.user.count({
  where: { published: true },
})

Update

// Update one record (uniquely identified by where)
const user = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Updated' },
})
 
// Update multiple records
await prisma.user.updateMany({
  where: { published: false },
  data: { published: true },
})
 
// Upsert (update if exists, create if not)
const user = await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  update: { name: 'Alice' },
  create: { email: 'alice@example.com', name: 'Alice' },
})

Delete

// Delete one record
await prisma.user.delete({
  where: { id: 1 },
})
 
// Delete multiple records
await prisma.user.deleteMany({
  where: { createdAt: { lt: new Date('2024-01-01') } },
})

Select Specific Fields

const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    email: true,
    // name is not fetched
  },
})
// user type becomes { id: number; email: string }

5. Filtering, Sorting & Pagination

Filter Operators

await prisma.post.findMany({
  where: {
    title: { contains: 'Prisma', mode: 'insensitive' }, // case-insensitive
    published: true,
    createdAt: { gte: new Date('2024-01-01') },         // on or after
    authorId: { in: [1, 2, 3] },                        // matches any
  },
})
OperatorDescription
equalsEqual (default)
notNot equal
inMatches any value in array
notInDoes not match any value in array
lt/lteLess than / less than or equal
gt/gteGreater than / greater than equal
containsSubstring match (string)
startsWithStarts with
endsWithEnds with

AND / OR / NOT

await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: 'Prisma' } },
      { title: { contains: 'TypeScript' } },
    ],
    AND: [
      { published: true },
      { authorId: { not: null } },
    ],
  },
})

Sorting

await prisma.post.findMany({
  orderBy: [
    { published: 'desc' },
    { createdAt: 'asc' },
  ],
})

Pagination

// Offset-based
await prisma.post.findMany({
  skip: 20,  // skip 20 records
  take: 10,  // take 10 records (= page 3)
})
 
// Cursor-based (efficient for large datasets)
await prisma.post.findMany({
  cursor: { id: lastId },
  take: 10,
  skip: 1, // exclude the cursor record itself
})

6. Relation Operations

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
  },
})
// user.posts contains Post[]
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    posts: {
      create: [
        { title: 'First post', published: true },
        { title: 'Draft' },
      ],
    },
  },
  include: { posts: true },
})

Connect Existing Records

await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: {
      connect: [{ id: 10 }, { id: 11 }],     // link existing tags
      disconnect: [{ id: 9 }],               // unlink a tag
      set: [{ id: 10 }],                     // replace all with this set
    },
  },
})

connectOrCreate

await prisma.post.create({
  data: {
    title: 'New post',
    author: {
      connectOrCreate: {
        where: { email: 'alice@example.com' },
        create: { email: 'alice@example.com', name: 'Alice' },
      },
    },
  },
})

7. Transactions

$transaction (Array Form)

All operations must succeed, otherwise the transaction rolls back.

const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: 'alice@example.com' } }),
  prisma.post.create({ data: { title: 'Post', authorId: 1 } }),
])

$transaction (Interactive Form)

Use when later operations depend on the result of earlier ones.

const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: 'alice@example.com' },
  })
 
  const post = await tx.post.create({
    data: { title: 'Post', authorId: user.id },
  })
 
  return { user, post }
})

8. Key Commands

# Apply schema changes as a migration (development)
npx prisma migrate dev --name <name>
 
# Apply migrations to production DB
npx prisma migrate deploy
 
# Regenerate Prisma Client
npx prisma generate
 
# Sync schema directly to DB (development prototypes)
npx prisma db push
 
# Browse DB contents in a GUI
npx prisma studio
 
# Generate schema from existing DB
npx prisma db pull
 
# Check migration status
npx prisma migrate status