Databases and migrations

An agent added a table to your research-automation service. There is a new model class, an endpoint that writes to it, and a file under alembic/versions/ with a name like 7f2c9a1b3e40_add_respondent_table.py. The tests pass. The question you cannot answer by reading the diff is whether applying that migration to the live database will destroy data, and whether the new endpoint will still respond in under a second when the table has two million rows. This article is about answering both.

The three objects: engine, session, model

Engine. Created once per process, from the database URL. It owns the connection pool — a set of already-open connections that requests borrow and return, so nobody pays TCP setup, TLS and authentication per query.

Session. Short-lived, one per request. It borrows a connection, keeps an identity map of the objects you have loaded, accumulates your changes, and writes them inside a transaction when you commit.

Model. A Python class mapped to a table. Current SQLAlchemy 2.0 style is typed:

from typing import Optional
from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

class Base(DeclarativeBase):
    pass

class Survey(Base):
    __tablename__ = "survey"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(200))
    closed_at: Mapped[Optional[str]]              # Optional -> nullable column
    responses: Mapped[list["Response"]] = relationship(back_populates="survey")

If an agent hands you id = Column(Integer, primary_key=True) and queries written as session.query(Survey).filter_by(...), that is the older 1.x idiom. It still runs, but it is a signal the model was trained on pre-2.0 examples, so check the rest for other stale habits. The 2.0 way to read is to build a statement and execute it:

from sqlalchemy import select

stmt = select(Survey).where(Survey.wave == 3).order_by(Survey.created_at.desc())
surveys = session.scalars(stmt).all()      # scalars() gives you Survey objects, not rows

N+1 queries: the bug that only appears in production

This is the single most common performance defect in agent-written database code, and it is invisible in review because the code looks like ordinary Python.

# Agent's version: 1 query, then one more per survey
surveys = session.scalars(select(Survey)).all()
return [
    {"name": s.name, "responses": len(s.responses)}   # each s.responses is a fresh SELECT
    for s in surveys
]

responses is a lazily loaded relationship: SQLAlchemy fetches it the moment you touch it. With 500 surveys that is 501 round trips. On your laptop, where the database is a local socket, each costs microseconds and the endpoint feels instant. Against Azure Database for PostgreSQL, each costs a couple of milliseconds, and the endpoint takes nine seconds — and nothing in the logs says “N+1”. You see one slow endpoint and, if you have query logging, a wall of near-identical SELECT statements.

The fix is a loader option telling SQLAlchemy to fetch the children up front:

from sqlalchemy.orm import selectinload

stmt = select(Survey).options(selectinload(Survey.responses))   # 2 queries, total
surveys = session.scalars(stmt).all()

selectinload runs a second query with the parent keys in an IN clause. Use it for collections. joinedload uses one query with an outer join — right for many-to-one scalars, but on a collection it repeats each parent row once per child, and the result needs .unique() applied. And if you only want a count, do not load the rows at all: ask the database for func.count() grouped by survey.

To stop N+1s coming back, set relationships to lazy="raise" (or add raiseload()), so any accidental lazy load raises instead of quietly querying. It turns a performance bug into a test failure, which is the only mechanism that survives agents adding code you will not read line by line.

Transactions: commit, rollback, and the silent no-op

A session opens a transaction on first use. Until you commit, your changes are real only inside that transaction.

# Agent's version: returns 200, writes nothing
@app.post("/responses")
def create(payload: ResponseIn, db: DbSession):
    row = Response(**payload.model_dump())
    db.add(row)
    return row          # no commit: rolled back when the session closes

The giveaway in the response body is "id": null — the primary key is assigned by the database on flush, so a missing id means nothing reached it. The correct version commits, and refreshes if it needs server-generated values:

@app.post("/responses")
def create(payload: ResponseIn, db: DbSession):
    row = Response(**payload.model_dump())
    db.add(row)
    db.commit()
    db.refresh(row)     # pull back the generated id and any server defaults
    return row

The opposite failure is a missing rollback. If an error happens mid-transaction and the session is returned to the pool without rolling back, locks stay held, and on PostgreSQL the next borrower of that connection finds an aborted transaction where every statement errors. One request’s bug becomes a burst of unrelated failures. Either let a dependency’s finally close the session (closing rolls back uncommitted work), or wrap the unit of work explicitly:

with db.begin():        # commits on success, rolls back on any exception
    db.add(row)
    db.add(audit_entry)

Two writes that must both succeed belong in the same transaction. A common agent mistake is committing after each one, so a failure halfway leaves an inserted row with no audit entry.

Pooling, and why cloud scaling breaks it

SQLAlchemy’s default QueuePool keeps pool_size=5 connections and allows max_overflow=10 temporary extras: up to 15 per process. Pools are per process, so the arithmetic compounds.

If your managed PostgreSQL allows 100 connections, that configuration falls over under load with too many connections — without anyone having touched a pool setting. Size pools against the database’s limit and the real worker count, not against intuition.

Two settings matter more than the sizes. pool_pre_ping=True tests a connection on checkout and silently replaces it if dead; on managed cloud databases, where idle connections get culled by the database or the network path, this removes a whole class of intermittent “server closed the connection unexpectedly” errors. And pool_recycle caps how long a connection is reused, for the same reason.

Serverless inverts the logic. Pooling assumes a long-lived process reusing connections. A platform that spins up many short-lived instances gives each one its own pool, so a traffic burst multiplies pools rather than sharing connections. There the answer is NullPool (open and close per use) plus an external proxy such as PgBouncer or Azure’s built-in pooler — or a data layer built for it. Cloudflare D1 is queried over HTTP from a Worker, so there is no pool to exhaust; the costs move elsewhere, to per-query latency and no long transactions.

Indexes

An index is a sorted structure that lets the database find matching rows without reading the whole table, paid for in storage and slower writes. Index what you actually filter, join and sort on: foreign keys, and timestamp columns used for recent-first listings.

Decide from evidence rather than instinct:

EXPLAIN ANALYZE
SELECT * FROM response WHERE survey_id = 42 ORDER BY submitted_at DESC LIMIT 50;
-- Seq Scan on response  (rows=2,100,000)  <- reading every row; index survey_id

A sequential scan over a large table in a query you run constantly is the signal. Note that indexes only help if the query can use them: wrapping a column in a function (WHERE lower(email) = ...) usually defeats a plain index on that column.

Alembic: migrations and what they can destroy

Alembic turns model changes into ordered schema changes. Each migration file has a revision id, a down_revision pointing at its parent, and upgrade() / downgrade() functions. Alembic records applied revisions in an alembic_version table, so alembic upgrade head applies whatever is missing.

alembic revision --autogenerate -m "add respondent table"   # writes a candidate
alembic upgrade head --sql > migration.sql                   # render SQL, run nothing
alembic upgrade head                                          # actually apply

Autogenerate compares your models with the live database. It reliably finds added and removed tables and columns, nullability changes, and basic index and foreign-key changes. The documentation is explicit that it is not intended to be perfect and that candidate migrations must always be reviewed. What it misses is what hurts:

• Renames. A renamed column is emitted as drop_column plus add_column. Applied as written, the data is gone, and downgrade() recreates an empty column — it cannot restore what the upgrade deleted. The intended operation is op.alter_column(..., new_column_name=...).
• Anonymously named constraints and some free-standing constraints.
• Server default changes, unless compare_server_default is enabled.
• Sequences, and Enum types on backends without native enums.

The other hazard is scale. op.add_column("response", sa.Column("locale", sa.String(), nullable=False)) works on an empty dev database and fails on two million rows, because existing rows would violate the constraint. The staged pattern is: add the column nullable, backfill in batches, then alter it to NOT NULL in a later migration.

Think forward-only. Recover from a bad migration by writing a new one that fixes forward, not by running an untested downgrade() in production. For a rename, that means four releases of boring safety — add the new column, backfill, switch the code to read it, drop the old column — instead of one clever one.

What to check in your own code

• grep -rn "session.query\|Column(" — 1.x idioms an agent may have copied wholesale.
• Every loop over ORM objects: does the body touch a relationship? That is an N+1. Look for .options(selectinload( on the statement.
• grep -rn "db.add\|session.add" and confirm each write path reaches a commit(), and that related writes share one transaction.
• Look for commit() inside a loop — usually a transaction that should be one unit.
• Check your engine call for pool_pre_ping=True, and compute pool size x workers x instances against your database’s connection limit.
• Run EXPLAIN ANALYZE on your two slowest queries and look for sequential scans on large tables.
• Read every alembic/versions/ file the agent wrote, top to bottom. Grep for drop_column, drop_table, nullable=False, alter_column, DELETE and UPDATE.
• Render alembic upgrade head --sql and read the statements before applying anything to production.
• Apply new migrations to a restored copy of production data, never only to an empty schema.