The pitfalls that bite teams new to SQLAlchemy 2.0 — and how to fix them quickly.

N+1

Symptom: a loop over results triggers a query per row.

users = (await s.execute(select(User))).scalars().all()
for u in users:
    print(u.posts)   # one query per user

Fix:

stmt = select(User).options(selectinload(User.posts))

Or set lazy="raise" on the relationship to catch all N+1 in dev/tests.

DetachedInstanceError

Symptom: attribute access raises after the session closes.

async with AsyncSessionLocal() as s:
    user = await s.get(User, 1)
print(user.posts)   # DetachedInstanceError

Fix: load needed attributes inside the session (selectinload), keep session open, or set expire_on_commit=False.

MissingGreenlet

Symptom: MissingGreenlet raised on attribute access in async.

Cause: lazy-load triggered in async context without awaitable_attrs or eager-load.

Fix:

# Eager-load
stmt = select(User).options(selectinload(User.posts))

# Or
posts = await user.awaitable_attrs.posts

# Or set lazy="raise" so it's loud
posts: Mapped[list[Post]] = relationship(lazy="raise")

IntegrityError on unique constraint

Symptom: app crashes with psycopg.errors.UniqueViolation (or similar).

Fix: catch and map:

from sqlalchemy.exc import IntegrityError

try:
    await session.commit()
except IntegrityError as e:
    await session.rollback()
    if "users_email_key" in str(e.orig):
        raise EmailTaken
    raise

Stale data after commit

Symptom: after await session.commit(), reading attributes re-queries DB.

Cause: expire_on_commit=True (default).

Fix:

AsyncSessionLocal = async_sessionmaker(engine, expire_on_commit=False)

For FastAPI / web apps: always False.

Pool exhaustion

Symptom: requests hang; TimeoutError: QueuePool limit of size N overflow N reached.

Cause:

  • Long-held sessions during external HTTP calls.
  • N+1 keeping connections busy.
  • Pool too small.

Fix: acquire late, release early; pool sizing; PgBouncer.

# BAD
async with AsyncSessionLocal() as s:
    user = await s.get(User, 1)
    await some_http_call()         # connection held
    await s.commit()

# GOOD
async with AsyncSessionLocal() as s:
    user = await s.get(User, 1)
await some_http_call()
async with AsyncSessionLocal() as s:
    # ...

Sync driver in async engine

# WRONG
engine = create_async_engine("postgresql+psycopg2://...")   # psycopg2 is sync

Fix: use asyncpg or psycopg (v3).

Forgetting to flush before reading auto-generated IDs

session.add(user)
print(user.id)   # None!

Fix:

session.add(user)
await session.flush()
print(user.id)   # set

Mutable default

class M(Base):
    tags: Mapped[list[str]] = mapped_column(JSON, default=[])   # SHARED!

Fix:

tags: Mapped[list[str]] = mapped_column(JSON, default=lambda: [])

CONCURRENTLY in transaction (Postgres)

op.execute("CREATE INDEX CONCURRENTLY ...")   # fails in transaction

Fix in alembic.ini:

[alembic]
transaction_per_migration = false

Forgetting to import models in env.py

Symptom: alembic revision --autogenerate produces empty migration.

Fix: explicitly import models in env.py so Base.metadata is populated:

import src.app.models  # noqa

Bulk update doesn’t fire ORM events

await session.execute(update(User).where(...).values(...))
# before_update events DON'T fire

Fix: if you need events, fetch then mutate; or use SQL triggers; or pass execution_options={"synchronize_session": "fetch"}.

SELECT * is slow on wide tables

Fix: load_only(...) or specify columns.

stmt = select(User).options(load_only(User.id, User.email))

Filter not using index

Symptom: query is slow; EXPLAIN shows seq scan.

Cause: function on indexed column.

# BAD — bypasses index
stmt = select(User).where(func.lower(User.email) == "x")

# GOOD — use expression index
__table_args__ = (Index("ix_users_lower_email", func.lower(User.email)),)

# Or citext column (Postgres)

Forgetting commit

session.add(user)
# session closes; nothing persists

Fix: always await session.commit() before exit (or use session.begin() block).

Cross-session object usage

async with AsyncSessionLocal() as s1:
    user = await s1.get(User, 1)
async with AsyncSessionLocal() as s2:
    s2.add(user)   # detached; behavior depends on state

Fix: one object lives in one session at a time. Use session.merge() if you must.

Hot reload while engine is alive

In dev, file changes trigger reload while engine has connections. Pool isn’t disposed → leftover connections.

Fix: lifespan with await engine.dispose() on shutdown; dev reload tools call shutdown.

Read this next

If you want my N+1 detector + DetachedInstance guard middleware, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .