How to handle transactions with FastAPI and async SQLAlchemy

user_5031836 0
Viewed 476

I'm confused about transactions with FastAPI and async SQLAlchemy (i.e. version 1.4+). The FastAPI tutorial for SQL databases uses request-scope transactions created via a FastAPI dependency. This is what I'd expect.

But then the documentation has another tutorial for async SQL databases in which I can't see any sign of transactions. It does use a PyPi dependency called databases which provides "simple asyncio support for a range of databases". I don't know if it does transactions internally but I don't see how they could be request-scoped without help from the calling application.

And then again I see a blog article about FastAPI and async SQLAlchemy which appears to do things the original way with request-scoped transactions and FastAPI dependencies. Which is the "correct" approach? Does it matter if one is using Core or ORM mode or is that no longer a thing in SQLAlchemy 1.4?

fastapi async-await sqlalchemy transactions
1 Answers

databases is a separate library that have different logic of work. It take from SQLAlchemy only query building and do not use SQLAlchemy Sessions.

If you are interesting how to handle transactions in databases code will be something like that:

from databases import Database
from sqlalchemy import insert, select


async def create_something(db: Database, something: SomethingCreateSchema) -> SomethingSchema:
    async with db.transaction():  # <- here
        something_id = await db.execute(query=insert(SomethingDBModel), values=something.dict())
        db_something = await db.fetch_one(query=select(SomethingDBModel).filter(User.id == user_id))
        return SomethingSchema(**db_something)

In this case all DB calls will be in same transaction

user_173207170
Related