12. Databases

12.3 Prisma ORM

Prisma is a modern TypeScript‑first ORM that simplifies database access in Node.js/Next.js applications.

12.3.1 Setup

Install the CLI and client, then initialize the project.

npm install prisma @prisma/client
npx prisma init

This creates a prisma folder with schema.prisma and a .env file. Add your database URL:

DATABASE_URL="postgresql://user:pass@localhost:5432/mydb?schema=public"

Generate the client:

npx prisma generate

In Next.js, instantiate PrismaClient as a singleton in development (to avoid hot‑reload creating many instances) and globally in production:

import { PrismaClient } from '@prisma/client'

const globalForPrisma = global as unknown as { prisma: PrismaClient }

export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: ['query'],
  })

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

12.3.2 Schema

The prisma.schema file defines models, fields, enums, composite types, and indexes.

Example model:

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]   @relation("UserPosts")
  profile   Profile? @relation("UserProfile")
  createdAt DateTime @default(now())
}

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

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation("UserPosts", fields: [authorId], references: [id])
  tags      Tag[]    @relation("PostTags")
  createdAt DateTime @default(now())
}

model Tag {
  id        Int      @id @default(autoincrement())
  name      String   @unique
  posts     Post[]   @relation("PostTags")
}

@@index([email])

Features:

• Enums – Define a fixed set of values (enum Role { ADMIN USER }).
• Composite types – Group related scalar fields (type Address { street String city String zip String }).
• Indexes – @@index([email]) creates a B‑tree index; @@unique enforces uniqueness.
• Prisma‑level validations – Use @db.VarChar(255) or custom validation functions in application logic.

12.3.3 Migrations

Prisma Migrations manage schema changes safely.

• Development – npx prisma migrate dev --name init creates a migration folder and applies changes.
• Production – npx prisma migrate deploy applies pending migrations.
• Reset – npx prisma migrate reset drops and recreates the database (use with caution).
• Baseline – If you have an existing database, npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script generates a baseline migration.

Migration history lives in prisma/migrations. Handle failed migrations by inspecting the error, fixing the schema, and re‑running prisma migrate resolve --rolled-back or prisma migrate reset in dev. Drift detection (prisma migrate check) alerts when the database diverges from the schema.

12.3.4 Relations

Prisma expresses relations via model fields and the @relation attribute.

• One‑to‑One – profile Profile? @relation("UserProfile", fields: [profileId], references: [id]) plus @@unique([profileId]).
• One‑to‑Many – posts Post[] on the User model; inverse author User @relation("UserPosts").
• Many‑to‑Many – Two approaches:
  1. Implicit – Prisma creates a hidden join table when you define posts Post[] @relation("PostTags") on both sides.
  2. Explicit – Define a join model (model PostTag { post Post @relation(fields: [postId], references: [id]) tag Tag @relation(fields: [tagId], references: [id]) @@id([postId, tagId]) }) giving you control over extra columns.
• Referential actions – Set onDelete: Cascade or onDelete: SetNull in the relation attribute to control cascading behavior.

12.3.5 Transactions

Prisma supports interactive and batch transactions to guarantee atomicity across multiple operations.

Interactive transaction (array of promises):

await prisma.$transaction([
  prisma.user.create({ data: { email: 'alice@example.com', name: 'Alice' } }),
  prisma.profile.create({ data: { bio: 'Hello', user: { connect: { email: 'alice@example.com' } } } })
])

Async callback transaction (for complex logic):

await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email: 'bob@example.com' } })
  await tx.post.create({ data: { title: 'First post', author: { connect: { id: user.id } } } })
  // additional steps…
})

You can specify isolation levels (ReadUncommitted, ReadCommitted, RepeatableRead, Serializable) by passing maxWait and timeout options. Errors inside the transaction trigger an automatic rollback; successful completion commits all changes.

Best practices:

• Keep transactions short to reduce lock contention.
• Handle validation errors before entering a transaction.
• Use prisma.$queryRaw for raw SQL when needed, but prefer Prisma’s typed API for safety.