Alembic migrate with existing SQLAlchemy engine

Question

I have a certain SQLAlchemy declarative Base that I create on a sqlite memory DB:

engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)

I'm using this for unit testing logic.

With this I have my tables in the DB. But now I wish to migrate certain things using alembic.

AFAIK alembic migrations use the env.py run_migrations_online and there uses a SQLAlchemy function called engine_from_config creating a new engine here.

The problem I wish to solve is to have a way to use the previously created connection, which holds the recently created tables, for the alembic migrations.

I used this on my test scripts: Using Alembic API from inside application code, so that my script does the following after the previous create_all call:

import alembic.config
alembicArgs = ['--raiseerr', '-x', 'dbPath=sqlite:///:memory:', 'upgrade', 'head']
alembic.config.main(argv=alembicArgs

[Please mind, I would just create my schemas with the Base.metadata.create_all(engine) call but my alembic versions not only hold schema changes, they also have some filling of catalog tables data, that's why I intend to use alembic here. In fact, if my alembic migrations hold some "create tables" logic, these two would conflict. So I can safely remove the create_all call and depend on alembic alone to create my schemas here.]

Having already modified my alembic's env.py:

def run_migrations_online():
    ini_section = config.get_section(config.config_ini_section)

    db_path = context.get_x_argument(as_dictionary=True).get('dbPath')

    if db_path:
        ini_section['sqlalchemy.url'] = db_path

    connectable = engine_from_config(
        ini_section,
        prefix ... # everything from here the same as default env.py

As far as I can tell the connectable=engine_from_config creates a connection to a new engine on a new sqlite:///:memory: database, and that's why I can't upgrade via alembic the previously created DB on my script with create_all(engine).

So... TLDR; is there a way to pass my previously existing engine connection (with my created tables) to alembic so that it can migrate it? (I'm pretty sure that the dbPath arg I created is useless here, in fact, I'm just copying what the other post I referenced uses).

Answer 1

You can create an alembic config instance and do operations on it:

def migrate_in_memory(migrations_path, alembic_ini_path=None, connection=None, revision="head"):
    config = alembic.config.Config(alembic_ini_path)
    config.set_main_option('script_location', migrations_path)
    config.set_main_option('sqlalchemy.url', 'sqlite:///:memory:')
    if connection is not None:
        config.attributes['connection'] = connection
    alembic.command.upgrade(config, revision)

It might take some fine tuning, but that's the general gist of things.