Rollbacks in kubernetes

[sondrelg]

Hi!

I was surprised to see rollbacks fail, due to MigrateError::VersionMissing. As a fix, I'm now running migrations like this:

match sqlx::migrate!("./migrations").run(&mut *tx).await {
    Ok(()) => {}
    Err(MigrateError::VersionMissing(e)) => {
        warn!("Skipping migrations as we're behind the last-applied version: {e}");
        return Ok(());
    }
    Err(e) => return Err(Error::from(e)),
};

Is this the recommended way to handle this issue? I couldn't find any other references to it.

[abonander]

There shouldn't be much cause for surprise. You're rolling back to a version of the application that doesn't have the migration. If the migration just failed silently then it could lead to data loss or other runtime errors when the database schema doesn't match what the application was built for.

The prevailing advice is to not actually roll back deployments, but to patch and roll forward. This works much better with how Kubernetes handles application lifecycles.

We have support for reversible migrations because people demanded it, but we don't use them in practice. The problem is, migrations are almost always destructive. Most of the time, you can't just undo them without suffering some form of data loss. Even if a migration just added a column, rolling back means dropping the data in that column, which is probably important because otherwise why store it in the database in the first place? If a migration dropped data, or moved it around, or transformed it in a lossy manner, you can't just magically reconstruct the information that was lost.

Even with reversible migrations, you'd have to roll back using the broken version of the application before downgrading the image, which means your application has to know when you're rolling back. You could implement a preStop hook, but that's invoked whenever a container is being stopped, which could be for anything: an upgrade, a node replacement, a down-scale event, etc. I think you'd have to query the Kubernetes API to learn that it's due to a downgrade, and then decide to roll back migrations. It's so much easier just to roll with the punches.

If we had the bandwidth, we'd create a guide to cover this all, but in general you should have a plan for when things go wrong, before they do. Hopefully you have backups.

Assuming the migration wasn't too destructive, you should be able to resolve this manually. You can delete the row from the _sqlx_migrations table and undo the changes it made, or just patch the version of the application with that migration and re-deploy.

[sondrelg]

Sorry @abonander, I think I did a bad job of communicating my issue above. I see that I've use some ambigous language and probably should have added a bit more details. Thanks for getting back to me so quickly though, I'll try to rephrase!

We deploy our applications using canaries. When a canary-pod is created, it runs new migrations as a part of the initialization of the app, appending a new version to the _sqlx_migrations table.

The canary's rollout is designed to be very slow, and pods in the old/existing replicaset still run in parallel for up to 30-60 minutes. Meanwhile, the old/existing replicaset will scale up and down with traffic as normal. The canary slowly ramps up the amount of traffic directed at it, until it eventually takes over 100%.

My surprise came when the HPA/autoscaler added a new pod to the existing/old replicaset during a canary rollout, and that added pod failed to start. That version of the app did not have the latest (up) migration, since it was built earlier than the canary.

As I understand it, the same problem would happen if the canary failed and we "rolled back" to the previous replicaset; and I think this would also happen if we patched the deployment with the new image version, as the article suggests 👍

I'm totally fine with this design from sqlx's side - I just wanted to ask if this error handling seems to make sense, given the use-case ☺️ Mostly I'm scared that this home-made solution has some unknown unknowns that will bite me, since my app is already serving a decent amount of traffic and recovering from database issues can be tricky sometimes. Would this be the best way to handle this case, or would you perhaps handle it differently?

Thanks again for the swift reply, and for all the work you do on this crate!

[abonander]

Yeah, you buried the lede pretty badly there. My original reply sounds really condescending now.

I just wanted to ask if this error handling seems to make sense, given the use-case

Sorry if you were expecting a prescriptive answer here, but that's a judgement call you're going to have to make. I looked at a lot of sources, mainly ones for Java apps, and I got the distinct impression that this is far from a solved problem. There's not a ton of good advice out there about how to manage database lifecycles with a Kubernetes application besides either "do it very carefully," or... "don't".

In the first camp, the prevailing advice is to ensure that you always maintain N-1 compatibility (I detest videos for technical discussion but this is one of the few results that actually talks about how to manage upgrades), i.e. the new version of the database doesn't break the existing version of the application. In this case, it should be safe to ignore the error, with one caveat: you must be certain that the application is only one version behind.

We don't currently expose this in the VersionMissing error, though you can infer this if you're using sequential versioning for migrations. I'm open to discussing whether we should return an array of missing version numbers instead; I was working on improvements to migration errors in #3357 when I got sidetracked.

This is complicated by the fact that you could have multiple migrations added to the source between releases, and in practice we tend to prefer smaller, more atomic migrations, each named after a specific table or change, so a blanket assumption of N-1 compatibility wouldn't really work.

This makes me wonder if we should support a two-component version number for migrations, so you can indicate whether or not a migration is breaking directly in the version number. The minor version number could still be a timestamp so that concurrently created migrations don't conflict. Then the application could indicate what range of version numbers it supports just like a Cargo dependency specification, and the migrator would only throw an error if that cannot be satisfied.

You could retrofit this into the existing version numbers by splitting the range of values, e.g. top 32 bits is the major version, bottom 32 is the minor version/a timestamp. Or split by decimal digits instead since that would be more intuitive to a human. The most annoying part there is that sqlx migrate add wouldn't understand that and would just create a new timestamped migration... I think it might not even be smart enough currently, to handle when the latest version is greater than the timestamp it would generate.

We've talked about other major changes to the migrations API, like supporting roll-ups so you don't need to keep hundreds of migration files around forever and take an eternity to spin up a new development database. Maybe we should just take the time to design a new migrator from the ground-up, with an RFC process and everything. If only I had the time.

[sondrelg]

Yeah migrations are tricky. Our aim is to never author migrations that, at a minimum, don't break our version +-1; like you say 👍

I'm open to discussing whether we should return an array of missing version numbers instead; I was working on improvements to migration errors in #3357 when I got sidetracked.

It could be nice to know whether we're one or many versions behind, from the error, but we already querying the _sqlx_version table before applying migrations, so it isn't critical for us. Moreover, like you say, a new release could contain n migrations. Even in an "invalid case" where we're multiple release behind, I think our approach would need to be that we assume the developer knows what they're doing, and to always allow it. I'll take a look at that video when I get time though - interested to hear why only one version behind is important!

This makes me wonder if we should support a two-component version number for migrations, so you can indicate whether or not a migration is breaking directly in the version number

For the case of canary deployments, how would you then change the application of migrations based on the type of migration you're applying?

Something I'm on the lookout for (or might take a stab at creating if nothing exists), is a good AST-based linter that would flag n-1-incompatible changes for me. A few years back, for a Python project, we used this linter, which just does string matching, but works well enough to flag most breaking operations. It saved us from major pain a few times.

From my perspective, the problem with migrations is that there is often a bit of judgement needed to assess whether a breaking changes are acceptable or not. For example:

• I'm not fussed about adding NOT NULL columns in a CREATE TABLE block
• I'm not fussed about adding NOT NULL columns to a table that will be created in the same release, but a different migration file
• I'm not fussed about adding NOT NULL columns to a table that's in production, but isn't interacted with, yet
• I'm very fussed about adding NOT NULL columns to a table that's in production, and heavily used

So while a linter could flag "Hey this operation can be dangerous!" when I add a NOT NULL-column to an existing table, it couldn't easily make good judgements about the second and third example.

I'd like to be notified and have to make a decision about the operation, but I don't see how I could make the migration process itself smarter - this seems mostly like something I'd want to make judgements about when writing the migration files. Happy to be proven wrong, of course ☺️

We've talked about other major changes to the migrations API, like supporting roll-ups so you don't need to keep hundreds of migration files around forever and take an eternity to spin up a new development database. Maybe we should just take the time to design a new migrator from the ground-up, with an RFC process and everything. If only I had the time.

Supporting roll-ups would indeed be great 🙇 Given the time constraints, this does seem like a universal problem, so not sure how much of this would need to be inveted by sqlx itself? Hypothetically, if there was an existing de-facto tool for combining SQL operations, would supporting roll-ups in sqlx mainly involve allowing modifications to the _sqlx_version table during a migration?

---

Thanks for the help! I'll proceed with my current implementation given it seems safe 🙇

[abonander]

Moreover, like you say, a new release could contain n migrations.

Another thing we talked about was supporting folder migrations, so you would have something like:

migrations/
    0001_foobar/
        20250325124300_foo.sql
        20250325124400_bar.sql

So you could still have atomic, tightly scoped files that are executed like normal migrations, but are tracked as a single group. So you would just accumulate migrations in the latest folder until you cut a release, at which point you'd create a new one. That would theoretically allow you to have a hard requirement on N-1 compatibility.

You could maybe keep adding migrations to the latest folder and deploy them incrementally, with the stipulation that they're strictly backwards-compatible. I suppose that's basically the same thing as supporting two-component migrations, just making more efficient use of the filesystem.

For the case of canary deployments, how would you then change the application of migrations based on the type of migration you're applying?

Yeah that's a hard one. I think in general you'd want to break changes into two phases where possible. For example:

adding NOT NULL columns to a table that's in production

The first phase would be to add the column to the table, but allow it to be nullable. Then update the application to use it, and maybe concurrently fill the field for existing rows with a default value. Once that passes canary testing and is promoted to stable, and you're certain no NULL fields remain, only then would you add the migration marking that column as NOT NULL.

In practice, I'm actually not sure how wise it is to add any new columns to a table with more than a handful of rows, because I imagine it almost always requires all existing tuples to be rewritten, especially in the NOT NULL case where you have to add a default value to existing rows. Even in the nullable case, it still has to update the number of attributes and possibly expand the null bitmap for each tuple. In a lot of cases, it'd be more efficient to add a new table foreign-keyed to the existing one.

Supporting roll-ups would indeed be great 🙇 Given the time constraints, this does seem like a universal problem, so not sure how much of this would need to be inveted by sqlx itself? Hypothetically, if there was an existing de-facto tool for combining SQL operations, would supporting roll-ups in sqlx mainly involve allowing modifications to the _sqlx_version table during a migration?

What I had in mind was more of a framework for rollups, and then we could evolve the user experience over time. The core idea was that it would be defined as a migration like any other, but instead of a .sql file it would be a .toml file. This TOML file would specify what the _sqlx_migrations table should look like before (so that the migrator may assert that the current state is correct) the rollup, as well as afterward.

This would then be combined with a <version>_baseline folder, which is a set of migrations that should create an identical database schema as all of the migrations that were rolled up. How this is actually generated is an orthogonal concern: we don't necessarily need to implement it to release rollup support, we could just let the user hand-write it to start. Maybe we add a utility command to migrate up a fresh database using the baseline and assert that it matches an existing one.

I was thinking for an MVP, we could just dump the existing database schema to SQL, organized by table for convenience. That would be easier than trying to simulate the effects of all the migrations and merge them correctly, though we'd have a graph traversal problem because we'd need to ensure tables are created in the right order to satisfy foreign-key constraints.

When applying the roll-up, the migrator would assert that the _sqlx_migrations table matches that specified in the TOML file. It would then drain the table and replace it with a single record representing the rollup itself.

You would deploy a release with the roll-up, but retain the previous set of migrations until the roll-up had made its way through the production database and was stable, at which point you could delete the old migration files. Any new development or testing database would be spun up using the new baseline. You could then make a point to just do this every, e.g., 6 months to a year, and that'll keep your migrations/ folder from growing asymptotically.

I really need to write this up as an RFC issue, because we only just discussed it internally so far.