supabase
feat(cli): add repeatable migrations
What kind of change does this PR introduce?
Similar to flyways repeatable migrations this adds rudimentary functionality for repeatable database migrations to the supabase cli.
- Adds a flag to
supabase migration newto create a repeatable migration - Implementation to differentiate between repeatable and common migrations in
supabase migration up - Tests
Instead of versioning repeatable migration files they are applied after all pending migrations locally and can then be pushed to remote.
What is the current behavior?
Currently database objects like views, functions etc. must be duplicated in the migration files when changed. E.g. to change a view we create a new versioned migration in which we duplicate the definition of the view.
What is the new behavior?
-
supabase migration newcan now be used to create repeatable migrations -
supabase migration upnow applies repeatable migrations after all pending migrations
Additional context
Should close #2784
Pull Request Test Coverage Report for Build 12941917448
Details
- 25 of 48 (52.08%) changed or added relevant lines in 4 files are covered.
- 6 unchanged lines in 2 files lost coverage.
- Overall coverage decreased (-0.05%) to 58.238%
| Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
|---|---|---|---|
| pkg/migration/file.go | 1 | 3 | 33.33% |
| pkg/migration/list.go | 4 | 6 | 66.67% |
| internal/migration/list/list.go | 7 | 26 | 26.92% |
| <!-- | Total: | 25 | 48 |
| Files with Coverage Reduction | New Missed Lines | % |
|---|---|---|
| internal/migration/up/up.go | 1 | 69.05% |
| internal/gen/keys/keys.go | 5 | 12.9% |
| <!-- | Total: | 6 |
| Totals | |
|---|---|
| Change from base Build 12926704562: | -0.05% |
| Covered Lines: | 7617 |
| Relevant Lines: | 13079 |
💛 - Coveralls
@sweatybridge as mentioned in the issue #2784 there are a few different options to implement this.
I have opted for a simple solution where repeatable migrations are treated like a seed file.
I.e. all repeatable migrations are applied after the pending migrations have been applied in each supabase migration up run.
Currently they won't be pushed to remote or show up in the studio, as this would create discrepancies when versioning with timestamps and subsequent comparisons between remote/local. They are treated the exact same as the seed file.
I experimented locally with creating repeatable migrations and then using the current timestamp as new version for each repeatable migration when applied. (See opening comment of issue).
This will create a mismatch between local and remote every time the migration up command is run, because the version on remote will always be different to local.
A way around this would be to remove (?!) the remote migration and apply the local one, since the local migration will always supersede the remote migration. Not really sure what to think of this.
all repeatable migrations are applied after the pending migrations have been applied in each supabase migration up run
I think this is the right approach for as far as I understand the behaviour of flyway.
They are treated the exact same as the seed file.
Currently seed files are also versioned and tracked in seed_files table, similar to versioned migration. But it used to be untracked so I can understand where the misconception came from.
I guess I should also add the repeatable migrations to the supabase db reset and add tests in supabase migration squash.
@sweatybridge that should be everything now.
I wasn't aware the seed files are versioned as well. I think it would be good to version repeatable migrations, too if possible.
Does the cli manage the seed file versioning, or is that done somewhere else? I couldn't find it in internal or cmd.
I think it would be good to version repeatable migrations, too if possible.
What's the use case for tracking repeatable migrations? My understanding is that repeatable migrations should always after all versioned migrations, even if there are no changes.
Does the cli manage the seed file versioning, or is that done somewhere else?
The versioning of seed file is done in https://github.com/supabase/cli/blob/develop/pkg/migration/seed.go#L33
A few more complications that need answers
- What should happen when user runs
supabase db reset --version <timestamp>? Some repeatable migrations might depend on new schemas defined in later versions. - We also need to handle
supabase db reset --linkedwhich triggers the code path here https://github.com/supabase/cli/blob/develop/internal/db/reset/reset.go#L235 - Should repeatable migrations appear in
migration listand be downloadable frommigration fetch? If so, we probably need to record the contents of repeatable migrations in theschema_migrationstable as well, together with versioned ones.
I think it would be good to version repeatable migrations, too if possible.
What's the use case for tracking repeatable migrations? My understanding is that repeatable migrations should always after all versioned migrations, even if there are no changes.
I suppose tracking them within the db isn't necessary considering they are tracked in version control and always applied after the versioned migrations. The only use case I see is that you could inspect them in supabase studio, which currently isn't possible (I think?). Leaning towards not tracking them then.
Does the cli manage the seed file versioning, or is that done somewhere else?
The versioning of seed file is done in https://github.com/supabase/cli/blob/develop/pkg/migration/seed.go#L33
Thanks, will have a look at it.
A few more complications that need answers
- What should happen when user runs
supabase db reset --version <timestamp>? Some repeatable migrations might depend on new schemas defined in later versions.
I think it would be ok to break if the reset can be reverted. Alternatively not allow the command with a warning. Maybe we could do a dry run, see if it succeeds, and react accordingly.
- We also need to handle
supabase db reset --linkedwhich triggers the code path here https://github.com/supabase/cli/blob/develop/internal/db/reset/reset.go#L235- Should repeatable migrations appear in
migration listand be downloadable frommigration fetch? If so, we probably need to record the contents of repeatable migrations in theschema_migrationstable as well, together with versioned ones.
True, 2 needs to be considered.
I guess at this point it might make sense to treat repeatable migrations as versioned migrations with only one or an empty/"" version field.
We could then load them through LoadPartialMigrations/LoadLocalMigrations, which should solve point 2. above as well as make them listable via migration list and inspectable in the studio.
What are your thoughts on that? @sweatybridge
I guess at this point it might make sense to treat repeatable migrations as versioned migrations with only one or an empty/"" version field.
We could then load them through LoadPartialMigrations/LoadLocalMigrations, which should solve point 2. above as well as make them listable via migration list and inspectable in the studio.
Many birds in one stone. I think this is the most ideal solution. Thank you for coming up with the suggestion.
@sweatybridge the current implementation should satisfy most requirements.
I changed it so repeatable migrations are now treated as versioned migrations, with their name as version. Leaving it as an empty string wouldn't work because the version is the PK in the schema_migrations table. Since the filenames must be unique anyway, this should be fine and not too confusing IMO.
The regex to check the migration files for correct formatting and version/name group matching now includes an adjustment to match repeatable migrations as well.
Both supabase db reset --local and supabase db reset --linked work as expected.
I also modified the makeTable func to append the repeatable migrations to the versioned migrations. They always append to the end (chronologically newest) and don't include a timestamp, as they currently don't have one. If there are discrepancies between local and remote, this should also be visible (ignore the two Println, I added them for debugging purposes):
The repeatable migrations now also show up in the studio:
Since the repeatable migrations don't have a timestamp in the name, it can't be shown in the studio. The UI could show another placeholder for repeatable migrations like
applied after every run, or we could a created_at field in the schema_migrations table and then pull it from there. That field should be easily backfilled for existing instances, since all preexisting migrations have the timestamp as version.
Before I implement the supabase db reset --version <version> and tests, does this seem better than the solution before?
Sorry again for the delay. Given the complexity of this change, I took a step back and suggested an alternative in the original issue https://github.com/supabase/cli/issues/2784#issuecomment-2623582604
Let's see if users are comfortable with declarative schema which imo better addresses the duplication issue.
Hey @sweatybridge, just checking in if this still relevant. Happy to close if not.
