SQLAlchemy: pool_size and SQLite

Question

The code `create_engine(sqlite:////infodb/timestamp.db', pool_size=10)` works fine in SQLAlchemy version 0.6.3. However, it does not work in version 0.7.1. The error message I am getting is as follows TypeError: Invalid argument(s) 'pool_size' sent to create_engine(), using configuration SQLiteDialect_pysqlite/NullPool/Engine. Please check that the keyword arguments are appropriate for this combination of components. In version 0.7.1 the default value of the `poolclass` variable is an instance of `sqlalchemy.pool.NullPool`. In turn, the `NullPool` object does not accept `pool_size` variable. In version 0.6.3, `poolclass` was an instance of `sqlalchemy.pool.SingletonThreadPool` and thus there was no problem dealing with `pool_size`. As a result, version SQLAlchemy 0.7.1 is not backward-compatible with 0.6.3. So I wondering, what is the reason for going from `SingletonThreadPool` to `NullPool`? It breaks the user's code.

Accepted Answer

From the SQLAlchemy 0.7 What's New document:

SQLite - the SQLite dialect now uses `NullPool` for file-based databases

This change is 99.999% backwards compatible, unless you are using temporary tables across connection pool connections.

A file-based SQLite connection is blazingly fast, and using `NullPool` means that each call to `Engine.connect` creates a new pysqlite connection.

Previously, the `SingletonThreadPool` was used, which meant that all connections to a certain engine in a thread would be the same connection. It’s intended that the new approach is more intuitive, particularly when multiple connections are used.

`SingletonThreadPool` is still the default engine when a `:memory:` database is used.

Note that this change breaks temporary tables used across Session commits, due to the way SQLite handles temp tables. See the note at http://www.sqlalchemy.org/docs/dialects/sqlite.html#using-temporary-tables-with-sqlite if temporary tables beyond the scope of one pool connection are desired.

#1921

Major SQLAlchemy releases (so between 0.5 and 0.6 or 0.6 to 0.7) so far have always included backwards incompatible changes as major ideas are being worked out. 0.7 is no exception, see the Backwards Incompatible API Changes section.

You can still provide a different pool class if you have to, by passing a `poolclass` keyword in with the `connect` call:

```
from sqlalchemy.pool import SingletonThreadPool

engine = create_engine('sqlite:///mydb.db', poolclass=SingletonThreadPool)
```

Passing in an explicit pool class also works on 0.6. Personally, I'd use exception handling here:

```
try:
    engine = create_engine(URL, pool_size=10)
except TypeError:
    # The pool_size argument won't work for the default SQLite setup in SQLAlchemy 0.7, try without
    engine = create_engine(URL)
```

SQLAlchemy: pool_size and SQLite

Solution

Problem