SQLAlchemy Engine vs. Session: The Switchboard and the Call

WHY IT EXISTS: Web applications need to talk to databases, but managing connections is tricky. Opening a new connection for every query is slow, and keeping one connection open forever is fragile and unsafe for concurrent requests. This pattern provides a robust way to manage a pool of connections efficiently and safely.

THE MENTAL MODEL: The Engine is the factory for connections to a single database. It's a heavyweight object, created once when your application starts. Think of it as the central telephone exchange for your city. A Session is a short-lived conversation with the database, using one of those connections. It's a single phone call. You make the call (start a session), do your business (read/write data), and hang up (close the session). You don't build a new telephone exchange for every call.

HOW IT WORKS: First, you create a global Engine instance using a database URL. This Engine manages a pool of underlying DB-API connections. Second, you create a sessionmaker, often called SessionLocal, which is a factory for creating new Session objects bound to that Engine. In a web framework like FastAPI, you create a dependency that gets a new session from SessionLocal for each incoming request, passes it to your path operation function, and then guarantees it's closed in a finally block.

WHEN TO USE IT: Use this pattern in any SQLAlchemy-based application that handles concurrent requests, especially web servers like FastAPI, Flask, or Django. It's the standard, recommended way to ensure each request has a clean, isolated transaction scope without leaking database connections.

WHEN NOT TO USE IT: For a simple, single-threaded script that runs once and exits, like a data migration script, you might just create an engine and a single session for the script's entire duration. The complexity of the session-per-request pattern isn't necessary when there are no concurrent requests to isolate.

ONE CANONICAL EXAMPLE: In FastAPI, you define a dependency: def get_db(): db = SessionLocal(); try: yield db; finally: db.close(). Then, in your path operation, you use db: Session = Depends(get_db). FastAPI handles calling this for every request, giving you a fresh, isolated session db that is automatically closed.