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