Re:Atlas – Terraform but for Database Migrations
by throwdbaaway on Saturday January 22, 2022 @06:34PM
(#30041959)
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.
Re:Atlas – Terraform but for Database Migrations
by jrockway on Saturday January 22, 2022 @06:47PM
(#30042066)
I think that's the established common wisdom, but it's not well-enforced by anything, and it's not innate. Everyone learns this the hard way once.
Re:Atlas – Terraform but for Database Migrations
by jokethrowaway on Sunday January 23, 2022 @03:49AM
(#30044665)
I would say a fair share of companies bent on following good practices do that.
It's not all of them or not even the majority but I haven't seen cowboys migrations in 10+ years
Re:Atlas – Terraform but for Database Migrations
by throwdbaaway on Saturday January 22, 2022 @06:51PM
(#30042100)
Right, I did have to learn this the hard way back in 2014.
Re:Atlas – Terraform but for Database Migrations
by kodah on Saturday January 22, 2022 @08:12PM
(#30042650)
You can get by on that "wisdom" for a while, but eventually your DB will garner a significant size and performance impact as a result. That said, even on very active products that takes a while, so there's probably something to blending the idea of one large migration every so often and only making schema compatible changes along the way.
Re:Atlas – Terraform but for Database Migrations
by tianzhou on Saturday January 22, 2022 @09:32PM
(#30043065)
>> In general, I think that database migrations are fundamentally flawed. The database schema and the application should have a schema version that they expect, and rules for translating between versions.
I have the same feeling, that could be a very desirable native feature for DB Engine, but no vendor seems to be interested in addressing that.
Re:Atlas – Terraform but for Database Migrations
by jen20 on Sunday January 23, 2022 @01:30PM
(#30048839)
> The database schema and the application should have a schema version that they expect, and rules for translating between versions.
That… is migrations. The rules for translating between versions are literally what migrations do.
Re:Atlas – Terraform but for Database Migrations
by smrtinsert on Sunday January 23, 2022 @09:04PM
(#30052822)
I have never seen a db rollback. Once you get into prod you're capturing data. A roll ack could lose data. I've only seen forward migrations to fix issues.
What I personally do these days is to write migrations as normal (sql files for the "up" and "down" steps), and then have a `go generate` step that creates a randomly-named database, applies each migrations, and dumps the schema into a file that gets checked in. A test assures that the generated content is up to date with the rest of the repository. This gives you 3 things:
1) PR reviewers can see what your migration does to the database in an easy-to-read diff. (A tiny bit of regexing needs to be done to make PostgreSQL dumps compatible between machines; it puts the OS name and the build time in there.)
2) You have the full database schema in an easy-to-read form. I open the SQL dump all the time to remind myself of what fields are named, what the default values are, etc.
3) Unit tests against the database are faster. Instead of creating a new database and applying 100s of migrations, you just apply one dump file. Takes milliseconds.
In general, I think that database migrations are fundamentally flawed. The database schema and the application should have a schema version that they expect, and rules for translating between versions. That way, you could upgrade your code to something that reads and writes "version 2", but understands "version 3", and then apply the database migration at your leisure, and update the code to start writing "version 3" records. But, nobody does this. They just cross their fingers and hope they don't have to roll back the migration. And honestly, I don't think I've ever had to roll back a migration, because they're so scary that you test it a billion times before it ever makes it to production. But, that testing-a-billion-times comes at the cost of writing new features, and the team that only tests it 999 million times no doubt has a horror story or two.