Appearance
3.14.6 — Databases with SQLAlchemy 2.0
The order service's "my orders" page is fast in development, where each test customer has three orders. In production, a loyal customer with four hundred orders opens it, and the page takes six seconds. The logs show four hundred and one database queries for one page view. Meanwhile, during a flash sale, two customers manage to buy the last mug at the same moment. Both problems live in the layer between Python objects and database rows. This page covers that layer with SQLAlchemy, the standard database toolkit for Python: how it talks to the database, how to model tables, how sessions and transactions work, how to query efficiently from async FastAPI code, and how to change the schema safely with migrations. Part 7 explains databases themselves. This page is about using one well from Python.
1. The story, and the two layers
Michael Bayer released SQLAlchemy in 2006. Its idea was unusual at the time: instead of hiding SQL, it would give Python programmers the full power of SQL, expressed as Python objects. It has two layers.
- Core builds SQL statements from Python expressions, runs them, and manages connections.
select(Product).where(Product.stock > 0)is Core. - ORM, short for object-relational mapper, maps database rows to Python objects. A row in
ordersbecomes anOrderobject with attributes, and related rows become lists of related objects.
Version 1.4 (2021) added asyncio support. Version 2.0 (January 2023) unified the query style around one select() function and added type-hinted models using Mapped[...]. The examples here use SQLAlchemy 2.1 with the async SQLite driver aiosqlite, so they run anywhere. In production you would point the same code at PostgreSQL with the asyncpg driver by changing one URL.
2. The engine and the connection pool
Opening a database connection is slow. It means a TCP connection, often TLS, authentication and session set-up: several milliseconds, sometimes tens. An engine owns a connection pool: a set of already-open connections that are lent to code that needs them and returned afterwards.
python
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
engine = create_async_engine(
"sqlite+aiosqlite:///shop.db", # (1) postgresql+asyncpg://user:pass@host/shop in production
pool_size=10, # (2)
max_overflow=5, # (3)
pool_pre_ping=True, # (4)
)
SessionFactory = async_sessionmaker(engine, expire_on_commit=False) # (5)(1)The URL names the database type, the driver and the location.(2)Keep up to 10 connections open and reusable.(3)Allow 5 extra connections in a burst, closed again when returned.(4)Check a connection is still alive before lending it, so a connection the database closed overnight does not fail the first morning request.(5)A factory that makes sessions bound to this engine.expire_on_commit=Falseis explained in section 4.
Create the engine once per process, in the lifespan from 3.14.5, and call await engine.dispose() on shutdown. Then do the arithmetic that catches teams out. Each Uvicorn worker process has its own pool. Four workers × 15 connections × 6 server instances is 360 connections, and a default PostgreSQL server allows 100. Size pools from the database's limit downwards, or put a connection pooler such as PgBouncer in front of the database.
3. Models: tables as classes
A model class describes one table. SQLAlchemy 2.0 reads the type hints, exactly like Pydantic and dataclasses do.
python
from datetime import datetime, timezone
from decimal import Decimal
from sqlalchemy import CheckConstraint, DateTime, ForeignKey, Numeric, String
from sqlalchemy.ext.asyncio import AsyncAttrs
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(AsyncAttrs, DeclarativeBase): # (1)
pass
class Customer(Base):
__tablename__ = "customers"
id: Mapped[int] = mapped_column(primary_key=True) # (2)
email: Mapped[str] = mapped_column(String(255), unique=True)
orders: Mapped[list["Order"]] = relationship(back_populates="customer") # (3)
class Product(Base):
__tablename__ = "products"
__table_args__ = (CheckConstraint("stock >= 0", name="stock_not_negative"),) # (4)
sku: Mapped[str] = mapped_column(String(20), primary_key=True)
price: Mapped[Decimal] = mapped_column(Numeric(10, 2)) # (5)
stock: Mapped[int]
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(primary_key=True)
customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id")) # (6)
status: Mapped[str] = mapped_column(String(20), default="pending")
created_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), default=lambda: datetime.now(timezone.utc))
customer: Mapped[Customer] = relationship(back_populates="orders")
lines: Mapped[list["OrderLine"]] = relationship(
back_populates="order", cascade="all, delete-orphan") # (7)
class OrderLine(Base):
__tablename__ = "order_lines"
id: Mapped[int] = mapped_column(primary_key=True)
order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
sku: Mapped[str] = mapped_column(ForeignKey("products.sku"))
quantity: Mapped[int]
unit_price: Mapped[Decimal] = mapped_column(Numeric(10, 2))
order: Mapped[Order] = relationship(back_populates="lines")(1)All models inherit from one base class, which collects their table definitions inBase.metadata.AsyncAttrsadds a helper for async code, used in section 6.(2)Mapped[int]says the column holds integers and isNOT NULL.Mapped[int | None]would allowNULL.mapped_columnadds database details.(3)A relationship is not a column. It tells the ORM how to load related objects through a foreign key.back_populateslinks the two sides, soorder.customerandcustomer.ordersstay consistent.(4)A database rule: stock can never go below zero, whatever any code does. Constraints in the database are the last line of defence.(5)Numeric(10, 2)stores exact decimals and maps to Python'sDecimal, never a float (3.13.3).(6)A foreign key makes the database refuse an order whose customer does not exist.(7)cascade="all, delete-orphan"means lines are saved with their order and deleted with it.
How does Order.status become both a value on an object and a column in a query? Each mapped attribute is replaced on the class by a descriptor (3.13.7). Read through an instance, order.status returns the loaded value and records changes. Read through the class, Order.status returns an expression, which is why Order.status == "paid" builds SQL instead of returning True or False.
4. The session: unit of work and identity map
You do not send INSERT and UPDATE statements yourself. You work with objects inside a session, and the session works out the SQL. It does two jobs.
- Unit of work. It tracks every object you add or change. On
flush, it sends all the neededINSERT,UPDATEandDELETEstatements, in the right order for foreign keys. Oncommit, it flushes and then commits the database transaction. - Identity map. It keeps one Python object per database row. Load order 7 twice in the same session and you get the same object back, so two parts of a request cannot hold conflicting copies.
python
from sqlalchemy import select
async def seed():
async with SessionFactory() as session: # (1)
async with session.begin(): # (2)
session.add_all([Product(sku="MUG", price=Decimal("450.00"), stock=5),
Product(sku="PEN", price=Decimal("40.00"), stock=100)])
customer = Customer(email="asha@example.com")
customer.orders.append(Order(lines=[
OrderLine(sku="MUG", quantity=1, unit_price=Decimal("450.00"))])) # (3)
session.add(customer)
# (4) committed here(1)A session borrows a connection from the pool when it first needs one, and returns it when theasync withblock ends.(2)session.begin()opens a transaction. Leaving the block normally commits it. Leaving with an exception rolls it back.(3)We never setorder.customer_idorline.order_id. Adding the customer adds its order and lines too, and the unit of work fills in the foreign keys once the parent rows have IDs.(4)One commit writes a customer, an order and a line, or none of them.
About expire_on_commit=False: by default, after a commit the session marks every loaded object as stale, so reading an attribute triggers a fresh query. In synchronous code that is a convenience. In async code, an attribute read cannot await, so a stale attribute read crashes, as section 6 shows. Async applications almost always turn expiry off.
The session per request comes from the yield dependency of 3.14.4.
python
from typing import Annotated
from fastapi import Depends
from sqlalchemy.ext.asyncio import AsyncSession
async def get_session():
async with SessionFactory() as session: # closes the session after the request
yield session
Session = Annotated[AsyncSession, Depends(get_session)]Remember the timing lesson from that page: commit inside the endpoint or service, before the response is sent, so a failed commit becomes an error response rather than a broken promise.
5. Querying with select()
Every query in SQLAlchemy 2.0 starts from select(), which builds a SQL statement as an object. Nothing touches the database until you execute it.
python
from sqlalchemy import func, select
async def queries(session):
paid = await session.scalars( # (1)
select(Order).where(Order.status == "paid").order_by(Order.created_at.desc()).limit(20))
order = await session.scalar(select(Order).where(Order.id == 7)) # (2)
order = await session.get(Order, 7) # (3)
spending = ( # (4)
select(Customer.email,
func.count(func.distinct(Order.id)).label("orders"),
func.sum(OrderLine.quantity * OrderLine.unit_price).label("spent"))
.join(Order, Order.customer_id == Customer.id)
.join(OrderLine, OrderLine.order_id == Order.id)
.group_by(Customer.email))
for row in (await session.execute(spending)).all(): # (5)
print(row.email, row.orders, row.spent)(1)scalarsreturns model objects..all()on the result gives a list.(2)scalarreturns the first object, orNone.(3)getloads by primary key, and checks the identity map first, so it may need no query at all.(4)Joins and aggregates in Python, compiled to one SQL statement. Notecount(distinct(...)): joining orders to their lines repeats each order once per line, so a plaincount(Order.id)would count lines, not orders. That is a classic reporting bug.(5)executereturns rows of columns, readable by label.
Values in where clauses are always sent as bound parameters, separately from the SQL text, so customer input can never become SQL. That is the protection against SQL injection, which Part 8 covers.
6. Async and loading related objects: the N+1 problem
Now the opening story. The "my orders" page loads the orders, then shows each order's lines. In async SQLAlchemy, you cannot simply write order.lines.
python
async def show(session):
order = await session.scalar(select(Order).where(Order.id == 1))
print(order.lines)text
StatementError: (sqlalchemy.exc.MissingGreenlet) greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place?
[SQL: SELECT order_lines.id, order_lines.order_id, order_lines.sku, order_lines.quantity, order_lines.unit_price
FROM order_lines
WHERE ? = order_lines.order_id]The lines were not loaded with the order. Reading order.lines tried to run a query from inside an attribute access, which is lazy loading. In synchronous code that silently works. In async code, a plain attribute read cannot await, so SQLAlchemy refuses. The error looks cryptic, but it is protecting you: a hidden query is exactly what causes the next problem.
You can ask for the lines explicitly with await order.awaitable_attrs.lines. Here is what happens when the page does that for every order.
python
async def my_orders(session):
orders = (await session.scalars(select(Order))).all() # (1)
for order in orders:
lines = await order.awaitable_attrs.lines # (2)
print(order.id, len(lines))text
1 2
2 2
3 2
queries: ['SELECT orders', 'SELECT order_lines', 'SELECT order_lines', 'SELECT order_lines'](1)One query for the orders.(2)One more query for each order's lines.
Three orders cost four queries. Four hundred orders cost four hundred and one. This is the N+1 query problem: one query for the list, then N queries for the related data. Each query is fast, but four hundred round trips to the database add up to seconds.
The fix is to tell SQLAlchemy up front what you will need, called eager loading.
python
from sqlalchemy.orm import selectinload
async def my_orders(session):
stmt = select(Order).options(selectinload(Order.lines)).order_by(Order.id) # (1)
orders = (await session.scalars(stmt)).all()
print([(order.id, [line.sku for line in order.lines]) for order in orders]) # (2)text
[(1, ['MUG', 'PEN']), (2, ['MUG', 'PEN']), (3, ['MUG', 'PEN'])]
queries: ['SELECT orders', 'SELECT order_lines'](1)selectinloadruns one extra query,SELECT … FROM order_lines WHERE order_id IN (1, 2, 3), and attaches the lines to their orders.(2)Noworder.linesis already loaded, so a plain attribute read works, even in async code.
Two queries, whether there are three orders or four hundred. There are two main eager-loading strategies:
selectinloadruns a second query withIN (...). It is the best default for one-to-many collections like order lines.joinedloaduses a SQLJOINin the same query. It is best for many-to-one links, such as each order's single customer, where joining does not multiply rows.
A good habit for async services is lazy="raise" on relationships, which turns any accidental lazy load into an immediate, clear error during development, so every N+1 is caught before it ships.
7. Transactions and concurrency: selling the last mug once
3.13.14 showed a check-then-act race in Python memory and promised the real fix lives in the database. Here it is. The naive version reads the stock, checks it in Python, then writes it back. Two requests can both read "1 left" and both sell. The correct version makes the database do the check and the change in one atomic statement.
python
from fastapi import HTTPException
from pydantic import BaseModel, ConfigDict, Field
from sqlalchemy import update
class LineIn(BaseModel):
sku: str
quantity: int = Field(ge=1)
class OrderIn(BaseModel):
customer_id: int
lines: list[LineIn] = Field(min_length=1)
class LineOut(BaseModel):
model_config = ConfigDict(from_attributes=True) # (1)
sku: str
quantity: int
unit_price: Decimal
class OrderOut(BaseModel):
model_config = ConfigDict(from_attributes=True)
id: int
status: str
lines: list[LineOut]
@app.post("/orders", status_code=201)
async def create_order(payload: OrderIn, session: Session) -> OrderOut:
async with session.begin(): # (2)
order = Order(customer_id=payload.customer_id)
for line in payload.lines:
reserved = await session.execute(
update(Product)
.where(Product.sku == line.sku, Product.stock >= line.quantity) # (3)
.values(stock=Product.stock - line.quantity)
.returning(Product.price)) # (4)
price = reserved.scalar_one_or_none()
if price is None:
raise HTTPException(status_code=409, detail=f"not enough stock for {line.sku}") # (5)
order.lines.append(OrderLine(sku=line.sku, quantity=line.quantity, unit_price=price))
session.add(order)
return order # (6)text
$ curl -X POST localhost:8000/orders -d '{"customer_id": 1, "lines": [{"sku": "PEN", "quantity": 3}]}'
{"id":4,"status":"pending","lines":[{"sku":"PEN","quantity":3,"unit_price":"40.00"}]} ← 201
$ curl -X POST localhost:8000/orders -d '{"customer_id": 1, "lines": [{"sku": "PEN", "quantity": 2}, {"sku": "MUG", "quantity": 50}]}'
{"detail":"not enough stock for MUG"} ← 409
PEN stock afterwards: 97 ← the 2 pens reserved before the MUG failure were rolled back(1)from_attributes=Truelets Pydantic read the ORM object's attributes, as in 3.14.2. Only declared fields reach the client.(2)One transaction for the whole order. Commit happens when the block ends, before the response is built.(3)The condition is part of theUPDATE. The database checks and decrements in one step, holding a row lock while it does. Two concurrent requests for the last mug are serialised by the database: one updates one row, and the other finds the condition false and updates zero rows.(4)RETURNINGhands back the current price in the same round trip. It is supported by PostgreSQL and SQLite.(5)Raising inside thebegin()block rolls back everything, including the pens reserved for earlier lines. The final stock check proves it: 100 − 3 = 97, not 95.(6)The order object, with its lines already attached in memory, becomes anOrderOut.
Other tools for concurrent updates, each with its place: SELECT … FOR UPDATE locks rows you will change after more complex logic. Optimistic locking stores a version number and fails an update if someone else changed the row first. Unique constraints let the database reject duplicates.
python
from sqlalchemy.exc import IntegrityError
async def register(session, email):
try:
async with session.begin():
session.add(Customer(email=email))
except IntegrityError as error:
raise EmailTaken(email) from error # becomes a 409 via an exception handlertext
IntegrityError: UNIQUE constraint failed: customers.emailChecking "does this email exist?" with a query first and then inserting is another check-then-act race. Let the unique constraint decide, and translate the IntegrityError into a domain error (3.14.5).
8. Migrations with Alembic
Production databases cannot be dropped and recreated when a model changes. Every schema change, such as a new column, a new index or a renamed table, must be applied as a migration: a script that changes the live schema in place, recorded in order. Alembic, also by Michael Bayer, is SQLAlchemy's migration tool.
text
$ alembic init -t async migrations # one-time set-up, async template
$ alembic revision --autogenerate -m "create shop tables"
INFO [alembic.autogenerate.compare.tables] Detected added table 'customers'
INFO [alembic.autogenerate.compare.tables] Detected added table 'products'
INFO [alembic.autogenerate.compare.tables] Detected added table 'orders'
INFO [alembic.autogenerate.compare.tables] Detected added table 'order_lines'
$ alembic upgrade head
INFO [alembic.runtime.migration] Running upgrade -> b892d593a157, create shop tablesAutogenerate compares your models with the real database and writes a script.
python
def upgrade() -> None:
op.create_table('customers',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('email', sa.String(length=255), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email')
)
...Three rules make migrations safe.
- Read every generated script. Autogenerate cannot see everything. It cannot tell a renamed column from a dropped one plus a new one, which would delete the data. And it does not detect some changes, such as CHECK constraints. The script is a draft, not a decision.
- Migrations run before the new code, and old code is still running. During a rolling deploy, old and new versions serve traffic together, so every migration must work with both. Renaming a column in one step breaks the old code instantly. The safe pattern is called expand and contract: add the new column, deploy code that writes both and reads the new one, backfill old rows, deploy code that only uses the new column, and only then drop the old one.
- Large tables need care. Adding an index or a column with a default can lock a big table for minutes. PostgreSQL's
CREATE INDEX CONCURRENTLYand batched backfills avoid that. Part 7 explains the locks involved.
9. Async or sync driver?
Everything above used the async engine, which suits async def endpoints. SQLAlchemy's synchronous API is the same apart from the awaits, and it is a perfectly good choice with plain def endpoints, which FastAPI runs in its thread pool (3.14.3). The one combination that must never happen is a synchronous session inside an async def endpoint, which blocks the event loop on every query. Choose one style per service and keep it consistent.
What the interviewer will push on
"What is the N+1 problem, and how do you fix it in SQLAlchemy?" One query for a list, then one per item for related data. Fix with eager loading: selectinload for collections, joinedload for many-to-one. The tell is knowing how to detect it: query logging or counting, and lazy="raise". In async SQLAlchemy, lazy loads raise MissingGreenlet, which is a hint, not a bug in the library.
"How do you prevent overselling under concurrency?" A conditional atomic update, UPDATE … SET stock = stock - :n WHERE sku = :sku AND stock >= :n, checking the affected row count, inside a transaction. Mention SELECT … FOR UPDATE and optimistic locking as alternatives, and a CHECK constraint as a final guard. The wrong answer is a Python lock, which only protects one process.
"What does the session do?" Unit of work, which tracks changes and flushes them in dependency order, and identity map, which gives one object per row per session. One session per request, committed explicitly before responding.
"How do you size the connection pool?" Per process. Multiply by workers and instances, and stay under the database's connection limit, or use a pooler such as PgBouncer. Use pool_pre_ping to survive dropped connections.
"How do you change a column safely in production?" Expand and contract across several deploys, never a one-step rename. Review autogenerated migrations, and watch for table-locking operations on large tables.
Something to volunteer that nobody asks for: joining orders to their lines multiplies order rows, so COUNT(orders.id) silently counts lines. Use COUNT(DISTINCT orders.id) or aggregate in a subquery. It is one of the most common wrong numbers on business dashboards.
Recall
- SQLAlchemy (Michael Bayer, 2006; 2.0 in 2023) has Core (SQL as Python expressions) and ORM (rows as objects). It supports async with
create_async_engineand drivers such asasyncpgandaiosqlite. - The engine owns a connection pool, created once per process in the lifespan. Pools are per worker, so multiply before sizing. Use
pool_pre_ping. - Models:
Mapped[...],mapped_column, relationship withback_populates, foreign keys,Numericfor money, database constraints. Mapped attributes are descriptors, soOrder.status == "paid"builds SQL. - The session is a unit of work plus identity map.
async with session.begin()commits or rolls back. Useexpire_on_commit=Falsein async code. One session per request, committed before responding. select(),scalars,scalar,get,execute. Values are bound parameters. Usecount(distinct(...))after joins.- Lazy loading in async raises
MissingGreenlet. The N+1 query problem is fixed by eager loading:selectinloadfor collections,joinedloadfor many-to-one.lazy="raise"catches it early. - Prevent overselling with a conditional atomic
UPDATE … WHERE stock >= n RETURNING …. TranslateIntegrityErrorinto domain errors. Consider optimistic locking orFOR UPDATE. - Alembic migrations: review autogenerate output, expand and contract, and be careful with locks on large tables.
Self-test: Why did three orders cost four queries, and how many does selectinload need for four hundred? Why does order.lines raise in async code but not in sync code? Why does a conditional UPDATE prevent overselling when "read, check, write" does not? Why must a column rename take several deploys?
Quiz Bank
FoundationalWhat is the difference between flush and commit in a SQLAlchemy session?
flush sends the pending INSERT, UPDATE and DELETE statements for tracked changes to the database, inside the current transaction. Database-generated values such as new IDs become available, but other connections cannot see the changes yet, and they can still be rolled back. commit flushes and then commits the transaction, making the changes permanent and visible to everyone. Queries also flush automatically first, so they see your pending changes.
AppliedAn endpoint returns a list of 50 orders with customer names and line items, and takes 900 ms. Query logs show 101 queries. Fix it.
It is an N+1 pattern twice over: one query for the orders, then one per order for its customer and one per order for its lines. Load everything up front: select(Order).options(joinedload(Order.customer), selectinload(Order.lines)). joinedload is right for the many-to-one customer, because joining does not multiply rows. selectinload is right for the one-to-many lines, with one IN query. That makes two or three queries in total. Add lazy="raise" to the relationships, so any future code that forgets eager loading fails loudly in development instead of quietly slowing production.
InterviewTwo requests try to buy the last unit of a product at the same moment. Walk through how your code guarantees only one succeeds.
Each request runs inside a transaction and executes UPDATE products SET stock = stock - 1 WHERE sku = 'MUG' AND stock >= 1 RETURNING price. The database takes a row lock for the update. The first request decrements stock to 0 and gets a row back. The second request waits for the lock, then re-evaluates the condition against the committed value, finds stock >= 1 false, and updates zero rows, so RETURNING gives nothing. The code treats "no row" as out of stock, raises, and the transaction rolls back any other reservations for that order. A CHECK constraint stock >= 0 backs this up if any other code path ever tries to go negative. No application-level lock is needed, and it works across any number of processes and servers.
StaffYour team needs to rename orders.total to orders.total_amount on a 200-million-row table with zero downtime. Plan it.
Never rename in place while old code is running. Step one: add a nullable total_amount column, which is a fast metadata-only change in PostgreSQL. Step two: deploy code that writes both columns and still reads total. Step three: backfill total_amount from total in small batches with pauses, so locks stay short and replication keeps up, checking progress as you go. Step four: deploy code that reads total_amount, still writing both. Step five: verify the columns match, add a NOT NULL constraint as NOT VALID and validate it separately to avoid a long lock. Step six: deploy code that stops writing total. Step seven, a release later: drop total. Each step is reversible until the drop, and every migration is reviewed by hand rather than trusted from autogenerate.
Next: every endpoint so far trusted a header that said who the user was. 3.14.7 replaces that with real authentication: hashing passwords properly, issuing and checking JSON Web Tokens, scopes for permissions, and the security mistakes that appear again and again in FastAPI services.