Do you support importing Asynchronous Sessions from sqlmodel？

[yuanjie-ai]

Privileged issue

• I'm @tiangolo or he asked me directly to create an issue here.

Issue Content

from sqlmodel import AsyncSession

[AtticusZeller]

here for you

from sqlmodel.ext.asyncio.session import AsyncSession

you can search more info in discussion if interested

[supermacro]

Relatedly, suppose I wanted to use sqlmodel with https://fastapi-users.github.io/ ... the docs require that I create an async engine via create_async_engine... How can I integrate this with sqlmodel?

More generally, want to learn how to use async io with sqlmodel

[AtticusZeller]

Relatedly, suppose I wanted to use sqlmodel with https://fastapi-users.github.io/ ... the docs require that I create an async engine via create_async_engine... How can I integrate this with sqlmodel?

More generally, want to learn how to use async io with sqlmodel

1. sqlmodel is built upon SQLAlchemy, so create_async_engine can normally work with sqlmodel without conflicts.

2. i quit async orm because it annoying to write test for them

[ekalosak]

Using the create_async_engine, I run into this issue:

ValueError: the greenlet library is required to use this function. No module named 'greenlet'

tl;dr

1. pip install greenlet asyncpg
2. Make sure your DB URL is specified like this: [1.4] The asyncio extension requires an async driver to be used. The loaded 'psycopg2' is not async. sqlalchemy/sqlalchemy#6046 (comment)
3. Setup like this:

from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import sessionmaker
from sqlmodel.ext.asyncio.session import AsyncSession
_engine = create_async_engine(PG_URL, echo=PG_VERBOSE)
_async_session_factory = sessionmaker(_engine, class_=AsyncSession)
async with _engine.begin() as conn:
    await conn.run_sync(SQLModel.metadata.create_all)

4. In your code, interact with sessions like this:

async with _async_session_factory() as session:
    heroes = await session.exec(select(Hero))
heroes = heroes.all()

Troubleshooting

• `AttributeError: 'AsyncEngine' object has no attribute '_run_ddl_visitor' https://stackoverflow.com/questions/68230481/sqlalchemy-attributeerror-asyncengine-object-has-no-attribute-run-ddl-visit
• I didn't seem to need any future=True in the create_async_engine call but maybe that's required and I'm missing something.

Example code

• https://docs.sqlalchemy.org/en/14/_modules/examples/asyncio/async_orm.html

Environment

• Python 3.12.9
• sqlalchemy 2.0.38
• sqlmodel 0.0.22