How to migrate SQLAlchemy database without generating migration script

Question

I am using Flask and SQLAlchemy in a serverless function, which I don't have access to the command line. And there are times that I want to change the tables. Basically I want to automatically create new tables (already done by create_all) and columns, and change column (e.g. longer string).

How to migrate SQLAlchemy database without generating migration script in the command line? It will be better if I can review the tasks (e.g. [add column A, drop table B]) to be done.

Allan Chain · Accepted Answer · 2021-04-24T02:53:21.097

Yes, it is possible using alembic and not very hacky.

I use alembic autogenerate API. compare_metadata is convenient to generate diff for review, and produce_migrations is convenient to generate migration operations, which are than invoked by Opertaions.invoke.

One thing to notice is that upgrade_ops may contain OpContainer, which itself is not invoke-able, but has ops attribute containing nested operations.

Below is my code to achieve this:

@bp.route("/migrate", methods=["GET", "POST"])
def migrate():
    from alembic.runtime.migration import MigrationContext

    # use `db.session.connection()` instead of `db.engine.connect()`
    # to avoid database lock hang
    context = MigrationContext.configure(db.session.connection())

    if request.method == "GET":
        import pprint

        from alembic.autogenerate import compare_metadata

        diff = compare_metadata(context, db.metadata)
        diff_str = pprint.pformat(diff, indent=2, width=20)
        logger.info("Migrate steps: %s", diff_str)
        return respond_success(migration=diff_str)

    from alembic.autogenerate import produce_migrations
    from alembic.operations import Operations
    from alembic.operations.ops import OpContainer

    migration = produce_migrations(context, db.metadata)
    operation = Operations(context)
    for outer_op in migration.upgrade_ops.ops:
        logger.info("Invoking %s", outer_op)
        if isinstance(outer_op, OpContainer):
            for inner_op in outer_op.ops:
                logger.info("Invoking %s", inner_op)
                operation.invoke(inner_op)
        else:
            operation.invoke(outer_op)
    return respond_success()

For anyone reading this, if you want to ensure the reviewed operations is the same as operations to invoke, try POSTing the hash of diff_str and comparing them.

How to migrate SQLAlchemy database without generating migration script

1 Answers1