[Question]Can i use async db driver to serve db changes?

[crotaoryx]

Hi guys!
I'm newbie with alembic and wanted clarify one thing.

As i understood based on docs, alembic uses sync database driver(for example, psycopg2) to serve db changes. At the same time i saw SQLAlchemy is moving forward with async, which means i can use asyncpg. As result, it seems app need has both of them - psycopg2 and asyncpg.

Is it possible to use just asyncpg for alembic and SQLAlchemy? Or maybe it's planned future feature? I know that alembic don't need async features to modify db, but i think it's good for "clean requirements" purpose use one driver for both cases.
Or i'm wrong and maybe missing something?

Thanks!

[CaselIT]

Hi,

You are right, currently alembic does not support async drivers.
As far as I know this is not currently planned, since there are not many advantages of async in alembic use case, other than avoiding to deal with two different db driver.

That being saind I think we would accept PR that add that functionality.
Thinking at how sqlalchemy async is implemented, it may just be that an async version of env is required? @zzzeek

[crotaoryx]

Thanks for answer @CaselIT!
Hope there is someone interested with this implementation and skills to prepare PR.

Have a great day!

[CaselIT]

Speaking with mike on gitter it is indeed just the env.py that needs to change, to something like this:

async def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    engine = create_async_engine(...)

    def do_migrations(connection):
        context.configure(connection=connection, target_metadata=target_metadata, **config_options)

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

    async with engine.connect() as connection:
        await connection.run_sync(do_migrations)

    await engine.dispose()


if context.is_offline_mode():
    run_migrations_offline()
else:
    import asyncio
    asyncio.run(run_migrations_online())

[crotaoryx]

Great! Thank you @CaselIT, I'll try this!
I hope there is no difference to use uvloop instead of asyncio.

[CaselIT]

There shouldn't, but I don't think it would make a difference in this case using one or the other

[sqla-tester]

CaselIT has proposed a fix for this issue in the master branch:

Add async template to Alembic https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/2609

[zzzeek]

you konw you could just leave everything as it is, and use a database URL with "?async_fallback=True". this will run each DBAPI method in a new asyncio loop under the hood. @CaselIT would that be a less trouble?

[CaselIT]

Ah right we also support that. One thing I'm not sure is if we want to make that api public. IIRC it is currently not documented.

Also maybe having the async call in env feels less magic for people?

[zzzeek]

well theyre both magic :) but this way the user feels like they're a little bit more a part of it :)

[crotaoryx]

HI @zzzeek!
Do you mean all i need is just change
postgresql+asyncpg://root:root@db_host:5432/db_name
to
postgresql+asyncpg://root:root@db_host:5432/db_name?async_fallback=True
?

you konw you could just leave everything as it is, and use a database URL with "?async_fallback=True". this will run each DBAPI method in a new asyncio loop under the hood. @CaselIT would that be a less trouble?

[CaselIT]

No that's not needed if you modify the env with the async bits.
Adding async_fallback would make the normal sync env work, but it's probably a bit too "magic" for most people :)

[zzzeek]

@crotaoryx yes, you can just change the URL to have ?async_fallback=True and you have to change nothing else. What this basically does is move the "sync->async" part to happen for each database call, rather than your initial asyncio.run().

@CaselIT and I tend to differ on this approach, for me "magic is magic" but he is correct that the more longhand way is a tiny bit less "magic". It's also more efficient at scale though that doesn't matter much for a migration script.

[janaSunrise]

Hi there! I am also trying to use it with my sqlalchemy project which uses asyncpg, but I also got the normal env.py.

The version is 1.5.7, which is the latest.
Should I configure the version / code, or follow some other steps?

[CaselIT]

both should work. we added a faq entry for this https://alembic.sqlalchemy.org/en/latest/cookbook.html#using-asyncio-with-alembic

please let us know if you find issues

[janaSunrise]

Just got this issue, How do I fix it?

[CaselIT]

I cannot reproduce, are you sure you are using an updated version of alembic?

[janaSunrise]

Yeah, I am using the latest of 1.5.7

[CaselIT]

I guess you should try uninstalling and re-installing it. I tried in an empty docker image, so installing from a clean environment works

[janaSunrise]

Alright, I'll do that. I am having the trouble with pipenv locking and I even commented on their official repo's issues too. Its just too crappy atm

[nikitagruia-dev]

Speaking with mike on gitter it is indeed just the env.py that needs to change, to something like this:

async def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    engine = create_async_engine(...)

    def do_migrations(connection):
        context.configure(connection=connection, target_metadata=target_metadata, **config_options)

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

    async with engine.connect() as connection:
        await connection.run_sync(do_migrations)


if context.is_offline_mode():
    run_migrations_offline()
else:
    import asyncio
    asyncio.run(run_migrations_online())

I have used this one, but I have this error

[SQL: 
CREATE TABLE alembic_version (
        version_num VARCHAR(32) NOT NULL, 
        CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)

]
(Background on this error at: https://sqlalche.me/e/14/2j85)
Exception ignored in: <function Connection.__del__ at 0x1038479d0>
Traceback (most recent call last):
  File "/Users/nichitagruia/.local/share/virtualenvs/ProductsFinding-YAX73Aiq/lib/python3.9/site-packages/aiomysql/connection.py", line 1072, in __del__
  File "/Users/nichitagruia/.local/share/virtualenvs/ProductsFinding-YAX73Aiq/lib/python3.9/site-packages/aiomysql/connection.py", line 298, in close
  File "/usr/local/Cellar/python@3.9/3.9.5/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/selector_events.py", line 700, in close
  File "/usr/local/Cellar/python@3.9/3.9.5/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/base_events.py", line 746, in call_soon
  File "/usr/local/Cellar/python@3.9/3.9.5/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/base_events.py", line 510, in _check_closed
RuntimeError: Event loop is closed
(ProductsFinding) (base) nichitagruia@MacBook-Pro-Nichita ProductsFinding % 

[CaselIT]

we just added (yesterday) a clarification regarding that error at https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html#synopsis-core

tldr: add an await enginge.dispose() before the end of the run_migrations_online function. This is not needed, since the connection will be closed in any case since python is exiting, but it's better than to have the exception :)

[nikitagruia-dev]

doesn't work for me(( @CaselIT

db.py

import sqlalchemy
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker

from settings.base import SQLALCHEMY_DATABASE_URI

engine = create_async_engine(url=SQLALCHEMY_DATABASE_URI, future=True, echo=True)
# Base = declarative_base()
Session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
metadata = sqlalchemy.MetaData()


async def get_sql_db():
    async with Session() as session:
        yield session

env.py

async def run_migrations_online():
    def do_migrations(connection):
        context.configure(connection=connection, target_metadata=target_metadata)

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

    async with engine.connect() as connection:
        await connection.run_sync(do_migrations)

    await engine.dispose()


if context.is_offline_mode():
    run_migrations_offline()
else:
    import asyncio

    asyncio.run(run_migrations_online())

I just try to create the first migration but I got the same problem :(

alembic revision --autogenerate -m "Added user table"

[CaselIT]

I just tried and I cannot reproduce. (aiosqlite and asyncpg)
You may want to try using the NullPool when using alembic that should solve the issue since it disables connection pooling

[nikitagruia-dev]

@CaselIT
my proj looks like https://github.com/gruianichita/alembic-err
crash after this command

alembic revision --autogenerate -m "Added user table"

[CaselIT]

I cannot reproduce on windows and of linux (fedora). Commenting await engine.dispose() reproduces the error

If you have a reproducer it's probably best to report in another issue

[cnicodeme]

For those like me who read this issue to find a solution but couldn't make it work, the Alembic team as a "async" version of their env.py available at https://github.com/sqlalchemy/alembic/blob/9b01e5fa7178333f2e78ee0fc1322112307b51dd/alembic/templates/async/env.py

This one works well :)

[CaselIT]

There's also a cookbook entry: https://alembic.sqlalchemy.org/en/latest/cookbook.html#using-asyncio-with-alembic

[WittyGeek797]

Alembic has added async support for the env.py file and can be used with the below command:
alembic init -t async <script_directory_here>
Worked like a charm for me.

[alejoar]

What about upgrading programmatically?

When running await alembic.command.upgrade(config, "head") in my code I get

uvicorn/lifespan/on.py:89: RuntimeWarning: coroutine 'run_migrations_online' was never awaited
  return

[zzzeek]

alembic upgrade() is a sync-style method. therefore it has to be run in run_sync.

run_sync is given the "sync" connection, which has to be bound to the API per https://alembic.sqlalchemy.org/en/latest/cookbook.html#sharing-a-connection-with-a-series-of-migration-commands-and-environments .

Therefore, ensuring the changes to env.py are made as per https://alembic.sqlalchemy.org/en/latest/cookbook.html#sharing-a-connection-with-a-series-of-migration-commands-and-environments to accommodate a connection, this looks like:

def run_upgrade(connection, cfg):
    cfg.attributes['connection'] = connection
    command.upgrade(cfg, "head")

async def run_async_upgrade():
    async with async_engine.begin() as conn:
        await connection.run_sync(run_upgrade, config)

[alejoar]

@zzzeek I can't get it to work.

Following your instructions and running the upgrade in my code I get all the sql upgrade commands printed to the terminal and it finishes with:

INFO  [sqlalchemy.engine.Engine] [no key 0.00015s] ()
INFO  [sqlalchemy.engine.Engine] INSERT INTO alembic_version (version_num) VALUES ('49f3009ed437') RETURNING alembic_version.version_num
INFO  [sqlalchemy.engine.Engine] [generated in 0.00033s] ()
INFO  [sqlalchemy.engine.Engine] ROLLBACK

Nothing happens in the DB. I'm not sure what's going on.

Also the alembic cli upgrade command is now failing:

venv ❯ alembic upgrade head
ERROR [alembic.util.messaging] 'connection' argument to configure() is expected to be a sqlalchemy.engine.Connection instance, got Engine(postgresql+asyncpg://postgres:***@localhost/postgres)
  FAILED: 'connection' argument to configure() is expected to be a sqlalchemy.engine.Connection instance, got Engine(postgresql+asyncpg://postgres:***@localhost/postgres)

Am I using the wrong version of some library maybe?

EDIT: Just checked and I'm running on latest sqlalchemy and alembic

[CaselIT]

Hi @alejoar

Could you open a new issue or discussion with a complete example?
It's a bit difficult to otherwise suggest solutions without the full picture.

Thanks

[alejoar]

@CaselIT thank you. I created a minimal example repo and started a discussion here: #991

[rednaxelakvas]

CaselIT has proposed a fix for this issue in the master branch:

Add async template to Alembic https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/2609

this was extremely helpfull! ty

[eldano1995]

Alembic has added async support for the env.py file and can be used with the below command: alembic init -t async <script_directory_here> Worked like a charm for me.

Anyone experiencing this issue, just read the quoted answer.

[fazlehadi]

THANK YO SOO MUCH EVERYONE I FINALLY WITH THE HELP OF THE ASYNC env.py TEMPLATE SOLVED MY PROBLEM ITS BEEN TWO DAYS!!! my stackoverflow question: https://stackoverflow.com/questions/78742213/alembic-not-recognizing-multiple-models-using-fastapi