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?