Commutative Migrations

[AndriiSherman]

As part of the migration flow upgrades with drizzle-kit, we are introducing Commutative Migrations Checking. The Postgres implementation is already complete, and we are working on extending support to other dialects

Commutative Migrations will use a new migrations folder structure, explained here. This approach is especially useful for teams of all sizes who work on the same database schema and update it frequently

There may be situations where two feature branches modify the same database entity, making the migrations non-commutative (where order matters). We will detect such cases automatically.

Case 1:
If all migrations are commutative, you can apply them in any order without consequences. (Drizzle will automatically check if migrations are commutative)

Case 2:
Suppose we have migrations 123 and 124 on the dev/prod branches, and two new features are assigned to different team members. These create two branches:

Branch A: 129 → 143 → 150
Branch B: 135 → 145

Migrations 143 and 145 are non-commutative (marked in red). The team must manually decide which should run first. For example:

• 143 drops the posts table
• 145 alters a column in the posts table

To handle such cases, we will update how the check, generate, and migrate commands work.

To handle such cases we will update how each of check, generate, migrate commands work. Let's go one by one:

drizzle-kit check

This command now checks migration folders for commutativity and possible conflicts (non-commutative migrations)
For the example above, running drizzle-kit check may produce output like this:

We've detected 2 branches conflicting with each other. Both branches are starting from 124_migration.sql. 
We've detected conflicts between 143_migration.sql and 145_migration.sql. 

You have 2 options to resolve it:
Option 1: 
- Remove [143_migration.sql, 150_migration.sql] folders
- Run `drizzle-kit generate`

Option 2:
- Remove 145_migration.sql folder
- Run `drizzle-kit generate`

This command is useful for CI/CD pipelines, development workflows, and general checks.

Rule of thumb for resolving conflicts:

In case of a migration conflict, always resolve it by removing the conflicted migration (and all subsequent migrations) from the target branch.

drizzle-kit generate

This command now runs check by default to ensure that new migrations do not introduce conflicts. Possible outcomes:

Situation 1: No conflicts detected

A new migration will be generated with connections to all open branches in the migration history.
Example: When generating a new migration (152_migration), drizzle-kit will check for all migrations without a child and use them as parents. This marks the new migration as generated after all branches. We call this action "merging branches”

Situation 2: Conflict detected

If a conflict is detected, generate will output an error explaining how to resolve it. Typically, this requires manually removing a few migration folders, syncing the database schema downstream, and running drizzle-kit generate again.

Example: If drizzle-kit detects non-commutative migrations (145 and 143) after pulling from dev, you must remove migration 145, sync the dev schema locally, and then generate a new migration (152). This migration will have only one parent (150), ensuring no further conflicts

drizzle-kit migrate

This command behaves the same as generate - it runs check by default. If a conflict is found, you will be guided through the same resolution steps. After regenerating the migration, you can safely run migrate again

--ignore-conflicts

If you want to skip conflict checking, you can use the --ignore-conflicts flag:

drizzle-kit generate --ignore-conflicts
drizzle-kit migrate --ignore-conflicts