skip to content

In SQLAlchemy, when would you pass poolclass=NullPool to create_engine instead of keeping the default QueuePool, and what does that cost?

level: middleimportance: should knowfreq 28%

answer

  1. a pool that keeps nothing
  2. someone else already pools
  3. a connect on every checkout
  4. no ceiling, no timeout

basics

~20 s

NullPool opens a new DBAPI connection for every checkout and closes it on return. Use it behind an external connection pooler, for short batch jobs or around forking; the cost is a handshake per checkout and no connection ceiling.

solid answer

~40 s

`create_engine(url, poolclass=NullPool)` keeps the same Engine API but never holds a connection between uses: each checkout connects, each checkin closes. It fits when an external connection pooler already pools server connections, so a `QueuePool` per process would only hold idle connections twice; when a short script or nightly batch job should leave nothing open; and in code that forks, where pooled connections must not be copied. The cost is a full connect and authentication on every checkout, and no `pool_size`, `max_overflow` or `pool_timeout`, so nothing in the process caps how many connections it opens. Those keywords are rejected with a `TypeError` if passed alongside `NullPool`. `pool_size=0` is not a substitute; on a `QueuePool` it means no size limit.

code

python · 16 lines
python
import logging

from sqlalchemy import create_engine, text
from sqlalchemy.pool import NullPool

logging.basicConfig(format="%(levelname)s %(name)s %(message)s")
logging.getLogger("sqlalchemy.pool").setLevel(logging.DEBUG)

# nightly batch job: one short run, nothing worth keeping open afterwards
engine = create_engine("sqlite:///orders.db", poolclass=NullPool)

for _ in range(2):
    with engine.connect() as conn:
        conn.execute(text("SELECT 1"))

print(engine.pool.status())

go deeper

for a junior

Recall that poolclass=NullPool turns pooling off: each checkout opens a connection and each checkin closes it, with the same Engine API.

for a middle

Explain when NullPool fits, behind an external pooler, short jobs, forking code, and what it gives up: handshake latency and any per-process connection ceiling.

for a senior

Judge whether pooling is really owned elsewhere before switching, and spot the trap that pool_size=0 means unlimited rather than disabled.

for a principal

Set the rule for where pooling lives in your stack, in each process or in a shared pooler, so that per-process pools and an external pooler are never sized blind to each other.

## What NullPool does `sqlalchemy.pool.NullPool` is a pool class that **does not pool**. Every checkout opens a brand-new DBAPI connection, and every checkin **closes** it. You select it with the `poolclass` argument: ```python from sqlalchemy import create_engine from sqlalchemy.pool import NullPool engine = create_engine("postgresql://[email protected]/orders", poolclass=NullPool) ``` Your code does not change: `engine.connect()`, `engine.begin()` and a `Session` behave exactly as before. Only the lifetime of the underlying connection changes. ## When NullPool is the right choice - **An external connection pooler sits between the application and the database.** The pooler already keeps a set of server connections open and lends them out. A `QueuePool` in every application process would keep its own idle client connections open on top, so idle connections are held twice and the pooler's own limits are harder to reason about. With `NullPool`, each process holds a connection only while it is using it. - **A short-lived script or nightly batch job.** A job that runs a few statements and exits gains little from reuse, and `NullPool` guarantees it leaves nothing open behind it. (A default `QueuePool` is also fine here; it opens connections lazily, and `engine.dispose()` at the end closes the idle ones.) - **Code that forks child processes**, where no pooled connection may be copied into a child. A pool that keeps nothing has nothing to copy. (`engine.dispose(close=False)` in each child is the documented alternative that keeps pooling.) - **SQLite file databases with locking trouble.** SQLAlchemy's SQLite documentation notes that `NullPool` can help when files stay locked. ## What NullPool costs 1. **A connect per checkout.** Every unit of work pays the full TCP, TLS and authentication handshake. Behind a pooler that runs close to the application this is usually cheap; against a remote database it can dominate latency. 2. **No ceiling.** `NullPool` has no `pool_size` or `max_overflow`, so nothing in the process limits how many connections it opens at once, and there is no `pool_timeout` and no `TimeoutError`. The limit moves to the pooler or to the database. 3. **No QueuePool-only keywords.** `create_engine` checks the arguments against the pool class it builds. Passing `pool_size=10` together with `poolclass=NullPool` fails at once with `TypeError: Invalid argument(s) 'pool_size' sent to create_engine()`. ## How NullPool treats the other options - **`pool_pre_ping`** is accepted, but every connection a `NullPool` hands out was opened a moment ago, so the pool logs that it is fresh and skips the ping. It adds nothing here. - **`pool_recycle`** has nothing to act on, because no connection lives past its checkin. - **`engine.dispose()`** has nothing to close; the `NullPool` holds no idle connections. - **`Session` and `Connection` code** stays exactly as it was. Transactions, commits and rollbacks behave the same; only the connect and close around them change. ## The other pool classes, briefly | class | what it does | when you meet it | |---|---|---| | `QueuePool` | bounded pool with overflow and timeout | default for most dialects and SQLite files | | `AsyncAdaptedQueuePool` | `QueuePool` on an asyncio-compatible queue | default for `create_async_engine()` | | `NullPool` | opens and closes per checkout | external pooler, short jobs, forking code | | `SingletonThreadPool` | one connection per thread | default for a SQLite `:memory:` database | | `StaticPool` | exactly one connection shared by everyone | some test setups | | `AssertionPool` | allows one checked-out connection at a time | catching code that borrows two at once | The test recipes that pick `StaticPool` for an in-memory SQLite database belong with the testing material; here it is enough to know the class exists and that a test may choose it. ## Choosing in practice - Keep the **default `QueuePool`** for a long-running service that talks to the database directly; reuse is the whole point. - Use **`NullPool`** when something else owns pooling, or when the process is too short-lived or too fork-heavy for pooled connections to pay off. - Never try to get "no pooling" with `pool_size=0`: on a `QueuePool` that means **no size limit**, the opposite of what was intended.

  • What happens if you pass pool_size=10 together with poolclass=NullPool to SQLAlchemy's create_engine?
    `create_engine` only forwards the pool keywords the chosen pool class accepts, and complains about anything left over. NullPool has no `pool_size`, so the call raises `TypeError: Invalid argument(s) 'pool_size' sent to create_engine()`, naming the dialect and pool combination it was building.
  • Which pool class does SQLAlchemy pick for a SQLite :memory: URL, and why?
    `SingletonThreadPool`, one connection per thread. Each `:memory:` connection is its own private database, so the pool keeps reusing one connection in a thread to keep its tables visible. A SQLite file database gets a `QueuePool` instead.

  • pool_size=0 turns pooling off in SQLAlchemy
  • NullPool still caps connections at pool_size plus max_overflow
  • NullPool changes how Session and Connection code must be written
  • A QueuePool in each process behind an external pooler costs nothing extra