Kysely Migration Ordering: Handling Parallel Development

The Problem

When multiple developers work on feature branches simultaneously, they often create migrations with timestamps that become out of order when merged. This is a common scenario:

• Developer A creates migration 20240112_1200_add_users.sql on their feature branch
• Developer B creates migration 20240112_1201_add_products.sql on their feature branch
• Developer B merges first, production runs the 12:01pm migration
• Developer A merges second, but now there's a problem...

Default Behavior: Strict Ordering

By default, Kysely enforces strict alphabetical ordering of migrations. When Developer A's earlier-timestamped migration appears after an already-executed later migration, Kysely throws an error:

corrupted migrations: expected previously executed migration [last_executed] to be at index X but 20240112_1200_add_users was found in its place. New migrations must always have a name that comes alphabetically after the last executed migration.

This validation happens in src/migration/migrator.ts:621-633 in the #ensureMigrationsInOrder method.

How Kysely Tracks Migrations

Kysely stores migration history in a kysely_migration table (defined at src/migration/migrator.ts:10) with two columns:

• name (varchar(255)) - The migration file name serving as unique identifier
• timestamp (varchar(255)) - ISO string timestamp of when the migration was executed

The table structure is created at src/migration/migrator.ts:395-405.

The Solution: Allow Unordered Migrations

Kysely provides an allowUnorderedMigrations option (defined at src/migration/migrator.ts:819) to handle parallel development workflows:

const migrator = new Migrator({
  db,
  provider: migrationProvider,
  allowUnorderedMigrations: true  // Enable out-of-order migrations
})

How It Works

When allowUnorderedMigrations is enabled:

1. The strict ordering check at src/migration/migrator.ts:541-543 is bypassed
2. Kysely still checks for missing migrations (src/migration/migrator.ts:607-619)
3. Pending migrations are identified by name, not order (src/migration/migrator.ts:558-565)
4. Each migration runs exactly once, regardless of alphabetical position

With this setting:

• Developer A's 12:00pm migration will run even though 12:01pm already executed
• The execution order becomes: 12:01pm (already run) → 12:00pm (runs now)
• Future migrations continue to work normally

Best Practices

1. Use timestamps in migration names to maintain chronological clarity
2. Enable allowUnorderedMigrations for teams with parallel development
3. Keep migrations independent - don't assume execution order when unordered migrations are enabled
4. Test migrations thoroughly in development before merging

Trade-offs

Pros:

• Eliminates merge conflicts and rebase issues with migration timestamps
• Supports parallel feature development without coordination
• Prevents blocking deployments due to out-of-order migrations

Cons:

• Migrations may run in a different order than their timestamps suggest
• Requires migrations to be more independent and idempotent
• Can make debugging migration issues slightly more complex

The allowUnorderedMigrations option provides a practical solution for modern development workflows where multiple developers create database migrations in parallel.