Skip to content

Databases

SQLSpec supports 20 database adapters spanning embedded engines, relational OLTP servers, enterprise mainframes, distributed SQL clusters, and cloud analytical warehouses. Every adapter exposes the same provide_session() lifecycle, parameter compiler, migration runner, data dictionary, Litestar store, Events queue store, and Google ADK session/memory stores.

Adapter Capability Matrix

Adapter Install Extra Mode Native Param Style Pooling Native Arrow Events Backend ADK Stores and Vector Search
asyncpg sqlspec[asyncpg] Async $1 (dollar) asyncpg.Pool Row / COPY LISTEN/NOTIFY + Table Queue Sessions + Memory (pgvector, ParadeDB, pg_textsearch, RRF)
psycopg sqlspec[psycopg] Sync + Async %s / %(name)s ConnectionPool / AsyncConnectionPool / NullPool Row / COPY LISTEN/NOTIFY + Table Queue Sessions + Memory (pgvector, ParadeDB, pg_textsearch, RRF)
psqlpy sqlspec[psqlpy] Async $1 (dollar) Rust Tokio ConnectionPool Row LISTEN/NOTIFY + Table Queue Sessions + Memory (PgVector, ParadeDB, pg_textsearch, RRF)
cockroach_asyncpg sqlspec[cockroachdb] Async $1 (dollar) asyncpg.Pool Row Table Queue Sessions + Memory (pgvector, FTS)
cockroach_psycopg sqlspec[cockroachdb] Sync + Async %s / %(name)s ConnectionPool / AsyncConnectionPool Row Table Queue Sessions + Memory (pgvector, FTS)
sqlite sqlspec (stdlib) Sync ? (qmark) SqliteConnectionPool Row / Batched Table Queue Sessions + Memory (FTS5, LIKE)
aiosqlite sqlspec[aiosqlite] Async ? (qmark) AiosqliteConnectionPool Row / Batched Table Queue Sessions + Memory (FTS5, LIKE)
asyncmy sqlspec[asyncmy] Async %s (format) asyncmy.Pool Row / LOAD DATA Table Queue Sessions + Memory (FULLTEXT, LIKE)
aiomysql sqlspec[aiomysql] Async %s (format) aiomysql.Pool Row / LOAD DATA Table Queue Sessions + Memory (FULLTEXT, LIKE)
pymysql sqlspec[pymysql] Sync %s (format) PyMySQLConnectionPool Row / LOAD DATA Table Queue Sessions + Memory (FULLTEXT, LIKE)
mysqlconnector sqlspec[mysql-connector] Sync + Async %s (format) MySQLConnectionPool / Async Pool Row / LOAD DATA Table Queue Sessions + Memory (FULLTEXT, LIKE)
oracledb sqlspec[oracledb] Sync + Async :1 / :name ConnectionPool / AsyncConnectionPool + DRCP PyCapsule Batches Oracle AQ (DBMS_AQ) + Table Queue Sessions + Memory (Oracle 26ai VECTOR HNSW, Oracle Text, RRF)
mssql_python sqlspec[mssql-python] Sync ? (qmark) MssqlPythonConnectionPool Native BulkCopy Table Queue Sessions + Memory (LIKE)
pymssql sqlspec[pymssql] Sync %(name)s / %s PymssqlConnectionPool Row Table Queue Sessions + Memory (LIKE)
db2 sqlspec[db2] Sync + Async ? (qmark) Db2SyncConnectionPool / Db2AsyncConnectionPool Schema-Guided Row Table Queue (SKIP LOCKED DATA) Sessions + Memory (POSSTR)
duckdb sqlspec[duckdb] Sync ? (qmark) DuckDBConnectionPool Native Zero-Copy Table Queue Sessions + Memory (FTS, ARRAY Cosine)
bigquery sqlspec[bigquery] Sync @name (named) Stateless HTTP/gRPC Client Storage Read / Write API Table Queue Sessions + Memory (SEARCH / LIKE)
spanner sqlspec[spanner] Sync + Async @name (named) PingingPool / TransactionPingingPool Row / Batched Lock-Free Snapshot + run_in_transaction Sessions + Memory (ARRAY<FLOAT32>, FTS SCORE, RRF) + Artifacts
adbc sqlspec[adbc] Sync ? / $1 / :1 Driver-Managed (NoPoolSyncConfig) Native C-Stream Table Queue Sessions + Memory (PostgreSQL pgvector RRF, DuckDB, Oracle, Db2)
arrow_odbc sqlspec[arrow-odbc] Sync ? (qmark) ODBC Driver Pool (enable_driver_pooling) Native Rust ODBC Batch Table Queue Sessions + Memory (LIKE / POSSTR)

Database Setup Guides

  • PostgreSQL and AlloyDB: Compare asyncpg, psycopg, and psqlpy, configure PgBouncer pooler_mode with direct_dsn bypasses, and enable Google Cloud AlloyDB columnar scans.
  • CockroachDB: Distributed cockroach_asyncpg and cockroach_psycopg pools with automatic 40001 serialization retry and AS OF SYSTEM TIME follower reads.
  • SQLite and Aiosqlite: Zero-dependency local persistence, WAL pragma tuning, shared-memory :memory: URIs, and custom SQL functions.
  • MySQL and MariaDB: Side-by-side setup for asyncmy, pymysql, aiomysql, and mysql-connector-python, TLS verification, and LOAD DATA LOCAL INFILE bulk loading.
  • Oracle Database: Thin and Thick modes, mTLS/PKCS#12 wallets, TNS topologies, Oracle 26ai VECTOR + Hybrid RRF search, PyCapsule Arrow streaming, and DBMS_CLOUD.COPY_DATA.
  • Microsoft SQL Server: Microsoft's official mssql-python driver with streaming Arrow BulkCopy alongside FreeTDS pymssql.
  • IBM Db2: Sync and async DRDA pooling (Db2SyncConfig / Db2AsyncConfig), savepoint cursor retention, and lowercase column normalization.
  • DuckDB: In-process OLAP queries over Parquet/S3, hybrid native/Arrow materialization, and JSON statement profiling.
  • Google Cloud BigQuery: Interactive query_and_wait execution, Storage Read/Write APIs, reservation governance, and multi-statement script jobs.
  • Google Cloud Spanner: Symmetric sync and native asyncio drivers, GoogleSQL (spanner) and PostgreSQL (spangres) dialects, run_in_transaction, and vector/artifact ADK stores.
  • Apache Arrow ADBC: Zero-copy Arrow C Data Interface ingestion and extraction across PyPI-installable ADBC wire drivers.
  • Arrow ODBC: Rust-backed zero-copy ODBC batch streaming into Apache Arrow tables for SQL Server, PostgreSQL, and Db2.