Preventing database connection leaks

Part 1 explained the format: short posts, one database practice each. Here’s the first real one — the most boring-sounding bug I know that can take down an entire service: forgetting to return a database connection when a request fails.

The setup: connections are borrowed, not owned

Opening a database connection is slow, and a database can only handle so many of them. So every serious backend keeps a small pool of already-open connections shared by the whole service — in one of my services it’s 20 connections plus 10 overflow. Thirty, total, for everything.

Each request borrows a connection (by opening a session), does its work, and returns it. The rule is unforgiving: whatever you borrow, you give back.

Here’s the bug, reduced to its skeleton:

def handle_request(engine, work):
    session = Session(engine)  # will borrow a pool connection on first query
    result = work(session)     # may raise
    session.close()            # returns it -- but only on success
    return result

When work raises, session.close() never runs and the connection is never returned. That’s a leak. (Strictly speaking, CPython’s garbage collector may eventually claw the connection back, and SQLAlchemy logs an angry error when it does. But that’s nondeterministic, it doesn’t happen at all with asyncio engines, and under load “eventually” is not a plan.)

Why this never shows up until it hurts

One leak is invisible. Local testing runs one request at a time, so there’s always a free connection, and everything looks fine.

Now put it in production. Say one endpoint raises on 1% of its inputs. Each failure leaks one connection. After roughly 30 failing requests — a few thousand total at that rate — the pool is empty. The next request that needs a connection, maybe a healthy one hitting a completely different endpoint, waits pool_timeout seconds and dies with SQLAlchemy’s TimeoutError: QueuePool limit of size 20 overflow 10 reached, connection timed out.

From the outside it looks exactly like “the database is down.” Dashboards go red, someone pages the DBA, and the database is sitting there idle. Your service strangled itself.

The fix: cleanup goes in finally

def handle_request(engine, work):
    session = Session(engine)
    try:
        return work(session)
    finally:
        session.close()

Two properties of finally make it exactly right for this:

• It runs on every path — success, exception, even an early return.
• It does not swallow the exception. The caller still sees the original error; you cleaned up, you didn’t hide anything.

Do it once, in one place

The real fix isn’t remembering try/finally in every handler — it’s making sure no handler has to. Own the session lifetime in exactly one place. In a FastAPI app, that’s a dependency with yield:

def get_db():
    session = SessionLocal()
    try:
        yield session
    finally:
        session.close()

FastAPI runs the code after yield even when the endpoint raises. A middleware that opens one session per request and closes it in a finally works the same way, and for code without a request — a queue consumer, a cron job — a context manager is the same pattern with nicer syntax: a with block is try/finally wearing a suit.

What to remember

• Borrowed resource → return it in finally. Connections, file handles, locks — same rule.
• Leaks hide in the error path and only surface under load. Test the failing path, not only the happy one.
• Own the lifetime in one place (dependency, middleware, context manager) so individual handlers can’t forget.
• If your service dies with pool timeouts while the database looks idle, suspect a leak before you suspect the database.