sqlspec skill
✓ VerifiedA type-safe SQL query mapper for Python that provides flexible connectivity and validation across various database adapters and web frameworks.
- GitHub stars
- 17
- Files
- 28
- Last commit
- Nov 6, 2026
- License
- MIT
- Allowed tools
- —
What does the sqlspec skill do?
sqlspec is a type-safe SQL query mapper for Python that provides flexible connectivity and validation across various database adapters and web frameworks.
Summary generated from the skill's SKILL.md.
About the sqlspec skill
Python developers needing a type-safe alternative to ORMs can use sqlspec to map raw SQL queries to structured data. It provides a consistent interface for connecting to various databases like PostgreSQL, SQLite, DuckDB, and BigQuery through specific adapter configurations. Instead of manual dictionary parsing, users can pass a schema type such as a Pydantic model or dataclass to receive validated, typed objects directly from the database.
An agent should load this skill when building data-driven applications that require high-performance query execution or complex data movement. The tool supports writing raw SQL, using a programmatic query builder for dynamic statements, or loading SQL from files via the SQLFileLoader. It integrates deeply with web frameworks like FastAPI, Litestar, Flask, Starlette, and Sanic to manage database sessions and dependency injection. For large-scale data tasks, it utilizes Arrow for efficient bulk exports and ingestions.
What an agent can do with it
- 1Execute type-safe queriesMap database rows directly to Python dataclasses or Pydantic models using the schema_type parameter.
- 2Build dynamic SQL statementsConstruct complex queries programmatically using the builder API to avoid manual string concatenation.
- 3Integrate with web frameworksConnect database sessions to a FastAPI or Litestar application using first-party plugins.
Written from the skill's SKILL.md. Every use case is checked against a quote from it.
How to install the sqlspec skill
Each command copies the skill folder into the client's skills directory. Restart the client or start a new session to load it.Install the sqlspec skill in Plugin
/plugin marketplace add litestar-org/litestar-skills /plugin install litestar@litestar
Install the sqlspec skill in Claude Code
~/.claude/skills/sqlspec/SKILL.md
$ npx degit litestar-org/litestar-skills/skills/sqlspec ~/.claude/skills/sqlspecInstall the sqlspec skill in Claude Code (project)
.claude/skills/sqlspec/SKILL.md
$ npx degit litestar-org/litestar-skills/skills/sqlspec .claude/skills/sqlspecInstall the sqlspec skill in Codex
~/.agents/skills/sqlspec/SKILL.md
$ npx degit litestar-org/litestar-skills/skills/sqlspec ~/.agents/skills/sqlspecInstall the sqlspec skill in Gemini CLI
~/.gemini/skills/sqlspec/SKILL.md
$ npx degit litestar-org/litestar-skills/skills/sqlspec ~/.gemini/skills/sqlspecInstall the sqlspec skill in Claude.ai
npx degit litestar-org/litestar-skills/skills/sqlspec sqlspec && zip -r sqlspec.zip sqlspec # claude.ai → Customize → Skills → + → Create skill → Upload a skill → sqlspec.zip
Automated checks
Ran Nov 14, 2026Full report →Passed our automated checks. That is not a security audit: read the scripts before you let an agent run them. How checks work
- Source repository linked and reachablelitestar-org/litestar-skills/skills/sqlspec is public and reachablegithub.com/litestar-org/litestar-skills/tree/HEAD/skills/sql ↗Pass
- Open source license (OSI)MITspdx.org/licenses/MIT.html ↗Pass
- Commit or release in the last yearLast commit 2026-11-06github.com/litestar-org/litestar-skills/commits ↗Pass
- No download-and-run or credential theft in scriptsNo scripts in the skill folderPass
- No hidden instructions in SKILL.mdNo override phrases, safeguard switches, credential requests or hidden charactersgithub.com/litestar-org/litestar-skills/blob/HEAD/skills/sql ↗Pass
- Pre-approved tools are scopedNo pre-approved tools; the agent asks before using onegithub.com/litestar-org/litestar-skills/blob/HEAD/skills/sql ↗Pass
- SKILL.md follows the Agent Skills formatValid name and description; says when to use it; 331 linesgithub.com/litestar-org/litestar-skills/blob/HEAD/skills/sql ↗Pass
SKILL.md
View on GitHubSQLSpec Skill
SQLSpec is a type-safe SQL query mapper for Python -- NOT an ORM. It provides flexible connectivity with consistent interfaces across its database adapter packages. Write raw SQL, use the builder API, or load SQL from files. Statements pass through a sqlglot-powered AST pipeline for validation, parameter handling, and dialect conversion.
Match-Your-Framework — read first
sqlspec ships first-party extensions for five web frameworks. If your project uses one of these, jump directly to the matching integration guide and skip the others:
- Litestar — register configs on
SQLSpec, then pass that registry toSQLSpecPlugin. The plugin adds DI, thelitestar dbCLI, and request observability. Seereferences/extensions.md. - FastAPI →
references/fastapi-integration.md—Depends(plugin.provide_session())DI,Annotated[...]handlers, filter providers. - Flask →
references/flask-integration.md—plugin.init_app(app), pull-basedplugin.get_session(), async-via-portal. - Starlette →
references/starlette-integration.md—request.state-based session access, lifespan wrapping, middleware variants. - Sanic — first-party ASGI-style extension for Sanic applications; match Sanic's app/request lifecycle instead of copying Litestar DI examples.
Shared topics that apply to every framework live in references/commit-modes.md (autocommit / manual middleware) and references/multi-database.md (multi-config registry). Read the framework guide first, then those for depth.
The rest of this SKILL.md covers framework-agnostic topics: adapter setup, query builder, driver methods, filters, observability, migrations, the ADK extension, and data-dictionary introspection.
Code Style Rules
from __future__ import annotationsrule — SQLSpec adapter config modules and driver definitions avoidfrom __future__ import annotationsbecause configs are introspected at runtime. Consumer application modules (handlers, services, tests that use a configured driver) MAY and typically SHOULD use it — canonical Litestar apps use it in 100+ files.
Quick Reference
Adapter Pattern
from sqlspec import SQLSpec
from sqlspec.adapters.asyncpg import AsyncpgConfig
config = AsyncpgConfig(
connection_config={
"dsn": "postgresql://user:pass@localhost:5432/mydb",
"min_size": 2,
"max_size": 10,
},
)
db_manager = SQLSpec()
db_manager.add_config(config)
async with db_manager.provide_session(config) as db:
users = await db.select(
"SELECT * FROM users WHERE active = $1",
True,
schema_type=User,
)
Query Builder Essentials
from sqlspec import sql
stmt = (
sql.select("id", "name", "email")
.from_("users")
.where_eq("status", "active")
.where("created_at > :since", since=cutoff_date)
.order_by("created_at", desc=True)
.limit(50)
.to_statement()
)
insert_stmt = (
sql.insert("users").columns("name", "email").values(name="Alice", email="alice@example.com").to_statement()
)
merge_stmt = (
sql.merge("inventory", dialect="postgres")
.using("updates")
.on("inventory.product_id = updates.product_id")
.when_matched_then_update(qty="updates.qty")
.when_not_matched_then_insert(product_id="updates.product_id", qty="updates.qty")
.to_statement()
)
Driver Method Summary
| Method | Returns | Use Case |
|---|---|---|
select() / fetch() |
List of rows | Filtered queries, listing |
select_value() |
Single scalar | COUNT(*), MAX(), existence checks |
select_value_or_none() |
Scalar or None |
Optional scalar lookup |
select_one() |
One row (strict) | Get-by-ID, raises NotFoundError |
select_one_or_none() |
One row or None |
Optional lookup |
select_with_total() |
Rows plus total | Pagination |
select_stream() / fetch_stream() |
Context-managed row stream | Bounded row iteration where adapter supports native streaming |
select_to_arrow() / fetch_to_arrow() |
ArrowResult |
Bulk data export, analytics |
select_to_storage() |
StorageBridgeJob |
Export query results to local or cloud storage |
execute() |
SQLResult |
INSERT/UPDATE/DELETE metadata |
execute_many() |
SQLResult |
Batch operation metadata |
execute_script() |
SQLResult |
Multi-statement SQL script execution |
execute_stack() |
tuple[StackResult, ...] |
Ordered statement-stack execution |
load_from_arrow() |
StorageBridgeJob |
Adapter-supported Arrow ingest |
load_from_storage() |
StorageBridgeJob |
Adapter-supported staged-file ingest |
load_from_records() |
StorageBridgeJob |
Records normalized through the Arrow ingest path |
transaction() |
Context manager | Atomic transaction or savepoint scope on a driver |
Arrow Integration Basics
arrow_result = await db.select_to_arrow(
"SELECT * FROM large_dataset WHERE region = $1",
region,
return_format="reader",
batch_size=10_000,
)
await db.load_from_arrow("users", arrow_result)
await db.load_from_records("users", [{"id": 1, "name": "Ada"}])
Workflow
Step 1: Choose Adapter and Pattern
| Need | Adapter | Key Feature |
|---|---|---|
| PostgreSQL async | asyncpg, psycopg |
Async, NUMERIC/PYFORMAT params, auto-probed pgvector / pg_textsearch / paradedb |
| PostgreSQL sync | psycopg |
Sync+async, PYFORMAT params |
| SQLite | sqlite, aiosqlite |
QMARK params, local dev |
| DuckDB analytics | duckdb |
Arrow-native OLAP, extension load/install lifecycle, direct object-store transfer |
| Arrow / multi-engine ETL | adbc |
Arrow-native ingest/export across DuckDB, PostgreSQL, BigQuery, Flight SQL, GizmoSQL, Quack, Oracle, and Db2 |
| MySQL async | asyncmy |
PYFORMAT params |
| Oracle | oracledb |
NAMED_COLON params, sync+async |
| IBM Db2 | db2 |
QMARK params, sync+async via ibm_db, SYSCAT catalog reflection |
| SQL Server / Sybase / SQL Anywhere | mssql_python, pymssql, sybase, sqlanywhere |
T-SQL (tsql), Sybase ASE (sybase), and SAP SQL Anywhere (sqlanywhere) dialects |
| BigQuery / Spanner | bigquery, spanner |
NAMED_AT params, cloud job/session controls, Spanner sync+async drivers, Property Graph & Vector/Search index AST |
| Raw SQL strings | Driver methods | select(), execute() |
| Dynamic queries | Query builder | sql.select()...to_statement(), sql.update()...from_(), sql.upsert() |
| SQL from files | SQLFileLoader |
Metadata directives, -- param:, -- fragment:, /* include: */, /* slot: */, caching |
| High-volume ingest/export | Storage bridge | Check the adapter matrix before selecting load_from_arrow(), load_from_storage(), load_from_records(), or select_to_storage() |
Step 2: Implement
- Configure the adapter with connection details (standardized aliases like
dsn/url/conninfo,database/dbname,user/usernamenormalize automatically) and pool settings - Register the config with
SQLSpec.add_config()and useSQLSpec.provide_session(config)or config-backedSQLSpecAsyncService(config=..., loader=...)for connection lifecycle - Choose the appropriate driver method for your query shape
- Use
schema_typeparameter for typed results (msgspec Structs, dataclasses, or Pydantic models) - Apply filters with
LimitOffsetFilter,CursorFilter,OrderByFilter,SearchFilter,BeforeAfterFilter, orInCollectionFilter - Use
select_stream(..., native_only=True)when bounded-memory streaming is mandatory - Check adapter ingest capabilities, then use
load_from_records(),load_from_arrow(),load_from_storage(), orselect_to_storage()for high-volume data movement
Step 3: Validate
Run through the validation checkpoint below before considering the work complete.
Guardrails
- Always use typed adapters: import the specific adapter config, not generic base classes
- Always use
schema_typefor query results -- get typed objects, not raw dicts - Always use context managers for driver lifecycle --
async with db_manager.provide_session(config) as db:orasync with service.provide_session() as db: - Prefer the query builder for complex dynamic queries -- avoids string concatenation, handles dialect conversion
- Prefer
SQLFileLoaderfor static queries -- keeps SQL out of Python, supports reusable-- fragment://* include: */blocks and validated/* slot: */splicing, and reuses the global file-cache namespace - Use
-- param:declarations for named SQL files that cross service boundaries -- load-time and execute-time validation catches name drift and required parameter omissions - Use
native_only=Truefor streaming or Arrow paths only when fallback is unacceptable -- unsupported adapters otherwise use eager row conversion - Pass regular query bind values as positional arguments --
await db.select("... WHERE id = $1", user_id, schema_type=User), notawait db.select(..., [user_id], ...) - Never concatenate SQL strings -- use parameterized queries, query builder expressions, or declared
/* slot: <name> */fragments - Never hold connections outside context managers -- connection leaks exhaust the pool; prefer config-backed services (
SQLSpecAsyncService(config=...)) when operations should hold a connection only for the duration of a single query orbegin_transaction()block - Match parameter style to adapter:
$1for asyncpg,%sfor psycopg,?for sqlite/duckdb/db2,:namefor oracledb - Cloud adapter controls -- BigQuery job controls live in
driver_features; Spanner request controls live indriver_featuresorprovide_session()kwargs - Adapter config / driver modules avoid
from __future__ import annotations. Consumer app modules MAY use it.
Validation Checkpoint
Before delivering SQLSpec code, verify:
- Adapter config uses the correct import path (
sqlspec.adapters.<name>) - Connection lifecycle uses
SQLSpec.provide_session(config)orservice.provide_session()context manager - Parameter style matches the adapter (see adapter registry table)
- Query results use
schema_typefor type-safe mapping - Complex dynamic queries use the builder API or declared SQL slots, not string concatenation
- Filters use SQLSpec filter objects (
LimitOffsetFilter,CursorFilter,OrderByFilter, etc.) not manual LIMIT/OFFSET - Dishka-first Litestar apps that disable SQLSpec DI set
extension_config={"litestar": {"disable_di": True, "manage_lifespan": True}}whenSQLSpecPluginshould still manage pool lifespan - Streaming code uses context managers and sets
native_only=Truewhen eager fallback would be a bug - Bulk ingest/export code checks the adapter matrix before using
load_from_arrow(),load_from_storage(),load_from_records(), orselect_to_storage() - ADK stores are selected from supported adapter
adkpackages; BigQuery is not an OLTP live-agent ADK backend
Example
Task: "Set up an asyncpg adapter, define a typed model, and execute a parameterized query with pagination."
from dataclasses import dataclass
from sqlspec import SQLSpec
from sqlspec.adapters.asyncpg import AsyncpgConfig
from sqlspec.core.filters import LimitOffsetFilter, OrderByFilter
@dataclass
class User:
id: int
name: str
email: str
active: bool
config = AsyncpgConfig(
connection_config={
"dsn": "postgresql://user:pass@localhost:5432/mydb",
"min_size": 2,
"max_size": 10,
},
)
db_manager = SQLSpec()
db_manager.add_config(config)
async def list_active_users(page: int = 1, page_size: int = 25) -> list[User]:
filters = [
OrderByFilter(field_name="name", sort_order="asc"),
LimitOffsetFilter(limit=page_size, offset=(page - 1) * page_size),
]
async with db_manager.provide_session(config) as db:
users = await db.select(
"SELECT id, name, email, active FROM users WHERE active = $1",
True,
*filters,
schema_type=User,
)
return users
async def get_user_count() -> int:
async with db_manager.provide_session(config) as db:
count = await db.select_value("SELECT COUNT(*) FROM users WHERE active = $1", True)
return count
References Index
Choosing between
sqlspecandadvanced-alchemy:advanced-alchemygives you an opinionated ORM service layer withUUIDAuditBase, lifecycle hooks, repository / service / Alembic integration, andOffsetPagination[T]out of the box — pick it when you want a complete CRUD surface with attribute-style row access and you're happy inside the SQLAlchemy ecosystem.sqlspecgives you direct SQL control, first-party database adapter packages (asyncpg, oracledb, Db2, Sybase ASE, SQL Anywhere, DuckDB, BigQuery, Spanner, SQLite, and more), Arrow result paths for analytics, and a builder API when you need it — pick it when you want explicit SQL, heterogeneous database backends, or Arrow integration. Both skills integrate with Litestar via first-party plugins; see../advanced-alchemy/SKILL.mdfor the ORM path.
For detailed instructions, patterns, and API guides, refer to the following documents:
Standards & Style
- Code Quality & Mypyc -- Type annotation rules, import standards, test structure.
Core Utilities
- SQLglot Best Practices -- v30+ guardrails, AST manipulation,
copy=Falsepattern.
Architecture & Performance
- Architecture & Caching -- Core data flow, global cache configuration, namespaces,
StatementCacheHolder, and zero-copy AST sharing. - Performance & Cloud Controls -- Bounded async bridge, cache/fetch tuning, BigQuery job controls, Spanner sync/async session controls.
- Data Dictionary -- Dialect feature flags, runtime introspection (
get_tables,get_columns,get_indexes), Spanner Property Graph/Change Stream metadata, ADBC native metadata/statistics.
Query Building & Execution
- Query Builder API --
sqlfactory: select, insert, update, delete, merge. - Driver Method Reference --
select(),select_one(),select_stream(),select_to_arrow(), load methods. - Filter & Pagination System --
LimitOffsetFilter,OrderByFilter,SearchFilter.
Data Integration
- Arrow & ADBC Integration --
select_to_arrow()formats, Arrow-native paths, conversion fallbacks. - Native Bulk Ingest --
load_from_arrow(),load_from_storage(),load_from_records(), adapter gates. - SQL File Loading --
SQLFileLoaderwith search paths, metadata directives.
Adapters & Drivers
- Adapter & Driver Registry -- Full adapter registry with dialects and parameter styles.
Framework & Storage Integrations
- Framework Extensions -- Litestar plugin, FastAPI/Starlette integration.
- Storage Integration -- ADK store, Litestar session stores, event channel backends.
- Event Channels (Pub/Sub) --
AsyncEventChannel, subscribe/publish patterns. - ADK Extension -- ADK 2 session/memory/artifact stores, scoped state, Spanner hybrid vector/FTS/RRF & Property Graph memory.
Migrations & Schema
- Native Migration Runner -- standalone
sqlspecCLI, timestamp versioning,ddl_migrationstracker, extension migrations, and Litestarlitestar dbintegration.
Observability
- Observability & Tracing -- Telemetry semantics, correlation extraction.
Advanced Patterns
- Design Patterns -- Service layer, batch operations, upsert, AST tenant filters.
- Service Patterns -- SQLSpecAsyncService base, named SQL templates via db_manager.get_sql, direct driver API (select_value / select_one / execute), variadic filter composition, create_filter_dependencies() wiring.
- Dishka Integration -- FromDishka as Inject alias, multi-provider pattern (REQUEST-scoped domain services, REQUEST-scoped driver, APP-scoped singletons), handler injection.
- Vector Search — Oracle VECTOR_DISTANCE cosine similarity, Vertex AI embedding generation, SHA256-keyed embedding cache, intent classification via exemplar similarity, pgvector cross-reference.
Key Resources
- SQLglot Docs: https://sqlglot.com/sqlglot.html
- SQLglot GitHub: https://github.com/tobymao/sqlglot
- Mypyc Docs: https://mypyc.readthedocs.io/
- PyArrow Docs: https://arrow.apache.org/docs/python/
Official References
Shared Styleguide Baseline
- Use shared styleguides for generic language/framework rules to reduce duplication in this skill.
- General Principles
- Python
- Litestar
- Keep this skill focused on tool-specific workflows, edge cases, and integration details.
Files in this skill
28sqlspec skill questions
How do I install the sqlspec skill in Claude Code?
Run npx degit litestar-org/litestar-skills/skills/sqlspec ~/.claude/skills/sqlspec. Claude Code loads skills from ~/.claude/skills (all projects) or .claude/skills (one project) and uses a skill when its description matches the task.
Which clients can use the sqlspec skill?
Any client that reads the Agent Skills format: Claude Code, Codex, Gemini CLI and claude.ai (upload as a ZIP). Scripts in the skill run only where the client allows code execution.
Is SQLSpec an ORM?
No, SQLSpec is a type-safe SQL query mapper, not an ORM. For ORM repositories, you should use advanced-alchemy instead.
How do I handle complex dynamic queries in SQLSpec?
You should prefer the query builder API for complex dynamic queries to avoid string concatenation and handle dialect conversion automatically.
Can SQLSpec work with web frameworks like FastAPI or Litestar?
Yes, SQLSpec provides first-party extensions for Litestar, FastAPI, Flask, Starlette, and Sanic.
How can I get typed results instead of raw dictionaries?
You should use the schema_type parameter in your queries to map results to msgspec Structs, dataclasses, or Pydantic models.