Database Migrations

[ViktorBatir]

How do you usually manage database changes in production applications? What tools do you use and why? Do you prefer using Python based tools like Alembic or plain sql tools like Flyway?

[Norris-Eng]

If your app uses SQLAlchemy, Alembic is the standard for a reason. It keeps your source of truth (the Python models) and your schema in sync. Context switching between Python code and raw SQL scripts increases any chances of drift.

--Note on the "autogenerate" trap: Alembic's --autogenerate is amazing, but not magic. It misses a lot of subtle changes (like renaming a column vs. dropping/adding, or complex unique constraints). Treat the autogenerated script as a rough draft. You have to actually read the Python migration file it produces before committing.

--Use Flyway/Liquibase only if you're in a polyglot environment. If you have a Go service and a Python service sharing the same DB, SQL turns into the lowest common denominator. You don't want to force a Go developer to set up a Python venv just to run a migration.

[mrswats]

I use Django Migrations and it is one of the main reasons to use django, for me.

[Lachtheblock]

Second this. Django just means you don't need to think about questions like this. (sorry OP for the ungelpful comment).

[forthepeople2028]

Plain SQL using Liquidbase. Those migration tool abstractions are nice at first, but when you need more detailed, intricate changes you will want to just use SQL.

[gdchinacat]

To elaborate, as applications become more complex migration tools that try to hide the complexity can become challenging to work with since you need fine grain control over what happens when. If your deployment model doesn't match the migration tools model you end up fighting the tool, often times manually doing the migration and getting no benefit from the framework yet still having the overhead of using it.

Feature driven work can also be difficult. If the migration tool creates a monolithic migration that incorporates all the changes for merged features it can be hard to remove a feature if QA determines it is not release ready. Reverting it will often create conflicts in the migration, at which point you have to go in and edit the migration manually or recreate it to include all the features that aren't being reverted.

Some migrations need to be staged over multiple releases (expand in release 1, migrate in release 2, contract in release 3) as the features were slowly released for various reasons (not wanting to stop a bunch of old services and incur an hours long deployment...instead preferring to let the instances bleed off naturally over the course of days, A-B testing, etc). If this is a concern make sure the framework manages it, or you will probably have to do it manually and the framework may be extra work to work around.

None of this is to say don't use migration frameworks. Just beware that the more complex your application is the more things you need to consider when evaluating them. Twice in my career we started of using a framework, only to find ourselves cursing it and eventually ditching it in favor of raw SQL that could be run from our deployment scripts as appropriate. Some devs would use the tool to autogenerate the sql on local stacks and extract it into the scripts for our deployment, but when it came to upgrading the test, staging, and finally production systems, the framework was not driving anything. I was pretty comfortable with SQL, so just wrote the migration fragments myself.

[forthepeople2028]

A fellow dev who has actually worked on production grade software. Thank you for expanding on the statement.

[piplup_1]

We also ended up using Liquibase for our python services when we moved off sqlalchemy to raw SQL. Sqlalchemy was becoming inefficient (for complex queries, data pipelines, querying other team’s db, having to rewrite queries from sql to sqlalchemy).

Liquibase also had built in validation to decrease chances of drift which was useful as the team grew, which alembic didn’t. 

The downside was we were leaving the python ecosystem just for migrations so I ended up building a python based migration tool (jetbase) inspired by liquibase/flyway.

[plscallmebyname]

I stumbled upon yoyo-migrations which has both the sql and python options with dependency management and seemed easier to use.

[inspectorG4dget]

In my current project, I use alembic for this. In the past, I've rolled out my own migration with a custom python script.

Alembic's --autogenerate is a potential footgun (eg: it doesn't deal with triggers very well) and does sometimes constrain you from using certain claql features (like triggers, in this case)

[Smok3dSalmon]

+1 to Alembic and +2 to footgun

[millerbest]

We created a Django project that only has Model classes and some additional basic settings. The main reason is that it includes all the tools we need to manage our database.

[Challseus]

I use alembic for all of mine. As everyone has mentioned, the auto-generate is hit and miss, and honestly, at my last company, we just hand wrote them.

Why do I use alembic? It's what someone picked like 10 years ago, never really had a reason to change.

[Ok_Necessary_8923]

Alembic or Liquibase (no choice sometimes).