Ask HN: How do you organize and manage database migrations?
We've been using migrations and doing it all manually but it's become a bottleneck and a little bit of a nightmare with multiple consumers of the database needing to make schema changes.
Another concern is multiple environments, from local development to staging and production. We're using docker-compose for local development which runs the entire "full" schema and then I'm manually applying the migration files to staging and production before we deploy.
I've looked at some projects like flywaydb[1] and liquibase[2] but both are not completely free and seem proprietary. Does anyone know of another open source system that could help manage database schema versioning and migrations?
Thanks so much HN, this is something that I have been struggling with.
1: https://flywaydb.org/
2: https://www.liquibase.org/
60 comments
[ 4.0 ms ] story [ 47.5 ms ] threadThe actual application was in Go or nodejs.
I daresay that Activerecord and Alembic outclass Liquibase in a lot of things.
Some technical benefits to Alembic:
- It will give you the structure for upgrades and downgrades.
- Has a clean interface for DDL operations
- Supports every (?) database that SA does
- You can use it in "Offline" mode if you don't want to have Python and all the dependencies on the server or have to hand the migration off to someone else that has access.
- The branch feature is really nifty if you are in advanced situations.
Some non-technical benefits with Alembic
- It is open source
- zzzeek, the author, is pretty active on here and has built both SQLAlchemy and Alembic so there is a lot of cohesion in styles.
- The issue tracker is active and responsive
- The code is stable (something you want in a migration tool) and is unlikely to go anywhere.
Highly recommend.
Edit: Formatting
I have literally spun up a barebones Rails app just to manage my schema and migrations for a completely separate Python/PostgreSQL project before.
https://guides.rubyonrails.org/v5.2/active_record_migrations...
Large open source projects such as GitLab source code are also treasures to discover migration rules.
If you have a millions of users with high traffic tables you may want to check some migration guides for downtime, data migrations and batch processing here https://docs.gitlab.com/ee/development/migration_style_guide...
If you want to have some concurrent ways of DDL operations you may benefit from https://gitlab.com/gitlab-org/gitlab/blob/master/lib%2Fgitla...
One of the points was that migrations should always be reversible. I’ve struggled with this in the past when it comes to migrations that change or drop data. How do you write a downgrade script that recalls the previous data?
I’ve given up with downgrades now. I make sure to take a dB backup or snapshot before running an upgrade. I’ve never had to test this solution in a tense situation though...
Why do you think no other toolchain has come close to this for other languages or frameworks? It’s a very common problems that needs a solution no matter what you are building and what you are building it on.
[0] https://docs.djangoproject.com/en/2.2/topics/migrations/
https://pypi.org/project/yoyo-migrations/
- Database-First
- Code-First
Create an SQL project that defines the schema. It automatically compares the schema with your current dB schema and auto generates a migration script for you.
Then update entities from the dB.
Few steps involved but avoids writing and maintaing migrations yourself.
I don't want to go back to any sort of migrations outside of this.
https://github.com/amacneil/dbmate
The process of a sql migration management is pretty simple and like other comments it's not hard to write something yourself which works well I just prefer to use something a little more battle tested in the deployment process.
Also it's a go app so you don't need to pull down a stupid amount of dependencies for it to work just pull from it's github release and mark it as executable.
You only get support and templates by paying for it.
1) Use a migration tool (flyway, dbmigate, etc.)
2) We try and keep scripts idempotent to minimize on migration script explosion. Also helps to keep changes to similar assets located within the same file: easier to see the progress of a given asset as it changes throughout the lifetime of your app.
3) We have a dedicated repository for each database/schema. A CI/CD process triggers on a push to a given branch (dev, qa, stage, prod, etc.). The CI/CD process runs the migration script (in this case AWS Code Pipeline). Having the schema in its own repository decouples our databases from the service(s) that use them.
4) We try our best to separate schema changes from feature changes: a) push database changes first maintaining backwards compatibility, b) then push feature changes, c) then remove backwards compatibility. So, try to minimize on rollback script.
Local development is same as yours: docker-compose.
1: https://github.com/GuiaBolso/darwin
[0] https://www.se-radio.net/2012/06/episode-186-martin-fowler-a...
Also checkout the links from the show notes:
http://www.databaserefactoring.com/
http://www.martinfowler.com/articles/evodb.html
http://www.amazon.com/exec/obidos/ASIN/0321293533/agiledba-2...? creative=327641&camp=14573&link_code=as1
http://www.awprofessional.com/bookstore/product.asp?isbn=032...
* We have migrations written in SQL files named like 45_add_column_x_to_view_y.sql
* We track the migrations in git and we do merge requests and reviews
* We include a comment in each migration containing the original DDL of the object (1. it makes it easier for the reviewer to see what's changing, 2. it gives us a way to rollback, though it never happened so far)
* We track the applied migrations in a table migrations looking like "2019-10-30T10:49:00" ! "45_add_column_x_to_view_y.sql"
* We have a CLI in the same repo that allow us to apply the migrations. It's 50 LOC of python that basically 1. gets the DB host/user/pwd from an .env, 2. checks the last applied migration in the table migrations, 3. finds the new migrations to apply, 4. applies them following the order of the ids e.g. 45 before 46, 5. updates the table migrations
* We have 3 DBs (dev / staging / prod) in AWS RDS and I have an .env for each one of them
* We have a CI pipeline on the repo to automatically apply the merged migrations in staging and check if nothing goes wrong
* When the staging or dev is trashed, I delete it and recreate it from a snapshot of the prod (it takes 5 min) https://stackoverflow.com/a/49878477/652669
1. we don't have the entire database modeled into SQLAlchemy and we don't plan to
2. we have A LOT of views, triggers and functions and they are not very well supported by SQLAlchemy/Alembic (? I may be wrong)
Point 2 is indeed a big factor though. Alembic doesn’t have much first-class operation support beyond data storage constructs, and if most you need is op.execute() (which runs plain SQL) one might just as well build their own tool like you did.
Sequence is to run pre-release migrations, deploy the release, then run post-release migrations. Sometimes days apart, in case things need to be stabilized. Testing environment runs only pre-release migrations, until some time before the release, then runs post.
We also have a handful of keywords to run operations in ways that don't lock large, frequently-used tables, or do other environment- or application-specific operations in standard ways.
Instead of writing migrations, developers simply add / remove / modify these CREATE statements, going through the same pull request and code review process as for code. The tool knows how to diff the desired state (expressed in the repo) vs the actual state of any database environment (prod / stage / dev, etc) to generate the appropriate DDL.
It's a bit of a paradigm shift relative to traditional mysql and postgres migration tools, but it's an approach that has been used successfully by Facebook internally for nearly a decade. It's also a common approach in the SQL Server world. I've written a blog post [2] describing some of the advantages of the declarative approach.
Skeema currently only supports MySQL and MariaDB, but some declarative tools for Postgres include sqldef [3] and migra [4].
[1] https://www.skeema.io
[2] https://www.skeema.io/blog/2019/01/18/declarative/
[3] https://github.com/k0kubun/sqldef/
[4] https://github.com/djrobstep/migra
Interesting ideas though. Microsoft does something similar with a DACPAC where they record the schema and diff, but they also record a list of rename actions etc. and keep a table to track those “migrations” in addition to the diff process. https://docs.microsoft.com/en-us/sql/relational-databases/da... and/or https://docs.microsoft.com/en-us/sql/ssdt/extract-publish-an... — for rename and other SSDT details, see https://docs.google.com/presentation/d/1DvC2gzCucjHFbGiBLa0R... (it’s a “RefactorLog” if searching)
Having seen all that, the Rails ActiveRecord approach strikes a decent balance between specify migrations as imperative actions and keeping a declarative schema checked in. A comparison between the two approaches is at https://blog.raph.ws/2019/01/migrations-vs-dacpacs/ but I find DACPAC to be useful but over-complicated for developers and not well-enough supported on non-windows hosts yet. (It basically requires VS for Windows or SSDT right now...)
Also, it’s likely a migration system needs some kind of code review checks and automation around which actions you’d allow in your migrations if you don’t have that already...
The next question after “how do you migrate?” is probably “how do you backup and how long would it take to restore after a bad migration and/or bad rollback?” These days your answer is probably either “no time at all” for smaller apps with SSDs or “it’s all outsourced to the cloud,” for the new cloud distributed data stores or for the smart DIY ones, “we only deploy one server at a time and can either afford the downtime or have copies in multiple AZs, perhaps eventual consistency...”
For bigquery, we wrote this nifty tool https://github.com/SplitmediaLabsLimited/supermigration
Alembic is extra convenient if you're already invested in sqlalchemy.
I've tried yoyo-migrations[1] once, a while ago.
With yoyo, like flyway, migrations are written as SQL queries. However, they're expressed as SQL steps in python files. Might worth a look if you're using python.
1. https://pypi.org/project/yoyo-migrations/
We use CI/CD for all components, including for the schema. The deployment playbook for the schema basically does a `flyway migrate` after installing the schema / migration files.
We have (again, historical reasons) a home-built schema diff tool for our subset of mysql that we use. For Postgres I'd look into apgdiff. We use the diff tool to generate initial versions of the migrations, potentially modify them by hand, and then `git add` them in the schema project.
If you don't like flyway, you could check out https://sqitch.org/ which is fully Open Source.
I don't mean to be "that guy", but unless your whole business's "thing" is no-proprietary-software, please try to pay for commercial licenses of good software. That money pays for bug fixes, security fixes, general maintenance, feature development, testing, support, and a host of other crap you probably don't want to have to do by yourself. If they provide a free tier, you're also supporting the free users.
Flyway is kind of the gold standard, I recommend it.
So when I need to do some complex migration on a big database, I usually open postgres shell, open a transaction and I develop a migration like a code, in a REPL. And in case of these automatic tools, after I did that I have to go read their documentation, and port SQL to their syntax.
If I have junior members on the team who don't know SQL yet on a good level, easier tasks are automated by the tool, and for more complex task they completely lack skills developing a migration and have a much steeper wall to climb.
Another thing is that many tools have downgrade migrations. Downgrade migrations are a lie! How could I revert a migration that drops a NOT NULL column with some data? In case I really need to revert a migration I will write another forward migration. Which I did exactly zero times in more than ten years. So, writing downgrade migrations is a waste of time.
Another minor point is that sometimes migrations are running for a loooooong time. Not a second or two - it can be many hours. So I don't want this migration to start automatically during my deploy process. But I don't want to have it as a separate script, so on local dev installations these migrations just take the same `make migrate` route. It's much more convenient to take a part of a commented SQL and run it separately, than to take a part of a migration script written in Python and run it separately.
I'm using a Nomad tool https://pypi.org/project/nomad/ which is written in Python. I think any tool that supports plain SQL migrations, has dependencies between migrations and doesn't require to write downgrades would be acceptable.
There are pro and enterprise licenses in case you have additional requirements. These licenses as I understand it are proprietary and hence non-free but the the community edition absolutely is free software.
I’ve been using the community edition in multiple projects for several years now and it works great, particularly in the context of Spring Boot applications.
It might not be just as polished and well-integrated as ActiveRecord is for Ruby on Rails but keep in mind Flyway is supposed to be framework-independent so it can’t be too tightly integrated.
That said, its integration with Spring Boot is really good. Just add it as a dependency and configuration and migrations during application startup will be taken care of automatically.
Repeatable migrations are great especially when combined with upserts in Postgres, if you keep config in your database as well.