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

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

#81

Post by nickserv » Fri, May 10, 2019, 9:36 PM UTC

PaulMest wrote: post #76

liyanchang wrote: post #30

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 infrastr…

I am guessing that you are probably not using Python/Django... but is this any different than what Django offers? Django allows you to define your models (schema) and then run a command that will generate the migrations. If you don't like the migration that was generated, you can modify it. You can customize up and down operations. There are also tools that will take an existing database and generate the Django model…

Django migrations can be problematic because they're meant to be sequential and have interdependencies. I've had problems merging multiple feature branches because of this, even though there are no code conflicts.

A system like Saltstack or Puppet for databases would not have checked in migrations, these would be generated on the fly at deploy time.

So you could very well have multiple state changes in a single run, by comparing actual DB state and desired DB state, then creating the SQL code as needed for that deployment.

Honestly not having to fiddle with the migrations table on a live server seems pretty nice ;-)

This could very well turn out to be Django's next gen migration tool...

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

#82

Post by darkr » Fri, May 10, 2019, 9:40 PM UTC

We wrote a tool that pulls in several open source tools, the most important of which is sqitch[1] - a beautifully simple Perl based tool for managing migrations that supports rollbacks and verifications - all written in SQL.

All hooked up into CI, as well as being available from the CLI for developers, so they can open a PR and get feedback, as well as seeing what locks are required for a given migration.

1: https://sqitch.org/

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

#83

Post by evanelias » Fri, May 10, 2019, 9:41 PM UTC

PaulMest wrote: post #76

liyanchang wrote: post #30

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 infrastr…

I am guessing that you are probably not using Python/Django... but is this any different than what Django offers? Django allows you to define your models (schema) and then run a command that will generate the migrations. If you don't like the migration that was generated, you can modify it. You can customize up and down operations. There are also tools that will take an existing database and generate the Django model…

That's great initially, but problems definitely crop up at scale:

* What happens when your company creates new systems that aren't Python/Django? You can either still shoehorn all migrations into Django models, or have multiple separate schema change processes/pipelines... both options are not good.

* If someone makes an out-of-band schema change manually (either by accident or to do a rapid hotfix), you're no longer on a known version. Not sure about Django, but when this happens, most traditional migration systems cannot operate without additional manual cleanups... whereas declarative tools can inherently transition any state to any other state.

* Depending on the DBMS, with large tables and certain types of ALTERs, using an external online schema change tool is greatly preferable to just running an ALTER directly.

* Does Django support sharding / the notion that a change to a sharded model must be made to multiple shards?

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

#84

Post by brlewis » Fri, May 10, 2019, 9:46 PM UTC

theclaw wrote: post #80

We use dbup[0]. Its philosophy[1] is that you should treat your DB changes like code changes in source control, and only perform up migrations. I agree with this. We previously spent a lot of time and effort writing down migrations that were never, ever used. If you need a down migration, take a backup before running your up migrations. We're a C# shop. Our DB migration scripts are simply named with a datestamp and a…

>If you need a down migration, take a backup before running your up migrations.

Aren't you missing some steps? Or does your db back up schema structure and data separately?

  1. Make sure nobody can insert/update/delete anything
  2. Take a backup
  3. Run your up migration
  4. Thoroughly test everything
  5. Allow insert/update/delete again

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

#85

Post by wvenable » Fri, May 10, 2019, 9:54 PM UTC

liyanchang wrote: post #30

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 infrastr…

Can that handle column renames? Most schema-to-schema diff tools can't tell the difference between a rename and a delete/add.

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

#86

Post by nickserv » Fri, May 10, 2019, 9:55 PM UTC

evanelias wrote: post #37

liyanchang wrote: post #30

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 infrastr…

I call this declarative schema management, since the repo declares the desired state, and the tooling knows how to reach this state. This concept is finally catching on lately, although some huge companies have already been doing it this way for quite some time. Facebook is a key example; they've managed their schema changes in a pure-SQL declarative fashion, company-wide, for nearly a decade. I'm developing a suite…

Curious, how do you deal with renaming fields or tables?

This is a (minor) pain point for traditional migration systems.

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

#87

Post by barrkel » Fri, May 10, 2019, 9:55 PM UTC

We use Rails migrations, which means incremental deltas rather than automatic derivation of the result. Usually, our more interesting migrations include data transformation, and not merely adding / removing tables, columns and indexes. For example, perhaps a field needs to be denormalized on one of its associations to speed up queries, or a 1:1 relationship now needs to become 1:n. I think it's less easy to build declarative tooling around this.

We have a tenanted architecture, so we need to run the same migrations on lots of different databases of wildly different sizes and data complexity. We test migrations by restoring a snapshot of a sample of customers and running the migration through, and some smoke tests before and after that can be compared.

More recently, migrations are becoming a release bottleneck. That is, they take too long to run within a downtime window (fintech, so Sunday afternoon is when releases go out). We're looking at building tooling around Percona OSC, and using triggers to maintain data invariants for data migrations until code is able to catch up.

Migrations aren't what really slows us down though. Instead, it's data volume, and the difficulty in writing efficient queries while also providing a flexible UI experience.

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

#88

Post by barrkel » Fri, May 10, 2019, 9:57 PM UTC

liyanchang wrote: post #30

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 infrastr…

Data migrations? Denormalizing columns from one table to one or more child tables, possibly more than one relation away? Switching one set of fields in a single table to be in a different table via a relation, converting something from 1:1 to 1:n?

The concept appeals to me, but it only seems to work for trivial migrations.

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

#89

Post by Guillaume86 » Fri, May 10, 2019, 9:58 PM UTC

.NET/MSSQL shop here:

Our source controlled files contain the database definition:

- a dacpac file generated by SSDT for the schema

- sql scripts for seed data (which changes a lot in our case), generated by a custom CLI tool (uses bcp.exe).

We have a "dev" database that is basically the "master". We have a CLI tool to generate the database definition files from the dev database. When someone make/need changes in the database, he makes them in the dev DB then call the CLI tool to update the source files.

When publishing a version the dacpac and data script are included. Migrations are created on demand (no need to go through each version, skipping versions is common). Our migration tool create reference databases of the source and target versions, then generate a schema diff script with SSDT and a data diff script with ApexSQL Data Compare. We can review/editthe migrations scripts before applying migrations.

It works well enough that we run automated migrations at night (with backup before/restore after in case of issue).

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

#90

Post by notJim » Fri, May 10, 2019, 10:03 PM UTC

abetlen wrote: post #63

alanfranz wrote: post #29

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.

I'm more interested in hearing about what the workflow is like for developers on larger teams. Do they each work on their own features, write separate migrations, and have a DBA approve and merge them.

When I worked at Etsy (a couple years ago now, so this is out of date), devs wrote `ALTER` statements and included them in a ticket. Once a week, the DBAs would run all the migrations. The only real things I remember worrying about was making sure to set a default if you were adding a column, and if you were changing a large table, it'd take a long time.

My current company uses mongodb, so migrations aren't a thing. It's pretty nice, TBH.