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:
- Registry and Config:
SQLSpecand typed*Configclasses own connection pools, statement caches, named.sqlloaders, and observability hooks. - Driver Session: Short-lived
sessioncontext managers expose consistentselect*,fetch*,execute*,select_stream, andselect_to_arrowmethods across all database adapters. - Statement Compiler: Parameter styles (
:name,?,$1,@name),StatementFilterpredicates, and fluentsql.*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
LearnimportSqliteConfig(sync) orAiosqliteConfig(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 withandawait. TabbedSync/Asyncexamples appear wherever lifecycle syntax differs. - Swap Engines Without Rewriting Queries: Moving from SQLite in local tests to
AsyncpgConfig,OracleAsyncConfig,DuckDBConfig, orSpannerSyncConfigin 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 |