How to solve database migrations in the cloud era - call for proposals

[hedwigz]

With Atlas, we aim to provide a complete solution for database migrations.

When developing an application/service with one developer it usually suffices to use simple migration techniques such as the ones that come built-in with most of the popular ORMs out there. but, as teams grow in size, and their infrastructure grows in scale to multiple micro-services and multiple databases, it becomes more complex and error-prone to use this kind of migration tool.
For example, consider a service with a Django backend, a GraphQL server written with NodeJS+Sequelize, both using the same database and even the same tables.
When developing a feature in one microservice (one that requires a DB migration) you have to also make a change to the other repository.
There are many tools and products out there that try to solve this problem, Yet, there is no "golden standard" for DB migrations. everyone seems to solve it their own way.

Atlas, using the HCL format, inspired by Terraform, aims to unify the data schemas across multiple repositories (think about an organization with 3 databases and 10 micro-services in 3 different programming languages and therefore 3 different ORMs).

The goal of this discussion is to provide the stage for the community to propose ideas, features, and suggestions as well as describe what is missing or what is good with other tooling and/or approaches.

We'd love to hear:

1. What tools do you use today for migrating your databases - are you happy with them? what is missing in your opinion?
2. Where and when do you run your migration tool (CI/CD pipeline / init container / manual script / init of service)
3. What are the pros and cons of this method
4. What tools did you previously use and did not work well or why did you change?
5. What are the main pain points you find in the migration area today? Which types of failures have you had in the past/today, and what do you wish was exist in the migration area that could solve this problem?

This is an open discussion and as such - any feedback is more than welcome 🙂

Thank you,
The team @ Ariga.

[hedwigz]

I'd like to start by sharing some of my experiences.
In my previous workplace, we had 5-10 microservices (mostly go and node but also python) and a 1 Postgres DB, 1 Redis, and RabbitMQ.
The only service that connected to Postgres was the Go one, which simplified migrations.

1. We started with GORM built-in migrations and then moved to golang-migrate
2. init of service
3. regarding GORM auto-migrate: Doesn't require any effort, the code base of the backend defined the models and the automatic migration just worked. defining anything other than basic columns was done manually (e.g. indexes). It always felt weird to have auto migrate because it is so implicit (and I personally don't like "magic"), but it worked fine. The other thing that bothered me is that you can't review (in PRs) the changes that are about to happen.
4. I don't recall why we changed from GORM to golang-migrate and I wasn't in charge of that.

[masseelch]

We had 6 services all working on the same database. Each service had its "own" tables it was supposed to manage and this sometimes caused troubles, because some of the migration tools wanted to delete everything else, since they thought of the whole database being theirs (doctrine for example).

1. doctrine, manual SQL files managed with golang-migrate
2. in CI
3. A lot of manual work, double checking every single migration file in order to guard against accidently column or table drops
4. the above mentioned, but it still is running like this today

[sergisimo]

We have five services using the same MongoDB instance, with different databases and users. This architecture simplifies the migrations process since each service is responsible for its changes.
1. We use "https://github.com/xakep666/mongo-migrate" for the Go services, and we use the built-in Mongoose migration tool for the Node.JS services.
2. We run migrations on CI for Node.Js services and init service for the Go services.
3. We are evaluating both methods. The main con for CI is that in case of migration failure, you'll have the old version of the service running with maybe some of the new migrations already executed. The same happens but the other way around with executing it on the init service. Our conclusion here is doesn't matter when you execute if you have a proper rolling back system for the migrations you already executed. This get's complicated when you have to execute a non-reversible migration.
4. We used "https://github.com/stoleruradu/firegate" a powerful CLI tool to simplify Firebase Firestore migrations. We moved away since we migrated our databases to MongoDB.
5. Our main pain point is that many tools don't have an automatic rolling back system, and you need to run it manually in case of migration failure. If you have a large data migration, you need to stay until it finishes, and you can't run it overnight. This pain point has made us fix the migration instead of rolling it back since it takes us less work, and we can deploy the new code.

[hedwigz]

Hey @sergisimo Thanks for the detailed comment! 🙏

Indeed when running a migration there is a time when you have both your previous app and the new version running on the same schema.
This is why it is recommended to make "additive" changes and destructive changes manually.
Can you please elaborate on 5?
Specifically why did you migration failed?
Why did you need to run the large data migration?

Thanks again, this feedback is much appreciated!

[sergisimo]

Hey @hedwigz no problem, happy to help!

Specifically why did your migration fail?
Since we are a quick scaling startup and we use a NoSQL database, sometimes we do fixes straight on DB manually than can cause possible data corruption. If then the developer forgot to do all the null/undefined checks on the migration that can cause some migrations to exit with an exception while running.

Why did you need to run the large data migration?
We are working on a migration from Firebase to Mongodb which requires sometimes the re-indexing of the IDs to have better performance with MongoDB which can cause some migrations to be less efficient, especially because Firebase writes are really slow. If you add this to large datasets it leads to migrations that take between 1 and 3 hours.

[crossworth]

I'm relative new to auto-migration (only have experience with Hibernate, Ent and a few tests with GORM), so I will not comment about it.

Right now we have a few services, a few with database shared.

1. What tools do you use today for migrating your databases - are you happy with them? what is missing in your opinion?
Laravel migrations when using Laravel, Flyway when using Java and migrator when using GO.

Laravel:
Laravel migrations have a few small details that works really great, the --fresh flag is an good example, it recreates the database from the start (a fresh database).
I sometimes forget a field during the implementation, I dont want to add a new migration for it, I just want to edit the migration and re-run it, with --fresh I can do it without problems.
It's a feature on the developer side of things, but it's worth noting.
Other features that I like:

• Dumping the resulting SQL file
• Squashing migrations

Flyway:
I dont like Flyway that much, but I like the feature of placeholders, the ability of storing "project" related information on env vars and using them inside the migration scripts is something that I really like, I use for things like default account login, password.

migrator:
For me is the best migration library in go right now, it's dead simple, easy to reason about and allow for different patterns, you can create migrations in GO (that's something that Laravel allows as well and it makes hard migrations easy to handle, cases where we need to query for data during runtime, I know that this remove the safety of reproducibility, but sometimes is we have to do it).

I really like that we dont have to provide down migration, quoting the readme:

We also think that it's a good idea to follow an "append-only" philosophy when coming to database migrations, so correcting a defective migration comes in the form of adding a new migration instead of reversing it.

e.g. After a CREATE TABLE foo we'll simply add a new DROP TABLE foo instead of reverting the first migration, so both states are reflected both in the code and the database.

It makes the migration more like git, where we revert going forward and not backwards, this creates a history if you think about it.

To me seems like that down migrations only works for the easy cases, the reversible cases, the cases where a "static migration analyzer" could prevent in the first place.

2. Where and when do you run your migration tool (CI/CD pipeline / init container / manual script / init of service)
Init of the service, embedded on the artifact if GO/Java or one of the entry points of the container if PHP.

But if multiple different services talk to the same database, only one is responsible of handling the migration (usually the one that uses the database/schema most).

3. What are the pros and cons of this method
It works with a single or multiple instances of the same service, I dont need an additional process/state.
If feels like a initialization check-list, where the service connects to the database, run the migrations if possible and initialize all the others dependencies. If one of them fails, the service stops, so its easy to track the problematic service and rollback (the service not the migration).

It kinda works as a contract, the service is required to work with the migrations embedded on it.

4. What tools did you previously use and did not work well or why did you change?
Manual scripts/SQL files.

5. What are the main pain points you find in the migration area today? Which types of failures have you had in the past/today, and what do you wish was exist in the migration area that could solve this problem?
Down or rollback migrations are something that should be re-considered, I think if you use Postgres with transactional DDL, you dont even need them, just run the migration in a transaction and on error do a database rollback, I know that this is a feature for Postgres and not all databases support it, but in my opinion it should be the standard, let the database do the rollback.

Most of the reversible migrations could be generated by the migration engine (and I know that you guys already do something like this today), the hard cases, are already hard for humans and should be impossible for the migration engine.

Another point is that when on the early stages of development I try different things, the database schema is one of them, so helpers commands (like the --fresh from Laravel) improve the productive.

About failures, I dont think is a problem for us right now, we do integration tests that connects and talk to the database, this tests include the migration, so we dont have many problems with it, I mean, we have problems, but it's user/logic error that is fixed before a deploy.

Things get complicated with converting from NULL -> NOT NULL/changing column type or if we share the database with different services or even with multiple instances on a rolling deployment.
To solve this we do multi-step-migrations:

CHANGE COLUMN NAME:

1. First add a new column.
2. Migrate the services and write the same data to the old and new columns at the same time.
3. Delete the column after all the services are migrated and remove the code that writes to the old column.

NULL -> NOT NULL:

1. Write migration that populates all the rows.
2. Change the column to not null.

I'm sure that there is something that could be improved on this process, maybe something with views/instead trigger and maybe this could be handed by the migration engine.

And a final note, something like a "golangci-lint" for migrations/SQL would be really cool as well. We already know a few things not to do, when moving to schema first, we get more knowledge of what we are representing on the database/code and eventually we can use it to improve the usage in the first place.