Max rollback version

[steeling]

trafficstars

Is your feature request related to a problem? Please describe. At certain points in a schema migration, rollbacks become impossible. It would be nice to prevent rollbacks by storing an max_rollback_version along with the current version.

Describe the solution you'd like Add an optional value for migrations/migration steps.

ie:

migrate -source file://path/to/migrations -database postgres://localhost:5432/database up 2 --max_rollback_version 987654

Describe alternatives you've considered Wrapping the golang code and creating managing an additional table x-schema_max_rollback

Additional context Given that (the drivers I looked at) truncates the table to maintain the version as a singleton, it would probably be best to store the max rollback in a new table, also as a singleton.

This could also be done by providing a mutating option to the migrations struct, ie:

migrations.WithMaxRollback(version)

It is probably useful to update that single such that it is only ever increasing, unless some force option is applied. ie: don't allow to go from max_rollback_version 9 to max_rollback_version 1.

For the various types of migrations structs such that it is not a breaking api change.

Happy to contribute if this sounds desirable.

[dhui]

At certain points in a schema migration, rollbacks become impossible.

This isn't true in the general case and depends on how you're writing migrations.

Can you describe your usecase in more detail?

Can your issue be resolved by staging your migrations? e.g.

1. migrate forward to the migration that can't be rolled back
2. run the migration(s) that can't be rolled back and deal with any issues that may arise
3. continue running forward migrations

[steeling]

Ya it's true that through clever tricks and staged migrations between application and schema updates you can create roll forward and back scenarios. However that burden can become greater than the benefit sometime. Even a simple migration that transforms data between 2 columns, inputs to a new column, and drops the old columns, could make it impossible to rollback, without keeping around the original 2 columns, or some helper column for a short period of time.

Many applications, and databases (e.g. cockroachdb) perform breaking changes that can't be rolled back from.

To provide some insight, I'm looking a for a way to manage OSS services that might be composed of microservices, multiple databases (or anything that needs versioned contracts on RPC's). With persisted data, it can be much easier to just not support certain things, and provide a happy path for customers (and using this as a tool to prevent customers from "shooting themselves in the foot")

[steeling]

To provide one other important piece of info, we're looking to do this with helm, and helm hooks. Helm stores previous chart state, so we can't easily release a compacted schema at a specific version and prevent helm rollback from breaking some things

[dhui]

To clarify your example, a column rename would also need to coordinate with the application to ensure that the old column is no longer used before removing it. Such a migration can also be rolled back and also needs to be coordinated with the consuming application(s).

I think support for preventing reverse migrations past a certain version number could be useful. We may be able to do this in a backwards compatible manner too. Does the stopping point need to be stored in the DB? What if it's store in the code?

Make sure there's adequate documentation for the max reverse migration number, warning about the danger of the feature.

Can you think of a better name than MaxRollback, MaxReverseMigration, or MaxDownMigration? How's StopDownMigrationAt or StopReverseMigrationAt sound? The terminology/naming should be consistent within migrate.

[steeling]

A better example might be simply dropping a non-null column in a db that a previous code version requires

The issue we have, while using helm, is that a rollback will use code from the previous version (it even uses all of the hooks from the previous version), so if code at version 123 isn't aware of the future breaking changes we can't codify it. The answer may sound like doing staged versions, but also with helm users can skip versions, ie: upgrade from 123, to 125 (skipping 124), and so codifying the breakpoint in 124 never gets exercised.

Naming has never been my strong suit :) Some combination of

<Last|Max><Stable|Supported|Compatible><Schema|Version|Migration|Rolback>.

If we're bought into this feature, I think the next question is whether a insert_name_decision should be tied to a overall Migration operation, or a specific migration version.

I think the former would be much simpler, and isn't that difficult for the user to keep track of breaking schema changes without directly tying to a schema. Happy to expand on my thoughts more here if this lacks any clarity

[steeling]

To my last question, I guess it's actually possible to provide both options. I was thinking of the scenario of running a Migrate() on a Dir, and tying a max version to each schema entry, but we can just provide the option on each up and Migrate

[dhui]

Does your app have other config that persists between versions? An issue with migrate is that it doesn't dogfood itself. e.g. the schema version table is not managed by migrate which makes backwards incompatible DB driver changes harder See also: https://github.com/golang-migrate/migrate/issues/14

---

Tying migrations limits to an operation will be simpler than tying limits to a version since:

1. limits would need to be stored/tracked per version
2. limits per version would need to be "merged" during an operation

As interesting as limits per version are, unless there's a need for them, I'd avoid the complexity.

[steeling]

Unfortunately all the configs are controlled by helm, which will overwrite configs on releases and rollbacks. We could append a unique ID to each one, but then we're left with many configs and cleaning them up manually which isn't very nice either.

Merging limits isn't too tough, since it probably makes the most sense to just keep max(cur_limit, prev_limit), unless some force option is specified or something. This could be done similar to the current truncate that happens on versions.

I'm wondering if there's a way to implement this without making backwards incompatible changes. either creating a new table, or just adding a new column, but initializing that information on startup.

Anyways, I'm ok with doing this in a separate lib for our needs if need be. If I have more free time maybe I'll draft a PR to see what it might look like

[tianzhou]

What if we adopt the semantic version https://semver.org,

1. Thus the max rollback version can be based on the semantic version. We can just use the existing version field and doesn't require the painful schema migration.

2. It leads to better integration with the application. Normally, applications bump the major semantic version because of the underlying non-compatible schema changes.

[dhui]

@tianzhou using semver is a great idea! e.g. it's well understood and we could provide different rollback options (e.g. first of current major, prev minor, etc)

We could also provide tooling to help people migrate to semver migrations. e.g. default to using the last migration as the current major version. The other option would be to provide a script or shell command to rename migrations so that they're prefixed with 1.