0

We use SQLAlchemy + Alembic to generate migrations as python code (default behaviour for Alembic).
We want to move from python to SQL scripts as it provides more control over the generated scripts.
It seems the command revision --autogenerate doesn't work with --sql option.
We don't want to use updgrade --sql option as it just spits the SQL script but doesn't track it or can't be subsequently used with revision --autogenerate
Is there a way to generate migrations as SQL scripts automatically?

jarlh
  • 42,561
  • 8
  • 45
  • 63
deepdive
  • 9,720
  • 3
  • 30
  • 38

1 Answers1

0

I've not seen a way to autogenerate new migrations as SQL, but we also wanted to keep our migrations in SQL and handle it this way:

We store each migration as "forward" and "backward" SQL files, with a timestamp and the revision in the name (the timestamp was added simply because we prefer the revisions to be sortable by filename).

We use a wrapper shell script that does several things:

  • Runs alembic to generate the revision python file. We parse the output to get the revision string.
  • Creates the SQL files in <alembic_env>/migration_sql/ with names like YYYYMMDD_HHMM_<revision>_forward.sql

We then write the forward and backward (or upgrade/downgrade if you prefer) SQL DDL. We updated <alembic_env>/script.py.mako to generate the timestamp prefix and add it to the revision filename (so our revisions are YYYYMMDD_HHMM_<revision>_<message_slug>.py). The upgrade and downgrade functions in the revision file - which in a stock alembic revision use the op.* functions to describe migrations - now load the proper SQL file and execute it:

def upgrade():
    upgrade_file_path = SCRIPTS_DIR / f"{revision_prefix}_{revision}_forward.sql"
    if not os.path.exists(upgrade_file_path):
        print(f"{upgrade_file_path} not found")
        raise Exception(f"{upgrade_file_path} not found, aborting migration")

    with open(upgrade_file_path, "r") as upgrade_file:
        sql = upgrade_file.read()
        op.execute(sql)

This took some doing to set up, but works well and also makes it easy to have a DBA review the migration code in a merge request before we deliver. Hope this helps.

Steve Ivy
  • 138
  • 1
  • 9