SQLAlchemy combines the Core SQL expression builder with an ORM for sessions, relationships, and transactions.
Use this reference for models, transactions, queries, and relationship-loading choices.
The reference uses the select() style.
The examples use SQLAlchemy 2.1.
The examples retain the main constructs introduced in 2.0.
Python catalog · FastAPI and a session per request
| Task | API | Where |
|---|---|---|
| Models, types, defaults, constraints | DeclarativeBase, Mapped, mapped_column |
Models |
| CRUD and rollback | Session, session.begin(), rollback() |
Transactions |
| 1:N, N:1, 1:1, N:M | relationship, ForeignKey, Table |
Complete schema |
| WHERE/JOIN/CTE/CASE/UNION/EXISTS | select, func, aliased |
Queries |
| Eliminate excessive queries when reading collections | N+1, lazy, selectinload |
N+1 card · Standalone program |
| Choose collection loading by size and constraints | selectinload or joinedload |
Decision table · Select-in · Joined and unique |
| Load the author of each post | Compare repeated fields and SELECT counts | Scalar relationship |
| Prevent unexpected lazy SELECTs | raiseload |
Card and limitations |
| Load part of a relationship with a custom JOIN | contains_eager, populate_existing |
Partial collection |
| Process a large result set | yield_per, partitions |
Batches and limitations |
| Async sessions and attributes | AsyncSession, AsyncAttrs, awaitable_attrs |
Async |
| UPSERT / FOR UPDATE / partial index | PostgreSQL dialect | PostgreSQL compilation |
| Events, SQL logs, and query timing | event, logging, perf_counter |
Observability |
| Change a schema | Alembic | Migrations |
| Parameter | Value |
|---|---|
| API / dialect | SQLAlchemy 2.1. Executable examples use SQLite. PostgreSQL examples are marked separately |
| Prerequisites | Python, SQL, transactions. The async recipe requires async with |
| Standalone N+1 | Synchronous example with two models with separate setup and checks. It does not need the shared schema |
Situation — the user must be saved on success, with changes rolled back on an exception.
Change — separate the session lifetime from the transaction boundary.
The snippet uses User and new_engine() from the complete schema.
from sqlalchemy.orm import Session
from demo_models import User, new_engine
engine = new_engine()
try:
with Session(engine) as session, session.begin():
user = User(name="alice", email="alice@example.com")
session.add(user)
session.flush()
assert user.id is not None
finally:
engine.dispose()Result — flush provides the ID, and a successful exit from begin commits the transaction.
SQLite exists only in memory. The data disappears after the engine is disposed.
Complete CRUD and rollback checks.
For an introduction to N+1, use the separate single-file setup.
The installation below applies to the shared schema and the other recipes in this section.
Install the packages in a separate environment with python -m pip install 'sqlalchemy[asyncio]==2.1.8' pytest.
The asyncio extra is required by the shared model with AsyncAttrs, including its synchronous recipes.
For async examples, add aiosqlite and pytest-asyncio.
| Component | Purpose |
|---|---|
Engine / Connection |
Driver and pool access / a specific connection |
MetaData / Table / Column |
Core schema definition. Column remains useful in 2.x |
DeclarativeBase, Mapped[T] |
ORM mapping and annotations. Some supported styles do not require Mapped |
Session |
Identity map and unit of work. A session cannot serve as a shared thread-safe container |
select() |
Query definition. A session or connection executes the SQL |
Result / ScalarResult |
Rows / values from the first selected column |
with Session(engine) closes the session without committing by itself.
On success, with session.begin() commits the transaction.
On an exception, the context rolls back the transaction.
flush() sends changes to the database within the transaction.
The method also provides generated IDs.
Calling flush() does not commit.
Complete runnable example.
The diagram shows the session boundary and the outcomes of an explicit transaction.
flowchart LR
A[Open Session] -->|define the unit of work| B[Begin transaction]
B -->|apply changes| C[Modify objects]
C -->|synchronize with the database| D[flush: send SQL]
D -->|complete successfully| E[Success: commit]
D -->|handle an error| F[Error: rollback]
E -->|release resources| G[Close Session]
F -->|release resources| G
| Read operation | Result |
|---|---|
session.get(User, id) |
An object or None, accounting for the identity map |
session.scalars(select(User)).all() |
A list of ORM objects |
session.execute(select(User.name, User.email)) |
Rows containing two selected values |
result.scalar_one() / scalar_one_or_none() |
Checks the result count. More than one row raises an error |
Situation — retrieve names and ages of adults from the prepared Alice and Bob records.
Change — select columns explicitly and use execute to retain both columns.
The snippet uses a Session containing data from the complete seed().
from sqlalchemy import select
from demo_models import User
rows = session.execute(
select(User.name, User.age).where(User.age >= 18).order_by(User.id)
).all()
assert rows == [("alice", 30)]Result — one row with two values.
scalars() would retain only the first selected column.
Complete JOIN, CTE, EXISTS examples and checks.
Mapped[str | None]infers nullable. Explicitnullable=takes precedence.Numericusually returnsDecimal. Annotate the corresponding field as Decimal.defaultruns on the Python/SQLAlchemy side.server_defaultis part of the database DDL.onupdatedoes not create a server-side trigger.- Enforce uniqueness and foreign keys through constraints. A scalar
relationshipalone cannot enforce 1:1. A unique FK or PK is required. Enum(MyEnum)stores member names by default. Setvalues_callablefor string values. Integer storage requires an appropriate separate column or conversion model.- ORM
cascade="all, delete-orphan"and FKON DELETEare separate mechanisms. The schema and tests explicitly enable SQLite foreign keys.
Situation — a user list must include every post, and an additional SELECT is acceptable.
Change — load the collections with selectinload.
This query snippet comes from the standalone program with two models.
stmt = select(User).options(selectinload(User.posts)).order_by(User.id)
users = session.scalars(stmt).all()
rows = [(user.name, [post.title for post in user.posts]) for user in users]Practice dataset result — Alice has two posts, and Bob has an empty collection. Two SELECTs run instead of three. The query count applies to two users and a fresh Session. The complete program includes imports and setup. Strategy selection, JOIN costs, and limitations.
Queries covers SQL expressions and separate PostgreSQL compilation.
The loading cards help select the required relationship data.
Large result sets covers batch processing separately.
For complete collections across a list, start with selectinload if additional SELECTs are acceptable.
For a small collection, compare joinedload, accounting for repeated parent fields and the required unique() call.
For a scalar relationship, compare SELECT counts and repeated related-object fields.
The decision table and query limitations describe the selection criteria.
SELECT counts alone do not demonstrate a speed improvement.
Async and operations covers a session per task, events, and migrations.
Choose pool size, max_overflow, pool_recycle, and pool_pre_ping according to the driver and database limits.
Values in the examples are specific to those examples.
SQLAlchemy's compilation cache does not cache result rows.
A cache does not imply a server-side prepared statement.
New examples do not need the legacy sqlalchemy.ext.baked extension.
Reuse parameterized expressions.
Measure queries.
| Symptom | Correction |
|---|---|
| Session is “rolled back” after flush | Call rollback() before reusing it, or exit the transaction context |
DetachedInstanceError |
Load the required fields and relationships ahead of time, or convert the object to a DTO before closing the session |
Expecting expire_on_commit=False to solve everything |
It prevents expiration on commit, but unloaded relationships still require loading |
| A regular JOIN leaves a relationship unpopulated | JOIN filters rows. Eager loading requires loader options or contains_eager |
metadata used as an ORM attribute |
Declarative reserves this name. Use a different Python name with the intended SQL column name |
One AsyncSession in gather() |
Allocate a session to each concurrent task |
Session.query() is a legacy API.
Session.get() replaces Query.get().
Column constructs remain valid where supported.
Avoid restoring library-level autocommit.
Define explicit transaction boundaries for the task.