Acme Inc.

Database migrations track and version schema changes. This project uses drizzle-kit for migration management.

Migration Workflows

There are two workflows depending on your environment:

Development Workflow

Use db:push for rapid iteration:

npm run db:push

What it does:

Directly modifies the database schema to match your code
No migration files created
Fast for development and experimentation
Schema changes are immediate

When to use:

Local development
Rapid prototyping
Schema experimentation
Non-production databases

Production Workflow

Use db:generate and db:migrate for versioned migrations:

# 1. Generate migration SQL
npm run db:generate

# 2. Review generated SQL in src/db/migrations/

# 3. Apply migration
npm run db:migrate

Always use migrations for production:

Creates versioned migration files
Trackable in version control
Reversible (with manual rollback)
Auditable history

When to use:

Production deployments
Staging environments
Team collaboration
When schema history matters

Commands Reference

Command	Purpose	Environment
`npm run db:push`	Push schema directly to DB	Development
`npm run db:generate`	Generate migration SQL file	Production
`npm run db:migrate`	Apply pending migrations	Production
`npm run db:studio`	Open Drizzle Studio GUI	Any
`npm run db:check`	Validate migration files	Any

Migration Files

Generated migrations are stored in src/db/migrations/:

src/db/migrations/
├── 0000_initial_schema.sql
├── 0001_add_api_keys.sql
├── 0002_add_organizations.sql
└── meta/
    └── _journal.json

Migration File Structure

Each migration file contains:

-- Migration: 0001_add_api_keys.sql

-- Create table
CREATE TABLE "apikeys" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "name" TEXT,
  "key" TEXT NOT NULL,
  "user_id" UUID NOT NULL,
  "enabled" BOOLEAN DEFAULT TRUE,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  "updated_at" TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes
CREATE INDEX "api_keys_user_id_idx" ON "apikeys"("user_id");
CREATE INDEX "api_keys_key_idx" ON "apikeys"("key");

-- Add foreign key
ALTER TABLE "apikeys" 
ADD CONSTRAINT "apikeys_user_id_fkey" 
FOREIGN KEY ("user_id") 
REFERENCES "users"("id") 
ON DELETE CASCADE;

Step-by-Step: Creating a Migration

1. Modify Schema

Edit your table definition in src/db/tables/:

// src/db/tables/posts.ts
export const posts = createTable("posts", {
  ...commonColumns,
  title: text().notNull(),
  content: text(),
  // NEW: Add published field
  published: boolean().default(false),
});

2. Generate Migration

npm run db:generate

You'll be prompted for a migration name:

✔ Migration name: add_published_to_posts

This creates a new SQL file in src/db/migrations/.

3. Review Migration

Open the generated SQL file and verify it matches your intent:

-- 0003_add_published_to_posts.sql
ALTER TABLE "posts" ADD COLUMN "published" BOOLEAN DEFAULT FALSE;

Always review generated SQL before applying to production!

4. Apply Migration

npm run db:migrate

The migration is applied and tracked in the _drizzle_migrations table.

Drizzle Studio

Open Drizzle Studio for a GUI to browse and edit data:

npm run db:studio

Opens at https://local.drizzle.studio/ with:

Table browser
Data editor
Schema viewer
Query interface

Studio connects to your local database. Perfect for development!

Migration Best Practices

Do's ✅

Use migrations for production
```
npm run db:generate
npm run db:migrate
```
Review SQL before applying
- Check for data loss
- Verify column types
- Ensure indexes are correct

Commit migrations to version control

git add src/db/migrations/
git commit -m "Add published field to posts"

Test migrations in staging first
- Apply to staging environment
- Verify application works
- Then deploy to production

Add indexes for performance

export const posts = createTable(
  "posts",
  { /* columns */ },
  (t) => [
    index("posts_user_id_idx").on(t.userId),
    index("posts_published_idx").on(t.published),
  ],
);

Use transactions for complex changes

await dbDrizzle.transaction(async (tx) => {
  // Multiple related changes
});

Don'ts ❌

Don't use db:push in production
- No migration history
- Can't rollback
- Not auditable
Don't edit existing migrations
- Once applied, they're immutable
- Create a new migration to fix issues
Don't skip migration review
- Always verify generated SQL
- Check for unintended changes
Don't delete migration files
- History is important
- Needed for fresh installs
Don't make breaking changes without planning
- Coordinate with team
- Consider backward compatibility
- Plan rollback strategy

Common Migration Scenarios

Adding a Column

// Add to schema
export const users = createTable("users", {
  ...commonColumns,
  bio: text(), // NEW
});

npm run db:generate
# Creates: ALTER TABLE "users" ADD COLUMN "bio" TEXT;

Renaming a Column

Drizzle doesn't auto-detect renames. You'll need to manually edit the migration.

// Before
oldField: text()

// After
newField: text()

Manual migration:

-- Don't let Drizzle DROP and ADD
-- Instead, manually write:
ALTER TABLE "my_table" RENAME COLUMN "old_field" TO "new_field";

Dropping a Column

// Remove from schema
export const users = createTable("users", {
  ...commonColumns,
  // bio: text(), // REMOVED
});

npm run db:generate
# Creates: ALTER TABLE "users" DROP COLUMN "bio";

Adding an Index

export const posts = createTable(
  "posts",
  { /* columns */ },
  (t) => [
    index("posts_status_idx").on(t.status), // NEW
  ],
);

Adding a Foreign Key

export const posts = createTable("posts", {
  ...commonColumns,
  userId: uuid()
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }), // NEW FK
});

Rollback Strategies

Drizzle doesn't have automatic rollbacks. To rollback:

Option 1: Manual Rollback

Write a reverse migration manually:

-- If you added a column:
ALTER TABLE "posts" DROP COLUMN "published";

-- If you dropped a column (data lost!):
ALTER TABLE "posts" ADD COLUMN "old_field" TEXT;

Option 2: Database Backup

Take backups before migrations:

# PostgreSQL backup
pg_dump -U postgres -d mydb > backup_$(date +%Y%m%d).sql

# Apply migration
npm run db:migrate

# If needed, restore:
psql -U postgres -d mydb < backup_20260114.sql

Option 3: Version Control Revert

# Revert schema changes
git revert <commit-hash>

# Generate new migration
npm run db:generate

# Apply reverse migration
npm run db:migrate

CI/CD Integration

GitHub Actions Example

# .github/workflows/migrate.yml
name: Database Migration

on:
  push:
    branches: [main]

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      
      - name: Setup Node
        uses: actions/setup-node@v3
        with:
          node-version: '20'
      
      - name: Install dependencies
        run: npm ci
      
      - name: Run migrations
        run: npm run db:migrate
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

Troubleshooting

"Migration failed"

Check:

Database connection
Migration SQL syntax
Conflicting constraints
Check logs: drizzle-orm logs

"Schema drift detected"

Your database schema differs from code:

# Check current state
npm run db:check

# Reset (development only!)
npm run db:push

"Duplicate column/table"

Migration already applied manually. Either:

Skip the migration (edit _drizzle_migrations)
Or reset and reapply all migrations

Next Steps

Schema Definition - Define tables
Operations - CRUD operations
Caching - Cache invalidation
New Table Template - Create tables with migrations

Database migrations track and version schema changes. This project uses drizzle-kit for migration management.

Migration Workflows

There are two workflows depending on your environment:

Development Workflow

Use db:push for rapid iteration:

npm run db:push

What it does:

Directly modifies the database schema to match your code
No migration files created
Fast for development and experimentation
Schema changes are immediate

When to use:

Local development
Rapid prototyping
Schema experimentation
Non-production databases

Production Workflow

Use db:generate and db:migrate for versioned migrations:

# 1. Generate migration SQL
npm run db:generate

# 2. Review generated SQL in src/db/migrations/

# 3. Apply migration
npm run db:migrate

Always use migrations for production:

Creates versioned migration files
Trackable in version control
Reversible (with manual rollback)
Auditable history

When to use:

Production deployments
Staging environments
Team collaboration
When schema history matters

Commands Reference

Command	Purpose	Environment
`npm run db:push`	Push schema directly to DB	Development
`npm run db:generate`	Generate migration SQL file	Production
`npm run db:migrate`	Apply pending migrations	Production
`npm run db:studio`	Open Drizzle Studio GUI	Any
`npm run db:check`	Validate migration files	Any

Migration Files

Generated migrations are stored in src/db/migrations/:

src/db/migrations/
├── 0000_initial_schema.sql
├── 0001_add_api_keys.sql
├── 0002_add_organizations.sql
└── meta/
    └── _journal.json

Migration File Structure

Each migration file contains:

-- Migration: 0001_add_api_keys.sql

-- Create table
CREATE TABLE "apikeys" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "name" TEXT,
  "key" TEXT NOT NULL,
  "user_id" UUID NOT NULL,
  "enabled" BOOLEAN DEFAULT TRUE,
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  "updated_at" TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes
CREATE INDEX "api_keys_user_id_idx" ON "apikeys"("user_id");
CREATE INDEX "api_keys_key_idx" ON "apikeys"("key");

-- Add foreign key
ALTER TABLE "apikeys" 
ADD CONSTRAINT "apikeys_user_id_fkey" 
FOREIGN KEY ("user_id") 
REFERENCES "users"("id") 
ON DELETE CASCADE;

Step-by-Step: Creating a Migration

1. Modify Schema

Edit your table definition in src/db/tables/:

// src/db/tables/posts.ts
export const posts = createTable("posts", {
  ...commonColumns,
  title: text().notNull(),
  content: text(),
  // NEW: Add published field
  published: boolean().default(false),
});

2. Generate Migration

npm run db:generate

You'll be prompted for a migration name:

✔ Migration name: add_published_to_posts

This creates a new SQL file in src/db/migrations/.

3. Review Migration

Open the generated SQL file and verify it matches your intent:

-- 0003_add_published_to_posts.sql
ALTER TABLE "posts" ADD COLUMN "published" BOOLEAN DEFAULT FALSE;

Always review generated SQL before applying to production!

4. Apply Migration

npm run db:migrate

The migration is applied and tracked in the _drizzle_migrations table.

Drizzle Studio

Open Drizzle Studio for a GUI to browse and edit data:

npm run db:studio

Opens at https://local.drizzle.studio/ with:

Table browser
Data editor
Schema viewer
Query interface

Studio connects to your local database. Perfect for development!

Migration Best Practices

Do's ✅

Use migrations for production
```
npm run db:generate
npm run db:migrate
```
Review SQL before applying
- Check for data loss
- Verify column types
- Ensure indexes are correct

Commit migrations to version control

git add src/db/migrations/
git commit -m "Add published field to posts"

Test migrations in staging first
- Apply to staging environment
- Verify application works
- Then deploy to production

Add indexes for performance

export const posts = createTable(
  "posts",
  { /* columns */ },
  (t) => [
    index("posts_user_id_idx").on(t.userId),
    index("posts_published_idx").on(t.published),
  ],
);

Use transactions for complex changes

await dbDrizzle.transaction(async (tx) => {
  // Multiple related changes
});

Don'ts ❌

Don't use db:push in production
- No migration history
- Can't rollback
- Not auditable
Don't edit existing migrations
- Once applied, they're immutable
- Create a new migration to fix issues
Don't skip migration review
- Always verify generated SQL
- Check for unintended changes
Don't delete migration files
- History is important
- Needed for fresh installs
Don't make breaking changes without planning
- Coordinate with team
- Consider backward compatibility
- Plan rollback strategy

Common Migration Scenarios

Adding a Column

// Add to schema
export const users = createTable("users", {
  ...commonColumns,
  bio: text(), // NEW
});

npm run db:generate
# Creates: ALTER TABLE "users" ADD COLUMN "bio" TEXT;

Renaming a Column

Drizzle doesn't auto-detect renames. You'll need to manually edit the migration.

// Before
oldField: text()

// After
newField: text()

Manual migration:

-- Don't let Drizzle DROP and ADD
-- Instead, manually write:
ALTER TABLE "my_table" RENAME COLUMN "old_field" TO "new_field";

Dropping a Column

// Remove from schema
export const users = createTable("users", {
  ...commonColumns,
  // bio: text(), // REMOVED
});

npm run db:generate
# Creates: ALTER TABLE "users" DROP COLUMN "bio";

Adding an Index

export const posts = createTable(
  "posts",
  { /* columns */ },
  (t) => [
    index("posts_status_idx").on(t.status), // NEW
  ],
);

Adding a Foreign Key

export const posts = createTable("posts", {
  ...commonColumns,
  userId: uuid()
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }), // NEW FK
});

Rollback Strategies

Drizzle doesn't have automatic rollbacks. To rollback:

Option 1: Manual Rollback

Write a reverse migration manually:

-- If you added a column:
ALTER TABLE "posts" DROP COLUMN "published";

-- If you dropped a column (data lost!):
ALTER TABLE "posts" ADD COLUMN "old_field" TEXT;

Option 2: Database Backup

Take backups before migrations:

# PostgreSQL backup
pg_dump -U postgres -d mydb > backup_$(date +%Y%m%d).sql

# Apply migration
npm run db:migrate

# If needed, restore:
psql -U postgres -d mydb < backup_20260114.sql

Option 3: Version Control Revert

# Revert schema changes
git revert <commit-hash>

# Generate new migration
npm run db:generate

# Apply reverse migration
npm run db:migrate

CI/CD Integration

GitHub Actions Example

# .github/workflows/migrate.yml
name: Database Migration

on:
  push:
    branches: [main]

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      
      - name: Setup Node
        uses: actions/setup-node@v3
        with:
          node-version: '20'
      
      - name: Install dependencies
        run: npm ci
      
      - name: Run migrations
        run: npm run db:migrate
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

Troubleshooting

"Migration failed"

Check:

Database connection
Migration SQL syntax
Conflicting constraints
Check logs: drizzle-orm logs

"Schema drift detected"

Your database schema differs from code:

# Check current state
npm run db:check

# Reset (development only!)
npm run db:push

"Duplicate column/table"

Migration already applied manually. Either:

Skip the migration (edit _drizzle_migrations)
Or reset and reapply all migrations

Next Steps

Schema Definition - Define tables
Operations - CRUD operations
Caching - Cache invalidation
New Table Template - Create tables with migrations

Migrations

On this page

Migrations

On this page