Migrations - BE Monorepo

Drizzle Kit manages database migrations with a streamlined workflow that generates SQL migrations from your TypeScript schema definitions.

Migration Configuration

Migrations are configured in drizzle.config.ts:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./src/db/migrations",
  schema: "./src/db/schema.ts",
  dialect: "postgresql",
  casing: "snake_case",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Key Configuration:

out: Where migration files are generated
schema: Source schema file location
dialect: PostgreSQL database
casing: Automatic snake_case conversion for table/column names
dbCredentials: Connection string from environment

Migration Commands

All migration commands are available as npm scripts in package.json:

Generate Migrations

bun run db:gen

Generates a new migration file based on schema changes:

dotenvx run --env-file=.env.dev -- drizzle-kit generate

Compares current schema with database state
Creates SQL migration file in src/db/migrations/
Generates snapshot for tracking

Push Changes (Development)

bun run db:push

Directly applies schema changes to the database without generating migration files:

dotenvx run --env-file=.env.dev -- drizzle-kit push --verbose --strict

--verbose: Detailed output
--strict: Strict mode prevents destructive changes
Ideal for rapid development
Warning: Not recommended for production

Apply Migrations

bun run db:migrate

Runs pending migrations against the database:

dotenvx run --env-file=.env.dev -- drizzle-kit migrate

Executes SQL migrations in order
Updates migration journal
Safe for production use

Pull Schema

bun run db:pull

Introspects existing database and generates schema:

dotenvx run --env-file=.env.dev -- drizzle-kit pull

Useful for:

Syncing with existing databases
Recovering schema from production
Reverse engineering schemas

Drizzle Studio

bun run db:studio

Launches the Drizzle Studio GUI:

dotenvx run --env-file=.env.dev -- drizzle-kit studio --port 3003 --verbose

Visual database browser
Runs on port 3003
Query and explore data
Schema visualization

Migration Workflow

Standard Development Flow

Modify Schema - Edit src/db/schema.ts

export const newTable = pgTable("new_table", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: text("name").notNull(),
});

Generate Migration - Run bun run db:gen

Drizzle Kit detects changes and generates:
src/db/migrations/0002_curious_nickname.sql

Review Migration - Inspect generated SQL

CREATE TABLE "new_table" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "name" text NOT NULL
);

Apply Migration - Run bun run db:migrate
```
Migration applied successfully
```

Rapid Development Flow

For quick iterations during development:

Modify Schema - Edit src/db/schema.ts
Push Directly - Run bun run db:push
- Skips migration file generation
- Applies changes immediately
- Faster iteration

Note: Always generate proper migrations before deploying to production.

Migration File Structure

Migrations are stored in src/db/migrations/:

src/db/migrations/
├── 0000_acoustic_sasquatch.sql
├── 0001_abnormal_squadron_sinister.sql
└── meta/
    ├── 0000_snapshot.json
    ├── 0001_snapshot.json
    └── _journal.json

Migration File Format

Each migration is a SQL file with a numbered prefix and random name:

-- 0000_acoustic_sasquatch.sql
CREATE TABLE "user" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text NOT NULL,
  "email" text NOT NULL,
  "email_verified" boolean DEFAULT false NOT NULL,
  "image" text,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp,
  "deleted_at" timestamp,
  CONSTRAINT "user_email_unique" UNIQUE("email")
);

Statement Breakpoints

Migrations use --> statement-breakpoint to separate SQL statements:

CREATE TABLE "session" (
  "id" text PRIMARY KEY NOT NULL,
  "expires_at" timestamp NOT NULL,
  "token" text NOT NULL
);
--> statement-breakpoint
CREATE TABLE "user" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text NOT NULL
);

This ensures proper transaction handling and rollback capabilities.

Migration Journal

The meta/_journal.json file tracks all migrations:

{
  "version": "7",
  "dialect": "postgresql",
  "entries": [
    {
      "idx": 0,
      "version": "7",
      "when": 1754724542196,
      "tag": "0000_acoustic_sasquatch",
      "breakpoints": true
    },
    {
      "idx": 1,
      "version": "7",
      "when": 1754992282199,
      "tag": "0001_abnormal_squadron_sinister",
      "breakpoints": true
    }
  ]
}

Example Migrations

Initial Auth Schema (0000)

The first migration creates the authentication tables:

CREATE TABLE "user" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text NOT NULL,
  "email" text NOT NULL,
  "email_verified" boolean DEFAULT false NOT NULL,
  "image" text,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp,
  "deleted_at" timestamp,
  CONSTRAINT "user_email_unique" UNIQUE("email")
);
--> statement-breakpoint
ALTER TABLE "session" 
  ADD CONSTRAINT "session_user_id_user_id_fk" 
  FOREIGN KEY ("user_id") 
  REFERENCES "public"."user"("id") 
  ON DELETE cascade 
  ON UPDATE no action;

Rate Limiting Addition (0001)

The second migration adds rate limiting:

CREATE TABLE "rate_limit" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "key" text NOT NULL,
  "count" integer DEFAULT 0 NOT NULL,
  "last_request" bigint NOT NULL,
  CONSTRAINT "rate_limit_key_unique" UNIQUE("key")
);

Migration Best Practices

1. Always Review Generated SQL

Before applying migrations, review the generated SQL:

bun run db:gen
cat src/db/migrations/0002_*.sql

2. Test Migrations Locally

Always test migrations against a local database first:

# Generate migration
bun run db:gen

# Test locally
bun run db:migrate

# Verify with studio
bun run db:studio

3. Version Control

Commit migration files to version control:

git add src/db/migrations/
git commit -m "feat(db): add user preferences table"

4. One-Way Migrations

Drizzle migrations are forward-only. To rollback:

Create a new migration that reverses changes
Or restore from database backup

5. Production Deployment

Never use db:push in production. Always use proper migrations:

# Development: Fast iteration
bun run db:push

# Production: Controlled migrations
bun run db:gen
bun run db:migrate

6. Handle Data Migrations

For data transformations, manually edit generated SQL:

-- Generated migration
ALTER TABLE "user" ADD COLUMN "full_name" text;
--> statement-breakpoint
-- Manual addition: Migrate existing data
UPDATE "user" SET "full_name" = "name" WHERE "full_name" IS NULL;
--> statement-breakpoint
ALTER TABLE "user" ALTER COLUMN "full_name" SET NOT NULL;

7. Strict Mode

Use --strict flag to prevent destructive changes:

bun run db:push  # Already includes --strict

Prevents:

Column drops
Type changes that lose data
Constraint removals

Environment Configuration

All commands use dotenvx for environment management:

dotenvx run --env-file=.env.dev -- drizzle-kit generate

Environment files:

.env.dev - Local development
.env.prod - Production

Ensure DATABASE_URL is set in your environment file:

DATABASE_URL=postgresql://user:pass@localhost:5432/dbname

Troubleshooting

Migration Out of Sync

If migrations are out of sync:

# Pull current schema
bun run db:pull

# Generate fresh migration
bun run db:gen

Failed Migration

If a migration fails:

Check database logs
Fix the issue
Create a new migration with the fix
Apply the corrective migration

Reset Development Database

To start fresh:

# Drop all tables (careful!)
psql $DATABASE_URL -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"

# Reapply all migrations
bun run db:migrate

Get Started

Core Concepts

Backend (Hono)

Database

Authentication

Observability

Packages

Development

Deployment

​Migration Configuration

​Migration Commands

​Generate Migrations

​Push Changes (Development)

​Apply Migrations

​Pull Schema

​Drizzle Studio

​Migration Workflow

​Standard Development Flow

​Rapid Development Flow

​Migration File Structure

​Migration File Format

​Statement Breakpoints

​Migration Journal

​Example Migrations

​Initial Auth Schema (0000)

​Rate Limiting Addition (0001)

​Migration Best Practices

​1. Always Review Generated SQL

​2. Test Migrations Locally

​3. Version Control

​4. One-Way Migrations

​5. Production Deployment

​6. Handle Data Migrations

​7. Strict Mode

​Environment Configuration

​Troubleshooting

​Migration Out of Sync

​Failed Migration

​Reset Development Database

Build docs developers (and LLMs) love