Migrations

Database migrations allow you to version control schema changes and safely apply them across environments. CharleOS uses Drizzle Kit for automatic migration generation.

Migration Files

Migrations are stored in the drizzle/ directory:

drizzle/
├── 0000_initial_schema.sql
├── 0001_add_users.sql
├── 0002_add_tasks.sql
├── ... (47 migrations total)
└── meta/
    ├── _journal.json          # Migration history
    └── 0047_snapshot.json     # Latest schema snapshot

Each migration file contains raw SQL that transforms the database schema.

Development Workflow

Local Development (Fast Iteration)

For rapid development, use db:push to sync schema directly:

Edit Schema

Modify lib/db/schema.ts to add/change tables or columns

Push Changes

npm run db:push

Instantly applies changes to your local dev database (no migration file created)

Test Changes

npm run db:studio

Verify schema changes in Drizzle Studio

db:push is perfect for local development but doesn’t create migration files. It directly syncs your schema to the database.

Production Workflow (Safe Deployment)

For production, always generate migration files:

Edit Schema

Make your changes to lib/db/schema.ts

Generate Migration

npm run db:generate

Creates a new SQL file in drizzle/XXXX_name.sql

Review Generated SQL

cat drizzle/0048_*.sql

Always review the SQL to ensure it does what you expect

Test Locally

Apply the migration to your local database:

npm run db:migrate

Commit Changes

git add lib/db/schema.ts drizzle/
git commit -m "feat: add priority column to tasks"

Deploy

Push to GitHub. Migrations run automatically on Vercel deployment.

Common Migration Examples

Adding a Column

Schema change:

// lib/db/schema.ts
export const task = pgTable("task", {
  // ... existing columns
  priority: integer("priority").notNull().default(3), // ← New column
});

Generated migration:

-- drizzle/0048_add_task_priority.sql
ALTER TABLE "task" ADD COLUMN "priority" integer DEFAULT 3 NOT NULL;

Adding a New Table

Schema change:

// lib/db/schema.ts
export const tag = pgTable("tag", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => createId()),
  name: text("name").notNull(),
  color: text("color").notNull().default("#gray"),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
});

Generated migration:

-- drizzle/0048_add_tags.sql
CREATE TABLE IF NOT EXISTS "tag" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text NOT NULL,
  "color" text DEFAULT '#gray' NOT NULL,
  "createdAt" timestamp DEFAULT now() NOT NULL
);

Adding a Foreign Key

Schema change:

export const taskTag = pgTable("task_tag", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => createId()),
  taskId: text("taskId")
    .notNull()
    .references(() => task.id, { onDelete: "cascade" }),
  tagId: text("tagId")
    .notNull()
    .references(() => tag.id, { onDelete: "cascade" }),
});

Generated migration:

CREATE TABLE IF NOT EXISTS "task_tag" (
  "id" text PRIMARY KEY NOT NULL,
  "taskId" text NOT NULL,
  "tagId" text NOT NULL
);

ALTER TABLE "task_tag" ADD CONSTRAINT "task_tag_taskId_task_id_fk" 
  FOREIGN KEY ("taskId") REFERENCES "task"("id") ON DELETE cascade;

ALTER TABLE "task_tag" ADD CONSTRAINT "task_tag_tagId_tag_id_fk" 
  FOREIGN KEY ("tagId") REFERENCES "tag"("id") ON DELETE cascade;

Renaming a Column

Schema change:

// lib/db/schema.ts
export const task = pgTable("task", {
  // priority: integer("priority"), // Old name
  importanceLevel: integer("importanceLevel"), // ← New name
});

Generated migration:

-- drizzle/0048_rename_priority.sql
ALTER TABLE "task" RENAME COLUMN "priority" TO "importanceLevel";

Dropping a Column

Schema change:

// Remove column from schema.ts
export const task = pgTable("task", {
  // legacyField: text("legacyField"), // ← Removed
});

Generated migration:

-- drizzle/0048_remove_legacy_field.sql
ALTER TABLE "task" DROP COLUMN "legacyField";

Destructive changes like dropping columns or tables are irreversible. Always back up data before deploying destructive migrations.

Migration Commands

Command	Purpose	When to Use
`npm run db:push`	Push schema directly to DB	Local development (no migration file)
`npm run db:generate`	Generate migration file	Before deploying to production
`npm run db:migrate`	Apply pending migrations	After generating migrations locally
`npm run db:sync`	Sync migrations to Neon	After adding migration files manually
`npm run db:studio`	Open Drizzle Studio	Browse/verify schema changes

Handling Destructive Changes Safely

Approach 1: Multi-Step Migration

Instead of dropping a column directly, use a multi-step approach: Step 1: Make column nullable

ALTER TABLE "task" ALTER COLUMN "legacyField" DROP NOT NULL;

Step 2: Migrate data to new column

UPDATE "task" SET "newField" = "legacyField";

Step 3: Drop old column (separate deployment)

ALTER TABLE "task" DROP COLUMN "legacyField";

Approach 2: Feature Flags

Use feature flags to gradually roll out schema changes:

Add new column (deploy)
Update app to use new column (behind flag)
Enable flag and migrate data
Remove old column (new deployment)

Migration Conflicts

Resolving Conflicts

If multiple developers create migrations simultaneously:

Pull Latest Changes

git pull origin main

Check for Conflicts

Look for conflicts in drizzle/meta/_journal.json

Resolve Journal Conflicts

The journal tracks all migrations. Manually merge the arrays:

{
  "entries": [
    { "idx": 0, "tag": "0000_initial" },
    { "idx": 1, "tag": "0001_your_migration" },
    { "idx": 2, "tag": "0002_their_migration" }  // ← Add this
  ]
}

Regenerate Your Migration

npm run db:generate

This creates a new migration that accounts for both changes

Test Locally

Apply both migrations locally to ensure they work together

Rolling Back Migrations

Drizzle doesn’t have built-in rollback. Options:

Option 1: Create Reverse Migration

Manually write SQL to undo the change:

-- Original migration: 0048_add_priority.sql
ALTER TABLE "task" ADD COLUMN "priority" integer DEFAULT 3;

-- Rollback migration: 0049_remove_priority.sql
ALTER TABLE "task" DROP COLUMN "priority";

Option 2: Restore from Backup

For critical failures, restore from Neon backup:

Go to Neon Console → Your Branch → Backups
Select point-in-time before migration
Restore to a new branch
Test and switch production

Option 3: Fix Forward

Create a new migration that fixes the problem:

-- Fix incorrect default value
ALTER TABLE "task" ALTER COLUMN "priority" SET DEFAULT 3;

Seeding Data

Use seed scripts for initial configuration data:

# Seed all development data
npm run db:seed

# Custom seed (create in scripts/)
npx tsx scripts/seed-custom-data.ts

Example seed script:

// scripts/seed-tags.ts
import { db } from "@/lib/db";
import { tag } from "@/lib/db/schema";

async function seedTags() {
  await db.insert(tag).values([
    { name: "Bug", color: "#red" },
    { name: "Feature", color: "#blue" },
    { name: "Enhancement", color: "#green" },
  ]);
}

seedTags().then(() => console.log("Tags seeded!"));

Best Practices

One Logical Change Per Migration

Keep migrations focused and atomic:✅ Good:

0048_add_task_tags.sql - Adds tags table and task_tag junction

❌ Bad:

0048_massive_refactor.sql - Adds tags, renames 5 columns, drops 3 tables

Always Review Generated SQL

Don’t blindly trust auto-generated migrations:

# Review before committing
cat drizzle/0048_*.sql

# Check for:
# - Destructive operations (DROP, TRUNCATE)
# - Missing indexes for foreign keys
# - Incorrect default values

Test Migrations Locally First

Always apply migrations to local dev database before deploying:

# Apply migration locally
npm run db:migrate

# Verify in Drizzle Studio
npm run db:studio

# Test app functionality
npm run dev

Never Edit Applied Migrations

Once a migration is deployed to production:

Never edit the SQL file
Create a new migration to fix issues
Editing applied migrations breaks migration history

Add Indexes for Foreign Keys

Drizzle doesn’t auto-create indexes on foreign keys. Add manually:

export const task = pgTable("task", {
  clientId: text("clientId").references(() => client.id),
}, (table) => ({
  clientIdIdx: index("task_client_id_idx").on(table.clientId), // ← Add index
}));

Document Breaking Changes

In commit messages and PR descriptions:

feat: add task priority system

BREAKING CHANGE: Adds required `priority` column to tasks table.
Existing tasks will default to priority 3 (medium).

Migration: 0048_add_task_priority.sql

Troubleshooting

Migration Failed to Apply

Error: “relation ‘task’ does not exist” Fix:

Check if you’re on the correct database branch
Verify all previous migrations are applied
Check drizzle/meta/_journal.json for missing entries

Schema Out of Sync

Error: “Schema drift detected” Fix:

# Reset local database to match migrations
npm run db:push

# Or regenerate migrations from current schema
npm run db:generate

Duplicate Migration

Error: “Migration 0048 already exists” Fix:

# Delete the duplicate
rm drizzle/0048_*.sql

# Regenerate with next number
npm run db:generate

CI/CD Integration

Migrations run automatically on Vercel deployments via postinstall script:

// package.json
{
  "scripts": {
    "postinstall": "drizzle-kit migrate"
  }
}

This ensures migrations are applied before the app starts.

Database Overview

Database architecture and tools

Schema

Complete schema reference

Drizzle ORM

Query builder syntax

Deployment

How migrations run in production

Getting Started

Database

Authentication

Testing

Deployment

Migration Files

Development Workflow

Local Development (Fast Iteration)

Production Workflow (Safe Deployment)

Common Migration Examples

Adding a Column

Adding a New Table

Adding a Foreign Key

Renaming a Column

Dropping a Column

Migration Commands

Handling Destructive Changes Safely

Approach 1: Multi-Step Migration

Approach 2: Feature Flags

Migration Conflicts

Resolving Conflicts

Rolling Back Migrations

Option 1: Create Reverse Migration

Option 2: Restore from Backup

Option 3: Fix Forward

Seeding Data

Best Practices

Troubleshooting

Migration Failed to Apply

Schema Out of Sync

Duplicate Migration

CI/CD Integration

Database Overview

Schema

Drizzle ORM

Deployment

​Migration Files

​Development Workflow

​Local Development (Fast Iteration)

​Production Workflow (Safe Deployment)

​Common Migration Examples

​Adding a Column

​Adding a New Table

​Adding a Foreign Key

​Renaming a Column

​Dropping a Column

​Migration Commands

​Handling Destructive Changes Safely

​Approach 1: Multi-Step Migration

​Approach 2: Feature Flags

​Migration Conflicts

​Resolving Conflicts

​Rolling Back Migrations

​Option 1: Create Reverse Migration

​Option 2: Restore from Backup

​Option 3: Fix Forward

​Seeding Data

​Best Practices

​Troubleshooting

​Migration Failed to Apply

​Schema Out of Sync

​Duplicate Migration

​CI/CD Integration

​Related Documentation

Database Overview

Schema

Drizzle ORM

Deployment

Migration Files

Development Workflow

Local Development (Fast Iteration)

Production Workflow (Safe Deployment)

Common Migration Examples

Adding a Column

Adding a New Table

Adding a Foreign Key

Renaming a Column

Dropping a Column

Migration Commands

Handling Destructive Changes Safely

Approach 1: Multi-Step Migration

Approach 2: Feature Flags

Migration Conflicts

Resolving Conflicts

Rolling Back Migrations

Option 1: Create Reverse Migration

Option 2: Restore from Backup

Option 3: Fix Forward

Seeding Data

Best Practices

Troubleshooting

Migration Failed to Apply

Schema Out of Sync

Duplicate Migration

CI/CD Integration

Related Documentation