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.
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.
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.
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!
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.
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.
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.
We differentiate between DBas and DBAs (administrators verses architects). Our team has DBAs who make sure what we're doing is sane and more importantly tune SQL emitted by ORMs as needed.
> 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.
I've done that too, and agree re: rollbacks... I've also never been a huge fan of the fact that the final, in-production server isn't ever really reified as a source file. What you have instead is the initial state and a bunch of migration files. It works, and it's the most reflective way of how the final schema got to that state, but it's not the final state. I guess that's the trade off of the relatively unsophisticated approach taken by tools like Flyway.
I'm a fan of liquibase. It's a very mature tool which allows you to run it every time the application starts, useful for development to create in-memory dbs. Or you can just call it as part of the build process if you don't want to check the DB changelog tables on startup.
It tracks changes that have been applied in changelog tables so you don't apply them multiple times.
Common operations such as adding a column are defined in a supported markup language but for more complicated things, such as migrating data, you can reference ad-hoc SQL files. All of which can be checked-in to your codebase.
The only real downside is because you are applying the entire development history of the database, you can sometimes be caught doing illogical things like adding and then removing something later on. This can be mitigated by rewriting history if needed though.
Flyway was not as good for me because it lacked a domain language, raw SQL makes it less easy to interpret but I know developers who preferred that.
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.
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...
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.
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.
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.
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
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.
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.
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.
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.
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.
"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!"
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.
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.
That’s basically the proposition of any event sourcing system https://www.martinfowler.com/eaaDev/EventSourcing.html but you’re kinda right, that involves a level of code complication (but then it would be so simple as you described to restore / rebase / etc)
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.
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.
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.
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.
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.
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.
The one weakness of this system is that it doesn't understand or handle foreign key constraints. If you have those you have to manage it the old fashioned way (whatever that is for you)
That's true. Most large-scale MySQL shops, including Facebook, discourage or outright forbid foreign key constraints. This is sacrilege to many relational db purists, but there are a number of solid reasons:
Foreign keys aren't shard-aware, greatly reducing their utility.
They introduce performance bottlenecks due to extra locking. In an insanely-high-write-volume OLTP environment, such as a social network, this really matters.
They don't play nice with online schema change tools in general -- not just fb-osc. These tools all involve creating shadow tables and propagating changes to them, which is problematic with foreign keys.
We were a large MS-SQL shop and we had the same. No FKeys in test or prod and we were "only" a billion and change e-commerce, nowhere near a social media site level of traffic.
To the original question: hand-written ALTER scripts, each taggable as pre, during, or post release actions. We had standard patterns for adding non-null columns (pre to add a nullable column and a cursor-based/batched update, then another ALTER to make the nullable column (now populated) non-nullable). Also had a set of rules to ensure version N of the code (web and DB) could run on the DB at version N or N+1.)
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.
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/
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].
This is good to know. As someone who didn't do much with databases before, I was frankly worried given how it didn't seem like many others were taking this approach when it made so much sense (we did have the advantage of having a defined schema which I know isn't always available). Seems like I just didn't know what to search for.
Git would never have worked it required devs to write the up/down patches - why should we have to write the up/down migrations for my schema?
Excited to see more tooling around declarative schema!
I'm surprised this isn't more of a thing. It seems like the natural evolution of "[X] as code". I've always been a little turned off by migrations (though they were certainly an improvement over the previous situation, which was basically just indeterministic changes on the fly).
My thoughts exactly. But it's a major paradigm shift for those coming from the world of Rails/Django/etc migrations, and that unfamiliarity understandably leads to some initial resistance and skepticism.
fwiw, other declarative tools are starting to pop up -- besides my tool Skeema, some others I've seen recently are Migra [1] and sqldef [2]. And meanwhile a bunch of enterprise tools for MS SQL Server have operated in the declarative fashion for quite a long time, although usually with GUIs instead of being git / pull-request-driven. So I think/hope it's just a matter of time before this concept becomes more widely known.
It's definitely a thing, eg SQL Server Data Tools has this as a default - Schema Compare and Data Compare, and you can just use declarative approaches to defined your final state and let the tool take care of it.
That being said - if you want to do this the downside is usually that its slow as hell, and the non-migration approaches can cost you downtime.
Generic solutions to specific states often means copying all data somewhere else so you can modify the table and then put it back in a useful fashion - a migration often allows more piecemeal approaches.
Excellent question! The short answer is Skeema doesn't directly support renames yet. Renames are inherently more imperative than declarative, so they don't fit in well with the model. I've thought about handling them via tracking their history/state, but it would be hacky.
Two workarounds exist in Skeema currently:
* You can do the rename "out of band" (e.g. manually, rather than via `skeema push`), and then update your schema repo via `skeema pull`. This isn't ideal, but then again, table/col renames typically involve nasty code-deploy-order complexities to begin with (regardless of migration system): there's no way to rename something at the same exact instant that your new code goes live, and it's difficult/annoying to write code that can correctly interact with both names.
* For new empty tables, happily a rename is equivalent to drop-then-re-add. So this case is trivial, and Skeema can be configured to allow destructive changes only on empty tables.
If you use ms sql server ssdt you use refactor/rename and it finds all references and changes them and then when you go to deploy it generates a sp_rename - 100% killer feature right there :)
This sounds nice! One Question: You said that DML changes are handled via "standard check in sql file". Does this simply mean a new SQL file for each migration? And how are DML changes connected to DDL changes? For example, if some code is two versions behind and updated to the current schema, wouldn't this mean that the DDL is updated in one step to the current state, but the DML potentially in two steps, breaking the update?
That's correct. The DML changes as part of CI are somewhat new so we haven't ironed it all out yet.
Here's the scenario that I think you're laying out:
1. Commit A creates column foo
2. Commit B has DML that reference column foo
3. Commit C removes column foo
This works fine if our CI deployer does each commit individually. First roll out any schema changes, then run any DML SQL.
However, our deployer might pick up all those changes and since we roll out the schema migrations first (in this case a create + drop -> NOP) and then runs the DML (which will error), this is an issue because of the rollup.
In practice, we have yet to see this case (most of the time, the dev who write the DML is close enough to the code to know if it's going to be dropped soon and we don't drop that many columns - in part because we know that there be dragons) but truthfully, I haven't thought about it much and need to think through what the impact is beyond this example. Thanks for helping me refine my thinking and I'll have something to ponder on this weekend!
Not sure about the state of the world currently after living in BigCo filter bubble for the past few years, but do you even need custom tools to calculate the delta between the schema as checked into VCS vs the database's actual state?
Spanner (https://cloud.google.com/spanner/) I think can auto-compute the diff between its current state and a given schema, generate appropriate SQL statements to perform a migration and get user confirmation for destructive schema changes.
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 models for you.
All of these operations can also export the exact SQL that will run on each supported RDBMS platform in case you want to be extra sure on what exactly will be executed.
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...
> 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.
They're actually a directed graph; this means a conflict wasn't handled on the branches that should have been, and would probably have been a problem regardless.
I've rarely encountered logical merge conflicts with migrations, but I could see it happening.
I used to be on the SQL Server team at Microsoft and had some exposure to the customer support teams. So data integrity and eliminating any potential for errors was huge.
So while I love the idea of migrations being generated on the fly from actual state in Production-System-5 to desired state of commit 27a73e, I'm skeptical of it working that well in practice. Certain cases come to mind where there might be intermediate migrations from [full name] -> ([first name] [last name]) -> ([first initial] [last name]). The system would have to be smart enough to know A -> C may require A -> B -> C or prompt the engineering team for a better DML migration script.
Also, you will want there to be documentation about what was performed whether that is a migrations table that points to a .py file... or a .json output... or a log file.
Yeah. I’d love to see the academic paper with formalizations that help me understand the true scope of this problem. Your example is a great one that prompts many questions. Is it possible to travel directly to the commit o(1) or will the code have to calculate the diff of each commit and apply them one at a time o(n) and how much definition and dependency mapping humans need to do to have it work correctly?
The closest I can think of is trying to define a set of CRDT-compatible operations that are expressive enough to describe your database schema, starting from an empty database. Then, the migration you need to perform is whatever the CRDT merge operator says you need to do.
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?
* I see your point on not standardizing on one framework. Generally when that has happened for me, it turns into a new service and it has its own database/tables/migration management. It does get quite annoying, for sure.
* I've seen enough things go wrong that on my teams I do not allow DDL to be executed outside of a controlled process that comes from code. But yeah, if that were to happen, it would annoying to figure out what was done and then try to re-model.
* With Django you can specify exact SQL to run. So you can break up operations into multiple smaller steps... canonical example is building a new column based on an old column. You first add the column with NULL. Then you populate in batches of ~10k records. Then you add on the constraints/indexes.
* I haven't used Django with sharding. It appears there are some posts about it, but it all appears to be community generated content and not part of the official docs.
All-in-all, I could see that at a large scale with very mature engineering organizations with lots of activity and complex operations that something like Django could fall short and a home-grown system like this may be beneficial, assuming it were reliable enough.
I’ll openly admit that we don’t have everything ironed out. In fact my next big project is to tackle derived columns (rename is a column where the transformation is the identity function).
It requires a bit more finesse and integration into our code base as it requires multiple deploys with code that knows how to handle both columns.
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.
A totally valid point, but I'd argue those should be handled by a separate tool or process. Data migrations tend to be fully programmatic; tools and frameworks can help reduce the code required, but cannot handle every possible case. (having performed numerous multi-billion-row data migrations, I learned this painfully first-hand...)
For simpler cases, where it may make sense to run a data migration immediately after a schema change, a good generic middle-ground may be configurable hook scripts. A declarative schema management system can then pass relevant info to the hook (which tables were changed, for example) and then the script can run any arbitrary row data diff/apply/migrate type of logic.
I do understand your point though; for relatively straightforward data migrations, an imperative system can capture these much more cleanly by just coupling them with the corresponding schema migration code.
I honestly like the way Rails does it: both capturing the imperative deltas and dumping the final schema which gets checked in. Not a big fan of down migrations, usually a waste of time.
Otherwise I like Percona's OSC, particularly how it can tune down table rewrites when there's competing work, or replication is lagging too much. We're just at the point where we need to automate the OSC tool rather than using it as a point solution for migrating our bigger tenants.
I’ll openly admit that we don’t have everything figured out. You’re absolutely right that currently, we constrain ourselves on what we migrate to admittedly simple migrations.
I think there’s a whole set of problems to be solved in this space and frankly, I’m as surprised as anyone that given how SQL is declarative, we use procedural code to do migrations so part of my post was hoping people would tell me what tool I should be using or how this approach fails over time. So your examples are really helpful for me as I think through if it’s possible to do automatically, workaround, or get by without.
It seems to me that we just lack the ability to express these transitions mathematically that can help us do them. And of those, there’s probably only a subset which are possible to do without taking downtime.
In particular, the class of migrations that you outlines are a combination of DDL and DML changes and also have quite a bit of code complexity to do without downtime. It’s definitely a current weakness.
149 comments
[ 3.5 ms ] story [ 228 ms ] thread[0] https://flywaydb.org/
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
[1] https://flywaydb.org/
[2] 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!
Could you clarify what you mean by this?
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.
I've done that too, and agree re: rollbacks... I've also never been a huge fan of the fact that the final, in-production server isn't ever really reified as a source file. What you have instead is the initial state and a bunch of migration files. It works, and it's the most reflective way of how the final schema got to that state, but it's not the final state. I guess that's the trade off of the relatively unsophisticated approach taken by tools like Flyway.
It tracks changes that have been applied in changelog tables so you don't apply them multiple times.
Common operations such as adding a column are defined in a supported markup language but for more complicated things, such as migrating data, you can reference ad-hoc SQL files. All of which can be checked-in to your codebase.
The only real downside is because you are applying the entire development history of the database, you can sometimes be caught doing illogical things like adding and then removing something later on. This can be mitigated by rewriting history if needed though.
Flyway was not as good for me because it lacked a domain language, raw SQL makes it less easy to interpret but I know developers who preferred that.
It’s always one of the first things I miss when I have to work on a non-rails codebase.
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.
https://www.schemacrawler.com/
Just out of curiosity, has your team used Alembic's branching system with any success?
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-compar...
Does this mean you have a lot of unused tables and columns deprecated in the database?
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.
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.
You basically write SQL queries for up and down.
[1]: https://github.com/golang-migrate/migrate
Besides that, I tend to follow the same general principals RainforestHQ presented in 2014 https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-d...
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.
"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!
day saved!If only it were so simple.
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.
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.
A production DB is changed potentially millions of times a day.
If you have this goal from the beginning, you can create an append-only database, but that's orthogonal to migrations.
To have "append-only" migrations is seemingly out of scope for mainstream database engines.
See https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.htm... for an example.
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.
Turns out Visual Studio has a 'diff' generator for both schema and data. Holy hell that worked the treat.
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.
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.
Foreign keys aren't shard-aware, greatly reducing their utility.
They introduce performance bottlenecks due to extra locking. In an insanely-high-write-volume OLTP environment, such as a social network, this really matters.
They don't play nice with online schema change tools in general -- not just fb-osc. These tools all involve creating shadow tables and propagating changes to them, which is problematic with foreign keys.
To the original question: hand-written ALTER scripts, each taggable as pre, during, or post release actions. We had standard patterns for adding non-null columns (pre to add a nullable column and a cursor-based/batched update, then another ALTER to make the nullable column (now populated) non-nullable). Also had a set of rules to ensure version N of the code (web and DB) could run on the DB at version N or N+1.)
My current company uses mongodb, so migrations aren't a thing. It's pretty nice, TBH.
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/
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
[2] https://sendgrid.com/blog/schema-management-with-skeema/
Git would never have worked it required devs to write the up/down patches - why should we have to write the up/down migrations for my schema?
Excited to see more tooling around declarative schema!
fwiw, other declarative tools are starting to pop up -- besides my tool Skeema, some others I've seen recently are Migra [1] and sqldef [2]. And meanwhile a bunch of enterprise tools for MS SQL Server have operated in the declarative fashion for quite a long time, although usually with GUIs instead of being git / pull-request-driven. So I think/hope it's just a matter of time before this concept becomes more widely known.
[1] https://github.com/djrobstep/migra
[2] https://github.com/k0kubun/sqldef/
That being said - if you want to do this the downside is usually that its slow as hell, and the non-migration approaches can cost you downtime.
Generic solutions to specific states often means copying all data somewhere else so you can modify the table and then put it back in a useful fashion - a migration often allows more piecemeal approaches.
Edit: a guy I like wrote a good model/migration set of articles http://dlmconsultants.com/model-vs-mig/
This is a (minor) pain point for traditional migration systems.
Two workarounds exist in Skeema currently:
* You can do the rename "out of band" (e.g. manually, rather than via `skeema push`), and then update your schema repo via `skeema pull`. This isn't ideal, but then again, table/col renames typically involve nasty code-deploy-order complexities to begin with (regardless of migration system): there's no way to rename something at the same exact instant that your new code goes live, and it's difficult/annoying to write code that can correctly interact with both names.
* For new empty tables, happily a rename is equivalent to drop-then-re-add. So this case is trivial, and Skeema can be configured to allow destructive changes only on empty tables.
I've written a bit more about this at https://github.com/skeema/skeema/blob/master/doc/requirement... , as well as the bottom of https://www.skeema.io/blog/2019/01/18/declarative/ .
Here's the scenario that I think you're laying out: 1. Commit A creates column foo 2. Commit B has DML that reference column foo 3. Commit C removes column foo
This works fine if our CI deployer does each commit individually. First roll out any schema changes, then run any DML SQL.
However, our deployer might pick up all those changes and since we roll out the schema migrations first (in this case a create + drop -> NOP) and then runs the DML (which will error), this is an issue because of the rollup.
In practice, we have yet to see this case (most of the time, the dev who write the DML is close enough to the code to know if it's going to be dropped soon and we don't drop that many columns - in part because we know that there be dragons) but truthfully, I haven't thought about it much and need to think through what the impact is beyond this example. Thanks for helping me refine my thinking and I'll have something to ponder on this weekend!
Spanner (https://cloud.google.com/spanner/) I think can auto-compute the diff between its current state and a given schema, generate appropriate SQL statements to perform a migration and get user confirmation for destructive schema changes.
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 models for you.
All of these operations can also export the exact SQL that will run on each supported RDBMS platform in case you want to be extra sure on what exactly will be executed.
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...
They're actually a directed graph; this means a conflict wasn't handled on the branches that should have been, and would probably have been a problem regardless.
I've rarely encountered logical merge conflicts with migrations, but I could see it happening.
I used to be on the SQL Server team at Microsoft and had some exposure to the customer support teams. So data integrity and eliminating any potential for errors was huge.
So while I love the idea of migrations being generated on the fly from actual state in Production-System-5 to desired state of commit 27a73e, I'm skeptical of it working that well in practice. Certain cases come to mind where there might be intermediate migrations from [full name] -> ([first name] [last name]) -> ([first initial] [last name]). The system would have to be smart enough to know A -> C may require A -> B -> C or prompt the engineering team for a better DML migration script.
Also, you will want there to be documentation about what was performed whether that is a migrations table that points to a .py file... or a .json output... or a log file.
* 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?
* I've seen enough things go wrong that on my teams I do not allow DDL to be executed outside of a controlled process that comes from code. But yeah, if that were to happen, it would annoying to figure out what was done and then try to re-model.
* With Django you can specify exact SQL to run. So you can break up operations into multiple smaller steps... canonical example is building a new column based on an old column. You first add the column with NULL. Then you populate in batches of ~10k records. Then you add on the constraints/indexes.
* I haven't used Django with sharding. It appears there are some posts about it, but it all appears to be community generated content and not part of the official docs.
All-in-all, I could see that at a large scale with very mature engineering organizations with lots of activity and complex operations that something like Django could fall short and a home-grown system like this may be beneficial, assuming it were reliable enough.
It requires a bit more finesse and integration into our code base as it requires multiple deploys with code that knows how to handle both columns.
The concept appeals to me, but it only seems to work for trivial migrations.
For simpler cases, where it may make sense to run a data migration immediately after a schema change, a good generic middle-ground may be configurable hook scripts. A declarative schema management system can then pass relevant info to the hook (which tables were changed, for example) and then the script can run any arbitrary row data diff/apply/migrate type of logic.
I do understand your point though; for relatively straightforward data migrations, an imperative system can capture these much more cleanly by just coupling them with the corresponding schema migration code.
Otherwise I like Percona's OSC, particularly how it can tune down table rewrites when there's competing work, or replication is lagging too much. We're just at the point where we need to automate the OSC tool rather than using it as a point solution for migrating our bigger tenants.
I think there’s a whole set of problems to be solved in this space and frankly, I’m as surprised as anyone that given how SQL is declarative, we use procedural code to do migrations so part of my post was hoping people would tell me what tool I should be using or how this approach fails over time. So your examples are really helpful for me as I think through if it’s possible to do automatically, workaround, or get by without.
It seems to me that we just lack the ability to express these transitions mathematically that can help us do them. And of those, there’s probably only a subset which are possible to do without taking downtime.
In particular, the class of migrations that you outlines are a combination of DDL and DML changes and also have quite a bit of code complexity to do without downtime. It’s definitely a current weakness.
I have been advocating for this approach for a while now: https://djrobstep.com/talks/your-migrations-are-bad-and-you-...
Yes, and CFEngine pioneered this in 1993 (Mark Burgess).
I make my living as a CFEngine consultant.