drizzle-migrations

name	drizzle-migrations
description	Drizzle Kit workflow for Volatio using idempotent migrations (generate + edit + migrate), never use push. Idempotent patterns prevent partial failure issues, enable safe re-runs, and work cleanly in CI. Use when schema changes, migration errors, or database sync issues occur.

drizzle-migrations

When to Use

• Making schema changes to packages/db/src/schema/
• Seeing "relation X does not exist" errors
• Resolving schema-database drift
• Migration fails with "already exists" errors
• Need safe, repeatable migrations for CI/CD

Quick Start

# 1. Update schema in packages/db/src/schema/
# 2. Generate migration
pnpm --filter @volatio/db generate

# 3. Edit the generated SQL to add IF NOT EXISTS / DO blocks (see patterns below)
# 4. Apply migration (safe to run multiple times)
pnpm --filter @volatio/db migrate

# 5. Verify
docker exec volatio-postgres-1 psql -U volatio -d volatio_market -c "\d table_name"

CRITICAL: Understanding Drizzle Kit Prompts

Both push and generate will prompt when a diff is ambiguous (rename vs create). These prompts expect a real TTY (raw key events), so piping stdin (echo, yes, printf) won't work.

Is token column in password_reset_tokens table created or renamed from another column?
❯ + token              create column
  ~ token_hash › token rename column

You cannot automate these prompts. The only way to handle them is:

1. Run interactively in a terminal (local dev)
2. Use custom migrations to make the change explicit (CI-safe)
3. Reset the database to eliminate drift

Golden Path: Always Use generate + migrate (Never push)

The recommended workflow is:

# 1. Update your schema in packages/db/src/schema/
# 2. Generate SQL migration files
pnpm --filter @volatio/db generate

# 3. Make the migration idempotent (see below)
# 4. Apply the migration
pnpm --filter @volatio/db migrate

Why Not push?

drizzle-kit push directly modifies the database without creating migration files. This:

• Breaks CI/CD: No migration history to commit
• Loses reproducibility: Can't recreate the database state
• Causes prompts: Interactive questions about renames that can't be automated
• Risks data loss: No review step before schema changes

The Better Way: Idempotent Migrations

After running generate, always edit the SQL file to make it idempotent (safe to run multiple times).

Common Idempotency Patterns

-- CREATE TABLE
CREATE TABLE IF NOT EXISTS "table_name" (...);

-- CREATE TYPE (enums)
DO $$ BEGIN
 CREATE TYPE "public"."my_enum" AS ENUM('value1', 'value2');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

-- ALTER TYPE ADD VALUE
DO $$ BEGIN
 ALTER TYPE "public"."my_enum" ADD VALUE IF NOT EXISTS 'new_value';
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

-- ADD COLUMN
ALTER TABLE "table_name" ADD COLUMN IF NOT EXISTS "column_name" text;

-- ADD CONSTRAINT
DO $$ BEGIN
 ALTER TABLE "table_name" ADD CONSTRAINT "constraint_name" 
   FOREIGN KEY ("col") REFERENCES "other_table"("id");
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

-- CREATE INDEX
CREATE INDEX IF NOT EXISTS "index_name" ON "table_name" ("column");

-- DROP COLUMN
ALTER TABLE "table_name" DROP  IF  "column_name";


  IF  "table_name" CASCADE;

Real Example

Generated migration (NOT idempotent):

CREATE TYPE "public"."trade_intent" AS ENUM('open_long', 'close_long');
CREATE TABLE "paper_trades" (
  "id" uuid PRIMARY KEY,
  "symbol" text NOT NULL
);
ALTER TABLE "paper_trades" ADD COLUMN "intent" "trade_intent";
CREATE INDEX "idx_trades_symbol" ON "paper_trades" ("symbol");

Fixed migration (idempotent):

DO $$ BEGIN
 CREATE TYPE "public"."trade_intent" AS ENUM('open_long', 'close_long');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "paper_trades" (
  "id" uuid PRIMARY KEY,
  "symbol" text NOT NULL
);

ALTER TABLE "paper_trades" ADD COLUMN IF NOT EXISTS "intent" "trade_intent";

CREATE INDEX IF NOT EXISTS "idx_trades_symbol" ON "paper_trades" ("symbol");

Why Idempotency Matters

1. Partial failures: If migration fails halfway, you can re-run it
2. Development flexibility: Can run same migration against multiple local DBs
3. CI safety: Tests can run migrations without worrying about pre-existing state
4. Merge conflicts: Multiple branches can have overlapping schema changes

CI: ONLY use migrate

CI should only run drizzle-kit migrate against idempotent migrations committed to git.

# CI pattern
- Start Postgres (may have partial state from cache)
- pnpm --filter @volatio/db migrate  # Safe because migrations are idempotent
- Run tests

Local Development: Generate + Edit + Migrate

# 1. Generate migration from schema changes
pnpm --filter @volatio/db generate

# 2. Edit the SQL file to add IF NOT EXISTS / DO blocks
#    (see patterns above)

# 3. Apply migration (safe to run multiple times)
pnpm --filter @volatio/db migrate

# 4. If you need a clean slate (optional)
docker compose down -v && docker compose up -d
pnpm --filter @volatio/db migrate

Key Commands

Command	Purpose	When
drizzle-kit push	Push schema directly (interactive)	Local dev ONLY
drizzle-kit generate	Generate SQL migration files	Before deploy
drizzle-kit migrate	Apply pending migrations (non-interactive)	CI, deploy
drizzle-kit check	Verify migration consistency	Before merge
drizzle-kit generate --custom	Create empty migration for manual SQL	Renames, drift fixes

CRITICAL: Safe Column Renames

Drizzle Kit treats column renames as DROP + ADD = DATA LOSS.

Wrong Way (Loses Data)

// Before
triggeredAt: timestamp('triggered_at'),

// After - Drizzle generates DROP + ADD!
detectedAt: timestamp('detected_at'),

Safe Way: Custom Migration

# 1. Generate custom migration
pnpm --filter @volatio/db generate -- --custom --name=rename-triggered-to-detected

-- packages/db/drizzle/0006_rename-triggered-to-detected.sql
ALTER TABLE signals RENAME COLUMN triggered_at TO detected_at;

# 2. Apply the migration
pnpm --filter @volatio/db migrate

Fixing Schema Drift with Custom Migrations

When your DB is out of sync with your schema (e.g., after failed pushes or manual changes):

Step 1: Create a custom migration

pnpm --filter @volatio/db generate -- --custom --name=fix-schema-drift

Step 2: Write idempotent SQL

-- Example: Rename column if it exists with old name
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'password_reset_tokens'
      AND column_name = 'token_hash'
  ) AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'password_reset_tokens'
      AND column_name = 'token'
  ) THEN
    ALTER TABLE public.password_reset_tokens RENAME COLUMN token_hash TO token;
  END IF;
END $$;

-- Drop columns that shouldn't exist
ALTER TABLE public.password_reset_tokens
  DROP COLUMN IF EXISTS created_at;

Step 3: For tables that need complete reset

-- Drop and recreate if structure is completely different
DROP TABLE IF EXISTS public.scheduled_release_captures CASCADE;

CREATE TABLE public.scheduled_release_captures (
    "id" text PRIMARY KEY NOT NULL,
    "symbol" text NOT NULL,
    "created_at" timestamp with time zone DEFAULT now() NOT NULL
);

Step 4: Apply the migration

pnpm --filter @volatio/db migrate

Once applied, your DB won't be in an ambiguous state anymore, so future runs don't trigger rename prompts.

TimescaleDB Hypertables

Some tables are hypertables with special constraints:

# Check hypertables
docker exec volatio-postgres-1 psql -U volatio -d volatio_market -c \
  "SELECT * FROM timescaledb_information.hypertables;"

Limitations:

• Cannot rename columns on hypertables easily
• Cannot change time column after creation
• Some ALTER TABLE operations restricted

# After adding new time-series tables
pnpm --filter @volatio/db setup:hypertables

Verify Schema-Database Sync

Before migrations:

# Check database schema
docker exec volatio-postgres-1 psql -U volatio -d volatio_market -c "\d table_name"

# Compare with Drizzle schema
cat packages/db/src/schema/table-name.ts

Troubleshooting

"Is X created or renamed?" Prompt in CI

This breaks CI. You cannot automate the answer.

Solution: Write a custom migration instead:

pnpm --filter @volatio/db generate -- --custom --name=fix-ambiguous-column
# Write explicit ALTER TABLE ... RENAME COLUMN or DROP COLUMN
pnpm --filter @volatio/db migrate

"Column X does not exist"

# Option 1: Reset DB (fastest)
docker compose down -v && docker compose up -d
pnpm --filter @volatio/db migrate

# Option 2: Custom migration to add column
pnpm --filter @volatio/db generate -- --custom --name=add-missing-column

Migration Conflicts After Branch Merge

pnpm --filter @volatio/db drizzle-kit check

# If needed, regenerate
rm -rf packages/db/drizzle/
pnpm --filter @volatio/db generate -- --name=consolidated

Don'ts

• Don't use push in CI - prompts require TTY, breaks pipelines
• Don't use generate in CI - can also prompt for ambiguous changes
• Don't pipe stdin to push - echo, yes, printf won't work
• Don't rename columns without custom migration SQL
• Don't skip reviewing generated SQL - catch data loss

No Rollback Support

Drizzle Kit has NO drizzle-kit down command. To undo:

1. Write a forward migration that reverses changes
2. Or restore from database backup

References

• Drizzle Kit push
• Drizzle Kit migrate
• Custom migrations
• Drizzle Kit check
• GitHub issue on rename prompts