How to call command.stamp(alembic_cfg, 'head') asynchronously ?

[ghevge]

Hi,

I'm trying to set up by alembic to use async connections , but I'm facing the following error when calling: command.stamp(alembic_cfg, 'head')

I've also tried to await this command, but I still get the same error.

I've updated my env.py based on the details available at the bottom of: https://alembic.sqlalchemy.org/en/latest/cookbook.html

The migration works but the command.stamp doesn't. And I need this call to bootstrap my DB.

Any idea how I can fix this?

I use alembic 1.6.5 with sqlAlchemy 1.4.18

Thanks

INFO:root:Stamping the alembic version id
Traceback (most recent call last):
  File "/bolt-webserver/scripts/initdb.py", line 431, in <module>
    loop.run_until_complete(main())
  File "/usr/local/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "/bolt-webserver/scripts/initdb.py", line 120, in main
    await initdb(config, args.timeout, args.delay, args.wipe)
  File "/bolt-webserver/scripts/initdb.py", line 290, in initdb
    await create_db_schema(db_engine, db_url, alembic_scripts_location)
  File "/bolt-webserver/scripts/initdb.py", line 219, in create_db_schema
    await command.stamp(alembic_cfg, 'head')
  File "/usr/local/lib/python3.9/site-packages/alembic/command.py", line 570, in stamp
    script.run_env()
  File "/usr/local/lib/python3.9/site-packages/alembic/script/base.py", line 490, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 97, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.9/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 855, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "/bolt-webserver/bolt/core/db/migrations/env.py", line 74, in <module>
    asyncio.run(run_migrations_online())
  File "/usr/local/lib/python3.9/asyncio/runners.py", line 33, in run
    raise RuntimeError(
RuntimeError: asyncio.run() cannot be called from a running event loop
sys:1: RuntimeWarning: coroutine 'run_migrations_online' was never awaited
ERROR: 1

[zzzeek]

Alembc's APIs dont have explicit support for async, so when you say you want to run alembic under asyncio, there's ways to do this but they are firstly unnecessary and secondly have some different ways to do it. it depends on what database driver you are using. If you are using a sync database driver, you can just call alembic's commands from an async task, and they will just run and simply block other tasks. if you are using an async database driver then we would assume you are using SQLAlchemy's async api, and if that's the case then youd want to use the run_sync() approach.

but...this is all kind of conceptually difficult and IMO unnecessary. migrations are not subject to concurrency unless you are trying to do some kind of thing where you are running migrations against many databases in parallel. I'd use multiprocessing for that.

[ghevge]

The reason I'm trying to move alembic to async approach is mainly because I'm changing the sqlalemic to use async , though I have to change the drivers for this to work, so I don't want to end up with 2 sets of drivers / URLs : one for alembic and one for sqlalchemy.

[zzzeek]

If it were me I'd simply use two drivers, it's not that complicated. by using an async driver you're going to cause your alembic integration to be more awkward and difficult to understand.

otherwise, you can try to make this work by using the connection.run_sync() method, where you get your async connection from an async engine, then pass that connection into the alembic command using the recipe at https://alembic.sqlalchemy.org/en/latest/cookbook.html#sharing-a-connection-with-a-series-of-migration-commands-and-environments .

so:

from alembic import command, config
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(...)
cfg = config.Config("/path/to/yourapp/alembic.ini")

async def run_alembic_migration():
    async with engine.begin() as connection:
        cfg.attributes['connection'] = connection
        await connection.run_sync(command.upgrade, cfg, "head")

then in env.py, I think we need to alter how that recipe is a bit to not bother with the "branching" aspect, so like this:

def run_migrations_online():
    connection = config.attributes.get('connection')

    context.configure(
            connection=connection,
            target_metadata=target_metadata
    )

    with context.begin_transaction():
        context.run_migrations()

I'm not completely sure the above will work as I've never tried it. we should update the recipe a bit as the "branched" thing is deprecated in SQLAlchemy 1.4 and also illustrate the async version.

[zzzeek]

oh oh sorry, I also forgot we have an async template as an example, which illustrates how to use run_sync:

https://github.com/sqlalchemy/alembic/blob/master/alembic/templates/async/env.py