Skip to content

Learn SQLSpec

This curriculum walks through SQLSpec from your first in-memory SQLite query to production connection management, Arrow analytics, observability, and multi-database recipes.

Every core guide runs out of the box against Python's built-in sqlite3 module (or aiosqlite for asyncio) so you can copy and run examples locally without starting external database containers. When you are ready to connect to PostgreSQL, CockroachDB, Oracle, DuckDB, Spanner, BigQuery, MySQL, MariaDB, SQL Server, Sybase ASE, SAP SQL Anywhere, IBM Db2, ADBC, or Arrow ODBC, pair these guides with the adapter setup pages under Databases.

Execution Architecture

flowchart LR
    App["Application / Service"] --> Spec["SQLSpec Registry"]
    Spec --> Config["Adapter Config + Pool"]
    Config --> Session["Driver Session"]
    Session --> Compiler["SQL + Parameter Compiler"]
    Compiler --> Engine["Database Wire Driver"]

SQLSpec keeps each layer explicit:

  1. Registry and Config: SQLSpec and typed *Config classes own connection pools, statement caches, named .sql loaders, and observability hooks.
  2. Driver Session: Short-lived session context managers expose consistent select*, fetch*, execute*, select_stream, and select_to_arrow methods across all database adapters.
  3. Statement Compiler: Parameter styles (:name, ?, $1, @name), StatementFilter predicates, and fluent sql.* builders compile once per SQL shape and reuse cached plans across sessions.

Query Authoring

Raw SQL strings, SQL(...) statements, named .sql files, and fluent builders all feed the same driver execution methods (session.select*, session.fetch*, session.execute*) and accept composable *filters: StatementFilter arguments. Querying compares them side by side.

flowchart LR
    Raw["Raw SQL String"] --> Compiler["Statement + Filter Compiler"]
    Stmt["SQL(...) Statement"] --> Compiler
    Loader["SQLFileLoader (spec.get_sql)"] --> Compiler
    Builder["QueryBuilder (sql.select)"] --> Compiler
    Compiler --> Session["session.select* / fetch* / execute*"]

How to Read This Guide

  • Run Every Snippet Locally: Examples in Learn import SqliteConfig (sync) or AiosqliteConfig (async) and execute as standalone modules (uv run python -m docs.examples...).
  • Sync and Async Parity: Every async driver mirrors the synchronous session surface with async with and await. Tabbed Sync / Async examples appear wherever lifecycle syntax differs.
  • Swap Engines Without Rewriting Queries: Moving from SQLite in local tests to AsyncpgConfig, OracleAsyncConfig, DuckDBConfig, or SpannerSyncConfig in production changes only the config object and any engine-specific SQL features.

Getting Started

Start here to install sqlspec, run your first parameterized query, and wire database configurations cleanly.

Guide Focus
Installation Install the base package, verify your environment, and find setup guides for optional features.
Quickstart Execute synchronous and asynchronous SQLite queries and decode rows into msgspec.Struct models.
Configuration Register single or multiple databases on SQLSpec, choose pooled vs direct configs, customize StatementConfig, and attach lifecycle hooks.

Core Querying and Transactions

Master day-to-day query execution, parameter safety, typed row hydration, and explicit transaction boundaries.

Guide Focus
Querying Choose between execute, select*, fetch* aliases, select_with_total, execute_many, StatementStack, and select_stream.
Parameters Bind named and positional parameters safely across dialects and avoid mixed-parameter pitfalls when composing filters.
Schema Mapping Project result rows into msgspec.Struct, TypedDict, pydantic.BaseModel, dataclasses, and attrs classes with schema_type.
Transactions Manage with session.transaction(): context managers, explicit begin/commit/rollback, nested savepoints, and framework commit modes.

SQL Files, Query Builder, and Filtering

Organize larger query suites without giving up SQL readability or composable runtime predicates.

Guide Focus
SQL Files Load -- name: SQL blocks from disk or cloud storage, validate -- param: headers, and append dynamic WHERE clauses.
Query Builder: SELECT & Joins Construct parameterized SELECT queries, Column expressions, WHERE/OR WHERE predicates, joins, CTEs, set operations, locking, and EXPLAIN plans.
Expressions, Windows & Vector Compose aggregates, ROLLUP/CUBE/GROUPING SETS, scalar/math/string helpers, CASE, window functions, and cross-dialect vector distance / Oracle Hybrid RRF.
DML, MERGE & DDL Builders Build INSERT, UPDATE, DELETE, RETURNING, model-bound mutations, ON CONFLICT / MERGE upserts, and DDL builders with schema inference.
Filtering and Pagination Apply composable StatementFilter objects (LimitOffsetFilter, BeforeAfterFilter, SearchFilter, OrderByFilter) and keyset cursors.

Operations, Analytics, and Quality

Operate schemas, move columnar data, instrument production queries, and keep test suites fast.

Guide Focus
Migrations Author sequential .sql and .py migrations, configure pyproject.toml, and isolate version-tracking schemas.
CLI Inspect configs, scaffold migrations, apply or squash revisions, and run ADK memory maintenance from the sqlspec CLI.
Arrow and Storage Stream query results to Apache Arrow, Polars, or Pandas via select_to_arrow and run Parquet/JSONL pipelines with StorageRegistry.
Data Dictionary Introspect tables, columns, indexes, foreign keys, server versions, and dialect-optimal column types across engines.
Observability Configure StatementObserver callbacks, OpenTelemetry spans, Prometheus metrics, wire-level sqlcommenter, and log redaction.
Testing Combine in-memory SQLite unit tests, pytest-databases container fixtures, and JSONL table fixtures.
Performance Understand per-config statement caching, msgspec hydration, sqlspec[mypyc] wheels, and Arrow batch sizing.

Production Recipes

Combine SQLSpec primitives into reusable application patterns in Production Recipes:

  • Service Layer — encapsulate short-lived reads, atomic write transactions, and multi-pool composition roots with SQLSpecSyncService and SQLSpecAsyncService.
  • Multi-Tenancy — isolate tenants by bind_key, schema search paths, or row-level predicates.
  • Health Checks — build lightweight readiness and liveness probes across pooled sync and async adapters.
  • ETL and Bulk Ingest — stream chunks, Arrow batches, and batched writes between operational and analytical stores.