Database Migrations

ShipFree uses Drizzle ORM for database management and migrations. This guide covers how to generate, run, and manage database migrations in development and production.

Overview

Drizzle migrations are:

Type-safe: Generated from your TypeScript schema
Automatic: SQL is generated for you
Version-controlled: Migration files are committed to Git
Reversible: Can be rolled back if needed

Migration Commands

ShipFree provides three migration commands in package.json:

package.json

{
  "scripts": {
    "generate-migration": "drizzle-kit generate",
    "migrate:local": "bun run ./scripts/migrate.ts",
    "migrate:prod": "cross-env NODE_ENV=production bun run ./scripts/migrate.ts"
  }
}

Command Breakdown

Command	Purpose	Database Used
`generate-migration`	Generate SQL migration files from schema changes	None (just generates files)
`migrate:local`	Run migrations on local database	`DATABASE_URL`
`migrate:prod`	Run migrations on production database	`PROD_DATABASE_URL`

Drizzle Configuration

Migrations are configured in drizzle.config.ts:

drizzle.config.ts

import type { Config } from 'drizzle-kit'

export default {
  schema: './src/database/schema.ts',
  out: './migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config

Configuration details:

schema: Location of your database schema definition
out: Directory where migration files are generated
dialect: Database type (PostgreSQL)
dbCredentials.url: Database connection string

Migration Workflow

1. Update Database Schema

Edit your schema in src/database/schema.ts:

src/database/schema.ts

import { pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

// Add a new table
export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: uuid('author_id').references(() => users.id),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

2. Generate Migration

Generate SQL migration files from your schema changes:

bun run generate-migration

Output:

[✓] Generating migrations
[✓] Migration generated: migrations/0001_new_posts_table.sql

This creates a new file in the migrations/ directory with the SQL needed to apply your schema changes.

3. Review Generated Migration

Check the generated SQL file:

migrations/0001_new_posts_table.sql

CREATE TABLE IF NOT EXISTS "posts" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "title" text NOT NULL,
  "content" text,
  "author_id" uuid,
  "created_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "posts" ADD CONSTRAINT "posts_author_id_users_id_fk" 
  FOREIGN KEY ("author_id") REFERENCES "users"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

4. Run Migration Locally

Apply migrations to your local database:

bun run migrate:local

Output:

Connecting to: postgres://postgres:****@localhost:5432/shipfree
Running migrations....
Migrations completed!

Migration Script

The migration script is located at scripts/migrate.ts:

scripts/migrate.ts

import { config } from 'dotenv'
import { drizzle } from 'drizzle-orm/postgres-js'
import { migrate } from 'drizzle-orm/postgres-js/migrator'
import process from 'node:process'
import postgres from 'postgres'

config()

const isProd = process.env.NODE_ENV === 'production'
const connectionString = isProd ? process.env.PROD_DATABASE_URL : process.env.DATABASE_URL

if (!connectionString) {
  throw new Error(`${isProd ? 'PROD_DATABASE_URL' : 'DATABASE_URL'} is not set`)
}

console.log('Connecting to:', connectionString.replace(/:[^:@]+@/, ':****@')) // Log sanitized connection string

const migrationClient = postgres(connectionString, { max: 1 })

async function main() {
  console.log('Running migrations....')

  try {
    const db = drizzle(migrationClient)
    await migrate(db, { migrationsFolder: './migrations' })
    console.log('Migrations completed!')
  } catch (error) {
    console.error('Migrations failed:', error)
  } finally {
    await migrationClient.end()
  }
}

main().catch((err) => {
  console.error('Unexpected error:', err)
  process.exit(1)
})

How it works:

Loads environment variables from .env
Selects database URL based on NODE_ENV
Connects to the database
Runs all pending migrations from ./migrations folder
Closes the connection

Environment Variables

Local Development

Set in .env:

.env

DATABASE_URL=postgres://postgres:postgres@localhost:5432/shipfree

Production

Set in .env or environment:

.env

PROD_DATABASE_URL=postgres://user:password@host.neon.tech/database

Important: Never commit production credentials to Git!

Production Migrations

Option 1: Run Locally (Recommended)

Run migrations from your local machine against the production database:

# Set production database URL
export PROD_DATABASE_URL="postgres://user:password@host:5432/database"

# Run migrations
bun run migrate:prod

Output:

Connecting to: postgres://user:****@host.neon.tech:5432/database
Running migrations....
Migrations completed!

Option 2: CI/CD Pipeline

Run migrations in your CI/CD workflow:

.github/workflows/deploy.yml

name: Deploy

on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      
      - name: Setup Bun
        uses: oven-sh/setup-bun@v1
      
      - name: Install dependencies
        run: bun install
      
      - name: Run migrations
        env:
          PROD_DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }}
        run: bun run migrate:prod
      
      - name: Deploy to Vercel
        # ... deployment steps

Option 3: Vercel Post-Deploy Hook

Run migrations after Vercel deployment using a deploy hook:

Add a migrate script to package.json:

"migrate": "bun run ./scripts/migrate.ts"

Set up environment variables in Vercel:

PROD_DATABASE_URL=postgres://...

Create an API route for migrations (protected):

src/app/api/migrate/route.ts

import { NextResponse } from 'next/server'

export async function POST(request: Request) {
  const authHeader = request.headers.get('authorization')
  
  if (authHeader !== `Bearer ${process.env.MIGRATION_SECRET}`) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
  }

  // Run migrations
  const { exec } = await import('child_process')
  const { promisify } = await import('util')
  const execAsync = promisify(exec)

  try {
    await execAsync('bun run migrate:prod')
    return NextResponse.json({ success: true })
  } catch (error) {
    return NextResponse.json({ error: 'Migration failed' }, { status: 500 })
  }
}

Best Practices

1. Always Review Generated Migrations

Before running migrations, review the generated SQL:

# Generate migration
bun run generate-migration

# Review the file in migrations/
cat migrations/0001_*.sql

# Run if it looks correct
bun run migrate:local

2. Test Migrations Locally First

Always test on local database before production:

# 1. Generate migration
bun run generate-migration

# 2. Test locally
bun run migrate:local

# 3. Verify application works
bun dev

# 4. Run on production
bun run migrate:prod

3. Backup Production Database

Before running production migrations:

# For Neon
neon backup create

# For Supabase
# Use Supabase Dashboard → Database → Backups

# For self-hosted PostgreSQL
pg_dump -h host -U user -d database > backup.sql

4. Run Migrations in Maintenance Window

For large schema changes:

Schedule a maintenance window
Put application in maintenance mode
Run migrations
Verify data integrity
Restore application

5. Use Transactions

Drizzle automatically wraps migrations in transactions, so failed migrations are rolled back automatically.

6. Version Control Migrations

Always commit migration files to Git:

git add migrations/
git commit -m "Add posts table migration"

Troubleshooting

Migration Fails with “DATABASE_URL is not set”

Solution: Set the appropriate environment variable:

# For local
export DATABASE_URL="postgres://..."

# For production
export PROD_DATABASE_URL="postgres://..."

Migration Fails with Connection Error

Check database connectivity:

# Test connection using psql
psql "postgres://user:password@host:5432/database"

Common issues:

Incorrect connection string
Database server not running
Firewall blocking connection
SSL/TLS requirements (add ?sslmode=require to connection string)

Migration Already Applied

Drizzle tracks applied migrations in the database. If you try to run the same migration twice, it will skip it:

Migrations completed! (0 new migrations applied)

Rollback a Migration

Drizzle doesn’t have built-in rollback. To undo a migration:

Option A: Write a new migration to reverse changes
Option B: Restore from database backup
Option C: Manually write SQL to undo changes

Advanced Usage

Custom Migration Files

You can create custom SQL migrations:

# Create migrations directory
mkdir -p migrations

# Add custom SQL file
touch migrations/0010_custom_indexes.sql

migrations/0010_custom_indexes.sql

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_posts_author_id ON posts(author_id);

Migration Naming

Drizzle generates migration names based on:

Sequence number (0001, 0002, etc.)
Description from schema changes

Example: 0001_create_users_table.sql

​Overview

​Migration Commands

​Command Breakdown

​Drizzle Configuration

​Migration Workflow

​1. Update Database Schema

​2. Generate Migration

​3. Review Generated Migration

​4. Run Migration Locally

​Migration Script

​Environment Variables

​Local Development

​Production

​Production Migrations

​Option 1: Run Locally (Recommended)

​Option 2: CI/CD Pipeline

​Option 3: Vercel Post-Deploy Hook

​Best Practices

​1. Always Review Generated Migrations

​2. Test Migrations Locally First

​3. Backup Production Database

​4. Run Migrations in Maintenance Window

​5. Use Transactions

​6. Version Control Migrations

​Troubleshooting

​Migration Fails with “DATABASE_URL is not set”

​Migration Fails with Connection Error

​Migration Already Applied

​Rollback a Migration

​Advanced Usage

​Custom Migration Files

​Migration Naming

​Next Steps

​Resources

Overview

Migration Commands

Command Breakdown

Drizzle Configuration

Migration Workflow

1. Update Database Schema

2. Generate Migration

3. Review Generated Migration

4. Run Migration Locally

Migration Script

Environment Variables

Local Development

Production

Production Migrations

Option 1: Run Locally (Recommended)

Option 2: CI/CD Pipeline

Option 3: Vercel Post-Deploy Hook

Best Practices

1. Always Review Generated Migrations

2. Test Migrations Locally First

3. Backup Production Database

4. Run Migrations in Maintenance Window

5. Use Transactions

6. Version Control Migrations

Troubleshooting

Migration Fails with “DATABASE_URL is not set”

Migration Fails with Connection Error

Migration Already Applied

Rollback a Migration

Advanced Usage

Custom Migration Files

Migration Naming

Next Steps

Resources