FastAPI Database Transactions — Sessions, Atomicity & Connection Pooling

🎯 What You Will Learn

Commit at the wrong moment and a crash mid-request leaves your database in a broken state — money debited but never credited. This post is about making multi-step writes all-or-nothing, and keeping your connection pool healthy under load.

Why per-operation commits corrupt data (see the failure first)
Session vs transaction vs connection — the mental model
Atomic writes: one commit, rollback on any error
The clean with session.begin() unit-of-work pattern
Savepoints (begin_nested) for partial rollback
Connection pool tuning: pool_size, max_overflow, pool_pre_ping

Prerequisites: a FastAPI app connected to PostgreSQL (how-to-connect-fastapi-to-postgres) and basic SQLModel. We'll use a money-transfer example because it makes atomicity impossible to ignore.

fastapi-transactions/
├── requirements.txt
├── docker-compose.yml     # local Postgres
└── app/
    ├── __init__.py
    ├── settings.py         # DATABASE_URL via pydantic-settings
    ├── database.py         # engine (with pool tuning) + get_session
    ├── models.py           # Account table
    └── main.py             # transfer endpoints

The Problem: Partial Writes Corrupt Data (See It First)

A transfer is two writes: debit one account, credit another. Here's the version almost everyone writes first — commit as you go. It works in the happy path and destroys money in the sad one.

app/main.py (the naive version)

@app.post("/transfer-naive")
def transfer_naive(data: TransferIn, session: SessionDep):
    src = session.get(Account, data.from_id)
    src.balance -= data.amount
    session.add(src)
    session.commit()              # 💥 debit is now PERMANENT

    dst = session.get(Account, data.to_id)
    if dst is None:               # ...or any error: crash, timeout, bug
        raise HTTPException(status_code=404, detail="Destination not found")

    dst.balance += data.amount
    session.add(dst)
    session.commit()
    return {"ok": True}

Send money to an account that doesn't exist:

$ curl -s -X POST http://127.0.0.1:8000/transfer-naive \
    -H "Content-Type: application/json" \
    -d '{"from_id": 1, "to_id": 999, "amount": "100.00"}'
{"detail":"Destination not found"}

# Alice's balance AFTER the failed transfer:
$ curl -s http://127.0.0.1:8000/accounts/1
{"id":1,"owner":"Alice","balance":"900.00"}   <-- 100 pesos VANISHED

⚠️

This is the classic production bug. The first commit() made the debit permanent. When the credit failed, there was nothing to undo it — the money is simply gone. Two writes that must happen together were committed separately. That's a broken transaction boundary.

Session vs Transaction vs Connection

These three get muddled constantly. Keep them straight and transactions stop being scary:

Term	What it is	In FastAPI
Connection	A physical link to the database. Expensive to open.	Borrowed from a pool, not created per request.
Transaction	A unit of work that fully commits or fully rolls back (atomic).	Bounded by `commit()` / `rollback()`.
Session	SQLAlchemy's work area: tracks your objects and runs the transaction on a connection.	One `Session` per request via a dependency.

💡

Ang importante dito: a transaction is all-or-nothing. Every write between the start and the commit() lands together; if you rollback() (or an exception does it for you), none of them land. Your job is to draw that boundary around writes that belong together.

The Session Dependency (One Per Request)

Never share a session across requests or threads — a Session is not thread-safe. Create one per request with a dependency and let the with block close it (returning the connection to the pool) when the request ends.

app/database.py

from sqlmodel import Session, SQLModel, create_engine

from app.settings import settings

engine = create_engine(settings.database_url, echo=False)


def create_db_and_tables() -> None:
    SQLModel.metadata.create_all(engine)


def get_session():
    # one session per request; closed automatically at the end
    with Session(engine) as session:
        yield session

app/main.py (dependency wiring)

from typing import Annotated

from fastapi import Depends
from sqlmodel import Session

from app.database import get_session

SessionDep = Annotated[Session, Depends(get_session)]

💡

The with Session(engine) as session: block guarantees the session is closed even if the endpoint raises. Closing a session releases its connection back to the pool — that's what keeps you from running out of connections under load.

The Fix: One Commit, Rollback on Error

Do both writes, then commit once. If anything goes wrong before that single commit, roll back so neither write lands. Here's the explicit try/except form so you can see exactly what happens:

app/main.py

from decimal import Decimal

from fastapi import FastAPI, HTTPException, status
from pydantic import BaseModel

from app.models import Account

app = FastAPI(title="FastAPI Transactions", version="0.1.0")


class TransferIn(BaseModel):
    from_id: int
    to_id: int
    amount: Decimal


@app.post("/transfer")
def transfer(data: TransferIn, session: SessionDep):
    src = session.get(Account, data.from_id)
    dst = session.get(Account, data.to_id)
    if src is None or dst is None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Account not found")
    if src.balance < data.amount:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Insufficient funds")

    try:
        src.balance -= data.amount
        dst.balance += data.amount
        session.add(src)
        session.add(dst)
        session.commit()          # both writes land together, or...
    except Exception:
        session.rollback()        # ...neither does
        raise

    session.refresh(src)
    session.refresh(dst)
    return {"from_balance": src.balance, "to_balance": dst.balance}

✅

Now it's atomic: the balance changes only exist in memory until commit(). A failed lookup or a crash before that commit leaves the database exactly as it was — no vanished money.

The Clean Way: with session.begin()

The try/except/rollback dance is easy to get wrong. SQLAlchemy's session.begin() context manager does it for you: it commits on success and rolls back on any exception. This is the "unit of work" pattern, and it's what I reach for in real code.

app/main.py

@app.post("/transfer")
def transfer(data: TransferIn, session: SessionDep):
    # commit if the block finishes; rollback if it raises
    with session.begin():
        src = session.get(Account, data.from_id)
        dst = session.get(Account, data.to_id)
        if src is None or dst is None:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Account not found")
        if src.balance < data.amount:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Insufficient funds")

        src.balance -= data.amount
        dst.balance += data.amount
        # no explicit commit — the context manager commits on exit

    return {"from_balance": src.balance, "to_balance": dst.balance}

⚠️

Gotcha: call session.begin() before any query on that session. A plainSession auto-starts a transaction on first use, and calling begin() when one is already active raises InvalidRequestError. In the dependency above the session is fresh, so we begin first — that's why the queries live inside the with block.

💡

Raising HTTPException inside the block still triggers the rollback (it's an exception), then FastAPI turns it into the proper 4xx response on the way out. You get atomicity and a clean error response for free.

Savepoints: Partial Rollback with begin_nested()

Sometimes you want "try this sub-step; if it fails, undo just that part but keep the rest." That's a savepoint, created with begin_nested(). The outer transaction survives; only the nested block rolls back.

app/main.py

@app.post("/transfer-with-bonus")
def transfer_with_bonus(data: TransferIn, session: SessionDep):
    with session.begin():                      # outer transaction
        src = session.get(Account, data.from_id)
        dst = session.get(Account, data.to_id)
        if src is None or dst is None:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Account not found")

        src.balance -= data.amount
        dst.balance += data.amount

        # a "nice to have" bonus that must NOT break the core transfer
        try:
            with session.begin_nested():       # SAVEPOINT
                apply_referral_bonus(session, dst)
        except Exception:
            # bonus failed -> only the savepoint rolls back;
            # the transfer above still commits with the outer block
            pass

    return {"from_balance": src.balance, "to_balance": dst.balance}

💡

Use savepoints sparingly and deliberately — they're for a genuinely optional sub-step inside a bigger unit of work. If two things must both succeed, don't nest; keep them in the same outer transaction.

Connection Pooling (The Other Half)

Opening a database connection is expensive, so SQLAlchemy keeps a pool of them and hands one to each session, then takes it back when the session closes. Under real traffic, the pool settings decide whether your API stays up or throws QueuePool limit ... connection timed out.

app/database.py (tuned engine)

from sqlmodel import create_engine

from app.settings import settings

engine = create_engine(
    settings.database_url,
    pool_size=10,          # steady connections kept open
    max_overflow=5,        # extra temporary connections under spikes (max = 15)
    pool_timeout=30,       # seconds to wait for a free connection before erroring
    pool_pre_ping=True,    # check a connection is alive before using it
    pool_recycle=1800,     # recycle connections older than 30 min
    echo=False,
)

Setting	What it does	Why it matters
`pool_size`	Connections kept open at rest	Too low = requests queue; too high = you exhaust Postgres' max_connections
`max_overflow`	Extra connections allowed during spikes	Absorbs bursts without keeping them open forever
`pool_timeout`	Wait time for a free connection	Fail fast instead of hanging when the pool is drained
`pool_pre_ping`	Tests a connection before handing it over	Kills the dreaded 'server closed the connection unexpectedly' error
`pool_recycle`	Max age before a connection is replaced	Beats idle-timeout kills from Postgres/proxies (e.g. PgBouncer)

⚠️

Sizing rule of thumb: total connections = (pool_size + max_overflow) × number of worker processes. Four uvicorn workers with the config above = up to 60 connections. Keep that comfortably under Postgres' max_connections (default 100), or use a pooler like PgBouncer in front.

💡

Async note: on the async engine (fastapi-async-sqlalchemy) the same knobs exist on create_async_engine. The pooling concepts here carry over unchanged.

Try It (Postgres + curl)

requirements.txt

fastapi==0.116.1
uvicorn[standard]==0.30.6
sqlmodel==0.0.22
psycopg[binary]==3.2.1
pydantic-settings==2.5.2

1Start Postgres and point the app at it

docker-compose.yml

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
      POSTGRES_DB: bank
    ports:
      - "5432:5432"

app/settings.py

from pydantic_settings import BaseSettings, SettingsConfigDict


class Settings(BaseSettings):
    # never hardcode secrets — read from env / .env
    database_url: str = "postgresql+psycopg://app:app@localhost:5432/bank"
    model_config = SettingsConfigDict(env_file=".env")


settings = Settings()

2Model + seed two accounts, then run

app/models.py

from decimal import Decimal

from sqlalchemy import Numeric
from sqlmodel import Column, Field, SQLModel


class Account(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    owner: str
    balance: Decimal = Field(
        default=Decimal("0.00"),
        sa_column=Column(Numeric(12, 2), nullable=False),
    )

run.sh

docker compose up -d
python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
uvicorn app.main:app --reload
# (seed Alice=1000, Bob=500 on startup, then:)

3Prove the transfer is atomic

# happy path: 100 moves from Alice -> Bob
$ curl -s -X POST http://127.0.0.1:8000/transfer \
    -H "Content-Type: application/json" \
    -d '{"from_id":1,"to_id":2,"amount":"100.00"}'
{"from_balance":"900.00","to_balance":"600.00"}

# sad path: insufficient funds -> whole transaction rolls back
$ curl -s -X POST http://127.0.0.1:8000/transfer \
    -H "Content-Type: application/json" \
    -d '{"from_id":1,"to_id":2,"amount":"99999.00"}'
{"detail":"Insufficient funds"}

# Alice is UNCHANGED — no partial write
$ curl -s http://127.0.0.1:8000/accounts/1
{"id":1,"owner":"Alice","balance":"900.00"}

✅

You did it: the transfer either fully happens or fully doesn't. The failed request left both balances exactly as they were — that's a correct transaction boundary in action.

Common Mistakes (and Fixes)

Mistake	Symptom	Fix
Committing after each write	Partial writes on failure (vanished money)	One commit per unit of work, or `with session.begin()`
Not rolling back on error	Session stuck in a failed transaction; later queries error	`rollback()` in `except`, or let `begin()` do it
Committing inside a loop	N round-trips, slow, non-atomic	Do the loop, then a single commit at the end
Sharing one session across requests/threads	Random errors, corrupted state	One session per request via a dependency
No `pool_pre_ping`	'server closed the connection unexpectedly' after idle	Enable `pool_pre_ping=True` (and `pool_recycle`)
Pool too large for Postgres	'too many connections' under load	(pool_size + overflow) × workers < max_connections; add PgBouncer

Production Notes

Keep transactions short. Don't call external APIs or do heavy CPU work inside a with session.begin() block — you're holding a connection and locks the whole time. Fetch first, transact fast.
Use Decimal for money. Never float. A Numeric(12, 2) column plus Python Decimal avoids rounding surprises.
Guard against race conditions. Two concurrent transfers can both read the old balance. For real money, add row locking (SELECT ... FOR UPDATE via with_for_update()) or a DB constraint (CHECK (balance >= 0)).
Drive the URL and pool sizes from config. Different environments need different pool sizes — keep them in pydantic-settings, never hardcoded.

What's Next

✅

🚀 Recommended next reads:

how-to-connect-fastapi-to-postgres — the connection setup this post builds on
fastapi-async-sqlalchemy — the same transactions, async style
fastapi-crud-patterns — where these transaction boundaries live in real CRUD code
fastapi-error-handling — turn rollback-triggering errors into clean responses

Recap: a transaction is all-or-nothing — draw the boundary around writes that belong together, commit once, and let with session.begin() handle commit/rollback. Use savepoints only for genuinely optional sub-steps, give each request its own session, and tune the connection pool so it stays healthy under load.

fastapi-transactions/               # ✅ finished
├── requirements.txt
├── docker-compose.yml
└── app/
    ├── __init__.py
    ├── settings.py         # DATABASE_URL from env
    ├── database.py         # tuned engine (pool_size/pre_ping) + get_session
    ├── models.py           # Account (Numeric money column)
    └── main.py             # atomic /transfer via with session.begin()

FastAPI Database Transactions (Sessions, Atomicity & Connection Pooling)

🎯 What You Will Learn

The Problem: Partial Writes Corrupt Data (See It First)

Session vs Transaction vs Connection

The Session Dependency (One Per Request)

The Fix: One Commit, Rollback on Error

The Clean Way: with session.begin()

Savepoints: Partial Rollback with begin_nested()

Connection Pooling (The Other Half)

Try It (Postgres + curl)

Common Mistakes (and Fixes)

Production Notes

What's Next

About the Author

Thirdy Gayares

🎯 What You Will Learn

1The Problem: Partial Writes Corrupt Data (See It First)

2Session vs Transaction vs Connection

3The Session Dependency (One Per Request)

4The Fix: One Commit, Rollback on Error

5The Clean Way: with session.begin()

6Savepoints: Partial Rollback with begin_nested()

7Connection Pooling (The Other Half)

8Try It (Postgres + curl)

9Common Mistakes (and Fixes)

10Production Notes

11What's Next

About the Author

Thirdy Gayares

The Problem: Partial Writes Corrupt Data (See It First)

Session vs Transaction vs Connection

The Session Dependency (One Per Request)

The Fix: One Commit, Rollback on Error

The Clean Way: with session.begin()

Savepoints: Partial Rollback with begin_nested()

Connection Pooling (The Other Half)

Try It (Postgres + curl)

Common Mistakes (and Fixes)

Production Notes

What's Next