Drizzle: Migrations & Database Management

drizzle.config.ts

// drizzle.config.ts (project root)
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './src/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

drizzle-kit Commands

# Generate SQL migration from schema changes
npx drizzle-kit generate
# Creates timestamped SQL file in ./src/db/migrations/

# Apply migrations to database
npx drizzle-kit migrate

# Push schema directly (dev only — skips migration files)
npx drizzle-kit push

# Open Drizzle Studio (visual DB browser in browser)
npx drizzle-kit studio

# Check for drift between schema and DB
npx drizzle-kit check

# Drop all tables and push fresh schema (dev only!)
npx drizzle-kit push --force

Migration Workflow

# Standard development workflow:
# 1. Edit src/db/schema.ts (add table, column, index)
# 2. Generate migration
npx drizzle-kit generate

# 3. Review the generated SQL in src/db/migrations/
cat src/db/migrations/0005_add_nickname_column.sql

# 4. Apply to local dev DB
npx drizzle-kit migrate

# 5. Commit both schema.ts and migration SQL file
git add src/db/schema.ts src/db/migrations/
git commit -m "feat: add nickname column to users"

# Production deployment:
# Run migrations as part of deploy script
# Never use 'push' in production — always use migrations

Running Migrations Programmatically

// src/db/migrate.ts — run via: npx tsx src/db/migrate.ts
import { drizzle } from 'drizzle-orm/neon-http';
import { migrate } from 'drizzle-orm/neon-http/migrator';
import { neon } from '@neondatabase/serverless';

async function runMigrations() {
  const sql = neon(process.env.DATABASE_URL!);
  const db = drizzle(sql);

  console.log('Running migrations...');
  await migrate(db, { migrationsFolder: './src/db/migrations' });
  console.log('Migrations completed');
}

runMigrations().catch(console.error);

// package.json scripts
{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "npx tsx src/db/migrate.ts",
    "db:migrate:prod": "NODE_ENV=production npx tsx src/db/migrate.ts",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio"
  }
}

Seeding

// src/db/seed.ts
import { db } from './client';
import { users, folders, pages } from './schema';

async function seed() {
  console.log('Seeding database...');

  // Clean existing data (dev only!)
  await db.delete(pages);
  await db.delete(folders);
  await db.delete(users);

  // Insert seed data
  const [user] = await db.insert(users).values({
    clerkId: 'clerk_test_user',
    email: 'test@example.com',
    name: 'Test User',
    plan: 'pro',
  }).returning();

  const [reactFolder] = await db.insert(folders).values({
    userId: user.id,
    name: 'React',
    slug: 'react',
  }).returning();

  await db.insert(pages).values([
    { folderId: reactFolder.id, title: 'Hooks', slug: 'hooks', status: 'proficient' },
    { folderId: reactFolder.id, title: 'Context API', slug: 'context', status: 'to_learn' },
  ]);

  console.log('Seeding complete');
}

seed().catch(console.error);