Skip to content

SQLSpec

SQLSpec

SQL-first database execution for Python across synchronous and asynchronous database adapters. Write raw SQL, load named statements from .sql files, or compose dialect-portable queries with SQLGlot while SQLSpec handles parameter compilation, statement caching, migrations, Apache Arrow streaming, and typed row mapping.

Get Started Interactive Playground Database Adapters

Install

Install sqlspec with uv. Python's built-in sqlite3 adapter works immediately with zero extra driver packages:

uv add sqlspec

Add optional database driver, schema validation, or framework extras when connecting to external engines:

uv add "sqlspec[asyncpg,msgspec,litestar]"

First Query

Register an adapter configuration on SQLSpec, open a managed session, execute parameterized SQL, and read dictionary or typed model rows:

spec = SQLSpec()
config = spec.add_config(SqliteConfig(connection_config={"database": ":memory:"}))

with spec.provide_session(config) as session:
    session.execute(
        """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT,
            points INTEGER
        )
        """
    )
    session.execute(
        """
        INSERT INTO users (name, points)
        VALUES (:name, :points)
        """,
        name="Ada",
        points=42,
    )
    count = session.select_value(
        """
        SELECT COUNT(*)
        FROM users
        """,
        value_type=int,
    )
    row = session.select_one(
        """
        SELECT id, name, points
        FROM users
        WHERE name = :name
        """,
        name="Ada",
    )

Run live SQLite queries directly in your browser in the Interactive Playground.

Explore SQLSpec

Section What You Will Find
Learn Progressive SQLite-first tutorials covering configuration, querying, parameters, typed schema mapping, transactions, .sql files, query builders, filtering, migrations, Arrow pipelines, observability, testing, and production recipes.
Databases Central Capability Matrix plus concise setup, connection pool, type coercion, and dialect guides for PostgreSQL, CockroachDB, SQLite, MySQL, MariaDB, Oracle, SQL Server, Db2, DuckDB, BigQuery, Spanner, ADBC, and Arrow ODBC.
Integrations Unified plugin, dependency injection, session store, event queue, and agent memory guides for Litestar, Google ADK, Events, FastAPI, Starlette, Flask, Sanic, and Dishka.
Playground Zero-install WebAssembly (Pyodide) SQLite environment running SQLSpec directly in your browser tab.
Reference Focused API reference pages for SQLSpec, configurations, drivers, statements, builders, migrations, storage, data dictionaries, extensions, CLI commands, and adapter classes.

Core Capabilities

Capability Summary
SQL-First Execution Execute raw SQL strings, load named queries from .sql files with SQLFileLoader, or compose statements programmatically with sql.select, sql.insert, sql.update, sql.delete, and sql.merge.
Unified Sync and Async Drivers Consistent execute, select, select_one, select_one_or_none, select_value, execute_many, select_stream, and transaction APIs across synchronous and asyncio adapters.
Typed Row Mapping Decode rows directly into msgspec.Struct, TypedDict, pydantic.BaseModel, standard library dataclasses, or attrs models via schema_type=....
Parameter Translation and Caching Bind :named or positional parameters uniformly while SQLSpec compiles dialect placeholders ($1, %s, ?, :1, @name) and caches compiled AST plans across sessions.
Apache Arrow and Storage Pipelines Stream results into pyarrow.Table, RecordBatchReader, polars, or pandas via select_to_arrow, and read or write Parquet and JSONL across local paths, obstore, and fsspec.
Observability and Migrations Emit OpenTelemetry spans, Prometheus metrics, wire-level sqlcommenter trace tags, structured cloud logs, and manage sequential SQL or Python schema migrations from the CLI.

Architecture Overview

flowchart LR
    SQL["Raw SQL / SQLFileLoader / Query Builder"] --> Compiler["SQL Statement & Parameter Compiler"]
    Compiler --> Cache["Per-Config Statement & AST Cache"]
    Cache --> Driver["Sync or Async Adapter Driver"]
    Driver --> DB[("PostgreSQL / SQLite / MySQL / Oracle / DuckDB / Spanner / BigQuery")]
    Driver --> Output["dict / msgspec / Pydantic / Apache Arrow"]