SQLAlchemy AttributeError: 'AsyncEngine' object has no attribute '_run_ddl_visitor'

Question

I am trying to use the async version of SQLAlchemy in an asyncio app. However, when trying to create the tables using Metadata().create_all(), the following error occurs

AttributeError: 'AsyncEngine' object has no attribute '_run_ddl_visitor'

How do we fix this problem? Thanks!

import asyncio
from sqlalchemy import Column, String, 
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.sql.schema import MetaData
from sqlalchemy.ext.asyncio import create_async_engine


connection_url = f"postgresql+asyncpg://{user}:{password}@{host}:{port}/{dbname}"
engine = create_async_engine(connection_url)
Session = sessionmaker(autocommit=False, autoflush=False, bind=engine)
db_session = scoped_session(Session)

Base = declarative_base()
Base.query = db_session.query_property()

class Foo(Base):
    __tablename__ = "foo"    
    id = Column(String, primary_key=True)
    name = Column(String)

class Store:
    def __init__(self):
        super().__init__()
        self.connection = None

    async def connect(self):
        self.connection = await engine.begin()
        metadata = MetaData(bind=engine)
        await self.connection.run_sync(metadata.create_all())
    

async def main():
    store = Store()
    await store.connect()

if __name__ == '__main__':
    asyncio.run(main())

---

Attempt #2

import asyncio
from sqlalchemy import Column, String, 
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.sql.schema import MetaData
from sqlalchemy.ext.asyncio import create_async_engine


connection_url = f"postgresql+asyncpg://{user}:{password}@{host}:{port}/{dbname}"
engine = create_async_engine(connection_url)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)

Base = declarative_base()

class Foo(Base):
    __tablename__ = "foo"    
    id = Column(String, primary_key=True)
    name = Column(String)
    __mapper_args__ = {"eager_defaults": True}

class Store:
    def __init__(self):
        super().__init__()

    async def connect(self):
        async with async_session() as db_session:
            async with db_session.begin():
                await db_session.run_sync(Base.metadata.create_all)

async def main():
    store = Store()
    await store.connect()

if __name__ == '__main__':
    asyncio.run(main())

Error:

AttributeError: 'Session' object has no attribute '_run_ddl_visitor'

Answer 1

Replace:

Base.metadata.create_all(engine)

with this:

async def init_models():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)

asyncio.run(init_models())

in details:

@app.on_event("startup")
async def init_tables():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)

@app.post("/users")
async def create_user(user: User):
    async with async_session() as session:
        session.add(UserModel(**user.dict()))
        await session.flush()
        await session.commit()
        return UserResponse.from_orm(user)