Database Migrations

Tool: Drizzle Kit
Command: bun run db:migrate

Creating a Migration

1. Update Schema

Edit or create a file in src/lib/server/db/schema/:

// src/lib/server/db/schema/projects.ts
export const projects = pgTable('projects', {
  id: uuid('id').defaultRandom().primaryKey(),
  // ... add new column:
  isPublic: boolean('is_public').notNull().default(false)
})

2. Generate Migration File

bun run db:generate

This creates a file in drizzle/ like 0003_add_is_public.sql:

ALTER TABLE "projects" ADD COLUMN "is_public" boolean NOT NULL DEFAULT false;

3. Review the SQL

Always review the generated SQL before applying. Check for:

• Accidental table drops
• Missing DEFAULT values on NOT NULL columns
• Large table operations that could lock production

4. Apply Migration

# Local
bun run db:migrate

# Production (via CI or manually)
DATABASE_URL=postgresql://... bun run db:migrate

Scripts in package.json

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:studio": "drizzle-kit studio",
    "db:push": "drizzle-kit push"
  }
}

WARNING

db:push directly pushes schema changes without creating migration files. Never use in production. Use only for rapid local prototyping.

Drizzle Studio

bun run db:studio

Opens a GUI at https://local.drizzle.studio for browsing and editing database tables.

Rolling Back

Drizzle does not have built-in rollback. Options:

1. Write a reverse migration manually
2. Restore from Supabase backup (Point-in-Time Recovery)
3. Use ALTER TABLE ... DROP COLUMN manually for simple changes