Add documentation about how to use the async tools (session, etc)

[tiangolo]

Privileged issue

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

Issue Content

Add documentation about how to use the async tools (session, etc).

Funding

• You can sponsor this specific effort via a Polar.sh pledge below
• We receive the pledge once the issue is completed & verified

[alvynabranches]

First of all we have to import from sqlalchemy which I dont like. It is a bit confusing. Hence I would like to import it from sqlmodel itself.

Like how we have

with Session(engine) as session:
    session.exec()

we should have

async with AsyncSession(engine) as session:
    await session.exec()

so it becomes easier for us to make sessions.

[MatsiukMykola]

from collections.abc import AsyncGenerator
from typing import Annotated, Callable

from fastapi import Depends
from sqlalchemy import create_engine
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker, Session

from core.config import settings

##############

# region 'Async Session'
engine = create_async_engine(
    settings.SQLALCHEMY_DATABASE_URI_ASYNC,
    future=True,
    echo=settings.LOCAL_DEV,
    hide_parameters=not settings.LOCAL_DEV,
    connect_args={
        # https://www.postgresql.org/docs/current/runtime-config.html
        "server_settings": {
            "application_name": f"{settings.PROJECT_NAME} {settings.VERSION} async",
            "jit": "off",
        },
    },
)

AsyncSessionFactory = sessionmaker(
    bind=engine,
    autoflush=False,
    expire_on_commit=False,
    class_=AsyncSession,
)


async def get_db() -> AsyncGenerator:
    yield AsyncSessionFactory


Session = Annotated[AsyncSession, Depends(get_db)]
# endregion


##############
@router.post(...)
async def some_function(
    session: Session)
    async with session() as db:  # noqa
         ...
        stmt = select(Model1)
        items = await db.execute(stmt)
        data = items.all()
        return data

asyncpg, works fine, except:

1. asyncpg cant execute sql-files with multistatements
2. asyncpg not shows params statements

[deshetti]

A full working example of the following is here: https://github.com/deshetti/sqlmodel-async-example
Opened a PR to add documentation to the docs: #633

from contextlib import asynccontextmanager
from typing import Optional

from fastapi import FastAPI
from pydantic import BaseModel
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker
from sqlmodel import Field, SQLModel

# Initialize FastAPI application
app = FastAPI()


# Define User model for SQLModel
class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    age: int


# Define UserCreate model for Pydantic validation
# For id field to not show up on the OpenAPI spec
class UserCreate(BaseModel):
    name: str
    age: int


# Database connection string
DATABASE_URL = "postgresql+asyncpg://postgres:postgres@localhost/sampledb"

# Create an asynchronous engine for the database
engine = create_async_engine(
    DATABASE_URL,
    echo=True,
    future=True,
    pool_size=20,
    max_overflow=20,
    pool_recycle=3600,
)


# Ayschronous Context manager for handling database sessions
@asynccontextmanager
async def get_session() -> AsyncSession:
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
    async with async_session() as session:
        yield session


# Function to create a new user in the database
async def create_user(user: User) -> User:
    async with get_session() as session:
        session.add(user)
        await session.commit()
        await session.refresh(user)
    return user


# Event handler for startup event of FastAPI application
@app.on_event("startup")
async def on_startup():
    async with engine.begin() as conn:
        # For SQLModel, this will create the tables (but won't drop existing ones)
        await conn.run_sync(SQLModel.metadata.create_all)


# Endpoint to create a new user
@app.post("/users/", response_model=User)
async def create_user_endpoint(user: UserCreate):
    db_user = User(**user.dict())
    result = await create_user(db_user)
    return result


# Main entry point of the application
if __name__ == "__main__":
    import uvicorn

    uvicorn.run(app, host="0.0.0.0", port=8000)

[PookieBuns]

First of all we have to import from sqlalchemy which I dont like. It is a bit confusing. Hence I would like to import it from sqlmodel itself.

Like how we have

with Session(engine) as session:
    session.exec()

we should have

async with AsyncSession(engine) as session:
    await session.exec()

so it becomes easier for us to make sessions.

@tiangolo Can I work on this? Add a sqlmodel version of asyncsession as well as create_async_engine

[PookieBuns]

First of all we have to import from sqlalchemy which I dont like. It is a bit confusing. Hence I would like to import it from sqlmodel itself.

Like how we have

with Session(engine) as session:
    session.exec()

we should have

async with AsyncSession(engine) as session:
    await session.exec()

so it becomes easier for us to make sessions.

It looks like sqlmodel has already implemented its own asyncsession but is just not importable from the root directory. Currently I believe you need to import it through
from sqlmodel.ext.asyncio.session import AsyncSession

If we want to adhere to sqlalchemy model import structure it should be
from sqlmodel.ext.asyncio import AsyncSession

However, according to @alvynabranches the proposed solution is
from sqlmodel import AsyncSession

@tiangolo what do you think?

[1st]

@tiangolo when do you plan to add a fully-functional async support to this library?

We are using the the SQL Alchemy at the moment and recently rewrote our code to use async. But problem is in the async m2m and m2o relations, that we need to use .awaitable_attrs that our code a bit ugly. Look on this:

docs = await folder.awaitable_attrs.documents
docs_to_authors = {doc.id: await doc.awaitable_attrs.authors for doc in docs}

Do you plan to get rid of such awaitable_attrs syntax in your version and have something like this?

docs = await folder.documents
docs_to_authors = {doc.id: await doc.authors for doc in docs}

so that under the hood it will know that "documents" and "authors" are actually awaitable props and we need to use await keyword for them. Otherwise mypy needs to catch such a problems and notify us that we forgot to add await keyword for such m2m and m2o relations when trying to load them from DB.

[ryanrain2016]

@tiangolo when do you plan to add a fully-functional async support to this library?

We are using the the SQL Alchemy at the moment and recently rewrote our code to use async. But problem is in the async m2m and m2o relations, that we need to use .awaitable_attrs that our code a bit ugly. Look on this:

docs = await folder.awaitable_attrs.documents
docs_to_authors = {doc.id: await doc.awaitable_attrs.authors for doc in docs}

Do you plan to get rid of such awaitable_attrs syntax in your version and have something like this?

docs = await folder.documents
docs_to_authors = {doc.id: await doc.authors for doc in docs}

so that under the hood it will know that "documents" and "authors" are actually awaitable props and we need to use await keyword for them. Otherwise mypy needs to catch such a problems and notify us that we forgot to add await keyword for such m2m and m2o relations when trying to load them from DB.

If you want to fetch all related data, I suggest you to try selectinload

from sqlalchemy.orm import selectinload
...
(await session.exec(select(User).options(selectinload(User.roles))).all()