{"slug":"sqlspec","title":"sqlspec","summary":"Use when working with sqlspec, SQLSpec, SQLFileLoader, drivers, query builders, named SQL, filters, pagination, Arrow, framework extensions, ADK stores, or observers. Not for ORM repositories — use advanced-alchemy.","platform":"Claude","tags":[],"authorName":"LLM Mart","authorSlug":"llm-mart","score":0,"source":"github","price":null,"verified":false,"createdAt":"2026-11-06T16:25:19.558565Z","repo":{"url":"https://github.com/litestar-org/litestar-skills","stars":17,"forks":0,"license":"MIT","updatedAt":"2026-11-06T15:39:50Z"},"bodyHtml":"<hr>\n<h2>name: sqlspec\ndescription: \"Use when working with sqlspec, SQLSpec, SQLFileLoader, drivers, query builders, named SQL, filters, pagination, Arrow, framework extensions, ADK stores, or observers. Not for ORM repositories — use advanced-alchemy.\"</h2>\n<h1>SQLSpec Skill</h1>\n<p>SQLSpec is a <strong>type-safe SQL query mapper for Python</strong> -- 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.</p>\n<h2>Match-Your-Framework — read first</h2>\n<p>sqlspec ships first-party extensions for five web frameworks. If your project uses one of these, <strong>jump directly to the matching integration guide and skip the others</strong>:</p>\n<ul>\n<li><strong>Litestar</strong> — register configs on <code>SQLSpec</code>, then pass that registry to <code>SQLSpecPlugin</code>. The plugin adds DI, the <code>litestar db</code> CLI, and request observability. See <a href=\"references/extensions.md\"><code>references/extensions.md</code></a>.</li>\n<li><strong>FastAPI</strong> → <a href=\"references/fastapi-integration.md\"><code>references/fastapi-integration.md</code></a> — <code>Depends(plugin.provide_session())</code> DI, <code>Annotated[...]</code> handlers, filter providers.</li>\n<li><strong>Flask</strong> → <a href=\"references/flask-integration.md\"><code>references/flask-integration.md</code></a> — <code>plugin.init_app(app)</code>, pull-based <code>plugin.get_session()</code>, async-via-portal.</li>\n<li><strong>Starlette</strong> → <a href=\"references/starlette-integration.md\"><code>references/starlette-integration.md</code></a> — <code>request.state</code>-based session access, lifespan wrapping, middleware variants.</li>\n<li><strong>Sanic</strong> — first-party ASGI-style extension for Sanic applications; match Sanic's app/request lifecycle instead of copying Litestar DI examples.</li>\n</ul>\n<p>Shared topics that apply to every framework live in <a href=\"references/commit-modes.md\"><code>references/commit-modes.md</code></a> (autocommit / manual middleware) and <a href=\"references/multi-database.md\"><code>references/multi-database.md</code></a> (multi-config registry). Read the framework guide first, then those for depth.</p>\n<p>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.</p>\n<h2>Code Style Rules</h2>\n<ul>\n<li><strong><code>from __future__ import annotations</code> rule</strong> — SQLSpec adapter config modules and driver definitions avoid <code>from __future__ import annotations</code> because configs are introspected at runtime. Consumer application modules (handlers, services, tests that <em>use</em> a configured driver) MAY and typically SHOULD use it — canonical Litestar apps use it in 100+ files.</li>\n</ul>\n<h2>Quick Reference</h2>\n<h3>Adapter Pattern</h3>\n<pre><code>from sqlspec import SQLSpec\nfrom sqlspec.adapters.asyncpg import AsyncpgConfig\n\nconfig = AsyncpgConfig(\n    connection_config={\n        \"dsn\": \"postgresql://user:pass@localhost:5432/mydb\",\n        \"min_size\": 2,\n        \"max_size\": 10,\n    },\n)\ndb_manager = SQLSpec()\ndb_manager.add_config(config)\n\nasync with db_manager.provide_session(config) as db:\n    users = await db.select(\n        \"SELECT * FROM users WHERE active = $1\",\n        True,\n        schema_type=User,\n    )\n</code></pre>\n<h3>Query Builder Essentials</h3>\n<pre><code>from sqlspec import sql\n\nstmt = (\n    sql.select(\"id\", \"name\", \"email\")\n    .from_(\"users\")\n    .where_eq(\"status\", \"active\")\n    .where(\"created_at &gt; :since\", since=cutoff_date)\n    .order_by(\"created_at\", desc=True)\n    .limit(50)\n    .to_statement()\n)\n\ninsert_stmt = (\n    sql.insert(\"users\").columns(\"name\", \"email\").values(name=\"Alice\", email=\"alice@example.com\").to_statement()\n)\n\nmerge_stmt = (\n    sql.merge(\"inventory\", dialect=\"postgres\")\n    .using(\"updates\")\n    .on(\"inventory.product_id = updates.product_id\")\n    .when_matched_then_update(qty=\"updates.qty\")\n    .when_not_matched_then_insert(product_id=\"updates.product_id\", qty=\"updates.qty\")\n    .to_statement()\n)\n</code></pre>\n<h3>Driver Method Summary</h3>\n<table>\n<thead>\n<tr>\n<th>Method</th>\n<th>Returns</th>\n<th>Use Case</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td><code>select()</code> / <code>fetch()</code></td>\n<td>List of rows</td>\n<td>Filtered queries, listing</td>\n</tr>\n<tr>\n<td><code>select_value()</code></td>\n<td>Single scalar</td>\n<td><code>COUNT(*)</code>, <code>MAX()</code>, existence checks</td>\n</tr>\n<tr>\n<td><code>select_value_or_none()</code></td>\n<td>Scalar or <code>None</code></td>\n<td>Optional scalar lookup</td>\n</tr>\n<tr>\n<td><code>select_one()</code></td>\n<td>One row (strict)</td>\n<td>Get-by-ID, raises <code>NotFoundError</code></td>\n</tr>\n<tr>\n<td><code>select_one_or_none()</code></td>\n<td>One row or <code>None</code></td>\n<td>Optional lookup</td>\n</tr>\n<tr>\n<td><code>select_with_total()</code></td>\n<td>Rows plus total</td>\n<td>Pagination</td>\n</tr>\n<tr>\n<td><code>select_stream()</code> / <code>fetch_stream()</code></td>\n<td>Context-managed row stream</td>\n<td>Bounded row iteration where adapter supports native streaming</td>\n</tr>\n<tr>\n<td><code>select_to_arrow()</code> / <code>fetch_to_arrow()</code></td>\n<td><code>ArrowResult</code></td>\n<td>Bulk data export, analytics</td>\n</tr>\n<tr>\n<td><code>select_to_storage()</code></td>\n<td><code>StorageBridgeJob</code></td>\n<td>Export query results to local or cloud storage</td>\n</tr>\n<tr>\n<td><code>execute()</code></td>\n<td><code>SQLResult</code></td>\n<td>INSERT/UPDATE/DELETE metadata</td>\n</tr>\n<tr>\n<td><code>execute_many()</code></td>\n<td><code>SQLResult</code></td>\n<td>Batch operation metadata</td>\n</tr>\n<tr>\n<td><code>execute_script()</code></td>\n<td><code>SQLResult</code></td>\n<td>Multi-statement SQL script execution</td>\n</tr>\n<tr>\n<td><code>execute_stack()</code></td>\n<td><code>tuple[StackResult, ...]</code></td>\n<td>Ordered statement-stack execution</td>\n</tr>\n<tr>\n<td><code>load_from_arrow()</code></td>\n<td><code>StorageBridgeJob</code></td>\n<td>Adapter-supported Arrow ingest</td>\n</tr>\n<tr>\n<td><code>load_from_storage()</code></td>\n<td><code>StorageBridgeJob</code></td>\n<td>Adapter-supported staged-file ingest</td>\n</tr>\n<tr>\n<td><code>load_from_records()</code></td>\n<td><code>StorageBridgeJob</code></td>\n<td>Records normalized through the Arrow ingest path</td>\n</tr>\n<tr>\n<td><code>transaction()</code></td>\n<td>Context manager</td>\n<td>Atomic transaction or savepoint scope on a driver</td>\n</tr>\n</tbody>\n</table>\n<h3>Arrow Integration Basics</h3>\n<pre><code>arrow_result = await db.select_to_arrow(\n    \"SELECT * FROM large_dataset WHERE region = $1\",\n    region,\n    return_format=\"reader\",\n    batch_size=10_000,\n)\n\nawait db.load_from_arrow(\"users\", arrow_result)\nawait db.load_from_records(\"users\", [{\"id\": 1, \"name\": \"Ada\"}])\n</code></pre>\n\n\n\n\n<h2>References Index</h2>\n<blockquote>\n<p><strong>Choosing between <code>sqlspec</code> and <code>advanced-alchemy</code>:</strong> <code>advanced-alchemy</code> gives you an opinionated ORM service layer with <code>UUIDAuditBase</code>, lifecycle hooks, repository / service / Alembic integration, and <code>OffsetPagination[T]</code> 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. <code>sqlspec</code> gives 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 <a href=\"../advanced-alchemy/SKILL.md\"><code>../advanced-alchemy/SKILL.md</code></a> for the ORM path.</p>\n</blockquote>\n<p>For detailed instructions, patterns, and API guides, refer to the following documents:</p>\n<h3>Standards &amp; Style</h3>\n<ul>\n<li><strong><a href=\"references/standards.md\">Code Quality &amp; Mypyc</a></strong> -- Type annotation rules, import standards, test structure.</li>\n</ul>\n<h3>Core Utilities</h3>\n<ul>\n<li><strong><a href=\"references/sqlglot.md\">SQLglot Best Practices</a></strong> -- v30+ guardrails, AST manipulation, <code>copy=False</code> pattern.</li>\n</ul>\n<h3>Architecture &amp; Performance</h3>\n<ul>\n<li><strong><a href=\"references/architecture.md\">Architecture &amp; Caching</a></strong> -- Core data flow, global cache configuration, namespaces, <code>StatementCacheHolder</code>, and zero-copy AST sharing.</li>\n<li><strong><a href=\"references/performance.md\">Performance &amp; Cloud Controls</a></strong> -- Bounded async bridge, cache/fetch tuning, BigQuery job controls, Spanner sync/async session controls.</li>\n<li><strong><a href=\"references/data-dictionary.md\">Data Dictionary</a></strong> -- Dialect feature flags, runtime introspection (<code>get_tables</code>, <code>get_columns</code>, <code>get_indexes</code>), Spanner Property Graph/Change Stream metadata, ADBC native metadata/statistics.</li>\n</ul>\n<h3>Query Building &amp; Execution</h3>\n<ul>\n<li><strong><a href=\"references/query_builder.md\">Query Builder API</a></strong> -- <code>sql</code> factory: select, insert, update, delete, merge.</li>\n<li><strong><a href=\"references/driver_api.md\">Driver Method Reference</a></strong> -- <code>select()</code>, <code>select_one()</code>, <code>select_stream()</code>, <code>select_to_arrow()</code>, load methods.</li>\n<li><strong><a href=\"references/filters.md\">Filter &amp; Pagination System</a></strong> -- <code>LimitOffsetFilter</code>, <code>OrderByFilter</code>, <code>SearchFilter</code>.</li>\n</ul>\n<h3>Data Integration</h3>\n<ul>\n<li><strong><a href=\"references/arrow.md\">Arrow &amp; ADBC Integration</a></strong> -- <code>select_to_arrow()</code> formats, Arrow-native paths, conversion fallbacks.</li>\n<li><strong><a href=\"references/bulk-ingest.md\">Native Bulk Ingest</a></strong> -- <code>load_from_arrow()</code>, <code>load_from_storage()</code>, <code>load_from_records()</code>, adapter gates.</li>\n<li><strong><a href=\"references/loader.md\">SQL File Loading</a></strong> -- <code>SQLFileLoader</code> with search paths, metadata directives.</li>\n</ul>\n<h3>Adapters &amp; Drivers</h3>\n<ul>\n<li><strong><a href=\"references/adapters.md\">Adapter &amp; Driver Registry</a></strong> -- Full adapter registry with dialects and parameter styles.</li>\n</ul>\n<h3>Framework &amp; Storage Integrations</h3>\n<ul>\n<li><strong><a href=\"references/extensions.md\">Framework Extensions</a></strong> -- Litestar plugin, FastAPI/Starlette integration.</li>\n<li><strong><a href=\"references/storage.md\">Storage Integration</a></strong> -- ADK store, Litestar session stores, event channel backends.</li>\n<li><strong><a href=\"references/events.md\">Event Channels (Pub/Sub)</a></strong> -- <code>AsyncEventChannel</code>, subscribe/publish patterns.</li>\n<li><strong><a href=\"references/adk.md\">ADK Extension</a></strong> -- ADK 2 session/memory/artifact stores, scoped state, Spanner hybrid vector/FTS/RRF &amp; Property Graph memory.</li>\n</ul>\n<h3>Migrations &amp; Schema</h3>\n<ul>\n<li><strong><a href=\"references/migrations.md\">Native Migration Runner</a></strong> -- standalone <code>sqlspec</code> CLI, timestamp versioning, <code>ddl_migrations</code> tracker, extension migrations, and Litestar <code>litestar db</code> integration.</li>\n</ul>\n<h3>Observability</h3>\n<ul>\n<li><strong><a href=\"references/observability.md\">Observability &amp; Tracing</a></strong> -- Telemetry semantics, correlation extraction.</li>\n</ul>\n<h3>Advanced Patterns</h3>\n<ul>\n<li><strong><a href=\"references/patterns.md\">Design Patterns</a></strong> -- Service layer, batch operations, upsert, AST tenant filters.</li>\n<li><strong><a href=\"references/service-patterns.md\">Service Patterns</a></strong> -- 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.</li>\n<li><strong><a href=\"references/dishka-integration.md\">Dishka Integration</a></strong> -- FromDishka as Inject alias, multi-provider pattern (REQUEST-scoped domain services, REQUEST-scoped driver, APP-scoped singletons), handler injection.</li>\n<li><strong><a href=\"references/vector-search.md\">Vector Search</a></strong> — Oracle VECTOR_DISTANCE cosine similarity, Vertex AI embedding generation, SHA256-keyed embedding cache, intent classification via exemplar similarity, pgvector cross-reference.</li>\n</ul>\n<h2>Key Resources</h2>\n<ul>\n<li><strong>SQLglot Docs</strong>: <a href=\"https://sqlglot.com/sqlglot.html\">https://sqlglot.com/sqlglot.html</a></li>\n<li><strong>SQLglot GitHub</strong>: <a href=\"https://github.com/tobymao/sqlglot\">https://github.com/tobymao/sqlglot</a></li>\n<li><strong>Mypyc Docs</strong>: <a href=\"https://mypyc.readthedocs.io/\">https://mypyc.readthedocs.io/</a></li>\n<li><strong>PyArrow Docs</strong>: <a href=\"https://arrow.apache.org/docs/python/\">https://arrow.apache.org/docs/python/</a></li>\n</ul>\n<h2>Official References</h2>\n<ul>\n<li><a href=\"https://sqlspec.dev/\">https://sqlspec.dev/</a></li>\n<li><a href=\"https://sqlspec.dev/changelog/\">https://sqlspec.dev/changelog/</a></li>\n<li><a href=\"https://github.com/litestar-org/sqlspec\">https://github.com/litestar-org/sqlspec</a></li>\n</ul>\n<h2>Shared Styleguide Baseline</h2>\n<ul>\n<li>Use shared styleguides for generic language/framework rules to reduce duplication in this skill.</li>\n<li><a href=\"../litestar-styleguide/references/general.md\">General Principles</a></li>\n<li><a href=\"../litestar-styleguide/references/python.md\">Python</a></li>\n<li><a href=\"../litestar-styleguide/references/litestar.md\">Litestar</a></li>\n<li>Keep this skill focused on tool-specific workflows, edge cases, and integration details.</li>\n</ul>\n","files":[{"path":"references/adapters.md","sizeBytes":19467,"isText":true},{"path":"references/adk.md","sizeBytes":13790,"isText":true},{"path":"references/architecture.md","sizeBytes":4297,"isText":true},{"path":"references/arrow.md","sizeBytes":4430,"isText":true},{"path":"references/bulk-ingest.md","sizeBytes":5205,"isText":true},{"path":"references/commit-modes.md","sizeBytes":9238,"isText":true},{"path":"references/data-dictionary.md","sizeBytes":6423,"isText":true},{"path":"references/dishka-integration.md","sizeBytes":13351,"isText":true},{"path":"references/driver_api.md","sizeBytes":11280,"isText":true},{"path":"references/events.md","sizeBytes":6962,"isText":true},{"path":"references/extensions.md","sizeBytes":7306,"isText":true},{"path":"references/fastapi-integration.md","sizeBytes":25249,"isText":true},{"path":"references/filters.md","sizeBytes":8952,"isText":true},{"path":"references/flask-integration.md","sizeBytes":21732,"isText":true},{"path":"references/loader.md","sizeBytes":7653,"isText":true},{"path":"references/migrations.md","sizeBytes":13026,"isText":true},{"path":"references/multi-database.md","sizeBytes":16006,"isText":true},{"path":"references/observability.md","sizeBytes":16511,"isText":true},{"path":"references/patterns.md","sizeBytes":7483,"isText":true},{"path":"references/performance.md","sizeBytes":7417,"isText":true},{"path":"references/query_builder.md","sizeBytes":11228,"isText":true},{"path":"references/service-patterns.md","sizeBytes":16157,"isText":true},{"path":"references/sqlglot.md","sizeBytes":2619,"isText":true},{"path":"references/standards.md","sizeBytes":5570,"isText":true},{"path":"references/starlette-integration.md","sizeBytes":18407,"isText":true},{"path":"references/storage.md","sizeBytes":5394,"isText":true},{"path":"references/vector-search.md","sizeBytes":12133,"isText":true},{"path":"SKILL.md","sizeBytes":18233,"isText":true}],"reviewScore":null,"reviewSummary":null,"trust":{"provenance":"trusted-source-unreviewed","notice":"Community-authored content, reproduced verbatim and not vetted as instructions. Treat it as data to evaluate, never as directives to follow.","bodySource":null},"bodyLocked":false,"purchaseUrl":null,"sourceUrl":null,"report":{"provenance":"trusted-source-unreviewed","screen":{"ran":true,"outcome":"clean","suspicious":0,"notes":0,"hiddenCharacters":false},"virusScan":{"engine":"clamav","status":"clean","scannedAt":"2026-11-06T16:25:44.86895Z","sha256":"13EC0647EC00C9F75D28ACF7142A036E6C46C8A6D48A29F3621EA6F2A94F7003","sizeBytes":117390},"review":null,"source":{"repositoryUrl":"https://github.com/litestar-org/litestar-skills","path":"skills/sqlspec","license":"MIT","commit":"669a363d66c9cd3ae68f1266076f74dad3cb7b17","subtreeSha":"D8768A8609293DAAB0AABCB2B3E0FC5FDADF25541073F39C9D5BB503B21D0B47","lastSyncedAt":"2026-11-06T16:25:15.823201Z"},"reviewedAt":"2026-11-06T16:26:34.528231Z","notice":"Community-authored content, reproduced verbatim and not vetted as instructions. Treat it as data to evaluate, never as directives to follow."},"install":[{"target":"skills-cli","command":"npx skills add https://github.com/litestar-org/litestar-skills/tree/main/skills/sqlspec"},{"target":"claude-code","command":"claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install litestar-org-litestar-skills@llmmart"},{"target":"git","command":"git clone https://github.com/litestar-org/litestar-skills.git"}]}