How do you deal with migrations practically

[OscarVanL]

I am considering using sqlboiler for a new greenfield project having previously used NoSQL.

I would like to get a better idea of how people handle migrations in practical production terms.

I deploy my applications in K8S with HA, meaning I have at least 3 pods for the application, and it uses rolling deployments (a new pod gets brought up, then once it is ready the old pod is taken down, repeat until all pods are updated). AKA Blue/Green deployment.

Now I am trying to reason about how migrations work with Blue/Green deployments and SQLBoiler and am getting a bit stuck.

option 1:

1. Create migration using some tool
2. Run migration against my dev environment
3. Run sqlboiler against the dev DB to generate new ORM, fix application code where necessary.
4. Have migration run at service start

However if I do this, while the deployment is ongoing I will cause temporary unexpected behaviour for the pods that are still running the old schema (crashes or invalid data being read/written) due to lack of backwards compatibility.

So then there's option 2:

• The same as option 1, but as soon as the first pod is started and begins the migration, it locks the database for pods not running the new version of the ORM.

This would ensure consistency but would mean a temporary outage, which is unacceptable.

Option 3:

1. Create the migration, but do not apply it.
2. Create the sqlboiler ORM, but keep two versions of the ORM in source, one for the old non-migrated schema, and one for the new migrated schema. Use which version of the schema exists in the DB to select which version of the ORM to use.
   E.g. models/v2/<sqlboiler generated> and models/v3/<sqlboiler generated>
3. Litter your codebase with if-else statements to use different versions of the ORM depending on which version the database is currently running.
4. Deploy all services so they are all running the codebase with two compatibility ORM copies (do not run migration at startup)
5. Finally, run the migration once all pods have the compatibility logic deployed.
6. Now remove old compatibility/duplicate ORM code.

Option 4:

• Get better at only making backwards-compatible schema changes.
• In practice I think this only works to limited extent. At some point you will need to make significant changes that can't be backwards compatible. Perhaps you just accept some downtime in these scenarios?

Further questions:

• If the rolling deployment happens in quick succession, how do I ensure only one pod perform the migration at a time? Do migrations typically use transactions or locking to avoid this race condition?
• Can I run SQLBoiler against a SQL DDL or migration file without actually migrating the database first?

[stephenafamo]

Option 1, causes unexpected behaviours. Probably safe except if the "unexpected behaviour" would cause huge problems

Option 2 requires a bit of downtime. I think this is unavoidable if you're running multiple services on one database and you want to ensure that the version is always correct.

Option 3 doesn't seem practical. But for larger backwards-incompatible changes, perhaps something like that is necessary.

Option 4 sounds the same as Option 2. I think it makes sense the most.

Other questions:

If the rolling deployment happens in quick succession, how do I ensure only one pod perform the migration at a time? Do migrations typically use transactions or locking to avoid this race condition?

Migration tools tend to have a locking system to prevent issues like this.

Can I run SQLBoiler against a SQL DDL or migration file without actually migrating the database first?

SQLBoiler can only be run against a DB. Technically, if you built a driver for DDL statements, then it could work with that too.

[OscarVanL]

Thank you! 😀 That is about what I expected.

I suppose this is just a difficult problem and there is no magic solution apart from lots of planning.