Skip to content
SylphxSylphx
Sign inStart building
Console
Menu

Start

Platform

Agents

AI

App store purchases

Auth

Builds

Credits

Database

Email

Flags

Generated Assets

Hosting

Jobs and cron

Knowledge

KV

Localization

Monitoring

Network

Notifications

Organizations

Payments

Queues

Realtime

Runners

Sandboxes

Search

Storage

Webhooks

Workflows

Getting Started

Authentication

AI Services

KV Store

Webhooks

Email

Deploy & Infrastructure

Reference

On this page

How-to

Migrations

How schema changes reach a database, over the direct connection, without a window of downtime.

Migrations run over the direct_uri that connect returns, never over the pooled one: a migration needs a session of its own, and the pooler will not keep one for it.

The connection migrations use

connect answers with uri (pooled) and direct_uri (direct). Point the migration tool at direct_uri; point the application at uri. The quickstart shows the call.

#Migration workflow

Drizzle Kit generates SQL from your schema and applies it in order:

TypeScript
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './src/lib/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    // The direct connection, not the pooled one
    url: process.env.DIRECT_DATABASE_URL!,
  },
  // Optional: customize migration table name
  migrations: {
    table: '__drizzle_migrations',
    schema: 'public',
  },
})

#Generate a migration

Change the schema:

TypeScript
// src/lib/db/schema.ts
import { pgTable, text, timestamp, uuid, boolean } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name'),
  // NEW: Add a bio field
  bio: text('bio'),
  // NEW: Add email verification
  emailVerified: boolean('email_verified').default(false),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

Generate the SQL, then read it before you apply it:

Shell
# Generate migration SQL
npx drizzle-kit generate

# Output:
# [✓] Generated migration: 0001_add_bio_and_email_verified.sql
SQL
-- Generated by Drizzle Kit
ALTER TABLE "users" ADD COLUMN "bio" text;
ALTER TABLE "users" ADD COLUMN "email_verified" boolean DEFAULT false;
Shell
# Apply pending migrations
npx drizzle-kit migrate

# Output:
# [✓] Applied migration: 0001_add_bio_and_email_verified.sql
# [✓] Database schema is up to date

#Development workflow

While the schema is still moving, push it straight at a development database:

Shell
# Quick sync schema to database (development only!)
npx drizzle-kit push

# This directly applies schema changes without creating migration files
# WARNING: Don't use in production - changes aren't tracked

# Open Drizzle Studio to browse data
npx drizzle-kit studio
# Opens at https://local.drizzle.studio

Push or migrate

push changes the database without writing down what it did, so nothing records how it got that way and no other environment can follow. It is for a development database you can throw away. Every shared environment takes generated migrations.

#Roll back a migration

Drizzle Kit has no automatic rollback. A rollback is a new migration that reverses the old one, and in production that is the only rollback you want: the schema's history stays append-only, and every environment replays the same statements.

TypeScript
// Option 1: Create a new migration that reverts changes

// 1. Modify schema to remove the problematic changes
// 2. Run: npx drizzle-kit generate
// 3. This creates a migration that reverses the previous one

// Option 2: Manual SQL rollback
// Create a file: drizzle/0002_rollback_bio.sql
/*
ALTER TABLE "users" DROP COLUMN "bio";
ALTER TABLE "users" DROP COLUMN "email_verified";
*/

When the migration itself was the mistake — it backfilled wrongly, or a constraint is already rejecting writes — the faster path is a restore to a point before it ran. A restore takes the database back to a point in time inside its retention window, in place. The restore call is not served on the one API; write to hi@sylphx.com with the database name and the moment you want it back to.

A restore is not a rollback

It discards everything written after the restore point, including writes that have nothing to do with the migration. Reverse with a migration when you can; restore when the data is already wrong. Backups and restore has the full procedure.

#Zero-downtime migrations

A migration that takes a lock on a table blocks every query against it for as long as it holds. The safe shapes:

SQL
-- Safe: Adding a nullable column
ALTER TABLE "users" ADD COLUMN "bio" text;

-- Safe: Adding a column with default
ALTER TABLE "users" ADD COLUMN "status" text DEFAULT 'active';

-- Note: In PostgreSQL 11+, adding columns with defaults
-- doesn't rewrite the table, making it fast and safe
TypeScript
// WRONG: Removing a column directly breaks running code
// ALTER TABLE "users" DROP COLUMN "old_field"; // DON'T DO THIS

// RIGHT: Three-phase deployment

// Phase 1: Deploy code that stops WRITING to old_field
// (old_field still exists in DB, reads still work)

// Phase 2: Deploy code that stops READING old_field
// (code no longer depends on old_field)

// Phase 3: Drop the column
// ALTER TABLE "users" DROP COLUMN "old_field";
TypeScript
// WRONG: Direct rename breaks running code
// ALTER TABLE "users" RENAME COLUMN "name" TO "full_name"; // DON'T DO THIS

// RIGHT: Add new, migrate data, remove old

// Phase 1: Add new column, write to both
/*
ALTER TABLE "users" ADD COLUMN "full_name" text;
UPDATE "users" SET "full_name" = "name";
*/

// Deploy code that writes to BOTH columns

// Phase 2: Backfill and switch reads
// Deploy code that reads from "full_name"

// Phase 3: Stop writing to old column

// Phase 4: Drop old column
/*
ALTER TABLE "users" DROP COLUMN "name";
*/

The reason for the phases is that during a rollout, old and new code run at the same time. A schema change every version of the code can survive is one that never needs a maintenance window.

#Add an index without locking

CREATE INDEX holds a write lock on the table while it builds. CREATE INDEX CONCURRENTLY does not, at the cost of taking longer:

SQL
-- Standard index creation (BLOCKS writes during creation)
-- CREATE INDEX "posts_title_idx" ON "posts" ("title");

-- Concurrent index creation (NO blocking, but slower)
CREATE INDEX CONCURRENTLY "posts_title_idx" ON "posts" ("title");

-- Note: CONCURRENTLY can't run inside a transaction
-- Drizzle migrations run in transactions by default
-- You may need to run this manually or configure migration behavior
TypeScript
import { db } from '@/lib/db'
import { sql } from 'drizzle-orm'

// For large tables, create indexes outside of migrations
async function createSearchIndex() {
  // This runs outside a transaction
  await db.execute(sql`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS "posts_search_idx"
    ON "posts" USING gin(to_tsvector('english', "title" || ' ' || "content"))
  `)
}

// Run this in a background job or deployment script

Concurrent index caveats

A concurrent build that fails leaves an invalid index behind: writes keep paying for it and the planner will not use it. Drop it by name and build again. Check for them with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid.

#Integrate with CI/CD

Migrations belong in the deploy, before the code that needs them:

YAML
name: Deploy

on:
  push:
    branches: [main]

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

      - name: Setup Node
        uses: actions/setup-node@v4
        with:
          node-version: '20'

      - name: Install dependencies
        run: npm ci

      - name: Run migrations
        env:
          DIRECT_DATABASE_URL: ${{ secrets.DIRECT_DATABASE_URL }}
        run: npx drizzle-kit migrate

      - name: Deploy application
        run: npm run deploy

And a check on the pull request, so a schema change cannot merge without its migration:

YAML
name: Check Migrations

on:
  pull_request:
    paths:
      - 'src/lib/db/schema.ts'
      - 'drizzle/**'

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

      - name: Setup Node
        uses: actions/setup-node@v4
        with:
          node-version: '20'

      - name: Install dependencies
        run: npm ci

      - name: Check migration consistency
        run: npx drizzle-kit check

      - name: Verify schema matches migrations
        run: |
          npx drizzle-kit generate --dry-run
          # Fails if there are ungenerated schema changes

#Best practices

  • Add nullable columns and new tables first: old code has to work against the new schema, because during a rollout it will be running against it.
  • Rehearse on a database that is not production. A restore gives you one at the current data, and lets you throw the rehearsal away.
  • For a breaking change: add the new thing, migrate the data, move the code, then remove the old thing — four deploys, each of which can be rolled back.
  • One logical change per migration. It is easier to review, and when it fails it is obvious which part failed.
  • Once a migration has run anywhere shared, it is history: change it with a new migration, never by editing the file.
  • Apply the migration before the code that depends on it, and give the database a moment: the old code must survive the new schema, and it does only if the migration is backward compatible.

#Checklist

TypeScript
// Pre-deployment checklist:
// [ ] Migration tested on database branch
// [ ] Rollback plan documented
// [ ] Backup/snapshot created
// [ ] Off-peak deployment time scheduled (for large tables)
// [ ] Monitoring alerts configured
// [ ] Team notified of deployment

// Post-deployment verification:
// [ ] Migration completed without errors
// [ ] Application health checks passing
// [ ] No increase in error rates
// [ ] Query performance unchanged
// [ ] Rollback plan tested (optional)

Was this page helpful?

Tell us what is missing, wrong, or unclear — this opens a real inbox.

Send feedback

Platform links