SQLAlchemy engine, connection and session
What the engine, connection and session each do in SQLAlchemy 2.1, how transactions begin and end, and the session-per-request pattern for the inventory example.
SQLAlchemy has three objects I need to keep straight: the engine, the connection and the session. Most confusing bugs (connections left open, data that silently never commits, DetachedInstanceError) come from mixing up their lifetimes.
| Object | What it is | Lifetime |
|---|---|---|
Engine |
connection pool plus dialect; knows how to reach the database | one per database per process |
Connection |
one checked-out DBAPI connection, with a transaction on it | one unit of work (Core) |
Session |
ORM unit of work and identity map, using a connection under the hood | one request or job |
Installing
uv add sqlalchemy "psycopg[binary]"
SQLAlchemy 2.1 needs Python 3.11 or newer. psycopg[binary] ships a pre-compiled libpq, which is the easy option for development.
The engine
import os
from sqlalchemy import create_engine
DATABASE_URL = os.environ.get(
"DATABASE_URL",
"postgresql+psycopg://inventory:inventory@localhost:5432/inventory",
)
engine = create_engine(
DATABASE_URL,
echo=False, # True logs every SQL statement, BEGIN, COMMIT and ROLLBACK
pool_size=5, # connections kept open in the pool
max_overflow=10, # extra connections allowed under burst load
pool_pre_ping=True, # test a connection before use, to survive database restarts
)
- The URL format is
dialect+driver://user:password@host:port/database. Since 2.1, plainpostgresql://also defaults to psycopg 3, but writing+psycopgmakes the driver explicit and avoids surprises in older environments. - Creating the engine does not connect; the first connection happens on first use.
- The engine is expensive and thread-safe. Create it once at module level or application start-up, never per request.
- Passwords with special characters such as
@or/must be URL-encoded.sqlalchemy.URL.create(...)builds the URL from parts and avoids that problem.
Connections and Core
Core works with SQL statements directly. text() wraps raw SQL, and values always go in as bound parameters (:name), never by string formatting:
from sqlalchemy import text
with engine.connect() as conn:
result = conn.execute(
text("SELECT sku, quantity FROM item WHERE quantity < :threshold"),
{"threshold": 10},
)
for row in result:
print(row.sku, row.quantity)
Result objects have a few useful shapes:
conn.execute(text("SELECT count(*) FROM item")).scalar_one() # exactly one value
conn.execute(text("SELECT sku FROM item")).scalars().all() # first column as a list
conn.execute(text("SELECT sku, quantity FROM item")).mappings().all() # list of dict-like rows
Two ways to manage a transaction
SQLAlchemy never auto-commits. A transaction begins implicitly on the first statement, and I choose one of two styles to end it.
Commit as you go, with engine.connect():
with engine.connect() as conn:
conn.execute(
text("INSERT INTO warehouse (name) VALUES (:name)"),
[{"name": "London"}, {"name": "Leeds"}], # a list runs executemany
)
conn.commit()
# leaving the block without commit() rolls back
Begin once, with engine.begin():
with engine.begin() as conn:
conn.execute(text("UPDATE item SET quantity = quantity + 10 WHERE sku = :sku"), {"sku": "BOLT-M6"})
# commits if the block succeeds, rolls back if it raises
I prefer begin once: the transaction boundary is the indentation, and there is no way to forget the commit.
The session
The ORM Session holds the objects I have loaded or added, tracks changes to them, and writes those changes in a flush before queries and on commit. It gets a connection from the engine when it first needs one.
sessionmaker is a factory configured once with the engine:
from sqlalchemy.orm import sessionmaker
SessionLocal = sessionmaker(engine, expire_on_commit=False)
The same two transaction styles exist here. The begin-once form is the one to reach for:
from decimal import Decimal
from inventory_api.models import Item, Warehouse
with SessionLocal.begin() as session:
london = Warehouse(name="London")
london.items.append(Item(sku="BOLT-M6", name="M6 bolt", quantity=500, price=Decimal("0.12")))
session.add(london)
# commit happens here, then the session closes
The longer, equivalent form is with Session(engine) as session, session.begin():.
Session per request
In a web API, each request gets its own session, and one place in the code decides whether to commit. With FastAPI, for example, a dependency does this:
from collections.abc import Iterator
from sqlalchemy.orm import Session
def get_session() -> Iterator[Session]:
with SessionLocal.begin() as session:
yield session # commits after the handler returns, rolls back if it raised
Service functions take a Session argument and never call commit() themselves. That keeps them composable (two service calls share one transaction) and easy to test.
Expiring objects on commit
By default, commit() expires every loaded object, so the next attribute access reloads it from the database. That is safe while the session is open, but once the session is closed the object is detached and has nothing to reload from:
with Session(engine) as session:
warehouse = session.scalars(select(Warehouse)).first()
session.commit()
warehouse.name # DetachedInstanceError: the commit expired it, and the session is gone
Setting expire_on_commit=False on the sessionmaker keeps the loaded values usable after commit, which suits request-scoped sessions that return objects to a serialiser. The trade-off is that those values may be stale; better still, convert to plain data (for example Pydantic models) before the session closes.
Gotchas
- One engine, many sessions. Creating an engine per request leaks pools and connections.
- Never share a session between threads or concurrent requests. Sessions are not thread-safe.
flushis notcommit. A flush sends SQL inside the transaction; nothing is permanent until commit.- Autoflush runs before every statement the session executes (in 2.1 that includes Core statements too, not just ORM queries). If a pending object is invalid, the error appears at the next query, not at
add(). - f-strings in SQL are SQL injection. Use
:paramintext(), or the expression language. - Watch
max_connections. Postgres defaults to 100. Four app processes withpool_size=5, max_overflow=10can already use 60 at peak.
Check yourself
- Why should the engine be created once per process, and what does it hold?
- What happens to the data if a
with engine.connect() as conn:block ends withoutconn.commit()? - In the session-per-request dependency above, where is the commit, and what makes it roll back?
- What does
expire_on_commit=Falsechange, and what problem does it solve? - What is the difference between a flush and a commit?