SQL-first database execution for Python across synchronous and asynchronous database adapters.

Documentation · Quickstart · Databases · Integrations · Playground · API Reference

SQLSpec provides unified synchronous and asynchronous session APIs, SQLGlot-backed parameter compilation, statement caching, schema migrations, Apache Arrow streaming, and typed row mapping without an ORM. Write plain SQL strings, load named statements from .sql files, or compose dialect-portable queries programmatically while keeping full control over your SQL and underlying driver.

Install

uv add sqlspec

Install optional driver, schema validation, or framework extras as needed:

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

Quickstart

import msgspec

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


class User(msgspec.Struct, frozen=True, gc=False):
    id: int
    name: str


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
        )
        """
    )
    session.execute(
        """
        INSERT INTO users (name)
        VALUES (:name)
        """,
        name="Ada",
    )
    user = session.select_one(
        """
        SELECT id, name
        FROM users
        WHERE name = :name
        """,
        name="Ada",
        schema_type=User,
    )

Capabilities

Area	Overview
Unified Execution	Consistent execute, select, select_one, select_one_or_none, select_value, execute_many, select_stream, and transaction APIs across sync and asyncio drivers.
Database Adapters	PostgreSQL (asyncpg, psycopg, psqlpy), CockroachDB, SQLite (sqlite3, aiosqlite), DuckDB, MySQL & MariaDB, Oracle, SQL Server, Db2, BigQuery, Cloud Spanner, ADBC, and Arrow ODBC.
SQL Files & Builder	Load named queries from .sql files via SQLFileLoader or build statements with sql.select, sql.insert, sql.update, sql.delete, and sql.merge with cached AST compilation.
Typed Rows & Arrow	Project rows into msgspec.Struct, Pydantic, dataclass, TypedDict, or attrs models, or stream pyarrow, polars, and pandas datasets across local, fsspec, and obstore backends.
Integrations & Ops	First-party plugins and stores for Litestar, Google ADK, FastAPI, Starlette, Flask, Sanic, Dishka, and Events, plus OpenTelemetry spans, Prometheus metrics, and CLI migrations.

Documentation

• Learn & Quickstart — progressive tutorials, query patterns, migrations, and production recipes.
• Database Adapters — capability matrix, driver installation extras, pool configuration, and dialect notes.
• Framework & AI Integrations — Litestar, Google ADK, Events, FastAPI, Starlette, Flask, Sanic, and Dishka guides.
• Interactive Playground — zero-install browser SQLite environment powered by Pyodide WebAssembly.
• API Reference — class, configuration, driver, builder, and CLI reference documentation.

Reference Applications

• postgres-vertexai-demo — PostgreSQL pgvector semantic search and real-time chat with Litestar and Google ADK.
• oracledb-vertexai-demo — Oracle vector search with HNSW indexes, Litestar, and Google ADK.

Contributing & License

Contributions are welcome; see the Contributing Guide for local setup and adapter guidelines. Released under the MIT License.