The Python Database Layering Model

A Python service talking to a database is really running through several stacked layers, even when a single session.query(...) line makes it look like one step.

At the bottom sits a database driver speaking that database's actual wire protocol; above it sits a connection pool deciding how many of those wire-level connections stay open at once; above that sits SQLAlchemy Core, translating Python expressions into SQL; and above Core sits the ORM, tracking object identity and generating that SQL for you.

Databases Basics shows the working code for each of those layers; this page is about why the stack is shaped this way, and where sync-vs-async and connection pooling actually sit inside it.

Summary

• Python database access is a stack of layers - a DB-API driver, a connection pool, SQLAlchemy Core's expression language, and the ORM - each adding structure on top of the layer below it rather than replacing it.
• Insight: Confusing which layer owns which job (is the ORM slow, or is the pool undersized, or is the driver blocking?) is the most common source of misdiagnosed database performance problems in Python services.
• Key Concepts: DB-API 2.0, connection pool, SQLAlchemy Core, ORM unit of work, sync vs async drivers, N+1 queries.
• When to Use: Choosing between raw DB-API, Core, and the ORM for a new service; diagnosing whether a slowdown is driver-level, pool-level, or query-pattern-level; deciding whether an async rewrite will actually help.
• Limitations/Trade-offs: Each added layer buys convenience and portability at the cost of some visibility into the exact SQL and timing happening underneath it.
• Related Topics: connection pool sizing, the N+1 query pattern, Alembic migrations, sync-to-async driver differences.

Foundations

PEP 249, commonly called DB-API 2.0, is a specification, not a library: it defines a common shape - connect(), a cursor() object, .execute(), .fetchall() - that most Python database drivers (psycopg, sqlite3, mysqlclient) implement independently.

That shared shape is why application code that talks to Postgres through psycopg looks structurally similar to code talking to MySQL through a different driver, even though the two drivers share no code and the databases speak entirely different wire protocols underneath.

A useful analogy is a standard plug shape: DB-API doesn't make every database identical any more than a common outlet shape makes every appliance identical, but it means anything built to plug into that shape - like SQLAlchemy - doesn't need a separate integration for every driver's quirks.

SQLAlchemy Core sits directly on top of DB-API drivers: it adds an engine (which owns a connection pool) and a Python expression language (select(), insert(), Table) that compiles down to the specific SQL dialect a given database expects.

The ORM is a further layer built on Core, not a separate path to the database: declarative classes still execute through Core's engine and connections, but the ORM adds a Session that tracks which Python objects correspond to which database rows (the identity map) and batches changes into a single flush instead of one statement per attribute change.

Mechanics & Interactions

Connection pooling exists because opening a database connection is expensive relative to running a query - it typically means a TCP handshake, sometimes TLS, and an authentication exchange before the first statement can run.

SQLAlchemy's default pool keeps a small number of real DB-API connections open and hands them out to whoever calls engine.connect() or runs a query, returning the connection to the pool (not closing it) when the surrounding with block or session ends.

requests ──▶ engine.connect() ──▶ [pool: N connections] ──▶ driver ──▶ database
                                     ▲ borrowed / idle
request N+1 ── waits in queue ──────┘ (all N currently checked out)

That means pool size is a concurrency ceiling you set, not a speed dial - a pool of 10 caps concurrent in-flight queries at 10 regardless of how fast the surrounding code runs, and that ceiling multiplies across every worker process: five Gunicorn workers each holding a pool of 10 can present 50 simultaneous connections to a database that may only allow 100 total.

Async access is where this stack gets a genuinely new wrinkle, not just a faster version of the same path.

asyncpg is an async-native driver with its own protocol implementation; it doesn't implement DB-API 2.0 at all, because DB-API is inherently a synchronous specification.

SQLAlchemy's create_async_engine bridges this by using greenlet under the hood: it lets the ORM's traditionally synchronous-looking unit-of-work code run against an async driver by transparently switching context at I/O points, so await session.execute(...) isn't literally the same code path as the sync Session, but a bridged version of it.

# create_async_engine talks to asyncpg (async-native, not DB-API)
engine = create_async_engine("postgresql+asyncpg://user:pass@host/db")
 
async with AsyncSession(engine) as session:
    result = await session.execute(select(Item))
    # greenlet lets this ORM-style call await an async-native driver underneath

That bridging is precisely why async SQLAlchemy has its own gotchas (lazy-loading a relationship outside an explicit await context raises, rather than silently blocking) - the sync-style ORM API and the async driver underneath are reconciled by SQLAlchemy, not naturally compatible on their own.

Advanced Considerations & Applications

The driver-vs-Core-vs-ORM choice is really about where you want visibility to live, and it's worth separating from the sync-vs-async choice, because the two decisions interact differently at each layer.

Raw DB-API gives full control over exactly what SQL runs and when, at the cost of writing and maintaining that SQL by hand, including its parameterization.

SQLAlchemy Core keeps that level of SQL control while adding dialect portability and protecting against injection through its expression language rather than string formatting.

The ORM trades some of that visibility for developer velocity and typed, object-shaped code, at the cost of hiding potentially expensive patterns - most notoriously the N+1 query problem, where accessing a lazy-loaded relationship inside a loop turns one intended query into one-plus-N round trips.

Neither sync nor async is universally "the fast one": async helps when a service is I/O-bound and needs to hold many concurrent requests without blocking a worker thread on each one, but it does nothing for CPU-bound work, and an async engine with an oversized pool still hits the same database connection ceiling a sync engine would.

Migration tooling (Alembic) sits alongside this stack rather than inside it: it reads the same Core/ORM metadata to generate schema diffs, which is why keeping models and migrations in sync matters more as the stack above the database grows.

Common Misconceptions

• "Async database access is always faster." It helps concurrency under I/O-bound load by freeing a worker while waiting on the database, but it adds no benefit for CPU-bound work and doesn't raise the database's own connection ceiling.
• "The ORM replaces Core, and Core replaces the driver." Each layer sits on top of the one below it - the ORM's Session still executes through Core's engine and a pooled DB-API (or async-native) connection underneath.
• "A bigger connection pool is always safer." Past a point it just shifts the bottleneck to the database's own connection limit, which every worker process shares - oversized pools across several workers are a common cause of "database refusing connections" incidents.
• "asyncpg is just an async version of psycopg." They're independently implemented drivers with different protocol handling; psycopg (v3) actually supports both sync and async in one driver, while asyncpg is async-only and was never a DB-API implementation to begin with.
• "SQLAlchemy's Session is safe to share across concurrent requests." A Session is not thread-safe by default and is meant to be scoped to one unit of work (typically one request); sharing one across concurrent requests risks corrupting its identity map.

FAQs

Related

• Databases Basics - hands-on DB-API, engine, and ORM examples
• SQLAlchemy Core - the expression language and connection pool in detail
• SQLAlchemy ORM 2.0 - Session, identity map, and declarative models
• Async Database Access - async engines, async sessions, and greenlet bridging
• Connection Management - pool sizing, retries, and transaction scope
• Query Patterns & N+1 - diagnosing and fixing the N+1 pattern

Stack versions: This page was written for Python 3.14.0 (stable 3.14, maintenance 3.13).