search

pressly / goose

A database migration tool. Supports SQL migrations and Go functions.
http://pressly.github.io/goose/
Other
6.68k stars 503 forks source link

Migration version mismatch when running `goose fix` in CI #785

Open jon-mccormack opened 2 months ago

jon-mccormack commented 2 months ago

Hi all,

I might be misunderstanding here, but I've been running through a few scenarios where I'd like to implement a hybrid migration versioning system before merging to the main development branch. On merge, CI would run goose fix and commit those new sequential-number-versioned migrations.

The problem I've found is, if a developer were to create a date-version-based migration locally and submit a PR for their code, once their code is merged with their migration(s), and the developer pulled master after CI had renamed their date-version-based migration to a sequential-number-based migration, the developers environment is then broken because their date-version-based migration has been renamed and Goose doesn't know how to handle that.

See example below, where after goose fix'ing my already applied date-version-based migration, migrations failed to run as the old date-version-based migration is missing:

2024/06/26 13:56:21 goose run: error: found 1 missing migrations before current version 20240626133012:
        version 2: /workspaces/purple-user-translations-api/db/migrations/00002_modify_company.go

I think I'm following the recommended approach under this section of the docs, if not, could anyone advise how to approach this problem? Perhaps its an oversight in Goose?

I'm going to stick with a date-version-based approach for now but would be good to get this working in future if, for example, the migration tracking table was updated to be able to associate the original date-version-based migration name with the new seqential-number-based migration name.

Thanks!

mfridman commented 1 month ago

Hey, apologies for the delay. This is one of those tricky scenarios because local environments are notorious for getting out-of-sync with production. So trying to maintain correct state in local dev environments doesn't really have an elegant solution.

For example, say a developer creates a migration, applies it locally, tests, and then submits a PR. Another developers suggest a change to the query, it gets changed, and merged. But the initial developer didn't go back and down/up the migration. Their local environment is not different than production.

So although you've "fixed" subsequent migrations being applied (newer timestamps), you're potentially side-stepping a whole class of problems where developers are writing/testing local code that is operating against a schema that isn't the same as production.

On most projects I work on, I like to make it as simple as possible to drop a database and re-create it (takes ~seconds).

Happy to explore solutions, and/or hear feedback on ways to address this. Even if it's documenting best practices.