DEV Community

Atlas Whoff
Atlas Whoff

Posted on

Prisma in Production: Transactions, Soft Deletes, Cursor Pagination, and Zero-Downtime Migrations

#prisma #database #typescript #webdev

Prisma makes database access type-safe and ergonomic. But most tutorials stop at basic CRUD. These are the patterns that matter in production: transactions, soft deletes, pagination, full-text search, and schema evolution without downtime.

Transactions

Use $transaction for operations that must succeed or fail together:

// Sequential transaction -- each step uses results from the previous
const result = await db.$transaction(async (tx) => {
  const order = await tx.order.create({
    data: { userId, total },
  })

  await tx.inventory.updateMany({
    where: { productId: { in: productIds } },
    data: { reserved: { increment: 1 } },
  })

  await tx.payment.create({
    data: { orderId: order.id, amount: total, status: 'pending' },
  })

  return order
})

// Batch transaction -- independent operations, runs in parallel
const [users, products] = await db.$transaction([
  db.user.findMany(),
  db.product.findMany({ where: { active: true } }),
])
Enter fullscreen mode Exit fullscreen mode

Soft Deletes

Never permanently delete user data. Use a deletedAt timestamp:

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String
  deletedAt DateTime?
  createdAt DateTime @default(now())
}
Enter fullscreen mode Exit fullscreen mode 
// Soft delete
await db.post.update({
  where: { id },
  data: { deletedAt: new Date() },
})

// Query only non-deleted (add to every query)
const posts = await db.post.findMany({
  where: { deletedAt: null },
})

// Better: use Prisma middleware to auto-filter
db.$use(async (params, next) => {
  if (params.model === 'Post' && params.action === 'findMany') {
    params.args.where = { ...params.args.where, deletedAt: null }
  }
  return next(params)
})
Enter fullscreen mode Exit fullscreen mode

Cursor-Based Pagination

Offset pagination breaks when data changes. Use cursors for feeds and infinite scroll:

async function getPosts(cursor?: string, limit = 20) {
  const posts = await db.post.findMany({
    take: limit + 1, // fetch one extra to check if there's a next page
    cursor: cursor ? { id: cursor } : undefined,
    skip: cursor ? 1 : 0, // skip the cursor itself
    orderBy: { createdAt: 'desc' },
    where: { deletedAt: null },
  })

  const hasNextPage = posts.length > limit
  const items = hasNextPage ? posts.slice(0, -1) : posts

  return {
    items,
    nextCursor: hasNextPage ? items[items.length - 1].id : null,
  }
}
Enter fullscreen mode Exit fullscreen mode

Full-Text Search

PostgreSQL full-text search via Prisma:

// schema.prisma -- enable full text search
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch"]
}

// Query
const results = await db.post.findMany({
  where: {
    title: { search: 'nextjs performance' },
  },
  orderBy: {
    _relevance: {
      fields: ['title', 'content'],
      search: 'nextjs performance',
      sort: 'desc',
    },
  },
})
Enter fullscreen mode Exit fullscreen mode

Raw Queries for Complex Cases

When Prisma's query builder can't express what you need:

const results = await db.$queryRaw<Array<{ id: string; score: number }>>`
  SELECT id, ts_rank(search_vector, plainto_tsquery('english', ${query})) AS score
  FROM posts
  WHERE search_vector @@ plainto_tsquery('english', ${query})
  ORDER BY score DESC
  LIMIT 20
`
Enter fullscreen mode Exit fullscreen mode

The template literal syntax prevents SQL injection. Never use string concatenation.

Zero-Downtime Migrations

For production databases, never rename or drop columns in one migration:

Step 1 (deploy): Add new column, make it nullable
Step 2 (run): Backfill existing rows
Step 3 (deploy): Make column required, remove old column
Enter fullscreen mode Exit fullscreen mode 
# Create migration without applying it
npx prisma migrate dev --create-only --name add_search_vector

# Apply in production
npx prisma migrate deploy
Enter fullscreen mode Exit fullscreen mode

Connection Pooling 

// lib/db.ts -- singleton for serverless environments
import { PrismaClient } from '@prisma/client'

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

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

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = db
Enter fullscreen mode Exit fullscreen mode

For high-traffic apps, use PgBouncer or Prisma Accelerate to manage connection limits.

The AI SaaS Starter at whoffagents.com ships with Prisma configured with the singleton pattern, soft delete middleware, and cursor pagination helpers pre-built. $99 one-time.

Top comments (0)