Configuration#

SQLSpec configuration is centered around adapter-specific config objects. Each config captures connection parameters, optional pooling settings, and extension-specific options for framework integrations.

Pure-Python installations load public exports on first access. Compiled wheels retain eager exports to preserve concurrent access after package initialization. Both builds preserve the same public objects and static types.

Core Configuration#

basic configuration#
from sqlspec import SQLSpec, StatementConfig
from sqlspec.adapters.sqlite import SqliteConfig

statement_config = StatementConfig(enable_validation=False)

spec = SQLSpec()
primary = spec.add_config(
    SqliteConfig(connection_config={"database": ":memory:"}, statement_config=statement_config)
)

with spec.provide_session(primary) as session:
    session.execute("create table if not exists health (id integer primary key, ok bool)")
    result = session.execute("select * from health")
    print(result.all())

Pooling and Connections#

  • Sync database configs expose provide_session() and provide_connection() context managers.

  • Async database configs expose async context managers with the same method names.

  • provide_session() yields a driver adapter instance ready for executing queries and managing transactions.

  • provide_connection() yields the raw, underlying database connection from the driver.

  • Configs supporting connection pooling implement create_pool() and provide_pool().

  • On a SQLSpec registry instance, call spec.get_pool(config) to obtain the managed connection pool.

Extension Settings#

Use extension_config to give each extension its own settings map. Shared types live in sqlspec.config. Import an adapter's Litestar, Events, or ADK type when you need options that are specific to its tables or native transport. Each adapter's reference page lists those types. Plain dictionaries use the same keys.

Extension settings#

Map key

Shared type

Guide

litestar

LitestarConfig

Litestar

fastapi

FastAPIConfig

FastAPI

starlette

StarletteConfig

Starlette

flask

FlaskConfig

Flask

sanic

SanicConfig

Sanic

adk

ADKConfig

Schema

events

EventsConfig

Events

otel

OpenTelemetryConfig

OpenTelemetry

prometheus

PrometheusConfig

Prometheus

The other web frameworks, tracing, and metrics use shared settings. Keep Litestar, Events, and ADK table tuning in the extension map. Use manage_schema and create_schema for automatic table checks. Run versioned migrations through the migration commands and migration_config.

Configs can run queries before they build migration helpers. To check custom tracker setup and find extension migrations at startup, call config.get_migration_commands(). See Initialization and startup checks for a full example and the checks that still run when you create a config.

Multiple Databases#

Register multiple configs on a single SQLSpec instance and use each config handle independently. This pattern works for any combination of sync and async adapters.

multi-database with observability#
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
from sqlspec.observability import ObservabilityConfig, SamplingConfig

spec = SQLSpec(observability_config=ObservabilityConfig(sampling=SamplingConfig(sample_rate=0.1), print_sql=True))

# Primary database
primary = spec.add_config(SqliteConfig(connection_config={"database": str(tmp_path / "primary.db")}))

# Analytics database
analytics = spec.add_config(SqliteConfig(connection_config={"database": str(tmp_path / "analytics.db")}))

# Load SQL files from multiple directories
sql_dir = tmp_path / "sql"
sql_dir.mkdir()
(sql_dir / "queries.sql").write_text("-- name: list_users\nselect id, name from users order by id;\n")
spec.load_sql_files(sql_dir)

# Use each config independently
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')")
    result = session.execute(spec.get_sql("list_users"))
    print(result.all())

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')")
    result = session.execute("select * from events")
    print(result.all())

Key points:

  • Each add_config() call returns the config handle you pass to provide_session().

  • ObservabilityConfig on the SQLSpec instance applies to all registered configs.

  • load_sql_files() accepts multiple paths and loads queries into a shared namespace.