Skip to content

SQLSpec

SQLSpec SQLSpec

SQL-first database execution for Python across 20 sync and async 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 20 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:

docs/examples/quickstart/first_query.py
"""Execute a parameterized SQLite query and fetch a typed dictionary row."""

from typing import Any

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

__all__ = ("main",)


def main() -> dict[str, Any]:
    """Run the first-query quickstart example."""
    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")
        assert count == 1
        assert row == {"id": 1, "name": "Ada", "points": 42}
        return row


if __name__ == "__main__":
    print(main())

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 20-adapter 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"]