[ThePhysicist]

In our startup we moved away from Alembic to using plain SQL files for migrations, which (in our experience) is more robust and allows more control over the actual migration process. We wrote a simple migration manager class that loads a YAML config and a series of SQL files from a directory. Each migration is defined as two files of the form "[number]_[up/down]_[description].sql" and tied to a DB version, the YAML config specifies the name of a version table that contains the current version in the database. The manager then reads the current version from the table, compares it to the requested one and executes the necessary SQL files.

Alembic is great for many simple use cases but we found that for a production system it often isn't easy to maintain compatibility between two different DB systems like Postgres and SQLite anyway, as that would mean either adding a lot of feature switches and custom logic to our code or not using most of the interesting native Postgres features. Therefore Alembic offered very little benefit over a plain SQL file in terms of functionality and in addition made it harder to generate correct migrations in some case, as the auto-generation process does not work very reliably in our experience and some things are buggy/inconsistent, e.g. the creation and deletion of enum types. In addition, we found that it's much easier to write complex migration logic (e.g. create a new column and populate it with data from a complex SELECT statement) directly in SQL. Last point is that we can of course execute these migrations using any programming language / tool we like (for example we also wrote a small Go library to handle the migrations), which is a nice bonus.

That said we also heavily use SQLAlchemy in our Python backend code and like it a lot.