Schema & Migrations

schema.prisma

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"    // postgresql | mysql | sqlite | mongodb | sqlserver
  url      = env("DATABASE_URL")
}

// Basic model
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String
  bio       String?
  role      Role     @default(USER)
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  posts     Post[]
  profile   Profile?

  @@map("users")            // map to different table name
  @@index([email])
}

// Enum
enum Role {
  USER
  ADMIN
  MOD
}

// One-to-one
model Profile {
  id     String  @id @default(cuid())
  bio    String?
  avatar String?
  userId String  @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}

// One-to-many
model Post {
  id        String    @id @default(cuid())
  title     String
  content   String?
  published Boolean   @default(false)
  authorId  String
  author    User      @relation(fields: [authorId], references: [id])
  tags      Tag[]     // many-to-many
  comments  Comment[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  @@index([authorId])
  @@fulltext([title, content])   // MySQL only
}

// Many-to-many (explicit join table)
model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]
}

// Many-to-many with extra fields (explicit relation table)
model UserFollower {
  followerId  String
  followingId String
  createdAt   DateTime @default(now())

  follower  User @relation("following", fields: [followerId], references: [id])
  following User @relation("followers", fields: [followingId], references: [id])

  @@id([followerId, followingId])
}

Field Types & Attributes

model Example {
  // ID strategies
  id     Int    @id @default(autoincrement())
  id     String @id @default(cuid())
  id     String @id @default(uuid())

  // Field attributes
  email  String  @unique
  code   String  @default("N/A")
  price  Float   @default(0.0)
  data   Json?
  bytes  Bytes?

  // Timestamps
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?

  // DB-native attributes
  name   String @db.VarChar(100)    // PostgreSQL
  body   String @db.Text

  // Model-level attributes
  @@unique([firstName, lastName])
  @@index([email, createdAt(sort: Desc)])
  @@id([tenantId, userId])          // composite primary key
}

Migrations

# Generate and apply migration
npx prisma migrate dev --name add_user_bio

# Apply migrations in production (no schema changes, no data seeding)
npx prisma migrate deploy

# View migration status
npx prisma migrate status

# Reset database (drops, re-creates, re-applies migrations + seed)
npx prisma migrate reset

# Generate Prisma Client after schema changes
npx prisma generate

# Push schema changes without migration (dev/prototyping only — NEVER prod)
npx prisma db push

# Introspect existing database → generate schema
npx prisma db pull

# Open Prisma Studio (GUI)
npx prisma studio

# Seed database
npx prisma db seed

# package.json seed script
{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}

Client Setup

// lib/prisma.ts — singleton pattern (important for hot reloading)
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
  });

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

Queries & Relations

CRUD Operations

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

// Create
const user = await prisma.user.create({
  data: { email: 'alice@example.com', name: 'Alice' },
});

// Create with relation
const post = await prisma.post.create({
  data: {
    title: 'Hello World',
    author: { connect: { id: userId } },   // connect existing
    tags: { create: [{ name: 'intro' }] }, // create and connect
  },
  include: { author: true, tags: true },
});

// Find one
const user = await prisma.user.findUnique({ where: { id: '...' } });
const user = await prisma.user.findUnique({ where: { email: 'a@b.com' } });
const user = await prisma.user.findFirst({ where: { role: 'ADMIN' }, orderBy: { createdAt: 'desc' } });
const user = await prisma.user.findFirstOrThrow({ where: { id: '...' } }); // throws if not found

// Find many
const users = await prisma.user.findMany({
  where: {
    isActive: true,
    role: { in: ['USER', 'MOD'] },
    name: { contains: 'ali', mode: 'insensitive' },
    createdAt: { gte: new Date('2024-01-01') },
    posts: { some: { published: true } },  // has at least one published post
  },
  select: { id: true, email: true, name: true },   // projection
  include: { posts: { where: { published: true } } },
  orderBy: [{ createdAt: 'desc' }, { name: 'asc' }],
  skip: 20,
  take: 10,
});

// Count
const count = await prisma.user.count({ where: { isActive: true } });

// Update
const updated = await prisma.user.update({
  where: { id: userId },
  data: {
    name: 'Alice B.',
    posts: { disconnect: [{ id: postId }] },  // disconnect relation
  },
});

// Upsert
const user = await prisma.user.upsert({
  where: { email: 'a@b.com' },
  create: { email: 'a@b.com', name: 'Alice' },
  update: { name: 'Alice B.' },
});

// Delete
await prisma.user.delete({ where: { id: userId } });
await prisma.user.deleteMany({ where: { isActive: false } });

Relations & Includes

// Include related records
const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
      include: { tags: true },
    },
    profile: true,
  },
});

// Select (choose fields, more efficient than include)
const user = await prisma.user.findUnique({
  where: { id: userId },
  select: {
    id: true,
    name: true,
    posts: {
      select: { id: true, title: true },
      take: 3,
    },
  },
});

// Nested writes
await prisma.user.update({
  where: { id: userId },
  data: {
    posts: {
      create: { title: 'New Post' },           // create and connect
      connect: { id: existingPostId },          // connect existing
      disconnect: { id: postToRemove },         // disconnect
      delete: { id: postToDelete },             // delete
      update: {                                 // update connected
        where: { id: postId },
        data: { published: true },
      },
    },
  },
});

Transactions & Raw SQL

// Sequential transaction (each step depends on previous result)
const [user, post] = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email: 'a@b.com', name: 'Alice' } });
  const post = await tx.post.create({ data: { title: 'Hello', authorId: user.id } });
  return [user, post];
});

// Batch transaction (independent, faster)
const [users, posts] = await prisma.$transaction([
  prisma.user.findMany(),
  prisma.post.findMany({ where: { published: true } }),
]);

// Raw SQL — tagged template (safe from SQL injection)
const users = await prisma.$queryRaw`
  SELECT u.*, COUNT(p.id)::int AS post_count
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  WHERE u.is_active = true
  GROUP BY u.id
  HAVING COUNT(p.id) > ${minPosts}
`;

// Execute raw (for INSERT/UPDATE/DELETE)
const result = await prisma.$executeRaw`
  UPDATE users SET last_seen = NOW() WHERE id = ${userId}
`;  // returns number of affected rows

Filtering Cheatsheet

// String filters
{ name: { equals: 'Alice' } }
{ name: { not: 'Alice' } }
{ name: { contains: 'ali' } }
{ name: { startsWith: 'Ali' } }
{ name: { endsWith: 'ce' } }
{ name: { contains: 'ali', mode: 'insensitive' } }

// Number / date filters
{ age: { gt: 18 } }
{ age: { gte: 18, lte: 65 } }
{ createdAt: { gte: new Date('2024-01-01') } }

// List filters
{ role: { in: ['ADMIN', 'MOD'] } }
{ role: { notIn: ['BANNED'] } }

// Null filters
{ deletedAt: null }
{ deletedAt: { not: null } }

// Relation filters
{ posts: { some: { published: true } } }   // has at least one
{ posts: { every: { published: true } } }  // all are
{ posts: { none: { published: true } } }   // none are
{ posts: { is: { id: postId } } }          // for to-one

// Logical
{ AND: [{ isActive: true }, { role: 'USER' }] }
{ OR: [{ role: 'ADMIN' }, { role: 'MOD' }] }
{ NOT: { role: 'BANNED' } }

Prisma Interview Questions

Q: What is Prisma and how does it differ from other ORMs?

Prisma is a next-generation ORM for Node.js/TypeScript consisting of three tools: Prisma Client (auto-generated, type-safe query builder), Prisma Migrate (schema migration system), and Prisma Studio (GUI). Unlike ActiveRecord-style ORMs (TypeORM, Sequelize with class-based entities), Prisma generates a fully type-safe client from the schema — all queries return typed results with no casting needed.

Q: What is the difference between prisma migrate dev and prisma migrate deploy?

prisma migrate dev creates and applies new migrations in development — it also re-generates the Prisma Client and can reset the database. prisma migrate deploy only applies pending migrations in production — it never modifies the schema.prisma file or resets the database. Always use deploy in CI/CD pipelines.

Q: What is the difference between select and include?

include adds relation fields to the default result (all scalar fields + specified relations). select lets you explicitly choose which fields to return — more efficient as it only fetches what you specify. You cannot use both at the top level; within a nested include/select, you can use the other.

Q: How do you handle N+1 queries in Prisma?

Prisma prevents N+1 by batching relation queries automatically when using include. When you write include: { posts: true }, Prisma issues two queries (one for users, one for all their posts) and joins them in memory — not one query per user. For DataLoader-style batching in GraphQL, Prisma works well with graphql-dataloader.

Q: How do you handle soft deletes in Prisma?

// Add deletedAt to schema
model User {
  deletedAt DateTime?
}

// Soft delete
await prisma.user.update({
  where: { id: userId },
  data: { deletedAt: new Date() },
});

// Always filter in queries (or use Prisma middleware)
prisma.$use(async (params, next) => {
  if (params.model === 'User' && params.action === 'findMany') {
    params.args.where = { ...params.args.where, deletedAt: null };
  }
  return next(params);
});

Q: How do you paginate efficiently with Prisma?

// Offset pagination (simple but slow on large datasets)
const users = await prisma.user.findMany({ skip: page * limit, take: limit });

// Cursor pagination (fast, works with sorted data)
const users = await prisma.user.findMany({
  take: 10,
  cursor: { id: lastSeenId },  // start after this cursor
  skip: 1,                     // skip the cursor itself
  orderBy: { id: 'asc' },
});

// Return cursor for next page
const nextCursor = users.length === 10 ? users[9].id : null;