Atlas – Terraform but for Database Migrations

It will be interesting to see if this approach is defensible and better than other strategies, but I’m not holding my breath.

Learning HCL for Terraform is one of the worst parts, IMO. It’s like Dropwizard config language made a baby with JSON-but-not-quite-JSON.

For now, I’m going to hold back on this particular time investment.

P.s. Is this really another project called “Atlas”? The number of times I’ve encountered projects sharing this name over the course of my career is just depressing. Too generic to be any degree of informative or unambiguous, definitely wish it was called Schemmaform instead, a way better name, and took me all of 5 seconds to come up with.

In my experience, this sort of thing breaks down when you have a large production database that requires carefully crafted migrations to avoid affecting the existing system and taking too many locks.

For example, with Postgres some indexes have to be performed with "CREATE INDEX CONCURRENTLY" or "DROP INDEX CONCURRENTLY", which cannot be done inside a transaction. (Also, a "CREATE INDEX" like this can fail halfway and leave behind an invalid index that must be manually deleted.) Whether this must be done with "CONCURRENTLY" or not isn't something the tool can know.

In some cases, a change must done in multiple steps. For example, if you change a column from "NULL" to "NOT NULL", you have to provide a default value. Updating the database with a default value can in fact be a huge operation that might even have to be done in multiple stages to avoid locking rows for too long. There's no way to easily express this in a DSL. M

Then there's the database support. A tooo like this needs to support a huge range of features. Postgres has extensions (CREATE INDEX ... USING), special index operator classes, functional indexes, partitioning, and so on. I've seen many ORMs or SQL adapters (ActiveRecord/ARel, Squirrel, Goqu, Sequelize) try to be smart with how they let you build SQL from high-level code, but they all fail to cover all cases. (Recently I needed to do "ORDER BY CASE ... END" and was using Goqu, which has support for case expressions, but did not support sorting on them.)

So while a tool like this might be good when you're just starting out, for a "real" app you want to avoid this sort of automation, because the tool is almost certainly not going to be smart enough.

I'm a fan of non-magical tools that let me just write SQL migrations, like dbmate and Goose, because that gives me full control. Having a tool to magically figure out the diff isn't super helpful, because I really need to know the diff myself when writing the migration, in order to make it predictable. It's simply more convenient to specify the order yourself.

I wish people would explain why they created their project and what their pain points were with existing alternatives. All I can find is "Contrary to existing tools, Atlas intelligently plans schema migrations for you, based on your desired state."

What's so bad about writing an explicit migrations using something like Flyway? I'm a fan of declarative configuration for the most part but it doesn't seem that beneficial here to me. Sure, using it to modify the schema to the desired state is fine from just the schema perspective. But there is also data you have to worry about. Like someone else stated, the rename example is probably the most trivial example. How would the tool know if I wanted to delete one column and add another or if I wanted to rename the column?

Another more complicated example is what if I had a column called created_at that stored a string and I wanted it to store a date instead. How would atlas know that just by changing the type? I might want it deleted and recreated. But if not, how would it know what format I stored the string in and how to parse it?

For the rename problem, each column could have it's own id so if the id doesn't change it's a rename. Or it could ask while running the migrate command and then store those answers somewhere to be used when deploying to production which seems like a pain. Something like that might work for changing the data type too. You have two columns with the same name and different ids. The new column could have something to specify to transform the data from the old column.

(I know it's a terrible idea to change the data type of a column in production, but the same idea applies if you want to copy the data converted to a new type to a new column, get your production code using that new column, then delete the other column.)

Either way, this doesn't seem to by much over the classic migration approach. You still have to think about how the data is going to move or be manipulated. And with the classic approach they generate a schema so you can see the final state of your database and make sure it's what you want. With the atlas approach you have to approved the planned migration which is basically the same thing as making sure the schema generated is correct using the classic imperative approach.

Seems very similar to https://www.prisma.io/migrate.

I thought the established wisdom is to make schema migration compatible with both the old app and the new app, whenever it is possible? E.g. you can safely add a nullable column, and it shouldn't trip up the old app nor the new app, unless you are using some crappy ORM that does "SELECT *", or you are on an older MySQL version that may take hours/days/weeks to rewrite the whole table just to add a nullable column.

https://github.com/fabianlindfors/reshape, on HN front page a couple weeks ago, has some nice tricks to help with the incompatible cases.