Prisma ORM: Type-Safe Database Access in TypeScript

Setup and Schema

npm install prisma @prisma/client
npx prisma init

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

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

model User {
  id        String    @id @default(cuid())
  email     String    @unique
  name      String?
  role      Role      @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  @@index([email])
  @@map("users")  // Table name
}

model Post {
  id          String    @id @default(cuid())
  title       String
  content     String?
  published   Boolean   @default(false)
  viewCount   Int       @default(0)
  author      User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId    String
  tags        Tag[]
  publishedAt DateTime?
  createdAt   DateTime  @default(now())

  @@index([authorId])
  @@index([published, createdAt])
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Migrations

# Create migration
npx prisma migrate dev --name add-user-profile

# Apply in production
npx prisma migrate deploy

# Generate client
npx prisma generate

CRUD Operations

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

const prisma = new PrismaClient({
  log: ['query', 'error', 'warn'],
});

// Create with relation
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    posts: {
      create: [
        { title: 'My first post' },
        { title: 'Hello World', published: true },
      ],
    },
  },
  include: { posts: true },
});

// Find with filters and relations
const publishedPosts = await prisma.post.findMany({
  where: {
    published: true,
    author: { role: 'ADMIN' },
    createdAt: { gte: new Date('2024-01-01') },
  },
  include: {
    author: { select: { name: true, email: true } },
    tags: true,
  },
  orderBy: { createdAt: 'desc' },
  skip: 0,
  take: 20,
});

// Update
await prisma.post.update({
  where: { id: 'post_123' },
  data: {
    published: true,
    publishedAt: new Date(),
    viewCount: { increment: 1 },
  },
});

// Upsert
await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  create: { email: 'alice@example.com', name: 'Alice' },
  update: { name: 'Alice Updated' },
});

// Delete with cascade
await prisma.user.delete({ where: { id: userId } });

Transactions

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

// Sequential transactions (simpler)
const [usersCount, postsCount] = await prisma.$transaction([
  prisma.user.count(),
  prisma.post.count({ where: { published: true } }),
]);

Raw Queries

// Raw SQL when Prisma's API isn't enough
const users = await prisma.$queryRaw<User[]>`
  SELECT u.*, COUNT(p.id)::int as post_count
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  GROUP BY u.id
  ORDER BY post_count DESC
  LIMIT 10
`;

// Execute raw (no return)
await prisma.$executeRaw`
  UPDATE posts SET view_count = view_count + 1
  WHERE id = ${postId}
`;

Middleware and Soft Deletes

prisma.$use(async (params, next) => {
  // Soft delete middleware
  if (params.model === 'Post') {
    if (params.action === 'delete') {
      params.action = 'update';
      params.args['data'] = { deletedAt: new Date() };
    }
    if (params.action === 'findMany') {
      params.args.where = { ...params.args.where, deletedAt: null };
    }
  }
  return next(params);
});

Prisma's type safety catches schema changes at compile time, preventing runtime database errors.