🐍 Backend with Python · Chapter 6

Talking to a database: SQLAlchemy and connection pools

Opening a database connection costs a network handshake, TLS and an auth exchange — often more than the query itself — so a service that connects per request spends its time saying hello.

Study this lesson free in the app →

The basics

SQLAlchemy is Python's standard way to talk to relational databases: an Engine that owns a pool of open connections, a Session that groups one request's work into a transaction, and typed models (Mapped[int]) that map rows to objects.

The problem it solves: connecting per request is slow and Postgres caps total connections (100 by default), so a service must reuse a few connections safely and keep each request's writes all-or-nothing.

The mental model: a chai stall with 15 steel tumblers. Pour, serve, wash, reuse — nobody buys a new glass per customer. When every tumbler is out, the next customer waits; if someone walks off with one, the stall runs dry by evening.

Explained

Tumblers at the chai stall

A chai stall outside Chennai Central keeps 5 tumblers on the counter. At 8 AM rush, the owner pulls 10 more from a crate under the table. After rush, he puts the extras away.

A customer takes a tumbler, drinks, hands it back; it's rinsed and refilled for the next person. If all 15 are out, the next customer stands and waits — and after half a minute, gives up and walks off.

Two ways the stall dies:

• One customer wanders off holding a tumbler while he takes a phone call. Then another. By 9 AM there are no tumblers, and a queue of people who could have been served in seconds.
• Twelve stalls on the same pavement each want 15 tumblers from one dishwasher who can handle 100 at a time — 180 asked for, 100 available.

The tumblers are connections. The crate is overflow. The dishwasher is Postgres.

Three layers, each with one job

Engine      create_engine(url)        one per process; owns the pool
  └ Pool    QueuePool                 5 kept open, +10 overflow, wait 30 s
     └ Connection                     a real socket to the database
Session     Session(engine)           your unit of work for one request

• Engine: created once at import/startup. Creating one per request throws away the pool — every request pays the full connect cost.
• Session: borrows a connection from the pool at the first query, holds it until commit/rollback/close, then returns it. It also keeps an identity map — load ride 42 twice in one session, you get the same Python object.
• 2.0 style: models with Mapped[...] and mapped_column, queries with select(), results via session.scalars(...) or session.get(Model, id).

Defaults in the installed SQLAlchemy 2.1, checked: pool_size=5, max_overflow=10, pool_timeout=30.0, pool_recycle=-1 (never), pool_pre_ping=False.

A model, a write, a query — and the SQL it sends

from sqlalchemy import create_engine, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase): pass

class Ride(Base):
    __tablename__ = "rides"
    id: Mapped[int] = mapped_column(primary_key=True)
    pickup: Mapped[str] = mapped_column(String(80))
    fare: Mapped[int]

engine = create_engine("sqlite://", echo=True)
Base.metadata.create_all(engine)
with Session(engine) as session:
    session.add(Ride(pickup="HSR", fare=240))
    session.commit()
    ride = session.scalars(select(Ride).where(Ride.fare > 200)).first()

echo=True prints what really went over the wire (trimmed):

CREATE TABLE rides (id INTEGER NOT NULL, pickup VARCHAR(80) NOT NULL,
                    fare INTEGER NOT NULL, PRIMARY KEY (id))
BEGIN (implicit)
INSERT INTO rides (pickup, fare) VALUES (?, ?)   ('HSR', 240)
COMMIT
BEGIN (implicit)
SELECT rides.id, rides.pickup, rides.fare FROM rides WHERE rides.fare > ?   (200,)
ROLLBACK

Note the last line: leaving the with block without committing rolls back. Mapped[int] with no | None became NOT NULL — the type hint is the schema.

A payment that fails halfway

Run for real: after pay(24000) the balances were {'rider': 26000, 'driver': 24000}, and after the failing pay(10000) they were still exactly that. Without s.begin() you'd need explicit commit()/rollback() — and someone will forget one.

One Session per request in FastAPI

from typing import Annotated
from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

engine = create_engine("sqlite:///rides.db", pool_pre_ping=True)  # once

def get_db():
    with Session(engine) as session:
        yield session            # closed after the response, even on error

DB = Annotated[Session, Depends(get_db)]
app = FastAPI()

@app.post("/rides", status_code=201)
def create_ride(pickup: str, fare: int, db: DB):
    ride = Ride(pickup=pickup, fare=fare)
    db.add(ride)
    db.commit()
    return {"id": ride.id, "pickup": ride.pickup, "fare": ride.fare}

@app.get("/rides/{ride_id}")
def get_ride(ride_id: int, db: DB):
    ride = db.get(Ride, ride_id)
    if ride is None:
        raise HTTPException(404, "ride not found")
    return {"id": ride.id, "pickup": ride.pickup, "fare": ride.fare}

Via TestClient: {'id': 1, 'pickup': 'HSR', 'fare': 240}, then 200, then 404 for id 9 — and afterwards pool.status() showed Current Checked out connections: 0. Every borrowed connection came back.

The yield dependency is the whole trick: FastAPI runs the code after yield when the request finishes, so the session always closes. be-009 swaps this for AsyncSession; the shape stays the same.

The ride that vanished after commit

def book(fare):
    with Session(engine) as session:
        ride = Ride(fare=fare)
        session.add(ride)
        session.commit()
    return ride

ride = book(240)
print(ride.fare)

Pool maths across a fleet

Each process has its own pool. So the real ceiling is:

pods × workers per pod × (pool_size + max_overflow)
  4  ×        3        × (   5     +     10      ) = 180

Postgres ships with max_connections = 100. At peak, 180 > 100: new connections are refused and the service fails in a way no single pod's metrics explain. Autoscaling to 8 pods doubles the problem.

The opposite failure is a pool too small for the work: requests queue for a connection, and after pool_timeout you get TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 — every endpoint takes exactly 30 s, then fails.

Size it backwards: reserve ~10 for migrations, admin and cron; split the rest. (100 − 10) // (4 × 3) = 7 per process → e.g. pool_size=5, max_overflow=2, worst case 84.

Past a few hundred processes, put PgBouncer in front: thousands of app connections share a few dozen real ones. Also set pool_pre_ping=True (one cheap check per checkout, survives DB restarts) and pool_recycle below any firewall/proxy idle timeout so you never get handed a socket that was silently cut.

Five ways to run out of connections or speed

• N+1 queries — loop over drivers, touch driver.rides: 3 drivers made 4 queries in the run. select(Driver).options(selectinload(Driver.rides)) made 2, whatever the count.
• Holding a session across slow I/O — calling a payment API that takes 8 s while a transaction is open pins a connection for 8 s. Do the network call outside the transaction.
• Engine per request — no pool reuse; every request reconnects.
• Sharing one Session between requests or threads — Sessions are not thread-safe. One per request.
• Forgetting to commit — closing a Session rolls back; your write silently never happened. Use with Session(engine) as s, s.begin(): so commit is automatic on success.
• Raw f-string SQL — text(f"... {user_input}") is SQL injection. Use select() or bound parameters.

Words to know

In two minutes

• Engine = one per process, owns the connection pool; Session = one per request, owns a transaction.
• QueuePool defaults: pool_size 5, max_overflow 10, pool_timeout 30 s; pre_ping off, recycle off.
• Use with Session(engine) as s, s.begin(): — commit on success, rollback on exception, connection always returned.
• In FastAPI, a yield dependency gives each request its own session and closes it after the response.
• commit() expires loaded attributes; read what you need before the session closes or you get DetachedInstanceError.
• Lazy relationships in a loop cause N+1; selectinload turns N+1 queries into 2.
• Budget connections across the fleet: pods × workers × (pool_size + max_overflow) must fit under Postgres max_connections (100 by default).

Test yourself

There are 9 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.