Engine, Connection, Session

The three objects you’ll touch every day. Engine is the factory; Connection is one open DB conn (with a transaction); Session is the ORM’s working scope (which holds a Connection while alive).

Engine — the factory

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg://user:pw@host:5432/dbname",
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,
    echo=False,
)

The Engine is one object per process, holding:

• The dialect (postgres / mysql / sqlite-specific behavior).
• The DBAPI driver (psycopg, asyncpg, pymysql, etc.) referenced by the URL dialect+driver://....
• The connection pool (default QueuePool for sync engines).

Create the engine once at startup, share it across the app. Don’t create one per request — the whole point is to pool connections.

URL format:

dialect+driver://user:password@host:port/database
postgresql+psycopg://alice:pw@db.internal:5432/myapp
postgresql+asyncpg://alice:pw@db.internal:5432/myapp     # async
mysql+pymysql://...
sqlite:///./app.db                                        # file-based
sqlite:///:memory:                                        # in-memory (tests)

Connection — one open DB conn

with engine.connect() as conn:
    result = conn.execute(select(users))
    for row in result:
        print(row)
    # implicit transaction; need explicit commit
    conn.commit()

A Connection:

• Holds a single DBAPI connection checked out from the pool.
• Has a transaction (autobegun on first statement).
• Must be closed (with block handles it).
• Returns the underlying DBAPI conn to the pool on close.

Two transaction styles (1.x had multiple; 2.0 settled on these):

# "Commit as you go" — explicit commit when you want
with engine.connect() as conn:
    conn.execute(insert(users).values(name="alice"))
    conn.commit()

# "Begin once" — block-scoped transaction
with engine.begin() as conn:
    conn.execute(insert(users).values(name="alice"))
    # auto-commits at block exit, or auto-rollback on exception

engine.begin() is the idiomatic short form for “do this in one transaction.”

Session — the ORM working scope

from sqlalchemy.orm import Session

with Session(engine) as session:
    user = User(name="alice")
    session.add(user)
    session.commit()

A Session:

• Acquires a connection (lazily) from the pool when needed.
• Holds an identity map (one Python object per primary key per session).
• Tracks dirty objects (Unit of Work — flushed on commit).
• Manages a transaction (begin/commit/rollback).

Session ≠ connection. Multiple sessions don’t share state. One session uses one connection at a time (lazily, may not even check out a connection until you query).

Session lifecycle

session = Session(engine)
try:
    user = User(name="alice")
    session.add(user)        # pending
    session.flush()          # sends INSERT to DB; row exists in this transaction
    print(user.id)           # populated
    session.commit()         # transaction commits; objects are "expired" by default
except Exception:
    session.rollback()
    raise
finally:
    session.close()

Or the idiomatic context-managed form:

with Session(engine) as session:
    with session.begin():
        user = User(name="alice")
        session.add(user)
    # commit at inner block exit
# close at outer block exit

session.begin() as a context manager handles commit/rollback automatically. The outer Session(...) handles cleanup.

See 08_session_lifecycle.md for the full state diagram.

sessionmaker — the factory pattern

For applications, you don’t construct Session(engine) directly in every handler. Use sessionmaker:

from sqlalchemy.orm import sessionmaker

SessionLocal = sessionmaker(bind=engine, autoflush=True, expire_on_commit=True)

# Anywhere in your code:
with SessionLocal() as session:
    ...

The configured SessionLocal is callable; each call returns a fresh session bound to your engine. One sessionmaker per app.

For FastAPI dependency injection (the canonical pattern):

def get_session():
    with SessionLocal() as session:
        yield session

@app.get("/users/{uid}")
def get_user(uid: int, session: Session = Depends(get_session)):
    return session.get(User, uid)

The yield keeps the session open for the duration of the request and closes it after.

scoped_session — thread-local sessions (legacy)

Pre-FastAPI Flask apps often used:

from sqlalchemy.orm import scoped_session

Session = scoped_session(sessionmaker(bind=engine))
# Session() returns the same session for the same thread

scoped_session is a registry keyed on thread (by default). Flask-SQLAlchemy uses this under the hood. Modern recommendation for new code: explicit session passing or DI, not thread-local globals.

For async code, scoped_session uses task-local instead of thread-local (see 11_async_sqlalchemy.md).

echo, logging, and SQL inspection

engine = create_engine("...", echo=True)            # prints every SQL statement
engine = create_engine("...", echo="debug")          # also prints results

For production, use Python logging instead:

import logging
logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)
logging.getLogger("sqlalchemy.engine.Engine").setLevel(logging.INFO)

For one-off statement inspection:

stmt = select(User).where(User.name == "alice")
print(stmt.compile(engine, compile_kwargs={"literal_binds": True}))
# SELECT users.id, users.name FROM users WHERE users.name = 'alice'

literal_binds inlines parameter values (good for debugging; bad for production logs — SQL injection if you reuse).

Connection pool basics

By default create_engine uses QueuePool with:

• pool_size=5 — persistent connections kept open.
• max_overflow=10 — extra connections allowed beyond pool_size (closed when returned).
• pool_timeout=30 — seconds to wait for a connection.
• pool_recycle=-1 — never recycle (set to ~3600 to refresh hourly, dodges MySQL’s wait_timeout).
• pool_pre_ping=False — set to True to test connection liveness before use.

engine = create_engine(
    "postgresql+psycopg://...",
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,
    pool_recycle=3600,
)

See 10_connection_pooling.md for sizing and pgbouncer interaction.

Three things, three lifetimes

Anti-patterns:

• Engine per request → no pool, slow.
• Session shared across requests/threads → race conditions, leaked state.
• Connection outliving a transaction → leaks pool slots.

Common pitfalls

• Forgetting session.commit() — changes never flush. With autobegin (default 2.0), the transaction stays open until commit/rollback.
• Holding a session across HTTP request boundaries — long-held transactions hold locks; mid-flight commits fail strangely.
• Creating an Engine per request — defeats pooling; each request waits on TCP+TLS handshake to DB.
• Using expire_on_commit=True (the default) and accessing attributes after commit — triggers a SELECT or raises DetachedInstanceError. See 14_common_pitfalls.md.
• Sharing a Session across threads — Session is not thread-safe.

Common interview confusions

• “Engine == Connection.” — no. Engine is a factory + pool; Connection is one checked-out DB conn.
• “Session is the same as Connection.” — no. Session is an ORM concept (identity map, change tracking) that holds a Connection while active.
• “You can use one Engine across processes.” — usually not safely. After os.fork(), the pool’s TCP connections are invalid in the child. Recreate or dispose the engine in worker startup.

Interview angle

• “What’s the difference between Engine, Connection, and Session?” — Engine is the process-level factory + connection pool. Connection is one DB connection with a transaction. Session is the ORM’s working scope — identity map, change tracking, holds a Connection while active.
• “How long does a Session live?” — for one logical unit of work: usually one HTTP request, one job, one transaction. Closed at the end. Don’t share across requests.
• “Why use sessionmaker instead of Session(engine) directly?” — captures defaults (engine, autoflush, expire_on_commit) once; calling SessionLocal() creates a configured session anywhere.
• “What does engine.begin() do vs engine.connect()?” — connect() gives you a connection with autobegin transaction; you must commit/rollback. begin() is a context manager that auto-commits on success, rollbacks on exception.
• “How do connections get returned to the pool?” — when the Connection is closed (or its with block ends). The Session does this when closed. Pool keeps pool_size connections persistent; extras (up to max_overflow) are opened on demand and closed on return.
• “Why might pool_pre_ping=True matter?” — pooled connections can go stale (DB restart, network blip, MySQL’s wait_timeout closes them). Pre-ping does a cheap test before handing out; without it, the first query gets a stale-connection error.