I 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.
What I've tried:
run_migrations_offline() and run_migrations_online() as asyncasyncio.run() to so I can run themmodels.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:pass123@127.0.0.1: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
What can I try next?
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