15 ms·

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

269 points by abetlen 7y ago

What's your team workflow for delivering features that require database migrations. How do you keep migrations from slowing down development as your team grows.

thrower123 7y ago
SQL scripts. It is painful.
- nickserv 7y ago
  The horror. I actually had a dev tell me he didn't trust doctrine (symfony project), so would do it all in SQL. Thankfully the project was relatively new so it was possible to rewrite everything using migrations.
bahador 7y ago
I've used Flyway[0] at my current and previous shops. It seems to work well. [0] https://flywaydb.org/ https://flywaydb.org/
smoyer 7y ago
I've read and reread a great article titled "Evolutionary Database Design" on Martin Fowler's web-site [0]. This article describes database migrations as being a process. We've found that for complex changes, we'll often need a pre-migration and a post-migration (temporally being before the code change and after the code change respectively). We commit the migrations along-side the application code and in our case we use FlywayDB [1]. The only down-side is that this tool doesn't perform roll-back operations automatically. You can always do them by writing another migration that goes forward to the past. Another popular DB migration tool is Liquibase [2]. I don't have much experience with this tool as it doesn't fit our build pipe-line as well but it does support and encourage defining a roll-back for each migration. [0] https://www.martinfowler.com/articles/evodb.html https://www.martinfowler.com/articles/evodb.html [1] https://flywaydb.org/ https://flywaydb.org/ [2] http://www.liquibase.org/ http://www.liquibase.org/ EDIT: HN is the new StackOverflow? I think this is a really important question for development teams and yet I could see it being closed due to the "Questions asking for tool or library recommendations are off-topic for Stack Overflow ..." rule. Sad!
- freedomben 7y ago
  > HN is the new StackOverflow? Could you clarify what you mean by this?
  - smoyer 7y ago
    This is actually a good place to ask important questions that will be closed by the moderators there.
    - barrkel 7y ago
      In fact these are the only questions I'm really interested in asking of other developers any more, and most of the reason why I almost never ask or reply on SO, despite 37k rep. It was looser in the earlier days, but I guess moderators wanted easier to evaluate rules, and it's easier for moderators to decide to come down on the side of moderators than people who ask questions and start discussions.
      - scarejunba 7y ago
        Makes sense, though, right? They want a Q&A site where you can get definitive answers. This sort of discussion-oriented thing is better suited to a forum with threaded replies and whatnot.
        barrkel 7y ago
        I actually think there ought to be levels, where you can access different kinds of conversations at higher rep levels. Or some other way of gating out clueless people. Noise drowns out signal otherwise.
- abetlen 7y ago
  Thank you, the Fowler article is very helpful. Does your team have someone act as a DBA to oversee each migration?
  - 7y ago
config_yml 7y ago
rails db:migrate It’s always one of the first things I miss when I have to work on a non-rails codebase.
tln 7y ago
Migrate during continuous deployment. We use a staging environment, so CD deployed migrations are always run at least once before running on production. Migrations have to be written so that currently deployed code will work after the migration. Eg, to rename a column, add+copy, deploy, then in a second migration drop the column.
archarios 7y ago
At my previous job we either didn't migrate and wrote application level transforms that would update records as they were encountered by users (mongodb) or we had a custom built migration system that ran JavaScript snippets on our shards. The migration system was miserable to work with and it was hard to debug the code on stage in such a way that would allow us to anticipate whatever might be on prod...
- ezekg 7y ago
  This sounds like a nightmare. Why?
  - VectorLock 7y ago
    >(mongodb)
  - archarios 7y ago
    This is a fairly typical approach minus the pain points of our specific migration system for Mongodb as far as I know. We favored application level transforms for the sake of safety and speed. Doing that obviously gets really bothersome as time goes on though.
based2 7y ago
https://github.com/goldmansachs/obevo https://github.com/goldmansachs/obevo https://www.schemacrawler.com/ https://www.schemacrawler.com/
gigatexal 7y ago
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 practice just something I’ve been thinking about.
- abetlen 7y ago
  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?
  - gigatexal 7y ago
    Is branching where it forks and then comes back? I'm still a bit new to the process but if that's the case then yes. The team is still cool to the idea of my way of doing migrations they tend to do the destructive ones and call it a day albeit with the commonsense to do backups for truly data destroying things like dropping of columns or redefining of columns.
louthy 7y ago
We use the RedGate SQL compare tools [1] to compare our new schema to our old one and auto-apply the diffs to the production DB (this is done automatically by our deployment process). To reduce the chance of error we don’t destroy columns or tables. Our application then has an update step which runs on startup for any data migrations (or new data additions), and then updates a version number stored in the DB. The data migrations are super rare. This all means we can migrate from any past version to our latest one: because we have all previous schemas stored in git and n update functions in our app that can walk the versions It’s worked reliably for over a decade and is pretty much entirely pain free [1] https://www.red-gate.com/products/sql-development/sql-compare/ https://www.red-gate.com/products/sql-development/sql-compar...
- atwebb 7y ago
  I've got a fair amount of experience with it and the Change Automation tool they have. Both are fantastic products, the latter for certain scenarios over SSDT itself. Sounds like have had it sorted for a while, my main concerns are when it wants to rebuild certain tables when a simple sp_rename or other step would be sufficient.
  - teddyuk 7y ago
    This is such a common complaint!
- beart 7y ago
  > To reduce the chance of error we don’t destroy columns or tables. Does this mean you have a lot of unused tables and columns deprecated in the database?
  - louthy 7y ago
    No. It’s very rare that columns or tables are deprecated. It’s a very mature web app that basically just grows.
jakswa 7y ago
Depends on the database, in my opinion. Each one has quirks to be mindful of, depending on the amount of data you're migrating/touching. For example, if you accidentally put a 'default' on a column when you add it to postgres, it will lock the entire table while it rewrites every row, inserting that default value. Another common postgres blunder is creating indexes on big tables without using 'concurrently'. This also locks the table for writing and you'll have a bad time.
- adwf 7y ago
  Just for reference, as of PG11 it no longer locks or rewrites for default column creation. It's one of the nice things about Postgres, they're always improving. A few years ago, concurrent index creation wasn't a thing either. Nor were "CREATE/DROP IF (NOT) EXIST" statements for various bits and pieces, but they just keep adding to it over time. In the upcoming PG12 there is REINDEX CONCURRENTLY, so we'll finally be able to fix corrupt/bloated indexes on the fly without having to drop and recreate them.
mbostleman 7y ago
SSDT / DACPAC
pier25 7y ago
We use https://github.com/rubenv/sql-migrate https://github.com/rubenv/sql-migrate for Go You basically write SQL queries for up and down.
wutman 7y ago
This doesn't fit everywhere, but... 1) Never modify existing tables (okay, almost never). 2) Add new tables at will. 3) Pay a consultant to write PL/SQL that glues it all together and blame them for any and all issues.
- teddyuk 7y ago
  3) ha ha
frankwiles 7y ago
Django migrations it’s a truly great tool.
- Myrmornis 7y ago
  Yes it is. (Except when they made makemigrations be like O(2^N) or something in the number of models around 1.9).
stock_toaster 7y ago
We use migrate[1]. [1]: https://github.com/golang-migrate/migrate https://github.com/golang-migrate/migrate
- lovetocode 7y ago
  I second this. migrate works great for me.
deleted 7y ago
[deleted]
was_boring 7y ago
I don't think there is a way to change the database without slowing down development. After all, the data is the most valuable thing you have. Besides that, I tend to follow the same general principals RainforestHQ presented in 2014 https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-database-migrations/ https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-d...
1337shadow 7y ago
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.
- Bjartr 7y ago
  We have an ant script that does almost exactly this
jolmg 7y ago
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.
- whitepoplar 7y ago
  isn't this what datomic does?
- ben509 7y ago
  The main issue is you can't "reverse migrate" creating a table or adding a column as that deletes prod data. So your base tables are always forward migrated, and probably have only basic constraints. With good updatable view support, you can maintain versions of table views and constraints on the views that may be forward, reverse or laterally migrated. (You'd have a different set of views for each version of the schema in use, so each build of the client is connecting to exactly the schema it expects.) Then you'll want to checkpoint the base tables. So the system must track usage of view-set versions to determine when they can be dropped entirely. Since the constraints are enforced at the view level, you have to deal with legacy data that violates current constraints, but was hidden by the views. Then the system can do a checkpoint and delete obsolete columns and tables, moving forward the earliest version you can roll back to. Of course, that all requires good updateable view support, and I don't think any products offer that.
- mchristen 7y ago
  You can run/test your migrations in a transaction and roll back if it doesn't work.
  - jolmg 7y ago
    Not all problems are obvious, though. You can have an app work 100%, your database be completely coherent, and then realize some of the data is missing. It would be cool to not have to be so careful when committing migrations, needing to be absolutely sure that we're not screwing something up. This is similar to how one would be careful of changes done in source code before we learned how to use version control systems like git. I can go completely wild with git, deleting random files, overwriting others with random junk, sharing them to my coworkers, etc. It would only take a few minutes to fix that.
rocgf 7y ago
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.
- cdepatie 7y ago
  Liquibase is pretty solid, my company has used it across a couple of projects. Similar to your Flyway experience, it's not anything particularly amazing but it works.
tylerFowler 7y ago
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.
bungie4 7y ago
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.
sebringj 7y ago
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.
alanfranz 7y ago
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.
- abetlen 7y ago
  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.
  - evanelias 7y ago
    For a "very large company dedicated to moving fast" example, here's what the process looked like at Facebook a few years ago. AFAIK same process today, with one improvement noted below. Background: * Almost everything is self-service by necessity. Except for some high-blast-radius cases, dev teams are able to manage their own schemas without needing MySQL team intervention. This is made possible by having automation that has appropriate safeties built in. * There's a repository (git, hg, whatever) storing schemas. It has a couple levels of subdirectories to organize different database tiers and individual databases. In each of the bottom-level subdirs, there are text files containing CREATE TABLE statements, one file per table. In other words, this is a declarative repo, modeling the ideal state of tables in each database. Process to add or change a table: 1. Just add or change a CREATE TABLE statement, and commit in SCM. 2. Submit a diff (pull request). Someone on your team reviews it, same as a code review. 3. Once merged, the schema change can be kicked off. (A few years ago, a dev would need to run a simple CLI command to tell the automation "please begin working on this table", but I believe this has been automated away since then.) The tooling automatically manages running the correct DDL safely, on the correct machine(s), even in the case of a large sharded table. Devs never need to write ALTER TABLE statements; everything is just based on CREATE TABLE. There was a separate flow (with extra steps, on purpose) for destructive actions like dropping tables or columns.
liyanchang 7y ago
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/ https://www.devoted.com/about/tech-jobs/
- evanelias 7y ago
  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 of tools [1] to provide declarative schema management and "schema change by pull request" functionality, initially targeting MySQL and MariaDB. A few large companies have built pipelines using one of my tools -- including Twilio SendGrid, who wrote about their process in-depth recently [2]. [1] https://skeema.io https://skeema.io [2] https://sendgrid.com/blog/schema-management-with-skeema/ https://sendgrid.com/blog/schema-management-with-skeema/
aerojoe23 7y ago
.net has entity framework an ORM has has migrations. It isn't problem free, but we found workable solutions to our issues. By default EF will throw an error if the model (in code) and the database are out of sync. There is a setting you can set to tell the ef not to care about the version, but then you have to take the responsibility of making sure the old code will run against the new db version. Most of our stuff is adding a new column or table so the old code doesn't rely on it and everything goes fine anyway.
- sebazzz 7y ago
  Yes, we have used that for a bit and for the most part this works fine. We had however a case where several migrations were not applied in production, and that is a mess to figure out. But the development experience, especially at the start of the project (if you don't maintain your seeding code), is awesome. You just point the project at an empty or non-existing database, the schema is automatically created, and any necessary base data can automatically be seeded.
- ozim 7y ago
  But that is most important part of migrations to throw that error so you are aware of it. First thing you fix your db and then push to test server, and you never have broken db on production, NEVER.
sebazzz 7y ago
We use RedGate SQL Source Control. However, we also deploy our applications using MSDeploy and SQL Source Control has no good solution for that. We stitch the migration scripts together manually. Also, no matter how good you control it, your database gets out of sync with the migrations after a while. We also have an integration test which restores a base database and applies the migrations of the current branch on top of it. We're going to research DACPACs to deploy databases. We hope it will be better.
stephenr 7y ago
We used to use dbschema (https://pypi.org/project/dbschema/ https://pypi.org/project/dbschema/) to apply migrations generated by SchemaSync (http://mmatuson.github.io/SchemaSync/ http://mmatuson.github.io/SchemaSync/) using a helper script to simplify capturing a migration from a known base of previous migrations. After submitting a number of bug fix and feature patches to the upstream projects I ended up writing a (IMO better) tool to apply the migration scripts (https://bitbucket.org/koalephant/mallard https://bitbucket.org/koalephant/mallard). We currently still rely on SchemaSync for that part, but it’s been more reliable and it’s ultimately a tool for developers who review the generated sql anyway - the tool to apply them needs to run automated on remote environments.
gregmac 7y ago
Ohh, this is a favorite topic of mine and I'm of the opinion there's no clear best solution (possibly good market opportunity here), only a series of trade-offs. ---- In one project, we use DBGhost (for MS SQL Server). It's like RedGate SQL Compare, but in deployable, self-contained executable form. It does a complete schema sync, so internally we run on every build, and externally can upgrade from every previous release, without the pesky "in between" evolution you tend to get with ordered migration scripts. It's run as part of our upgrade process for every app version, and our deploy package is built from the 'initial database create' SQL script in source control. To make a schema change such as adding a new column, you modify the relevant `CREATE TABLE` script, commit it to source, and that's it. We also use a hand-built pre- and post-deploy script to do anything tricky that can't be automated (renaming a column, copying/converting data from one column to another, etc). Importantly, these scripts are idempotent (eg: `if (old_column_exists) { start transaction; create new column; copy+transform data; drop old_column; commit transaction; }`). We generally avoid major changes like this as much as we can, but it's possible when necessary. We also have DBGhost configured not to drop anything (to avoid mistakes, or dropping customer customization that they do even though our support contract explicitly says not to), and instead write those by hand. This process has been in many dozens of customer-facing releases -- including on-premise customers -- for several years, and 'just works'. DBGhost is a clunky app, but our interaction with it is now entirely scripted (via either build or deployment scripts), and the compare engine part of it is actually very good. ---- In another pretty simple app (that runs on our cloud infrastructure only, with 'production' deployments mirrored in a couple different regions, and usually a single 'dev' deployment though sometimes there are other temporary ones) we opted to only use an idempotent, hand-written script, executed as part of deployment. It has `create table if not exists` statements to setup from scratch, and it also has the transforms for changes done over time (`create index if not exists;` `drop column if exists` etc). We periodically remove the transform statements to clean it up after all deployments have been updated past that point. Even though it's manual, it's actually quite easy to maintain, so long as you're careful about ensuring it's idempotent. The nice part is it typically gets deployed to the dev infrastructure multiple times during the course of working on something, so if there's a problem it becomes obvious very quickly when the deployment fails. ---- There's also another app which uses the more traditional ordered migrations files, which I liked at first but over time I find it annoying. Deploying from scratch installs a really old initial database schema, then proceeds to apply dozens of modifications, many of which overwrite previous modifications. ---- I've also worked on an ordered migrations file app where there was a separate 'create' step for the initial creation (instead of running through each migration). The first time I deployed from scratch I found a bunch of problems where the create script wasn't 100% synchronized with the migrations, but also was synchronized enough that it wasn't possible to 'fix' by running each migration (one of the early ones failed). The only fix was to go through by hand and manually run the relevant migration files. I'm sure there can be better practices to help prevent this (eg some compare step on build), but this happened years ago and still sticks with me as a terrible way to do migrations.
deleted 7y ago
[deleted]
Myrmornis 7y ago
Django. Django generates the schema migrations automatically from the changed table schema definitions that the developer used when testing their branch, and those rarely cause problems. Data migrations need to be tested against staging DBs with realistic data. But neither is really a major pain point: individual developers create and commit the migration files while preparing their branches for review.
- djstein 7y ago
  really the best part about Django
- nickserv 7y ago
  Only major gotcha is when renaming fields: it'll drop the old field then create a new field, by default. You have to do the renaming by hand, trivial but can't be forgotten... Which is why you always test on staging first of course.
  - haihaibye 7y ago
    Makemigrations detects if the type is the same and asks you if you renamed it. So, when changing columns do the type change and name change in separate migrations and you won't have to do it by hand.
ben_jones 7y ago
Poorly
wesleytodd 7y ago
If anyone is looking for a javascript specific solution, I maintain a module originally written (and abandoned) by TJ. $ npm i migrate https://www.npmjs.com/package/migrate https://www.npmjs.com/package/migrate
- smt88 7y ago
  Why does it matter what the tool is written in? If anything, I'm a lot less likely to use something written in JS when correctness is absolutely paramount.
wenbin 7y ago
Django and other modern web development framework handles db migrations pretty well. $ python manage migrate
tshanmu 7y ago
we have had good success with liquibase[0] and flyway[1]. [1] http://www.liquibase.org/ http://www.liquibase.org/ [2] https://flywaydb.org/ https://flywaydb.org/
theclaw 7y ago
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 change description. The scripts are added to a console exe project as embedded resources and everything is committed to source control. These exe projects are very thin and contain a single line of C# code that passes the command line arguments directly into an in-house DB deployment library which is installed via nuget. This library handles command line parsing and executing dbup in various ways, using the scripts embedded in the calling assembly. The result is a simple executable file that, depending on its command line, can upgrade the DB directly, preview which scripts would run against a particular DB instance, test the DB deployment against a clean SQL Server LocalDB instance, or generate SQL scripts for DBAs. One nice feature is that the exe can also return the connection string of the SQL Server LocalDB test instance to external code as a test fixture. We can use this to directly unit test our repository types against a real, clean database. When the test fixture is disposed by XUnit, the temporary DB is cleaned up and removed. The console projects are built and tested as part of our CI builds, then pushed to Octopus Deploy[2] as packages. The Octopus deployment process simply runs the executable and passes it the connection string of the DB to update. [0] https://dbup.github.io/ https://dbup.github.io/ [1] https://dbup.readthedocs.io/en/latest/philosophy-behind-dbup/ https://dbup.readthedocs.io/en/latest/philosophy-behind-dbup... [2] https://octopus.com/ https://octopus.com/
- brlewis 7y ago
  >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
  - theclaw 7y ago
    That is our upgrade process, yes.
namelosw 7y ago
We use fluentMigrator for .Net, but basically the workflow is similar to Rails migration. It is working fine and did not slow us down. What slows us down and related to this topic is cross-database migrations. We have a bunch of microservices in different codebases. Sometimes we need to adjust bound contexts which move the responsibilities from services to services, we found it's much harder to migrate between different databases behind those services. It's either hard to keep track in the same repo and/or too slow to migrate large volume of data across hosts. In the end we wrote some optimized SQL to do that. Maybe monorepo could help but this doesn't happen too much, and we haven't start trying it yet.
darkr 7y ago
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/ https://sqitch.org/
barrkel 7y ago
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.
Guillaume86 7y ago
.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).
paulddraper 7y ago
We use dbmate [1]. SQL migration management with in-database version tracking. Super simple, but works for us. [1] https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate
sheeshkebab 7y ago
- Flyway for relational databases, and - flyway-like go based homegrown process for Cassandra (that includes support for executing go scripts for data migrations etc) Above work pretty well - for many, many microservices and continuous deployments all the way to prod.
sakopov 7y ago
At my .NET shop we use FluentMigrator [1] which allows you to just run up/down migrations. Our deployments are completely automated and, in all honesty, while this has been a great and a painless way to manage migrations there really is no silver bullet. Minor releases are dead simple. Larger releases require quite a bit of planning to make sure things are deployed in correct order and SQL updates are backwards-compatible (at least during the deployment window) so that we don't incur downtime or completely break things. The only time we ever had to roll back was when one of these large releases deployed a non-backwards-compatible SQL script which caused us a lot of bad data during 5 minute deployment window. So, this is usually a good motivating factor to try to deploy slim releases as frequently as possible. And if you do that you'll appreciate it when your DB migrations become boring and mundane things that just happen on auto-pilot. [1] https://github.com/fluentmigrator/fluentmigrator https://github.com/fluentmigrator/fluentmigrator
- Merad 7y ago
  How do you apply your migrations? I was setting up a project a few weeks ago and reached for FluentMigrator as it used to be my go to tool... but apparently the command line tool is now deprecated and they recommend writing custom code to do migrations. I eventually decided to give EF Core migrations a try, which is working pretty well so far.
  - sakopov 7y ago
    We use the CLI you're referring to on ci/cd servers. Frankly, I had no idea it was deprecated and couldn't find much on this after a little bit of googling. Would mind sharing a link?
    - Merad 7y ago
      Perhaps "deprecated" isn't quite accurate. I could have sworn one of their documentation pages basically said, "there's a good chance you'll run into problems if you use the CLI, and we don't recommend using it." If there was something that explicit I can't find it at the moment. There is [0] that says "please use the in-process runner if possible," [1] discussing problems with the cli migrators, and [2] where the maintainer seems to have a preference for removing the cli's in the long run. [0]: https://fluentmigrator.github.io/articles/migration-runners.html?tabs=vs-pkg-manager-console https://fluentmigrator.github.io/articles/migration-runners.... [1]: https://github.com/fluentmigrator/fluentmigrator/issues/1006 https://github.com/fluentmigrator/fluentmigrator/issues/1006 [2]: https://github.com/fluentmigrator/fluentmigrator/issues/1016 https://github.com/fluentmigrator/fluentmigrator/issues/1016
systematical 7y ago
We use https://github.com/cakephp/phinx https://github.com/cakephp/phinx works pretty well. It just runs on deployment. Thankfully I've never had to try using the rollback feature.
Foober223 7y ago
On my team we use numbered sql scripts. We restore a production db on our local boxes. This is the starting point. The numbered scripts are executed against this. So the actual deployment is getting tested out locally all the time. This is dead reliable, and handles tricky data transformation that would fail under a declarative/calculate approach.
lmiller1990 7y ago
I work at a Perl shop and we use Sqitch [1]. It helps manage your migrations, you write three raw SQL scripts per change: deploy, verify and revert. The tool works with any DB or backend, since it only does the schema. It does NOT integrate with your server framework so you need to figure an ORM solution out, independent of Sqitch. [1] https://sqitch.org/ https://sqitch.org/
ajcodez 7y ago
I wrote my own schema as code library and then switched back to normal migrations with no down method. It takes some care to not delete anything for a while until it’s definitely not needed ever again. https://www.ajostrow.me/articles/how-to-run-migrations https://www.ajostrow.me/articles/how-to-run-migrations
sqldba 7y ago
Scripts. And I hate to tell you but despite what you’ll read on HN every single company I’ve seen and every single vendor I’ve dealt with - is using scripts. Just simple scripts, in source control, amended by hand, lord hopes applied as part of a CI system, often without any kind of version numbering (so once it’s in the database you don’t know where it came from) and with no rollback. Declarative migrations do exist. If it’s in .NET then it’s invisible to me, but I imagine it’s pretty rare. Using SSDT (Microsoft) for tooling is possible but has so many edge cases and is poorly documented so coming up with an architecture requires the kind of expert where there may literally only be a dozen in the world, so it isn’t done outside of demos that fail and get thrown out the second you’d ever try to implement them.
ilaksh 7y ago
For my current project I have not needed to migrate data. I have a system called timequerylog and a tool tql-cli where data is just appended to JSONL files organized by key, date, and time.
GlennS 7y ago
I'm skeptical of tools that claim to handle schema migrations automatically or declaratively. I've had them fail in the past at critical moments. I also don't believe that they can handle the most important cases. I think that numbered sequential migration steps are still the correct way to go. They should be written at the time the feature/change is developed, ideally by the person doing that development. Whether they are in SQL or some sort of ORM thing probably doesn't matter that much. I don't know how to handle branching/merging issues with this approach beyond "pay attention, be careful, talk to your colleagues about database changes you're making". At my first company we stored the whole schema instead of the incremental steps to get there. We used SQL Delta to synchronize the real database to what we wanted. SQL Delta is a good tool, but one time we ran into a case it couldn't handle during a big deployment at a large hospital and had to abort and roll everything back. Very embarrassing. We switched to incremental scripts after that. SQL Delta is still a good tool if you have lots of views, user defined functions, or stored procedures. As long as they aren't needed for the migration itself, you can just synchronize all of those at the end. It's also a great tool for actually inspecting if the result of your migration is what you expected. I do like the idea of having easy access to the full schema text as well as the migrations (someone mentioned that Rails does this). Ideally I think this should be generated from the migrations and not checked into version control.
snicker7 7y ago
My team works with five database systems containing more or less the same data because we "switch" to a new DB every year without investing enough in migrating off the old one.
- edoceo 7y ago
  It's "job security" ;)
sylvain_ 7y ago
There is a great writing from Stripe which explains their process for database migration : - Dual writing to the existing and new tables to keep them in sync. - Changing all read paths in our codebase to read from the new table. - Changing all write paths in our codebase to only write to the new table. - Removing old data that relies on the outdated data model. https://stripe.com/fr/blog/online-migrations https://stripe.com/fr/blog/online-migrations
hestefisk 7y ago
I hand code my migrations for Postgres. When starting a project a create a base.sql file. Then for first database change I create db/000001-<name of change>.sql, db/00002 [...] and so forth. When I want to create a new database I just run psql<base.sql followed by psql<db/*.sql. Very simple but extremely effective. I don’t think any tool can handle migrations better than hand coded sql. Sometimes I use PL/PgSQL to handle procedural data migrations, which cannot be solved using normal sql.
- ec109685 7y ago
  How do you know which ones have already been run on the environment you are targeting?
  - hestefisk 7y ago
    I keep a simple table with the latest version run and a time stamp. Then I check in each file whether its version# (in the filename) is less or greater than what’s in the table. It’s easy because I can do it in simple sql.
- tylerrobinson 7y ago
  Have you experienced any problems with how long it might take to run the whole list of incremental changes?
  - hestefisk 7y ago
    No never. It is as fast as you can pipe data through to psql.
- mattmanser 7y ago
  If you think any tool can't handle this better than you are woefully misinformed. I was doing this method back in 2006, let me assure you that the automated tools available today make this look like amateur hour. Automatic schema transactions, automatic up/down scripts, automatic detection that a schema change hasn't been committed. Automatic change tracking table in the db. Ability to seed a database with test data. With a single command I can go back to any arbitrary point of changes, switch branch, work on a bug fix, switch back to dev and the next time I fire up the Dev site it'll automatically redeploy the new changes. Just streets ahead of hand coded scripts. Yes, there's a learning curve, yes you have to pay attention to your design still, but it is so good. I was skeptical at first, but honestly I'm never going back and I don't miss it. I'm using Entity Framework Migrations, I assume other languages have similar/better tools.
speedplane 7y ago
Is this question asking how to move from one schema to another, or one database backend to another? Most answers here focus on schema changes but I'm not sure that was the question. Schema changes are relatively straightforward, there are plenty of tools that can help. Changing the database backend is a different story altogether. There are so many unknown unknowns when changing databases, that it's generally best to avoid it if you can.
tluyben2 7y ago
We have been doing migrations with our internal ORM for many years now; first it was written in Perl, then ported to Java, later ported to PHP, after that ported to C# (past ~10 years) and it does up/down migrations with safe guards in place. It (the underlying algorithm/heuristics) has been working fine for around 20 years on around 1000 projects (client consultancy mostly) in that time. Lessons learned (opinionated/IMHO, no sweeping wisdom attempt); a) database changes should be in code so there is no discrepancy between code + db b) your tooling (in this case our ORM) should figure out how to get from the current state to the new state; it should not be a manual job (because you will make mistakes) c) migrations including rollback should be fully automated (we only had issues when people tried to do clever things manually) d) have common sense safe guards in place; do not try to be too clever to save a few MB's of disk space; for instance, deploy will fail if you attempt to remove a column; you can only do that manually.
GordonS 7y ago
Using DbUp, which is basically a library that you use to wrap SQL scripts in a console app. This gives you full control over your migrations, while still being incredibly simple - it's just SQL! For views, sprocs and functions, we don't use sequential migration scripts, instead opting for idempotent creation. This also means it's easy to see the evolution of views etc when looking in source control. In the past I've used Entity Framework and Entity Framework Core migrations, and hated them both - I'll never be a fan of codegen, but aside from that they sometimes generated wrong code that had to manually adjusted, and you also quickly end up with hundreds of scripts. I like DbUp very much.
quickthrower2 7y ago
Previous shop we used entity framework migrations in .net. So migrations are applied by octopus deploy. This place we do it manually but because of how the app is architected migrations are rare. If we migrate we try to make the code compatible with before and after schemes.

269 points by abetlen 7y ago

What's your team workflow for delivering features that require database migrations. How do you keep migrations from slowing down development as your team grows.

thrower123 7y ago

SQL scripts. It is painful.

nickserv 7y ago
The horror. I actually had a dev tell me he didn't trust doctrine (symfony project), so would do it all in SQL. Thankfully the project was relatively new so it was possible to rewrite everything using migrations.

bahador 7y ago

I've used Flyway[0] at my current and previous shops. It seems to work well. [0] https://flywaydb.org/ https://flywaydb.org/

smoyer 7y ago

I've read and reread a great article titled "Evolutionary Database Design" on Martin Fowler's web-site [0]. This article describes database migrations as being a process. We've found that for complex changes, we'll often need a pre-migration and a post-migration (temporally being before the code change and after the code change respectively). We commit the migrations along-side the application code and in our case we use FlywayDB [1]. The only down-side is that this tool doesn't perform roll-back operations automatically. You can always do them by writing another migration that goes forward to the past. Another popular DB migration tool is Liquibase [2]. I don't have much experience with this tool as it doesn't fit our build pipe-line as well but it does support and encourage defining a roll-back for each migration. [0] https://www.martinfowler.com/articles/evodb.html https://www.martinfowler.com/articles/evodb.html [1] https://flywaydb.org/ https://flywaydb.org/ [2] http://www.liquibase.org/ http://www.liquibase.org/ EDIT: HN is the new StackOverflow? I think this is a really important question for development teams and yet I could see it being closed due to the "Questions asking for tool or library recommendations are off-topic for Stack Overflow ..." rule. Sad!

freedomben 7y ago
> HN is the new StackOverflow? Could you clarify what you mean by this?
- smoyer 7y ago
  This is actually a good place to ask important questions that will be closed by the moderators there.
  - barrkel 7y ago
    In fact these are the only questions I'm really interested in asking of other developers any more, and most of the reason why I almost never ask or reply on SO, despite 37k rep. It was looser in the earlier days, but I guess moderators wanted easier to evaluate rules, and it's easier for moderators to decide to come down on the side of moderators than people who ask questions and start discussions.
    - scarejunba 7y ago
      Makes sense, though, right? They want a Q&A site where you can get definitive answers. This sort of discussion-oriented thing is better suited to a forum with threaded replies and whatnot.
      - barrkel 7y ago
        I actually think there ought to be levels, where you can access different kinds of conversations at higher rep levels. Or some other way of gating out clueless people. Noise drowns out signal otherwise.
abetlen 7y ago
Thank you, the Fowler article is very helpful. Does your team have someone act as a DBA to oversee each migration?
- 7y ago

config_yml 7y ago

rails db:migrate It’s always one of the first things I miss when I have to work on a non-rails codebase.

tln 7y ago

Migrate during continuous deployment. We use a staging environment, so CD deployed migrations are always run at least once before running on production. Migrations have to be written so that currently deployed code will work after the migration. Eg, to rename a column, add+copy, deploy, then in a second migration drop the column.

archarios 7y ago

At my previous job we either didn't migrate and wrote application level transforms that would update records as they were encountered by users (mongodb) or we had a custom built migration system that ran JavaScript snippets on our shards. The migration system was miserable to work with and it was hard to debug the code on stage in such a way that would allow us to anticipate whatever might be on prod...

ezekg 7y ago
This sounds like a nightmare. Why?
- VectorLock 7y ago
  >(mongodb)
- archarios 7y ago
  This is a fairly typical approach minus the pain points of our specific migration system for Mongodb as far as I know. We favored application level transforms for the sake of safety and speed. Doing that obviously gets really bothersome as time goes on though.

based2 7y ago

https://github.com/goldmansachs/obevo https://github.com/goldmansachs/obevo https://www.schemacrawler.com/ https://www.schemacrawler.com/

gigatexal 7y ago

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 practice just something I’ve been thinking about.

abetlen 7y ago
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?
- gigatexal 7y ago
  Is branching where it forks and then comes back? I'm still a bit new to the process but if that's the case then yes. The team is still cool to the idea of my way of doing migrations they tend to do the destructive ones and call it a day albeit with the commonsense to do backups for truly data destroying things like dropping of columns or redefining of columns.

louthy 7y ago

We use the RedGate SQL compare tools [1] to compare our new schema to our old one and auto-apply the diffs to the production DB (this is done automatically by our deployment process). To reduce the chance of error we don’t destroy columns or tables. Our application then has an update step which runs on startup for any data migrations (or new data additions), and then updates a version number stored in the DB. The data migrations are super rare. This all means we can migrate from any past version to our latest one: because we have all previous schemas stored in git and n update functions in our app that can walk the versions It’s worked reliably for over a decade and is pretty much entirely pain free [1] https://www.red-gate.com/products/sql-development/sql-compare/ https://www.red-gate.com/products/sql-development/sql-compar...

atwebb 7y ago
I've got a fair amount of experience with it and the Change Automation tool they have. Both are fantastic products, the latter for certain scenarios over SSDT itself. Sounds like have had it sorted for a while, my main concerns are when it wants to rebuild certain tables when a simple sp_rename or other step would be sufficient.
- teddyuk 7y ago
  This is such a common complaint!
beart 7y ago
> To reduce the chance of error we don’t destroy columns or tables. Does this mean you have a lot of unused tables and columns deprecated in the database?
- louthy 7y ago
  No. It’s very rare that columns or tables are deprecated. It’s a very mature web app that basically just grows.

jakswa 7y ago

Depends on the database, in my opinion. Each one has quirks to be mindful of, depending on the amount of data you're migrating/touching. For example, if you accidentally put a 'default' on a column when you add it to postgres, it will lock the entire table while it rewrites every row, inserting that default value. Another common postgres blunder is creating indexes on big tables without using 'concurrently'. This also locks the table for writing and you'll have a bad time.

adwf 7y ago
Just for reference, as of PG11 it no longer locks or rewrites for default column creation. It's one of the nice things about Postgres, they're always improving. A few years ago, concurrent index creation wasn't a thing either. Nor were "CREATE/DROP IF (NOT) EXIST" statements for various bits and pieces, but they just keep adding to it over time. In the upcoming PG12 there is REINDEX CONCURRENTLY, so we'll finally be able to fix corrupt/bloated indexes on the fly without having to drop and recreate them.

mbostleman 7y ago

SSDT / DACPAC

pier25 7y ago

We use https://github.com/rubenv/sql-migrate https://github.com/rubenv/sql-migrate for Go You basically write SQL queries for up and down.

wutman 7y ago

This doesn't fit everywhere, but... 1) Never modify existing tables (okay, almost never). 2) Add new tables at will. 3) Pay a consultant to write PL/SQL that glues it all together and blame them for any and all issues.

teddyuk 7y ago
3) ha ha

frankwiles 7y ago

Django migrations it’s a truly great tool.

Myrmornis 7y ago
Yes it is. (Except when they made makemigrations be like O(2^N) or something in the number of models around 1.9).

stock_toaster 7y ago

We use migrate[1]. [1]: https://github.com/golang-migrate/migrate https://github.com/golang-migrate/migrate

lovetocode 7y ago
I second this. migrate works great for me.

deleted 7y ago

[deleted]

was_boring 7y ago

I don't think there is a way to change the database without slowing down development. After all, the data is the most valuable thing you have. Besides that, I tend to follow the same general principals RainforestHQ presented in 2014 https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-database-migrations/ https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-d...

1337shadow 7y ago

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.

Bjartr 7y ago
We have an ant script that does almost exactly this

jolmg 7y ago

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.

whitepoplar 7y ago
isn't this what datomic does?
ben509 7y ago
The main issue is you can't "reverse migrate" creating a table or adding a column as that deletes prod data. So your base tables are always forward migrated, and probably have only basic constraints. With good updatable view support, you can maintain versions of table views and constraints on the views that may be forward, reverse or laterally migrated. (You'd have a different set of views for each version of the schema in use, so each build of the client is connecting to exactly the schema it expects.) Then you'll want to checkpoint the base tables. So the system must track usage of view-set versions to determine when they can be dropped entirely. Since the constraints are enforced at the view level, you have to deal with legacy data that violates current constraints, but was hidden by the views. Then the system can do a checkpoint and delete obsolete columns and tables, moving forward the earliest version you can roll back to. Of course, that all requires good updateable view support, and I don't think any products offer that.
mchristen 7y ago
You can run/test your migrations in a transaction and roll back if it doesn't work.
- jolmg 7y ago
  Not all problems are obvious, though. You can have an app work 100%, your database be completely coherent, and then realize some of the data is missing. It would be cool to not have to be so careful when committing migrations, needing to be absolutely sure that we're not screwing something up. This is similar to how one would be careful of changes done in source code before we learned how to use version control systems like git. I can go completely wild with git, deleting random files, overwriting others with random junk, sharing them to my coworkers, etc. It would only take a few minutes to fix that.

rocgf 7y ago

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.

cdepatie 7y ago
Liquibase is pretty solid, my company has used it across a couple of projects. Similar to your Flyway experience, it's not anything particularly amazing but it works.

tylerFowler 7y ago

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.

bungie4 7y ago

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.

sebringj 7y ago

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.

alanfranz 7y ago

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.

abetlen 7y ago
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.
- evanelias 7y ago
  For a "very large company dedicated to moving fast" example, here's what the process looked like at Facebook a few years ago. AFAIK same process today, with one improvement noted below. Background: * Almost everything is self-service by necessity. Except for some high-blast-radius cases, dev teams are able to manage their own schemas without needing MySQL team intervention. This is made possible by having automation that has appropriate safeties built in. * There's a repository (git, hg, whatever) storing schemas. It has a couple levels of subdirectories to organize different database tiers and individual databases. In each of the bottom-level subdirs, there are text files containing CREATE TABLE statements, one file per table. In other words, this is a declarative repo, modeling the ideal state of tables in each database. Process to add or change a table: 1. Just add or change a CREATE TABLE statement, and commit in SCM. 2. Submit a diff (pull request). Someone on your team reviews it, same as a code review. 3. Once merged, the schema change can be kicked off. (A few years ago, a dev would need to run a simple CLI command to tell the automation "please begin working on this table", but I believe this has been automated away since then.) The tooling automatically manages running the correct DDL safely, on the correct machine(s), even in the case of a large sharded table. Devs never need to write ALTER TABLE statements; everything is just based on CREATE TABLE. There was a separate flow (with extra steps, on purpose) for destructive actions like dropping tables or columns.

liyanchang 7y ago

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/ https://www.devoted.com/about/tech-jobs/

evanelias 7y ago
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 of tools [1] to provide declarative schema management and "schema change by pull request" functionality, initially targeting MySQL and MariaDB. A few large companies have built pipelines using one of my tools -- including Twilio SendGrid, who wrote about their process in-depth recently [2]. [1] https://skeema.io https://skeema.io [2] https://sendgrid.com/blog/schema-management-with-skeema/ https://sendgrid.com/blog/schema-management-with-skeema/

aerojoe23 7y ago

.net has entity framework an ORM has has migrations. It isn't problem free, but we found workable solutions to our issues. By default EF will throw an error if the model (in code) and the database are out of sync. There is a setting you can set to tell the ef not to care about the version, but then you have to take the responsibility of making sure the old code will run against the new db version. Most of our stuff is adding a new column or table so the old code doesn't rely on it and everything goes fine anyway.

sebazzz 7y ago
Yes, we have used that for a bit and for the most part this works fine. We had however a case where several migrations were not applied in production, and that is a mess to figure out. But the development experience, especially at the start of the project (if you don't maintain your seeding code), is awesome. You just point the project at an empty or non-existing database, the schema is automatically created, and any necessary base data can automatically be seeded.
ozim 7y ago
But that is most important part of migrations to throw that error so you are aware of it. First thing you fix your db and then push to test server, and you never have broken db on production, NEVER.

sebazzz 7y ago

We use RedGate SQL Source Control. However, we also deploy our applications using MSDeploy and SQL Source Control has no good solution for that. We stitch the migration scripts together manually. Also, no matter how good you control it, your database gets out of sync with the migrations after a while. We also have an integration test which restores a base database and applies the migrations of the current branch on top of it. We're going to research DACPACs to deploy databases. We hope it will be better.

stephenr 7y ago

We used to use dbschema (https://pypi.org/project/dbschema/ https://pypi.org/project/dbschema/) to apply migrations generated by SchemaSync (http://mmatuson.github.io/SchemaSync/ http://mmatuson.github.io/SchemaSync/) using a helper script to simplify capturing a migration from a known base of previous migrations. After submitting a number of bug fix and feature patches to the upstream projects I ended up writing a (IMO better) tool to apply the migration scripts (https://bitbucket.org/koalephant/mallard https://bitbucket.org/koalephant/mallard). We currently still rely on SchemaSync for that part, but it’s been more reliable and it’s ultimately a tool for developers who review the generated sql anyway - the tool to apply them needs to run automated on remote environments.

gregmac 7y ago

Ohh, this is a favorite topic of mine and I'm of the opinion there's no clear best solution (possibly good market opportunity here), only a series of trade-offs. ---- In one project, we use DBGhost (for MS SQL Server). It's like RedGate SQL Compare, but in deployable, self-contained executable form. It does a complete schema sync, so internally we run on every build, and externally can upgrade from every previous release, without the pesky "in between" evolution you tend to get with ordered migration scripts. It's run as part of our upgrade process for every app version, and our deploy package is built from the 'initial database create' SQL script in source control. To make a schema change such as adding a new column, you modify the relevant `CREATE TABLE` script, commit it to source, and that's it. We also use a hand-built pre- and post-deploy script to do anything tricky that can't be automated (renaming a column, copying/converting data from one column to another, etc). Importantly, these scripts are idempotent (eg: `if (old_column_exists) { start transaction; create new column; copy+transform data; drop old_column; commit transaction; }`). We generally avoid major changes like this as much as we can, but it's possible when necessary. We also have DBGhost configured not to drop anything (to avoid mistakes, or dropping customer customization that they do even though our support contract explicitly says not to), and instead write those by hand. This process has been in many dozens of customer-facing releases -- including on-premise customers -- for several years, and 'just works'. DBGhost is a clunky app, but our interaction with it is now entirely scripted (via either build or deployment scripts), and the compare engine part of it is actually very good. ---- In another pretty simple app (that runs on our cloud infrastructure only, with 'production' deployments mirrored in a couple different regions, and usually a single 'dev' deployment though sometimes there are other temporary ones) we opted to only use an idempotent, hand-written script, executed as part of deployment. It has `create table if not exists` statements to setup from scratch, and it also has the transforms for changes done over time (`create index if not exists;` `drop column if exists` etc). We periodically remove the transform statements to clean it up after all deployments have been updated past that point. Even though it's manual, it's actually quite easy to maintain, so long as you're careful about ensuring it's idempotent. The nice part is it typically gets deployed to the dev infrastructure multiple times during the course of working on something, so if there's a problem it becomes obvious very quickly when the deployment fails. ---- There's also another app which uses the more traditional ordered migrations files, which I liked at first but over time I find it annoying. Deploying from scratch installs a really old initial database schema, then proceeds to apply dozens of modifications, many of which overwrite previous modifications. ---- I've also worked on an ordered migrations file app where there was a separate 'create' step for the initial creation (instead of running through each migration). The first time I deployed from scratch I found a bunch of problems where the create script wasn't 100% synchronized with the migrations, but also was synchronized enough that it wasn't possible to 'fix' by running each migration (one of the early ones failed). The only fix was to go through by hand and manually run the relevant migration files. I'm sure there can be better practices to help prevent this (eg some compare step on build), but this happened years ago and still sticks with me as a terrible way to do migrations.

deleted 7y ago

[deleted]

Myrmornis 7y ago

Django. Django generates the schema migrations automatically from the changed table schema definitions that the developer used when testing their branch, and those rarely cause problems. Data migrations need to be tested against staging DBs with realistic data. But neither is really a major pain point: individual developers create and commit the migration files while preparing their branches for review.

djstein 7y ago
really the best part about Django
nickserv 7y ago
Only major gotcha is when renaming fields: it'll drop the old field then create a new field, by default. You have to do the renaming by hand, trivial but can't be forgotten... Which is why you always test on staging first of course.
- haihaibye 7y ago
  Makemigrations detects if the type is the same and asks you if you renamed it. So, when changing columns do the type change and name change in separate migrations and you won't have to do it by hand.

ben_jones 7y ago

Poorly

wesleytodd 7y ago

If anyone is looking for a javascript specific solution, I maintain a module originally written (and abandoned) by TJ. $ npm i migrate https://www.npmjs.com/package/migrate https://www.npmjs.com/package/migrate

smt88 7y ago
Why does it matter what the tool is written in? If anything, I'm a lot less likely to use something written in JS when correctness is absolutely paramount.

wenbin 7y ago

Django and other modern web development framework handles db migrations pretty well. $ python manage migrate

tshanmu 7y ago

we have had good success with liquibase[0] and flyway[1]. [1] http://www.liquibase.org/ http://www.liquibase.org/ [2] https://flywaydb.org/ https://flywaydb.org/

theclaw 7y ago

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 change description. The scripts are added to a console exe project as embedded resources and everything is committed to source control. These exe projects are very thin and contain a single line of C# code that passes the command line arguments directly into an in-house DB deployment library which is installed via nuget. This library handles command line parsing and executing dbup in various ways, using the scripts embedded in the calling assembly. The result is a simple executable file that, depending on its command line, can upgrade the DB directly, preview which scripts would run against a particular DB instance, test the DB deployment against a clean SQL Server LocalDB instance, or generate SQL scripts for DBAs. One nice feature is that the exe can also return the connection string of the SQL Server LocalDB test instance to external code as a test fixture. We can use this to directly unit test our repository types against a real, clean database. When the test fixture is disposed by XUnit, the temporary DB is cleaned up and removed. The console projects are built and tested as part of our CI builds, then pushed to Octopus Deploy[2] as packages. The Octopus deployment process simply runs the executable and passes it the connection string of the DB to update. [0] https://dbup.github.io/ https://dbup.github.io/ [1] https://dbup.readthedocs.io/en/latest/philosophy-behind-dbup/ https://dbup.readthedocs.io/en/latest/philosophy-behind-dbup... [2] https://octopus.com/ https://octopus.com/

brlewis 7y ago
>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
- theclaw 7y ago
  That is our upgrade process, yes.

namelosw 7y ago

We use fluentMigrator for .Net, but basically the workflow is similar to Rails migration. It is working fine and did not slow us down. What slows us down and related to this topic is cross-database migrations. We have a bunch of microservices in different codebases. Sometimes we need to adjust bound contexts which move the responsibilities from services to services, we found it's much harder to migrate between different databases behind those services. It's either hard to keep track in the same repo and/or too slow to migrate large volume of data across hosts. In the end we wrote some optimized SQL to do that. Maybe monorepo could help but this doesn't happen too much, and we haven't start trying it yet.

darkr 7y ago

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/ https://sqitch.org/

barrkel 7y ago

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.

Guillaume86 7y ago

.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).

paulddraper 7y ago

We use dbmate [1]. SQL migration management with in-database version tracking. Super simple, but works for us. [1] https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate

sheeshkebab 7y ago

- Flyway for relational databases, and - flyway-like go based homegrown process for Cassandra (that includes support for executing go scripts for data migrations etc) Above work pretty well - for many, many microservices and continuous deployments all the way to prod.

sakopov 7y ago

At my .NET shop we use FluentMigrator [1] which allows you to just run up/down migrations. Our deployments are completely automated and, in all honesty, while this has been a great and a painless way to manage migrations there really is no silver bullet. Minor releases are dead simple. Larger releases require quite a bit of planning to make sure things are deployed in correct order and SQL updates are backwards-compatible (at least during the deployment window) so that we don't incur downtime or completely break things. The only time we ever had to roll back was when one of these large releases deployed a non-backwards-compatible SQL script which caused us a lot of bad data during 5 minute deployment window. So, this is usually a good motivating factor to try to deploy slim releases as frequently as possible. And if you do that you'll appreciate it when your DB migrations become boring and mundane things that just happen on auto-pilot. [1] https://github.com/fluentmigrator/fluentmigrator https://github.com/fluentmigrator/fluentmigrator

Merad 7y ago
How do you apply your migrations? I was setting up a project a few weeks ago and reached for FluentMigrator as it used to be my go to tool... but apparently the command line tool is now deprecated and they recommend writing custom code to do migrations. I eventually decided to give EF Core migrations a try, which is working pretty well so far.
- sakopov 7y ago
  We use the CLI you're referring to on ci/cd servers. Frankly, I had no idea it was deprecated and couldn't find much on this after a little bit of googling. Would mind sharing a link?
  - Merad 7y ago
    Perhaps "deprecated" isn't quite accurate. I could have sworn one of their documentation pages basically said, "there's a good chance you'll run into problems if you use the CLI, and we don't recommend using it." If there was something that explicit I can't find it at the moment. There is [0] that says "please use the in-process runner if possible," [1] discussing problems with the cli migrators, and [2] where the maintainer seems to have a preference for removing the cli's in the long run. [0]: https://fluentmigrator.github.io/articles/migration-runners.html?tabs=vs-pkg-manager-console https://fluentmigrator.github.io/articles/migration-runners.... [1]: https://github.com/fluentmigrator/fluentmigrator/issues/1006 https://github.com/fluentmigrator/fluentmigrator/issues/1006 [2]: https://github.com/fluentmigrator/fluentmigrator/issues/1016 https://github.com/fluentmigrator/fluentmigrator/issues/1016

systematical 7y ago

We use https://github.com/cakephp/phinx https://github.com/cakephp/phinx works pretty well. It just runs on deployment. Thankfully I've never had to try using the rollback feature.

Foober223 7y ago

On my team we use numbered sql scripts. We restore a production db on our local boxes. This is the starting point. The numbered scripts are executed against this. So the actual deployment is getting tested out locally all the time. This is dead reliable, and handles tricky data transformation that would fail under a declarative/calculate approach.

lmiller1990 7y ago

I work at a Perl shop and we use Sqitch [1]. It helps manage your migrations, you write three raw SQL scripts per change: deploy, verify and revert. The tool works with any DB or backend, since it only does the schema. It does NOT integrate with your server framework so you need to figure an ORM solution out, independent of Sqitch. [1] https://sqitch.org/ https://sqitch.org/

ajcodez 7y ago

I wrote my own schema as code library and then switched back to normal migrations with no down method. It takes some care to not delete anything for a while until it’s definitely not needed ever again. https://www.ajostrow.me/articles/how-to-run-migrations https://www.ajostrow.me/articles/how-to-run-migrations

sqldba 7y ago

Scripts. And I hate to tell you but despite what you’ll read on HN every single company I’ve seen and every single vendor I’ve dealt with - is using scripts. Just simple scripts, in source control, amended by hand, lord hopes applied as part of a CI system, often without any kind of version numbering (so once it’s in the database you don’t know where it came from) and with no rollback. Declarative migrations do exist. If it’s in .NET then it’s invisible to me, but I imagine it’s pretty rare. Using SSDT (Microsoft) for tooling is possible but has so many edge cases and is poorly documented so coming up with an architecture requires the kind of expert where there may literally only be a dozen in the world, so it isn’t done outside of demos that fail and get thrown out the second you’d ever try to implement them.

ilaksh 7y ago

For my current project I have not needed to migrate data. I have a system called timequerylog and a tool tql-cli where data is just appended to JSONL files organized by key, date, and time.

GlennS 7y ago

I'm skeptical of tools that claim to handle schema migrations automatically or declaratively. I've had them fail in the past at critical moments. I also don't believe that they can handle the most important cases. I think that numbered sequential migration steps are still the correct way to go. They should be written at the time the feature/change is developed, ideally by the person doing that development. Whether they are in SQL or some sort of ORM thing probably doesn't matter that much. I don't know how to handle branching/merging issues with this approach beyond "pay attention, be careful, talk to your colleagues about database changes you're making". At my first company we stored the whole schema instead of the incremental steps to get there. We used SQL Delta to synchronize the real database to what we wanted. SQL Delta is a good tool, but one time we ran into a case it couldn't handle during a big deployment at a large hospital and had to abort and roll everything back. Very embarrassing. We switched to incremental scripts after that. SQL Delta is still a good tool if you have lots of views, user defined functions, or stored procedures. As long as they aren't needed for the migration itself, you can just synchronize all of those at the end. It's also a great tool for actually inspecting if the result of your migration is what you expected. I do like the idea of having easy access to the full schema text as well as the migrations (someone mentioned that Rails does this). Ideally I think this should be generated from the migrations and not checked into version control.

snicker7 7y ago

My team works with five database systems containing more or less the same data because we "switch" to a new DB every year without investing enough in migrating off the old one.

edoceo 7y ago
It's "job security" ;)

sylvain_ 7y ago

There is a great writing from Stripe which explains their process for database migration : - Dual writing to the existing and new tables to keep them in sync. - Changing all read paths in our codebase to read from the new table. - Changing all write paths in our codebase to only write to the new table. - Removing old data that relies on the outdated data model. https://stripe.com/fr/blog/online-migrations https://stripe.com/fr/blog/online-migrations

hestefisk 7y ago

I hand code my migrations for Postgres. When starting a project a create a base.sql file. Then for first database change I create db/000001-<name of change>.sql, db/00002 [...] and so forth. When I want to create a new database I just run psql<base.sql followed by psql<db/*.sql. Very simple but extremely effective. I don’t think any tool can handle migrations better than hand coded sql. Sometimes I use PL/PgSQL to handle procedural data migrations, which cannot be solved using normal sql.

ec109685 7y ago
How do you know which ones have already been run on the environment you are targeting?
- hestefisk 7y ago
  I keep a simple table with the latest version run and a time stamp. Then I check in each file whether its version# (in the filename) is less or greater than what’s in the table. It’s easy because I can do it in simple sql.
tylerrobinson 7y ago
Have you experienced any problems with how long it might take to run the whole list of incremental changes?
- hestefisk 7y ago
  No never. It is as fast as you can pipe data through to psql.
mattmanser 7y ago
If you think any tool can't handle this better than you are woefully misinformed. I was doing this method back in 2006, let me assure you that the automated tools available today make this look like amateur hour. Automatic schema transactions, automatic up/down scripts, automatic detection that a schema change hasn't been committed. Automatic change tracking table in the db. Ability to seed a database with test data. With a single command I can go back to any arbitrary point of changes, switch branch, work on a bug fix, switch back to dev and the next time I fire up the Dev site it'll automatically redeploy the new changes. Just streets ahead of hand coded scripts. Yes, there's a learning curve, yes you have to pay attention to your design still, but it is so good. I was skeptical at first, but honestly I'm never going back and I don't miss it. I'm using Entity Framework Migrations, I assume other languages have similar/better tools.

speedplane 7y ago

Is this question asking how to move from one schema to another, or one database backend to another? Most answers here focus on schema changes but I'm not sure that was the question. Schema changes are relatively straightforward, there are plenty of tools that can help. Changing the database backend is a different story altogether. There are so many unknown unknowns when changing databases, that it's generally best to avoid it if you can.

tluyben2 7y ago

We have been doing migrations with our internal ORM for many years now; first it was written in Perl, then ported to Java, later ported to PHP, after that ported to C# (past ~10 years) and it does up/down migrations with safe guards in place. It (the underlying algorithm/heuristics) has been working fine for around 20 years on around 1000 projects (client consultancy mostly) in that time. Lessons learned (opinionated/IMHO, no sweeping wisdom attempt); a) database changes should be in code so there is no discrepancy between code + db b) your tooling (in this case our ORM) should figure out how to get from the current state to the new state; it should not be a manual job (because you will make mistakes) c) migrations including rollback should be fully automated (we only had issues when people tried to do clever things manually) d) have common sense safe guards in place; do not try to be too clever to save a few MB's of disk space; for instance, deploy will fail if you attempt to remove a column; you can only do that manually.

GordonS 7y ago

Using DbUp, which is basically a library that you use to wrap SQL scripts in a console app. This gives you full control over your migrations, while still being incredibly simple - it's just SQL! For views, sprocs and functions, we don't use sequential migration scripts, instead opting for idempotent creation. This also means it's easy to see the evolution of views etc when looking in source control. In the past I've used Entity Framework and Entity Framework Core migrations, and hated them both - I'll never be a fan of codegen, but aside from that they sometimes generated wrong code that had to manually adjusted, and you also quickly end up with hundreds of scripts. I like DbUp very much.

quickthrower2 7y ago

Previous shop we used entity framework migrations in .net. So migrations are applied by octopus deploy. This place we do it manually but because of how the app is architected migrations are rare. If we migrate we try to make the code compatible with before and after schemes.