FastAPI – SQLAlchemy, Sessions and Migrations

FastAPI ships no database layer, which is a feature rather than an omission — it means the choice is yours and nothing is hidden. This lesson builds the layer StayHub actually uses: SQLAlchemy 2.0 with typed models, one session per request, a repository layer, Alembic for migrations, and the two performance problems that catch everyone.

Engine, session, and the difference

The engine is created once per process and owns the connection pool. The session is created per request and owns a unit of work.

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

SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False, expire_on_commit=False)

Three of those arguments are doing real work.

pool_pre_ping=True checks a pooled connection is alive before handing it out. Without it, a connection the database closed while idle — a nightly restart, a firewall timeout, a failover — surfaces as a random "server closed the connection unexpectedly" on some unlucky request rather than being quietly replaced. It costs one trivial round trip per checkout and removes a whole category of flaky.

expire_on_commit=False matters specifically in FastAPI. With the default, SQLAlchemy marks every object stale after commit(), so reading an attribute triggers a refresh query. Serialising the response happens after the route returns, which is sometimes after the session has closed — and the error you get is DetachedInstanceError pointing at a perfectly innocent attribute access in a response model.

autoflush=False stops SQLAlchemy silently issuing pending INSERTs before each query. Autoflush is convenient and makes it hard to reason about when statements actually run; explicit flush() is easier to debug.

Typed models

SQLAlchemy 2.0's declarative style uses real annotations, so the model is a type your editor understands:

class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=NAMING_CONVENTION)


class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False
    )

server_default=func.now() rather than a Python default: the database stamps the time, so rows written by a migration or a psql session get one too. onupdate is the Python-side counterpart for updates.

Mixins are the right tool for columns every table shares. StayHub has three, and the second one is a decision rather than a convenience:

class PublicIdMixin:
    """A UUID the API exposes, alongside the BIGINT primary key the database joins on.

    Two ids per row is deliberate. Integers make fast, small foreign keys; a sequential integer in
    a URL also tells the world how many rows you have and invites `/properties/1`, `/properties/2`.
    The UUID is the only id that ever leaves the process.
    """

class SoftDeleteMixin:
    """Rows are flagged, never removed — a booking's history references them forever."""

    deleted: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)

Column types that matter

Three choices in StayHub's models are worth copying deliberately.

Money is Numeric, never Float:

    price_per_night: Mapped[Decimal] = mapped_column(Numeric(10, 2), nullable=False)
    cleaning_fee: Mapped[Decimal] = mapped_column(Numeric(10, 2), default=Decimal("0"), nullable=False)

Numeric(10, 2) is exact decimal arithmetic in the database and gives you a Python Decimal back. A Float column is binary floating point at both ends, where 0.1 + 0.2 is not 0.3. Coordinates get the same treatment for the same reason — a round trip through a float turns 37.7749 into 37.774899999999995.

Enums are stored as VARCHAR, not as native Postgres enums:

"""Domain enums.

These are plain `str` enums stored as VARCHAR, not Postgres ENUM types. A native ENUM needs an
`ALTER TYPE` migration to add a value and cannot easily drop one; the check constraint is worth
less than the ability to change your mind. Hasura also exposes VARCHAR far more simply.
"""

That is a genuine trade rather than a rule. A native enum gives you database-level validation; a VARCHAR gives you the ability to add a status without an ALTER TYPE that locks the table. For a schema still moving, the second is usually worth more.

Denormalise deliberately, and say why:

    rating_average: Mapped[Decimal] = mapped_column(Numeric(3, 2), default=Decimal("0"), nullable=False)
    rating_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)

Those are derived values, kept on the row and recomputed when a review lands. Search results show a rating on every card, and computing it per card is the classic subquery-per-row that makes a listing page slow. Denormalisation is a cache with the usual cache problem — it can go stale — so it needs one place that owns updating it.

Relationships

A one-to-many is declared on both sides, with back_populates naming the other:

    host: Mapped["User"] = relationship(back_populates="properties")
    images: Mapped[list["PropertyImage"]] = relationship(
        back_populates="property",
        cascade="all, delete-orphan",
        order_by="PropertyImage.sort_order",
    )
    amenities: Mapped[list["Amenity"]] = relationship(secondary=property_amenities)
    bookings: Mapped[list["Booking"]] = relationship(back_populates="property")

cascade="all, delete-orphan" on images says an image has no life of its own: remove it from the list and the row goes. That is right for images and would be badly wrong for bookings, which is why only one of them has it.

order_by on the relationship means the list arrives sorted without every caller remembering to. Small, and it removes a whole class of "the photos are in a different order on this page" bug.

Many-to-many uses an association table. When the join carries no data of its own, a plain Table is the right shape rather than a mapped class:

property_amenities = Table(
    "property_amenities",
    Base.metadata,
    Column("property_id", ForeignKey("properties.id", ondelete="CASCADE"), primary_key=True),
    Column("amenity_id", ForeignKey("amenities.id", ondelete="CASCADE"), primary_key=True),
)

Give it a mapped class the moment it needs a column of its own — when it was added, who added it — because at that point it is an entity rather than a join.

Name your constraints

NAMING_CONVENTION = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

Set this on day one. Without it Postgres invents names like ck_bookings_1a2b3c, and a migration that needs to DROP a constraint cannot refer to it — so a downgrade you wrote months later simply does not work. Adding the convention afterwards is a migration that renames every constraint in the database, which nobody enjoys.

Let the database enforce what must be true

The most valuable thing in StayHub's schema is not a column. Bookings must not double-book, and that guarantee lives in Postgres:

        ExcludeConstraint(
            (literal_column("property_id"), "="),
            (literal_column("daterange(check_in, check_out, '[)')"), "&&"),
            where=literal_column(
                "status IN ('PENDING', 'CONFIRMED', 'COMPLETED')"
            ),
            using="gist",
            name="no_overlapping_bookings",
        ),

EXCLUDE USING gist is Postgres saying "no two rows may both match these operators": same property_id, and overlapping date ranges — but only among statuses that actually occupy the calendar, so a cancelled booking does not block its old dates.

Why this rather than a check in Python? Because two guests can pass an availability check simultaneously. Both queries run before either INSERT lands, both see free dates, and both proceed. No amount of application code fixes that; only the database can serialise it.

'[)' is the half-open bound — check-out day excluded — so one guest leaving on the 5th does not collide with another arriving on the 5th. Get that wrong and every back-to-back booking in the system is rejected.

The application still checks first, and that division of labour is the point:

        if self.bookings.overlapping(prop.id, req.check_in, req.check_out):
            raise ConflictException("Those dates are no longer available.")

The friendly check gives a readable message in the common case. The constraint is what holds when it loses the race:

        try:
            self.bookings.add(booking)
            self.db.commit()
        except IntegrityError as exc:
            self.db.rollback()
            if _is_overlap_violation(exc):
                raise ConflictException(
                    "Those dates were just booked by someone else."
                ) from exc
            raise

A check without a constraint is a race; a constraint without a check is a 500. Translating the IntegrityError is what turns the second into a 409 the UI can act on. Note the rollback() — after an IntegrityError the transaction is aborted, and every subsequent statement on that session fails until you roll back.

The repository layer

A generic base carries the operations every entity needs:

class BaseRepository(Generic[ModelT]):
    model: type[ModelT]

    def __init__(self, db: Session) -> None:
        self.db = db

    def get(self, id_: int) -> ModelT | None:
        return self.db.get(self.model, id_)

    def get_by_public_id(self, public_id) -> ModelT | None:
        stmt = select(self.model).where(self.model.public_id == public_id)
        return self.db.execute(stmt).scalar_one_or_none()

And a subclass adds the queries that entity actually needs:

class BookingRepository(BaseRepository[Booking]):
    model = Booking

The point of the layer is stated in its own docstring, and it is worth quoting because it is the argument, not the mechanism:

"""The repository layer: the only place that knows SQLAlchemy exists.

Why a layer at all, when `db.query(...)` works fine from a route? Because a route that builds
queries cannot be read without knowing the schema, and a service that builds queries cannot be
tested without a database. Keeping persistence here means the service layer reads as business
rules and the routes read as HTTP.

⚠️ Repositories NEVER commit. A commit is a transaction boundary, and only the caller knows where
that boundary is — "create a booking AND its payment, or neither" is one transaction spanning two
repositories. `flush()` is used instead where an id is needed before the commit.
"""

The half-open range, written down once

The overlap query is the availability rule, and it lives in the repository so that both the booking service and the calendar endpoint get the same answer:

        conditions = [
            Booking.property_id == property_id,
            Booking.status.in_([s.value for s in BookingStatus.blocking()]),
            Booking.check_in < check_out,
            Booking.check_out > check_in,
        ]

Both comparisons are strict, and that is what makes the ranges half-open. Using <= would reject every back-to-back booking — a bug that presents as "the calendar is wrong" rather than as an off-by-one, and therefore takes an afternoon to find.

BookingStatus.blocking() is the same classmethod the database constraint's WHERE clause mirrors. The two must agree, so they read one definition.

Querying, 2.0 style

SQLAlchemy 2.0 has one query API, and it is not db.query(). That older interface still works and is what most search results will show you; new code should use select():

# legacy — still functional, do not write new code this way
db.query(User).filter(User.email == email).first()

# 2.0
db.execute(select(User).where(User.email == email)).scalar_one_or_none()

Verbose by one method call, and worth it: the same select() works on the async API, composes cleanly, and returns a Result you unwrap deliberately. The unwrapping is where people trip, so it is worth learning the four:

scalar_one_or_none() is the right default for a lookup by unique key, because it raises when the "unique" key turns out not to be. first() silently returns one of several, which hides exactly that bug.

scalars() is the part people forget. Without it a Result yields rows — one-element tuples when you selected one entity — and the symptom is AttributeError: 'Row' object has no attribute 'email'. When you select more than one thing, rows are what you want:

    rows = db.execute(
        select(Booking, User.email, Property.title)
        .join(User, Booking.guest_id == User.id)
        .join(Property, Booking.property_id == Property.id)

That returns real tuples, unpacked as for b, email, title in rows — and it is one query rather than the twenty-one the lazy version would issue.

Indexes

Declare them on the column for a single-column index, and in __table_args__ for a composite one:

    property_id: Mapped[int] = mapped_column(
        ForeignKey("properties.id"), nullable=False, index=True
    )

        Index("ix_bookings_property_dates", "property_id", "check_in", "check_out"),

Two rules cover most of it. Index every foreign key — Postgres does not do it for you, and an unindexed FK makes both the join and the parent's delete slow. Index what you filter and sort by, in that column order: the composite above serves the availability query, which filters on property and then on a date range.

Indexes are not free. Each one is written on every INSERT and UPDATE, so a table with nine indexes writes nine extra structures per row. Add them for queries you actually run, and check with EXPLAIN ANALYZE rather than by intuition.

The N+1 problem

This is the performance bug you will actually hit. SQLAlchemy loads relationships lazily by default: reading booking.property issues a query. In a loop, that is one query per row.

bookings = db.execute(select(Booking).limit(20)).scalars().all()   # 1 query
for b in bookings:
    print(b.property.title)                                        # 20 more

Twenty-one queries for twenty rows, and it gets worse linearly while looking fine in development against ten rows of seed data. The fix is to say up front what you will need:

        stmt = (
            select(Booking)
            .options(
                # Chained joinedload: the booking's property AND that property's images, so a
                # confirmation page can show the cover photo without a second query.
                joinedload(Booking.property).joinedload(Property.images),
                joinedload(Booking.guest),
            )
            .where(Booking.public_id == public_id)
        )
        return self.db.execute(stmt).unique().scalar_one_or_none()

Two strategies, and the choice matters:

joinedload on a one-to-many multiplies the parent row by the number of children, so twenty bookings with five images each return a hundred rows to deduplicate in Python. That is what .unique() in the snippet above is for — and SQLAlchemy 2.0 requires it when a joinedload touches a collection, raising a clear error if you forget. selectinload avoids the multiplication with one extra round trip, which is usually the better trade for collections.

Transactions

The rule is one transaction per request, ended by the service. Everything the route does is inside it, and nothing commits until the work is complete:

booking = self.bookings.add(booking)      # flush — the id exists, nothing is durable
payment = self.payments.add(payment)      # flush — same transaction
self.db.commit()                          # both, or neither

If the second add raises, nothing is written. That is only true because the repositories did not commit — which is the rule from the top of this lesson, doing its job.

Rollback is automatic in one sense and not in another. An unhandled exception means get_db closes the session without committing, and the transaction is discarded. But if you catch an exception and continue, you must roll back explicitly before using the session again, as the IntegrityError handler above does.

Soft delete has a sharp edge

Flagging rows instead of removing them is the right call when other rows reference them forever. It comes with a cost that is easy to miss:

    """Headline numbers, from real aggregates — never mock data.

    ⚠️ Each of these filters `deleted = false` itself. The soft-delete flag is only automatic when
    a query goes through the ORM's own filtering; a hand-written aggregate has to say so. Forgetting
    it counts deleted rows as real ones, and the totals stay plausible while being wrong.
    """

"Plausible while being wrong" is the whole problem. A hard delete makes a forgotten filter impossible; a soft delete makes it invisible. Every hand-written count, sum and join has to repeat the condition, and the day somebody forgets, the dashboard is simply a bit off and nobody notices for a month.

Options, in increasing order of magic: repeat the filter and review for it; give the repository a base query that applies it; or use a SQLAlchemy event or a with_loader_criteria to apply it globally. StayHub does the first, because an explicit condition in a query you can read beats a filter applied somewhere you cannot see — particularly for staff tools, where "show me the deleted ones" is a real requirement.

Connection pooling

The pool is per process, and that is the number people get wrong when they deploy.

connections = workers × (pool_size + max_overflow)

  4 uvicorn workers × (5 + 10)  =  60 connections from ONE container
  3 containers                  = 180
  Postgres default max_connections = 100          <-- exhausted

The failure mode is FATAL: sorry, too many clients already under load, and it arrives all at once rather than gradually. Both halves are adjustable: raise the database's limit, lower the pool, or put PgBouncer in front so many application connections share few database ones.

Two more settings worth knowing. pool_recycle closes connections older than N seconds, which matters behind a proxy that silently drops idle ones. And pool_timeout is how long a request waits for a free connection before raising — the default of 30 seconds is usually far too long, since a request that has waited 30 seconds for a connection has already failed as far as the user is concerned.

Where the query belongs

A recurring question once there is a repository layer: does every query go through it?

StayHub's answer is no, and the exception is visible in its admin module, which builds aggregates inline:

    gross = db.execute(
        select(func.coalesce(func.sum(Booking.total), 0)).where(
            Booking.status.in_([BookingStatus.CONFIRMED, BookingStatus.COMPLETED])
        )
    ).scalar_one()

A repository method called gross_bookings_value_for_the_stats_page() would be a worse abstraction than the query it wraps — used once, named after its caller, and hiding three lines of perfectly readable SQL behind a function you have to open.

The distinction worth drawing: a repository is for queries with a domain meaning that more than one caller needs. "Bookings that overlap these dates" is a rule, belongs in the repository, and has three callers. "Sum the totals for the dashboard" is a report, has one caller, and reads better where it is used.

Getting this wrong in the other direction produces repositories with forty single-use methods, which is the same coupling with more indirection.

Migrations

Alembic owns the schema. create_all() is fine for a first sketch and unusable afterwards — it creates missing tables and does nothing about changed ones.

alembic revision --autogenerate -m "add cancellation_reason to bookings"
alembic upgrade head
alembic downgrade -1
alembic current

Autogenerate works by comparing your models against the live database, so it needs to see every model:

from app.models import Base  # noqa: F401 — importing app.models registers every table

target_metadata = Base.metadata

That noqa is load-bearing. The import looks unused and a linter will offer to remove it; if it goes, Base.metadata is empty, autogenerate compares an empty schema against your database, and cheerfully writes a migration that drops every table you have. Always read a generated migration before running it — that is the failure it protects you from.

compare_type=True is also set, so a column whose type changed produces a migration. It is off by default and its absence is silent.

Never edit an applied migration. Write a new one. An edited migration means two databases claiming the same version with different schemas, and nothing anywhere will tell you.

Autogenerate does not detect everything. It misses renames — a renamed column looks like a drop and an add, which is data loss — and it will not create the btree_gist extension the exclusion constraint above needs. Those go in by hand:

op.execute("CREATE EXTENSION IF NOT EXISTS btree_gist")

Next: designing the REST API — turning this data layer into endpoints somebody else can use, including pagination that does not repeat rows.