Repository files navigation

Type-safe SQL execution layer for Python.

SQLSpec handles database connectivity and result mapping so you can focus on SQL. Write raw queries when you need precision, use the builder API when you need composability, or load SQL from files when you need organization. Every statement passes through asqlglot-powered AST pipeline for validation, dialect conversion, and optimization before execution. Export results as Python objects, Arrow tables, Polars or pandas DataFrames.

It's not an ORM. It's the connectivity and processing layer between your application and your database that provides the right abstraction for each situation without dictating how you write SQL.

SQLSpec is currently in active development. The public API may change. Follow thedocs and changelog for updates.

Connection Management

• Connection pooling with configurable size, timeout, and lifecycle hooks
• Sync and async support with a unified API surface
• Adapters for PostgreSQL (psycopg, asyncpg, psqlpy), SQLite (sqlite3, aiosqlite), DuckDB, MySQL (asyncmy, mysql-connector, pymysql), Oracle, BigQuery, and ADBC-compatible databases

Query Execution

• Raw SQL strings with automatic parameter binding and dialect translation
• SQL AST parsing via sqlglot for validation, optimization, and dialect conversion
• Builder API for programmatic query construction without string concatenation
• SQL file loading to keep queries organized alongside your code (named SQL queries)
• Statement stacks for batching multiple operations with transaction control

Result Handling

• Type-safe result mapping to Pydantic, msgspec, attrs, or dataclasses
• Apache Arrow export for zero-copy integration with pandas, Polars, and analytical tools
• Result iteration, single-row fetch, or bulk retrieval based on your use case

Framework Integration

• Litestar plugin with dependency injection for connections, sessions, and pools
• Starlette/FastAPI middleware for automatic transaction management
• Flask extension with sync/async portal support

Production Features

• SQL validation and caching via sqlglot AST parsing
• OpenTelemetry and Prometheus instrumentation hooks
• Database event channels with native LISTEN/NOTIFY, Oracle AQ, and a portable queue fallback
• Structured logging with correlation ID support
• Migration CLI for schema versioning

pip install"sqlspec"

frompydanticimportBaseModelfromsqlspecimportSQLSpecfromsqlspec.adapters.sqliteimportSqliteConfigclassGreeting(BaseModel):message:strspec=SQLSpec()db=spec.add_config(SqliteConfig(connection_config={"database":":memory:"}))withspec.provide_session(db)assession:greeting=session.select_one("SELECT 'Hello, SQLSpec!' AS message",schema_type=Greeting,    )print(greeting.message)# Output: Hello, SQLSpec!

That's it. Write SQL, define a schema, get typed objects back. Connection pooling, parameter binding, and result mapping are handled automatically.

See theGetting Started guide for installation variants, adapter selection, and advanced result mapping options.

• Getting Started
• Usage Guides
• Examples Gallery
• API Reference
• CLI Reference

• PostgreSQL + Vertex AI Demo - Vector search with pgvector and real-time chat using Litestar and Google ADK. Shows connection pooling, migrations, type-safe result mapping, vector embeddings, and response caching.
• Oracle + Vertex AI Demo - Oracle 23ai vector search with semantic similarity using HNSW indexes. Demonstrates NumPy array conversion, large object (CLOB) handling, and real-time performance metrics.

See theusage docs for detailed guides on adapters, configuration patterns, and features like theSQL file loader.

• sqlglot - SQL parser, transpiler, and optimizer powering SQLSpec's AST pipeline

Contributions, issue reports, and adapter ideas are welcome. Review thecontributor guide and follow the projectcoding standards before opening a pull request.

SQLSpec is distributed under the MIT License.