Prisma in Production: Migrations, Safe Schema Changes, and Connection Pooling

Prisma Migrations in Production

Running prisma migrate dev in production is wrong. Here's the correct workflow.

Development vs Production Migration Commands

# Development: creates migration files + applies them
npx prisma migrate dev --name add_user_table

# Production: applies existing migration files ONLY
npx prisma migrate deploy

# Never in production:
# npx prisma migrate dev       <- drops/resets schemas
# npx prisma db push           <- schema drift, no migration history
# npx prisma migrate reset     <- drops all data

Deployment Pipeline

# .github/workflows/deploy.yml
name: Deploy
on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Install dependencies
        run: npm ci

      - name: Run database migrations
        run: npx prisma migrate deploy
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

      - name: Build application
        run: npm run build

      - name: Deploy to Vercel
        run: npx vercel --prod
        env:
          VERCEL_TOKEN: ${{ secrets.VERCEL_TOKEN }}

Safe Schema Changes

Some migrations are safe. Some cause downtime. Know the difference.

Safe (zero downtime):

-- Adding a nullable column
ALTER TABLE users ADD COLUMN bio TEXT;

-- Adding an index
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Adding a table
CREATE TABLE notifications (...);

Dangerous (requires care):

-- Renaming a column (breaks old code still reading old name)
ALTER TABLE users RENAME COLUMN username TO handle;

-- Adding NOT NULL without a default (fails on existing rows)
ALTER TABLE users ADD COLUMN plan VARCHAR NOT NULL; -- FAILS

-- Dropping a column (old deploys still reference it)
ALTER TABLE users DROP COLUMN old_field;

The Expand-Contract Pattern

For zero-downtime column rename:

Step 1: Add new column (nullable)
  ALTER TABLE users ADD COLUMN handle TEXT;

Step 2: Deploy code that writes to BOTH old and new column
  user.username = value
  user.handle = value

Step 3: Backfill: UPDATE users SET handle = username;

Step 4: Deploy code that reads from new column only

Step 5: Drop old column
  ALTER TABLE users DROP COLUMN username;

Prisma Schema Patterns

model User {
  id          String    @id @default(cuid())
  email       String    @unique
  name        String?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  // Relations
  subscription  Subscription?
  posts         Post[]
  apiKeys       ApiKey[]

  @@index([email])  // Index for email lookups
}

model Subscription {
  id                String    @id @default(cuid())
  userId            String    @unique
  user              User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  stripeCustomerId  String?   @unique
  stripePriceId     String?
  plan              String    @default("free")
  status            String    @default("active")
  currentPeriodEnd  DateTime?
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt

  @@index([stripeCustomerId])
}

Connection Pooling

Serverless environments (Vercel) open a new DB connection per request. Use PgBouncer or Prisma Accelerate:

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

// Prevent multiple instances in dev (hot reload)
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

For Vercel, use Prisma Accelerate or Neon's connection pooling:

# .env
DATABASE_URL="prisma://accelerate.prisma-data.net/?api_key=..."
DIRECT_URL="postgresql://user:pass@host/db"  # For migrations

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")    // Pooled (Accelerate)
  directUrl = env("DIRECT_URL")      // Direct (migrations)
}

Pre-Configured in the AI SaaS Starter

The AI SaaS Starter Kit ships with Prisma configured for Vercel deployment, connection pooling set up, User + Subscription + ApiKey schemas ready, and migration CI/CD included.

$99 one-time at whoffagents.com