Best way to deal with migration merge conflicts?

[adlrwbr]

Hello!

Sometimes team members will modify the DB schema on separate branches, resulting in conflicting journal.json and xxxx_snapshot.json. Editing these files by hand is quite tedious. What's the best practice? Btw, I'm using drizzle-kit to generate migration files.

[lxia1220]

imo simplest way is create migration file in merge CI

https://docs.github.com/en/repositories/configuring-branches-and-merges-in-your-repository/configuring-pull-request-merges/managing-a-merge-queue#managing-a-merge-queue

btw there are so many possibilities with migration merge conflicts, customize it for your team environment

[rychlis]

I don't get it - how do merge queues solve the issue? Are you proposing to generate migrations in CI somehow?

[rychlis]

I guess it solves the issue if there is a branch waiting to be merged with migration and you are working on another feature/branch. That's not very helpful in most cases IMHO as what happens is we have multiple people working on features at the same time...

[anthonyjoeseph]

I'm not sure how a merge queue helps without some CI-friendly script that fails in cases of migration conflicts (which drizzle doesn't natively support yet)

[adlrwbr]

@lxia1220 is right that there are many possible solutions, but for those who find this on Google, here's a manual fix I often perform:

Situation: You merge dev into your PR branch, but both your branch (HEAD) and dev created migrations numbered 0007.

Conflict:

Changes to be committed:
        new file:   db/migrations/0007_daffy_morg.sql
Unmerged paths:
  (use "git add <file>..." to mark resolution)
        both added:      db/migrations/meta/0007_snapshot.json
        both modified:   db/migrations/meta/_journal.json

Typical Solution: Regenerate your migrations

Steps:

1. Visit xxxx_snapshot.json and delete YOUR changes (HEAD). Accept changes from dev.
2. Visit _journal.json and again, delete YOUR changes (HEAD). Accept changes from dev. Note the deleted "tag".
3. Remove your migration file referenced by the now-deleted "tag".
4. npm run drizzle-kit generate

[alanhamlett]

Currently my process to solve this:

1. abort the rebase from conflicting main branch
2. revert my local changes to db/migrations/ folder
3. stash my local branch code changes
4. pull main branch & rebase changes into my local dev branch without conflicts this time
5. run drizzle-kit push to make my local db match main branch
6. un-stash my local code changes
7. run drizzle-kit generate to re-generate my local schema migration
8. run drizzle-kit migrate to apply my local schema changes
9. create a PR to get my changes into main branch
10. if anyone merges schema changes to main before my PR is merged, repeat steps above starting at 1

Depending on your team size and PR frequency, you might have to do this multiple times.

[anthonyjoeseph]

This is a missing feature - #2488 - I'll cross post an interim solution here, which automates the suggestions in the above comments:

It's a little script written by gburtini - github gist

if you run it with FORCE_FIX=true:

• if you are mid-merge, it will intelligently keep "theirs" and delete "ours"
  • specifically, it will git checkout --theirs /your/project/filepath/drizzle/meta
• it will delete any .sql file not referenced in _journal.json
• it will re-run drizzle-kit generate to generate the proper sql & config & overwrite/delete incorrect snapshots

if you run it with FORCE_FIX=false (the default behavior):

• this is useful for CI
• it will fail if:
  • there are two migrations with the same number
  • the _journal.json is not one-to-one with all .sql migration files, contains git conflicts or is malformed

You do have to be sure to modify line 9 and line 133 to match your particular setup

[anthonyjoeseph]

@gburtini pointed out to me that using the --name flag obviates the need for a CI step

Use it - drizzle-kit generate --name migration - to eliminate randomness, and generate migration files that always look like this - "0012_migration.sql". This will force merge conflicts and eliminate the problem of lingering migration conflicts

To ease the pain of undoing your migrations during a merge conflict, I wrote a little script - gist.

It's meant to be run mid-merge, and it just keeps "theirs" and deletes all of your generated migration files.

Then you're free to painlessly re-generate them with drizzle-kit generate --name migration

If you prefer to keep the randomly generated names for some reason, you can use the script above, but I think this simplifies things a bit

[anthonyjoeseph]

fwiw it's possible to use timestamp prefixes instead of a incrementing index prefixes (it's buried in release notes, not mentioned in their actual docs)

this resolves a bit of confusion - it's a bit less opaque as to why a particular migration might fail - and it seems to be much more standard practice, since other orms like prisma do this. But it doesn't make conflicts any easier to spot, or easier to resolve