SQL-first database execution for Python across 20 sync and async 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
from dataclasses import dataclass
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
@dataclass(frozen=True, slots=True)
class User:
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. |
| 20 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
pgvectorsemantic 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.