Research: Better approach for idempotent migrations in auto-migration system

[productdevbook]

Problem Statement

We've implemented an automatic database migration system (see #19) that runs migrations on application startup. However, the current approach requires manually modifying Drizzle-generated migration files to make them idempotent, which feels hacky and goes against the "don't touch generated files" principle.

Current Implementation Issues

Our current solution in #19 works but has several drawbacks:

1. Manual Migration File Modifications

We had to manually wrap all Drizzle-generated SQL with existence checks:

-- Instead of clean Drizzle output:
CREATE TYPE "public"."delivery_status" AS ENUM('PENDING', 'SENT', 'DELIVERED', 'FAILED', 'CLICKED');

-- We need this verbose approach:
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'delivery_status') THEN
        CREATE TYPE "public"."delivery_status" AS ENUM('PENDING', 'SENT', 'DELIVERED', 'FAILED', 'CLICKED');
    END IF;
END $$;

2. Foreign Key Constraint Complexity

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints WHERE constraint_name = 'apiKey_appId_app_id_fk') THEN
        ALTER TABLE "apiKey" ADD CONSTRAINT "apiKey_appId_app_id_fk" FOREIGN KEY ("appId") REFERENCES "public"."app"("id") ON DELETE cascade ON UPDATE no action;
    END IF;
END $$;

3. Maintenance Burden

• Every new migration needs manual modification
• Risk of human error in existence checks
• Breaks the automated nature of Drizzle migrations
• Makes it hard to review what actually changed

What We're Looking For

Better approaches for handling idempotent migrations that don't require manual SQL modifications:

Option 1: Migration State Tracking

• Use Drizzle's built-in migration tracking instead of forcing idempotency
• Implement proper migration state management
• Only run pending migrations, not all migrations every time

Option 2: Custom Drizzle Configuration

• Configure Drizzle to generate idempotent SQL by default
• Custom migration generator that adds IF NOT EXISTS automatically
• Plugin or configuration that handles this at generation time

Option 3: Database State Comparison

• Compare current database schema with target schema
• Only apply differences, similar to how drizzle-kit push works
• Generate differential migrations on-the-fly

Option 4: Migration Lock/State Management

• Implement proper migration locking mechanisms
• Track which migrations have been applied
• Skip already-applied migrations automatically

Questions for the Community

1. How do you handle automatic migrations in production Drizzle applications?
2. Are there Drizzle configuration options we're missing for idempotent migrations?
3. What's the recommended pattern for startup migrations with Drizzle?
4. Should we be using drizzle-kit migrate in a different way?
5. Are there existing tools/libraries that solve this problem elegantly?

Related Resources

• Current implementation: feat: Auto Database Migration Plugin for Nitro #19
• Drizzle ORM Migration docs: https://orm.drizzle.team/kit-docs/overview#running-migrations
• Similar discussion in Drizzle Discord/GitHub?

Success Criteria

An ideal solution would:

• ✅ Allow automatic migrations on startup
• ✅ Work in both development and production
• ✅ Not require manual modification of generated files
• ✅ Handle existing database objects gracefully
• ✅ Provide clear error messages when migrations fail
• ✅ Support rollback/recovery scenarios

Current Workaround

While we research better approaches, the current implementation in #19 works functionally but isn't the clean solution we'd prefer long-term.

Your insights, experiences, and suggestions are highly appreciated! 🙏

[productdevbook]

cc: @AndriiSherman