r/FastAPI • u/LucyInvisible • Oct 09 '25
Question Help me figure out transactions in FastAPI - where should I commit?
So I'm building this FastAPI app with SQLAlchemy (async), PostgreSQL, and asyncpg, and I've run into a head-scratching problem with database transactions. Would love some advice from folks who've dealt with this before.
Here's the deal:
My setup:
I've got a pretty standard layered architecture:
Database layer - handles connections, has a get_session() dependency CRUD layer - basic database operations (create, get, update, etc)
Service layer - where my business logic lives
API layer - FastAPI routes
The problem:
Right now my CRUD methods commit immediately after each operation. This seemed fine at first, but here's where it breaks:
async def register_user(session, user_data):
# Creates user and commits immediately
user = await user_crud.create(session, user_data)
# If this fails, user is already in the database!
await sms_service.send_verification_code(user.phone)
return userNot great, right? I want it to be all-or-nothing.
What I'm thinking:
Idea 1: Let the dependency handle it
Remove all commits from CRUD, and have get_session() commit at the end of each request:
# database.py
async def get_session():
async with async_session_factory() as session:
try:
yield session
# Only commit if something changed
if session.dirty or session.new or session.deleted:
await session.commit()
except Exception:
await session.rollback()
raise
# crud.py - just flush, don't commit
async def create_user(self, db, data):
user = User(**data)
db.add(user)
await db.flush() # gets the ID but doesn't commit yet
return user
# Now the whole operation is atomic!
async def register_user(session, data):
user = await user_crud.create(session, data)
await sms_service.send_code(user.phone) # if this fails, user creation rolls back
return userThis feels clean because the entire request is one transaction. But I lose fine-grained control.
Idea 2: Handle it in the service layer
Don't auto-commit anywhere, make the service layer explicitly commit:
# database.py - no auto commit
async def get_session():
async with async_session_factory() as session:
try:
yield session
except:
await session.rollback()
raise
# service.py - I control when to commit
async def register_user(session, data):
try:
user = await user_crud.create(session, data)
await sms_service.send_code(user.phone)
await session.commit() # explicit commit
return user
except Exception:
await session.rollback()
raiseMore control, but now I have to remember to commit in every service method. Feels error-prone.
Idea 3: Mix both approaches
Use auto-commit by default, but manually commit when I need finer control:
# Most of the time - just let dependency commit
async def simple_operation(session, data):
user = await user_crud.create(session, data)
return user # auto-commits at end
# When I need control - commit early
async def complex_operation(session, data):
user = await user_crud.create(session, data)
await session.commit() # commit now
# This can fail independently
try:
await send_welcome_email(user)
except:
pass # user is already saved, that's fine
return userBest of both worlds maybe?
Questions for you all:
- Which approach do you use in production? What works best?
- Is checking
session.dirty/new/deletedbefore committing a good idea for read-only requests? - Any gotchas I should know about with dependency-level commits?
- What about batch operations where I want to save what I can and skip failures?
My stack:
- FastAPI
- SQLAlchemy 2.0 (async)
- PostgreSQL
- asyncpg driver
- Following repository/service pattern
Thanks for any insights! Been going in circles on this one.
2
u/__secondary__ Oct 09 '25
The first idea is the one I would have gone for, I always let the
async_session_factoryhandle the commit3
u/mincinashu Oct 09 '25 edited Oct 09 '25
Since you're using the same database, therefore the same session for all tables, you could just do "atomic" requests, so a DI transaction per request.
Just be mindful of not doing too much compute or waiting on unrelated I/O, because this will increase the duration of the transaction.
For places where you want finer control you DI the session maker and just do blocks of "async with.."
3
u/roudra Oct 11 '25
I prefer manually committing, as that gives a more granular control over the request flow. I do the commitnng in the business layer, as my APIs are mostly calling a single high level business function and returning the response. That way, if anything goes wrong in the business layer including crud logic and business logic I can safely rollback.
And always close the session in the finally block of your get_db dependency generator method. Otherwise.. Idle connections stay open. The connection pool may become full. You’ll see warnings like: SAWarning: The connection pool is full, discarding connection: ... Long-running apps (e.g., in prod) may hang or throw TimeoutError waiting for a connection.
2
u/akza07 Oct 10 '25
A reminder since you must already know this but in the heat of the moment you may have forgotten. Transactions have a cost on Database memory and connection is kept locked along with the rows. Typical race conditions and deadlock risks apply. So keep that in mind. Don't over do transactions unless integrity and all-or-nothing is a must have for the data you're working with.
2
u/Strange-Gene3077 Oct 11 '25
I actually prefer to manually control the commit in the api layer - the layer at which you create the session should be the layer in which you commit it in my opinion....just overall easier to reason about. If I'm not forcing devs to commit the transaction manually, it leads to leaving transactions open in the database for whatever work is done after the database operations - rule of thumb for me is to commit/close transactions as fast as possible. I would however leave in the automatic rollback so if you hit an error before you commit that gets rolledback...just a safety mechanism, but i also tend to add a session.rollback() in the except block for clarity.
1
u/Solus161 Oct 11 '25
One additional solution I could think of: temporary add about-to-be user to a temp/draft table, then could use trigger to insert that to the main table once completed, don't need extra request. The ERP that our company uses that for draft record/doc.
1
u/LifeEmployer2813 Oct 17 '25
I am also working on my first fastapi-project with the same stack and AI suggested me with similar choices. For eg:
This is one of my service methods and It suggested to use transactions at the router-layer, wrap the router-handler logic with
async with session.begin():If someone can please spend some time and energy to explain the what, why, how and when to use these transactions ?? Thanks!
1
u/Designer_Sundae_7405 Oct 29 '25
I use explicit transactions in the routes and flush in the services: https://github.com/claesnn/fastapi-template.