Prisma ORM Setup for Web Application

Setting Up Prisma ORM for Web Application

Prisma is an ORM for Node.js and TypeScript with automatic type generation from database schema. Queries are typed automatically: autocomplete for fields, compilation error when accessing non-existent column, correct return types. This noticeably changes development speed.

Installation

npm install prisma @prisma/client
npx prisma init --datasource-provider postgresql

Schema

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

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

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")
}

model Profile {
  id        String  @id @default(cuid())
  bio       String? @db.Text
  avatarUrl String?
  userId    String  @unique
  user      User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

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

  @@index([authorId])
  @@index([published, createdAt(sort: Desc)])
  @@map("posts")
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  slug  String @unique
  posts Post[] @relation("PostToTag")

  @@map("tags")
}

enum Role {
  USER
  MODERATOR
  ADMIN
}

Migrations

# Create and apply migration
npx prisma migrate dev --name add_user_profile

# Production
npx prisma migrate deploy

# Reset database in dev
npx prisma migrate reset

# Check status
npx prisma migrate status

Client initialization

Singleton pattern for Next.js / hot reload:

// lib/prisma.ts
import { PrismaClient } from '@prisma/client'

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

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

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

  // Query logging in dev
  prisma.$on('query', (e) => {
    console.log(`Query: ${e.query} (${e.duration}ms)`)
  })
}

Query patterns

// Create with nested data
const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'Alice',
    profile: {
      create: { bio: 'Frontend developer' }
    }
  },
  include: { profile: true }
})

// Pagination with cursor (more efficient for large tables than offset)
async function getPosts(cursor?: string, limit = 20) {
  const posts = await prisma.post.findMany({
    take: limit + 1,
    ...(cursor && { cursor: { id: cursor }, skip: 1 }),
    where: { published: true },
    orderBy: { createdAt: 'desc' },
    select: {
      id: true,
      title: true,
      createdAt: true,
      author: { select: { id: true, name: true } }
    }
  })

  const hasMore = posts.length > limit
  return {
    posts: hasMore ? posts.slice(0, -1) : posts,
    nextCursor: hasMore ? posts[limit - 1].id : null
  }
}

// Transaction
async function publishPost(postId: string, authorId: string) {
  return prisma.$transaction(async (tx) => {
    const post = await tx.post.findUniqueOrThrow({
      where: { id: postId, authorId }
    })

    if (post.published) throw new Error('already published')

    return tx.post.update({
      where: { id: postId },
      data: {
        published: true,
        publishedAt: new Date()
      }
    })
  })
}

// Upsert
const tag = await prisma.tag.upsert({
  where: { slug },
  update: { name },
  create: { name, slug }
})

// Raw SQL when Prisma isn't enough
const result = await prisma.$queryRaw<{ count: bigint }[]>`
  SELECT count(*) FROM posts
  WHERE published = true
  AND created_at > ${new Date(Date.now() - 7 * 24 * 60 * 60 * 1000)}
`

Middleware for soft delete

prisma.$use(async (params, next) => {
  // Soft delete
  if (params.action === 'delete' && params.model === 'Post') {
    params.action = 'update'
    params.args.data = { deletedAt: new Date() }
  }

  // Filter deleted
  if (['findFirst', 'findMany', 'findUnique'].includes(params.action) && params.model === 'Post') {
    params.args.where = { ...params.args.where, deletedAt: null }
  }

  return next(params)
})

Seed data

// prisma/seed.ts
import { prisma } from '../lib/prisma'

async function main() {
  await prisma.user.upsert({
    where: { email: '[email protected]' },
    update: {},
    create: {
      email: '[email protected]',
      name: 'Admin',
      role: 'ADMIN',
    }
  })

  const tags = ['typescript', 'nodejs', 'prisma', 'postgresql']
  for (const name of tags) {
    await prisma.tag.upsert({
      where: { slug: name },
      update: {},
      create: { name, slug: name }
    })
  }
}

main()
  .then(() => console.log('Seed complete'))
  .catch(console.error)
  .finally(() => prisma.$disconnect())

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

Timelines

Prisma setup from scratch (schema, migrations, seed, typed client): 1 day. Integration into existing project with repository and test writing: 2–3 days. Migration from raw SQL or another ORM to Prisma: 3–5 days depending on codebase size.