Ask HN: How does your development team handle database migrations? • Hacker News Board

Re: Ask HN: How does your development team handle database migrations?

#21

Post by 1337shadow » Fri, May 10, 2019, 7:06 PM UTC

It doesn't matter what tool you use, as long as you have automated migrations as part of the automated deployment process.

A lot of the implementations look like this:

create a migrations directory; add an initial migration script in it; make a migrate command to execute before service starts but after the backup.

The migrate command recipe: create a migrations table in the db if it doesn't exist, otherwise fetch the list of migrations that have been applied inside this database; then, apply the initial migration script if it's name is not found in the said migrations table, and insert its name in the migrations table so that it will not be executed again in this database by the migrate command.

Re: Ask HN: How does your development team handle database migrations?

#22

Post by jolmg » Fri, May 10, 2019, 7:06 PM UTC

Would be cool to have git for databases.

"oh no! our migration deleted columns without re-representing that data in the new manner, and our users have already done changes to the database so we can't simply restore from a backup!"

quick!

  dbgit checkout -b fixed-migration before-original-migration
  # *run fixed migration*
  dbgit rebase --onto fixed-migration after-original-migration master

day saved!

If only it were so simple.

Re: Ask HN: How does your development team handle database migrations?

#23

Post by rocgf » Fri, May 10, 2019, 7:07 PM UTC

For Java projects, the most common one tends to be Flyway, in my experience. There's also Liquibase that I've heard of, but never used.

Flyway is okay in my experience. Can't complain about it, but I can't praise it either, it just does what you'd expect.

Re: Ask HN: How does your development team handle database migrations?

#24

Post by tylerFowler » Fri, May 10, 2019, 7:10 PM UTC

I feel like Terraform would be a fantastic tool for this but never found the time to look into what it would take. Less so for complex data changes maybe (or maybe not) but for things like basic schema changes certainly.

Re: Ask HN: How does your development team handle database migrations?

#25

Post by bungie4 » Fri, May 10, 2019, 7:11 PM UTC

I recently had to update 3 db machines with the updated schemas from one, and the data from a third. Then copy the finished version over to the first and finally the third.

Turns out Visual Studio has a 'diff' generator for both schema and data. Holy hell that worked the treat.

Re: Ask HN: How does your development team handle database migrations?

#26

Post by sebringj » Fri, May 10, 2019, 7:16 PM UTC

The one pain is having to manually create a cut off period for migration scripts, meaning when to start fresh from a single schema and restart the migrations again. This is basically free food for some incubator at HN so if they are going to make some breakthrough product, make it easier to not ever have to worry about that and it will be worth me posting this.

Re: Ask HN: How does your development team handle database migrations?

#27

Post by abetlen » Fri, May 10, 2019, 7:20 PM UTC

gigatexal wrote: post #9

By hand and with careful consideration. Nope wait we use Alembic. It’s actually pretty good. I like the notion of not doing any data destroying migrations. For example if you are adding a column that replaces a different one keep them both. Then at a later time when no code paths touch the old column and that can be proven drop the column that was deprecated. It’s safer that way. But I’ve not seen this done in practi…

We use Alembic as well and it does make the process easier. I like the suggestion of not applying destructive migrations immediately.

Just out of curiosity, has your team used Alembic's branching system with any success?

Re: Ask HN: How does your development team handle database migrations?

#28

Post by whitepoplar » Fri, May 10, 2019, 7:22 PM UTC

jolmg wrote: post #22

Would be cool to have git for databases. "oh no! our migration deleted columns without re-representing that data in the new manner, and our users have already done changes to the database so we can't simply restore from a backup!" quick! dbgit checkout -b fixed-migration before-original-migration # *run fixed migration* dbgit rebase --onto fixed-migration after-original-migration master day saved! If only it were so…

isn't this what datomic does?

Re: Ask HN: How does your development team handle database migrations?

#29

Post by alanfranz » Fri, May 10, 2019, 7:22 PM UTC

What's your precise problem?

Migrate in a backwards compatible manber, so that version N of the app works with N+1 schema (eg add a column, but don't destroy existing ones, use triggers to keep data aligned). When all nodes for an app are are at N+1, you can make a new version with destructive changes (that would break N but not N+1). There's a Fowler article about this.

Re: Ask HN: How does your development team handle database migrations?

#30

Post by liyanchang » Fri, May 10, 2019, 7:24 PM UTC

I've been really happy with how my current company[0] has been doing migrations and I've seen a couple others do it but it seems like it should be more widespread.

Database Schema as Code

Instead of writing up and down migrations, you define what the end state should look like. Then the computer will figure out how to get here. This is just how the industry started managing server configurations (Puppet) and infrastructure (Terraform).

We use protocol buffers so it was pretty straight forward to have a definition of what our tables should look like. We have a script that figures out what the delta is between two states (either proto files or a db) and can calculate the schema migration SQL (e.g. CREATE TABLE, etc).

From there, we run it through a safety check. Any unsafe migration (either for data loss or performance issues e.g. DROP TABLE) requires an extra approval file.

There's no real difference between an up migration and a down migration (except that one tends to result in an unsafe migrations). It's calculable at CI time so we can give devs a chance to look at what it's going to do and approve any unsafe migrations. API compatability checks enforce that you need to deprecate before you can drop.

DML, that is data changes, are handled via standard check in a sql file and CI will run it before the code deploy and after the schema migration.

Alembic is the one other place I've seen this concept (a couple others mentioned this) so it's not new, but surprised I haven't seen it more places.

[0] Shameless plug: We're hiring if you're interested in changing how healthcare is paid for, delivered, and experienced. https://www.devoted.com/about/tech-jobs/