Skip to content

Configuration

SQLSpec separates adapter configuration (connection credentials, pool bounds, dialect defaults, and extension settings) from active sessions (checked-out connections executing SQL). Every adapter inherits from one of these base configuration classes in sqlspec.config.

Pooled vs Non-Pooled Configs

Base Class Mode Connection Lifecycle Typical Adapters
SyncDatabaseConfig Sync Managed thread-safe connection pool (create_pool, close_pool, provide_session) SqliteConfig, PsycopgSyncConfig, OracleSyncConfig, DuckDBConfig, Db2SyncConfig, PymssqlConfig, AdbcConfig
AsyncDatabaseConfig Async Managed asyncio connection pool (await create_pool, await close_pool, async with provide_session) AsyncpgConfig, PsycopgAsyncConfig, PsqlpyConfig, AiosqliteConfig, OracleAsyncConfig, AsyncmyConfig
NoPoolSyncConfig Sync Stateless client or per-session direct connection BigQueryConfig, ArrowOdbcConfig
NoPoolAsyncConfig Async Async client managing its own multiplexed transport Custom stateless async adapters

You can open sessions either through the SQLSpec registry (spec.provide_session(config)) or directly on any config object (config.provide_session()).

Multi-Database Registration and Bind Keys

A single SQLSpec instance can coordinate multiple databases—such as an OLTP primary database alongside an analytical or audit store—and share loaded .sql files across them:

-- name: list_users
SELECT id, name
FROM users
ORDER BY id;
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
from sqlspec.observability import ObservabilityConfig, SamplingConfig


def run_multi_database(base_dir: Path, sql_dir: Path) -> None:
    """Configure independent primary and analytics SQLite databases."""
    sampling = SamplingConfig(sample_rate=0.1)
    obs = ObservabilityConfig(sampling=sampling, print_sql=False)
    spec = SQLSpec(observability_config=obs)

    primary_cfg = {"database": str(base_dir / "primary.db")}
    analytics_cfg = {"database": str(base_dir / "analytics.db")}
    primary_db = SqliteConfig(bind_key="primary", connection_config=primary_cfg)
    analytics_db = SqliteConfig(bind_key="analytics", connection_config=analytics_cfg)
    primary = spec.add_config(primary_db)
    analytics = spec.add_config(analytics_db)
    spec.load_sql_files(sql_dir)

    with spec.provide_session(primary) as session:
        session.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
        session.execute("INSERT INTO users (name) VALUES ('Alice')")
        users = session.execute(spec.get_sql("list_users")).all()
        assert users == [{"id": 1, "name": "Alice"}]

    with spec.provide_session(analytics) as session:
        session.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, event TEXT)")
        session.execute("INSERT INTO events (event) VALUES ('page_view')")
        events = session.execute("SELECT id, event FROM events").all()
        assert events == [{"id": 1, "event": "page_view"}]

When integrating with the CLI or web frameworks, assign bind_key="primary" and bind_key="analytics" on each config so migrations (sqlspec --bind-key analytics upgrade) address each database unambiguously, and give each config its own framework session key (for example extension_config={"litestar": {"session_key": "analytics_session"}}; the default is db_session). For multi-pool web and background worker composition roots (app/config.py), see Service Layer.

Customizing StatementConfig

Every adapter supplies a dialect-tuned StatementConfig automatically. Pass statement_config= on the adapter config when you want to override caching, parameter styles, or SQLGlot optimization defaults across all sessions created from that config:

from sqlspec import StatementConfig
from sqlspec.adapters.sqlite import SqliteConfig

config = SqliteConfig(
    connection_config={"database": ":memory:"},
    statement_config=StatementConfig(
        dialect="sqlite",
        enable_caching=True,
        enable_validation=True,
        enable_sqlcommenter=False,
    ),
)

Key StatementConfig options:

  • enable_caching: Caches parsed SQLGlot ASTs and compiled driver parameter plans in a thread-safe StatementCacheHolder shared across sessions from the same config.
  • enable_expression_simplification and enable_column_pruning: Opt-in SQLGlot simplification and unused-column pruning for statements (both False by default). Fluent builders control their own optimizer separately through enable_optimization (on by default).
  • enable_sqlcommenter: Appends OpenTelemetry/correlation comments at wire dispatch time without invalidating compiled statement cache keys.

Connection and Query Lifecycle Hooks

Attach callbacks for pool lifecycle, connection initialization, session boundaries, or query execution via ObservabilityConfig(lifecycle=...) or adapter driver_features. Every hook receives a single LifecycleContext (dict[str, Any]) mapping:

from typing import Any

from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
from sqlspec.observability import ObservabilityConfig


def on_connect(context: dict[str, Any]) -> None:
    conn = context["connection"]
    conn.execute("PRAGMA busy_timeout = 5000")


spec = SQLSpec(
    observability_config=ObservabilityConfig(
        lifecycle={"on_connection_create": [on_connect]}
    )
)
config = spec.add_config(
    SqliteConfig(connection_config={"database": ":memory:"})
)

Every LifecycleContext dictionary includes "config" (str), plus "bind_key" (str, when configured) and "correlation_id" (str, when active), alongside event-specific keys:

Hook Event(s) Trigger Point Event-Specific LifecycleContext Keys
on_pool_create, on_pool_destroying, on_pool_destroy Pool initialization, pre-shutdown, and post-close pool
on_connection_create, on_connection_destroy Raw driver connection creation and teardown connection
on_session_start, on_session_end Driver session context entry and exit session
on_query_start Immediately before wire statement dispatch sql, parameters, driver, operation, is_many, is_script
on_query_complete Immediately after wire statement execution succeeds sql, parameters, driver, operation, is_many, is_script, rows_affected
on_error When statement execution raises an exception exception, sql, parameters, driver, operation, is_many, is_script

Session Reset on Release

Pooled connections are reused, so a temporary table, attached database, or changed setting from one session can still be present when the next session gets the same connection. Set driver_features["reset_session_on_release"] = True to run a built-in reset every time a connection is released. The reset runs after any open transaction is rolled back. If it fails, the connection is closed instead of being reused.

Adapter What the reset removes or restores
SQLite, aiosqlite Detaches every database except main and temp, drops every object in the temp schema, restores the foreign_keys, query_only, trusted_schema, cell_size_check, and recursive_triggers settings, then re-applies the pool's configured PRAGMAs
psycopg, cockroach_psycopg Runs DISCARD ALL through the pool reset callback, then your own reset callback
asyncpg, cockroach_asyncpg, psqlpy Closes cursors, restores session authorization, resets settings, unlistens, releases session-level locks, and discards plans, temp objects, and sequences, without DEALLOCATE ALL, so the client statement cache stays valid. CockroachDB runs only the cursor, authorization, and settings statements
pymysql, aiomysql, asyncmy Sends COM_RESET_CONNECTION, then re-applies read-only session mode and on_connection_create. A driver without a command path closes the connection so the pool opens a new one
DuckDB Drops temporary tables, views, sequences, and macros, detaches databases that were not attached when the connection opened, then runs on_connection_create again

mysqlconnector keeps its native pool_reset_session option, which is on by default. The option is off by default. Custom resets keep using each driver's native pool callbacks, and the built-in reset runs first when both are set.

Startup Warmup and Shutdown Cleanup

In long-running web workers or CLI tools, verify connectivity and pre-initialize migration commands during startup, and always close the pool during shutdown:

from sqlspec.adapters.sqlite import SqliteConfig

conn_cfg = {"database": ":memory:"}
mig_cfg = {"script_location": "migrations"}
config = SqliteConfig(connection_config=conn_cfg, migration_config=mig_cfg)
commands = config.get_migration_commands()

try:
    with config.provide_session() as session:
        value = session.select_value("SELECT 1")
finally:
    config.close_pool()

When you integrate SQLSpec through SQLSpecPlugin (Litestar), an ASGI lifespan context manager (FastAPI / Starlette), or Dishka Scope.APP providers, pool creation and close_pool() are handled for you automatically.

Read Replicas and Pool Derivation

Add replicas= to a primary config for reader endpoints. Pass a DSN, a list, per-reader overrides, weighted ReplicaSpec values, named groups, or a ReplicaGroup. Use True only where the adapter defines a native read intent or local-reader shortcut.

from sqlspec.adapters.asyncpg import AsyncpgConfig

config = AsyncpgConfig(
    connection_config={"dsn": "postgresql://app:secret@writer/app"},
    replicas=["postgresql://app:secret@reader/app"],
)
reader = config.derive_pool(host="reporting", is_replica=True)

derive_pool() is available on pooled and no-pool configs. It creates a fresh config with the chosen overrides and no open socket. It keeps hooks, tenant setup, and statement settings. It does not create a remote database or replicate its data.

Use config.provide_replica_session() or spec.provide_replica_session(config) for the read path. They keep the concrete driver type. The generic path falls back to primary if no group is configured. Owned readers share pool startup, shutdown, and trace hooks; await spec.close_all_pools() closes the topology. spec.telemetry_snapshot() labels readers separately, including named groups. See Read Replicas for selection and failover.

Recap

  • Prefer config.provide_session() or spec.provide_session(config) over constructing driver instances directly so per-config statement caches and lifecycle hooks remain attached.
  • Assign bind_key whenever an application registers more than one database config.
  • Keep pool sizing and driver-specific options inside each adapter's typed connection_config and driver_features dictionaries (documented per engine in Databases).