Common Pitfalls

The mistakes that bite every SQLAlchemy developer at least once. Memorize the symptoms so you can debug them in 30 seconds instead of an hour.

DetachedInstanceError

sqlalchemy.orm.exc.DetachedInstanceError: Instance <User at 0x...> is not bound to a Session;
attribute refresh operation cannot proceed

Means: the object’s session is closed (or it was expunged), and you’re trying to access an attribute that requires a DB hit — usually a lazy-loaded relationship or an expired attribute.

def get_user():
    with Session(engine) as session:
        return session.get(User, 42)

user = get_user()
print(user.posts)        # DetachedInstanceError — session is closed

Fixes:

1. Eagerly load what you need before close: session.get(User, 42, options=[selectinload(User.posts)]).
2. expire_on_commit=False so attributes survive across commit (but not close).
3. Keep the session open as long as you need the object (one session per request).
4. Convert to plain data (dict / Pydantic model) before returning.

“Object has been deleted, or its row is otherwise not present”

Means: the row was deleted (often by another transaction) but you still hold a Python instance pointing at it.

user = session.get(User, 42)
# meanwhile, another transaction: DELETE FROM users WHERE id = 42
user.name = "x"
session.commit()    # error: row not present for UPDATE

Fix: refresh / re-query before mutating in long-running flows, or use optimistic locking (09_transactions.md).

PendingRollbackError

sqlalchemy.exc.PendingRollbackError: This Session's transaction has been rolled back due to a previous exception during flush.

Means: a previous statement in this transaction failed; you must rollback() before doing anything else.

try:
    session.add(User(email="dup@example.com"))  # unique violation
    session.commit()
except IntegrityError:
    pass
session.add(...)        # PendingRollbackError

Fix: always session.rollback() in the except block. Use with session.begin(): to make it automatic.

InvalidRequestError: refreshed but not present

Similar root: a flush partially failed, leaving the session in a weird state. Almost always means “rollback and retry.”

“This DBAPI connection is in an invalid transaction state”

Caused by issuing a non-transactional statement (often DDL on Postgres) in a transaction. Either commit first, or set the engine/connection to autocommit:

with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:
    conn.execute(text("VACUUM ANALYZE users"))

Lazy load after session close → MissingGreenlet (async)

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called

Async equivalent of DetachedInstanceError. Async sessions don’t allow lazy loads even before close. Always eager-load. See 11_async_sqlalchemy.md.

Forgot autoflush — query doesn’t see pending writes

session.add(User(name="alice"))
count = session.scalar(select(func.count(User.id)))
# count INCLUDES alice — autoflush=True (default)

# but with no_autoflush:
with session.no_autoflush:
    session.add(User(name="bob"))
    count = session.scalar(select(func.count(User.id)))
    # count does NOT include bob

If your query result seems missing recent writes, check for no_autoflush context.

Mutating a JSON column in place — no UPDATE

user = session.get(User, 1)
user.metadata["new_key"] = "value"     # in-place dict mutation
session.commit()                        # no UPDATE issued

SQLAlchemy tracks attribute assignments, not deep mutations. Fix with MutableDict.as_mutable(JSONB) on the column definition (see 03_declarative_models.md).

Or replace the whole value:

user.metadata = {**user.metadata, "new_key": "value"}
session.commit()    # OK

N+1 from len(user.posts)

for user in users:
    print(len(user.posts))    # N+1

len() on a lazy collection triggers a load. Either eager-load with selectinload(User.posts), or compute the count in SQL.

default=datetime.now() instead of default=datetime.now

created_at: Mapped[datetime] = mapped_column(default=datetime.now())  # evaluated ONCE at class load
created_at: Mapped[datetime] = mapped_column(default=datetime.now)    # callable, evaluated per insert

The first form gives every row the timestamp when the class was defined. Common typo.

Better still:

created_at: Mapped[datetime] = mapped_column(server_default=func.now())

Server-side, works for any insert path including raw SQL.

Forgetting to commit / commit too eagerly

Two opposite mistakes:

session.add(user)
session.flush()       # not committed; rollback discards it
# return — transaction never commits

for row in big_list:
    session.add(User(**row))
    session.commit()    # commit per row — slow, many round trips

The right shape: one logical operation, one commit. For batches: commit per N rows (chunking), not per row.

Sharing a session across threads / tasks

Sessions are NOT thread-safe. The DBAPI connection underneath isn’t either. Sharing leads to:

• Race conditions in identity map.
• Multiple statements interleaving on one connection (corrupted protocol).
• Mystery InvalidRequestErrors.

One session per thread / task / request. Engines are shared; sessions are not.

cascade="all" includes delete but not delete-orphan

class User(Base):
    posts: Mapped[list["Post"]] = relationship(cascade="all")
    # deleting user deletes posts
    # but: removing a post from user.posts does NOT delete it

# Fix for "child is owned":
posts: Mapped[list["Post"]] = relationship(cascade="all, delete-orphan")

DB cascade without passive_deletes=True

author_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))

class User(Base):
    posts: Mapped[list["Post"]] = relationship(cascade="all, delete")
    # SQLAlchemy issues per-row DELETE for each post AND the DB cascades too

Add passive_deletes=True so SQLAlchemy lets the DB handle it:

posts: Mapped[list["Post"]] = relationship(cascade="all, delete", passive_deletes=True)

Now SQLAlchemy doesn’t load+delete the posts; the DB cascade handles them. Huge speedup for many children.

expire_on_commit=True + read after commit = SELECT storm

def create_users(session, names):
    users = [User(name=n) for n in names]
    session.add_all(users)
    session.commit()           # all 100 users expired
    return [u.name for u in users]    # 100 SELECTs to refresh

Fix: expire_on_commit=False on the sessionmaker; access attributes before commit; or refresh in bulk.

Long-running sessions

A session held open for hours:

• Holds a connection.
• Holds a long transaction.
• DB autovacuum can’t clean up dead tuples it has visibility on.
• Locks held forever.

For background jobs, open a session per job; close when done. For streaming, commit periodically and consider closing/reopening to release the transaction.

Connection pool exhaustion

TimeoutError: QueuePool limit of size 10 overflow 20 reached, connection timed out

Means: 30 requests waiting on connections, all checked out. Causes:

• Session leak (didn’t close).
• Long-running transactions.
• Pool too small for load.

Find leaks: log every checkout/checkin event with stack trace. The leak is usually a code path that exits without closing.

Bulk operations bypass cascades

session.execute(delete(User).where(User.deleted_at < cutoff))
# doesn't trigger SQLAlchemy cascades — posts are not deleted via Python cascade

Either rely on DB-level ON DELETE CASCADE, or use session.delete(user) per row (slow).

“could not adapt type” / “expected str, got UUID”

DBAPI driver doesn’t know how to send a Python type. Common with UUIDs on drivers that don’t natively support them.

Fix: explicit column types:

from sqlalchemy import Uuid
id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True)

Or cast:

session.execute(text("INSERT INTO t (id) VALUES (:id)"), {"id": str(my_uuid)})

DB schema and SQLAlchemy model drift

Migrations are out of date relative to models, or someone manually altered the DB. Symptoms:

• column "x" does not exist
• relation does not exist
• Mysterious type errors.

Run alembic check (recent Alembic) or compare Base.metadata.tables to actual DB schema. CI should catch this.

Column of the wrong length silently truncates

MySQL with non-strict mode silently truncates a string longer than VARCHAR(50). Postgres errors. SQLite happily stores anything.

Always test against the production DB; don’t rely on SQLite for unit tests of column length / constraint behavior. See 13_testing_patterns.md.

Interview angle

• “What’s DetachedInstanceError and how do you fix it?” — accessing a relationship or expired attribute on an instance whose session is closed. Fix: eager-load before close, keep session open longer, or convert to plain data before returning.
• “You see PendingRollbackError — what happened?” — a previous statement failed and you didn’t roll back. Call session.rollback() and continue, or use with session.begin(): to make rollback automatic on exception.
• “In-place changes to a JSON column aren’t persisting — why?” — SQLAlchemy tracks attribute assignment, not deep mutation. Use MutableDict.as_mutable(JSONB) on the column or reassign the whole value.
• “What’s wrong with default=datetime.now()?” — the () evaluates once at class load time. Pass default=datetime.now (the callable) or use server_default=func.now().
• “How does cascade="all" differ from cascade="all, delete-orphan"?” — all is save-update, merge, refresh-expire, delete. delete-orphan adds: removing a child from the parent’s collection deletes it. Need both for “parent owns child” semantics.
• “You added ON DELETE CASCADE at the DB level but deletes are still slow — why?” — SQLAlchemy issues per-row DELETEs for loaded children unless passive_deletes=True on the relationship. Set it so the ORM lets the DB cascade do the work.
• “Connection pool exhaustion — what causes it and how to debug?” — session leaks (code paths missing close), long-running transactions, undersized pool. Log checkout/checkin events with stack traces to find the leak.