[FIXED] Alembic loves giving me `RuntimeWarning: coroutine 'connect' was never awaited`

Issue

Switched to using SQLAlchemy from TortoiseORM and thought I'd look into Alembic to handle its migrations. After editing the env.py and alembic.ini files I still can't get alembic to generate any migrations. The error sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s) sys:1: RuntimeWarning: coroutine 'connect' was never awaited is self-explanatory but I have no idea what exactly to change.

I'm following directions in the FastAPI-Users docs but am completely lost. Any help would be appreciated.

What I've tried:

• Setting run_migrations_offline() and run_migrations_online() as async
• Using asyncio.run() to so I can run them
• Offered a delectable sacrifice to Cthulu

models.py

import os
from typing import AsyncGenerator
from fastapi import Depends
from fastapi_users.db import SQLAlchemyBaseUserTableUUID, SQLAlchemyUserDatabase
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.ext.declarative import DeclarativeMeta, declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import Column, String, Integer, DateTime


DATABASE_URL = os.getenv('DATABASE_URL')
Base: DeclarativeMeta = declarative_base()


class Account(Base):
    __tablename__ = 'app_account'
    id = Column(Integer, primary_key=True, nullable=False)
    timezone = Column(String(5), default='+0800')


engine = create_async_engine(DATABASE_URL)
async_session_maker = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)


async def create_db_and_tables():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)                                       # noqa


async def get_async_session() -> AsyncGenerator[AsyncSession, None]:
    async with async_session_maker() as session:
        yield session

alembic.ini

sqlalchemy.url = postgresql+asyncpg://foo:[email protected]:5432/foo

env.py

# add your model's MetaData object here
# for 'autogenerate' support
from models import Base
target_metadata = Base.metadata 

Running alembic revision --autogenerate:

Traceback (most recent call last):
  File "/home/dever/venv/systemapp-ne8n42/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/alembic/config.py", line 590, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/alembic/config.py", line 584, in main
    self.run_cmd(cfg, options)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/alembic/config.py", line 561, in run_cmd
    fn(
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/alembic/command.py", line 229, in revision
    script_directory.run_env()
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/alembic/script/base.py", line 569, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 850, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "migrations/env.py", line 77, in <module>
    run_migrations_online()
  File "migrations/env.py", line 65, in run_migrations_online
    with connectable.connect() as connection:
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3234, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3313, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3280, in _wrap_pool_connect
    return fn()
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 256, in _do_get
    return self._create_connection()
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 597, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 777, in connect
    await_only(self.asyncpg.connect(*arg, **kw)),
  File "/home/dever/venv/systemapp-ne8n42/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 59, in await_only
    raise exc.MissingGreenlet(
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)
sys:1: RuntimeWarning: coroutine 'connect' was never awaited

Any help would be greatly appreciated.

Solution

It turns out if you're using Alembic with async then you just need to initialize it using the async template.

alembic init -t async migrations

Sauce: Using Asyncio with Alembic

Answered By - enchance