ADBC#
The Arrow Database Connectivity (ADBC) adapter connects SQLSpec to 10 relational,
analytical, cloud, and Arrow Flight SQL backends through a single
AdbcConfig interface.
Arrow-first data path:
select_to_arrow()andload_from_arrow()transfer ArrowTableandRecordBatchReaderpayloads without intermediate Python tuple allocation.Standard row & model APIs:
select(),select_one(), andselect_stream()materialize dictionaries or typed models (msgspec, Pydantic, dataclasses) from Arrow batches.Automatic dialect & parameter routing: SQLSpec detects the connected engine, configures SQLGlot dialect compilation, and normalizes parameter placeholders, exception classes, savepoint syntax, and column casing per backend.
Installation#
Install SQLSpec with the adbc extra (which installs adbc-driver-manager and
pyarrow), then install the ADBC driver package for your target database:
# Oracle (pure-Go ADBC driver wheel from GizmoData)
uv add "sqlspec[adbc]" adbc-driver-oracle
# IBM Db2 (pure-Go DRDA ADBC driver wheel from GizmoData)
uv add "sqlspec[adbc]" adbc-driver-db2
# PostgreSQL
uv add "sqlspec[adbc]" adbc-driver-postgresql
# SQLite
uv add "sqlspec[adbc]" adbc-driver-sqlite
# DuckDB (bundled driver or standalone pure-Rust Quack driver)
uv add "sqlspec[adbc]" adbc-driver-duckdb
uv add "sqlspec[adbc]" adbc-driver-quack
# Google BigQuery & Snowflake
uv add "sqlspec[adbc]" adbc-driver-bigquery adbc-driver-snowflake
# GizmoSQL (native driver with OAuth/OIDC support, or Flight SQL fallback)
uv add "sqlspec[adbc]" adbc-driver-gizmosql
uv add "sqlspec[adbc]" adbc-driver-flightsql
Supported Backends#
SQLSpec resolves the ADBC driver from either driver_name (or driver) or the
connection uri scheme:
Backend |
|
URI schemes |
Driver package / binary |
Dialect & parameter behavior |
|---|---|---|---|---|
PostgreSQL |
|
|
|
|
Oracle |
|
|
|
|
IBM Db2 |
|
|
|
|
SQLite |
|
|
|
|
DuckDB |
|
|
|
|
DuckDB (Quack) |
|
|
|
|
BigQuery |
|
|
|
|
Snowflake |
|
|
|
|
Flight SQL |
|
|
|
Arrow Flight SQL transport; defaults to |
GizmoSQL |
|
|
|
|
Tip
You can also pass a filesystem path to any ADBC shared library (.so,
.dylib, or .dll) as driver_name. SQLSpec automatically loads it
through adbc_driver_manager.dbapi.connect and forwards uri,
username, password, and entrypoint into db_kwargs.
Connection Examples#
from sqlspec.adapters.adbc import AdbcConfig
# Oracle (EasyConnect URI; discrete username/password are lifted to db_kwargs)
oracle_config = AdbcConfig(
connection_config={
"driver_name": "oracle",
"uri": "oracle://localhost:1521/FREEPDB1",
"username": "system",
"password": "secret",
}
)
# IBM Db2 (DSN string or db2:// URI)
db2_config = AdbcConfig(
connection_config={
"driver_name": "db2",
"uri": "DATABASE=testdb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=secret;",
}
)
from sqlspec.adapters.adbc import AdbcConfig
postgres_config = AdbcConfig(
connection_config={
"uri": "postgresql://user:password@localhost:5432/app",
}
)
sqlite_config = AdbcConfig(
connection_config={
"uri": "sqlite:///tmp/app.db",
}
)
duckdb_config = AdbcConfig(
connection_config={
"driver_name": "duckdb",
"path": "/tmp/app.duckdb",
}
)
# DuckDB / MotherDuck via adbc-driver-quack
quack_config = AdbcConfig(
connection_config={
"driver_name": "quack",
"uri": "quack:///md:analytics_db",
"token": "md_eyJ...",
}
)
from sqlspec.adapters.adbc import AdbcConfig
bigquery_config = AdbcConfig(
connection_config={
"driver_name": "bigquery",
"project_id": "analytics-project",
"dataset_id": "events",
}
)
snowflake_config = AdbcConfig(
connection_config={
"driver_name": "snowflake",
"uri": "snowflake://user:password@account/db/schema?warehouse=compute_wh",
}
)
from sqlspec.adapters.adbc import AdbcConfig
# GizmoSQL with username/password or browser-based OAuth/OIDC ("external")
gizmosql_config = AdbcConfig(
connection_config={
"driver_name": "gizmosql",
"uri": "grpc+tls://localhost:31337",
"username": "admin",
"password": "secret",
"tls_skip_verify": True,
"query_timeout": 60,
}
)
gizmosql_oauth_config = AdbcConfig(
connection_config={
"driver_name": "gizmosql",
"uri": "grpc+tls://gizmosql.example.com:31337",
"auth_type": "external",
"tls_root_certs": "/etc/certs/ca.pem",
}
)
Flight SQL & GizmoSQL Options#
SQLSpec accepts high-level shortcut keys in connection_config for Flight SQL and
GizmoSQL connections and maps them into the underlying ADBC db_kwargs options:
usernameandpassword: Basic authentication credentials.auth_type: Set to"external"on GizmoSQL to trigger browser-based OAuth/OIDC authentication (mapped toadbc.gizmosql.auth_typewhen usingadbc_driver_flightsqlor passed directly toadbc_driver_gizmosql).authorization_header: CustomAuthorizationheader value.tls_skip_verify: Boolean flag to disable TLS certificate verification for local development.tls_root_certs,mtls_cert_chain, andmtls_private_key: PEM certificate paths for TLS and mutual TLS.grpc_max_message_size: Maximum gRPC message size in bytes.query_timeoutandfetch_timeout: RPC timeout values in seconds.with_cookie_middleware: Enable Flight SQL cookie middleware (boolean).headers: Dictionary of custom RPC call headers (mapped toadbc.flight.sql.rpc.call_header.<name>).gizmosql_backend: Set to"sqlite"when targeting a SQLite-backed GizmoSQL server (defaults to"duckdb").
Transactions, Savepoints, and Dialect Behavior#
Savepoints and Nested Transactions#
ADBC connections manage transactions through session.begin(), session.commit(),
session.rollback(), and session.transaction(). Nested session.transaction()
blocks use SQL savepoints with dialect-specific syntax:
Backend Dialect |
|
|
|
|---|---|---|---|
PostgreSQL, SQLite, MySQL |
|
|
|
Oracle |
|
|
No-op (Oracle does not support |
IBM Db2 |
|
|
|
DuckDB, BigQuery, Snowflake |
Raises |
Raises |
Raises |
Oracle and Db2 Column Name Normalization#
Oracle and IBM Db2 fold unquoted SQL identifiers to uppercase (SELECT id, name FROM users
returns columns named ID and NAME). When the resolved dialect is oracle or
db2, SQLSpec defaults enable_lowercase_column_names=True in driver_features so
implicit-uppercase column names are normalized to lowercase (row["id"], row["name"])
while preserving quoted mixed-case identifiers (such as "MixedCase").
To preserve raw uppercase column names from Oracle or Db2, disable the feature in
driver_features:
from sqlspec.adapters.adbc import AdbcConfig
oracle_raw_columns = AdbcConfig(
connection_config={"uri": "oracle://system:secret@localhost:1521/FREEPDB1"},
driver_features={"enable_lowercase_column_names": False},
)
PostgreSQL UUID and Extension Dialects#
UUID Decoding & Binding: PostgreSQL ADBC drivers expose
UUIDcolumns as Arrow opaque binary extension types. SQLSpec automatically decodes scalarUUIDandUUID[]values touuid.UUIDon row APIs (select, select_one, select_stream) and adds explicit::uuid/::uuid[]casts when binding Pythonuuid.UUIDparameters. Setdriver_features={"enable_arrow_extension_types": False}to keep raw bytes on row APIs.Extension Dialect Auto-Upgrade: On the first PostgreSQL connection, SQLSpec checks
pg_extensiononce and upgrades the dialect topgvector(whenvectoris installed),pg_textsearch(whenpg_textsearchis installed), orparadedb(whenpg_searchis installed). Control detection withenable_pgvector,enable_pg_textsearch, andenable_paradedbindriver_features.
Schema Introspection and Statistics#
AdbcDataDictionary combines ADBC's native C-API metadata methods
(adbc_get_objects, adbc_get_table_schema) with SQLSpec's central SQL catalog
queries as an automatic fallback:
When a driver returns complete table, column, and foreign-key payloads via
adbc_get_objects, SQLSpec normalizes them directly without issuing catalog SQL.When native metadata is unsupported, incomplete, or omits index/DDL details,
AdbcDataDictionaryfalls back to the dialect's SQL catalog queries (postgres, oracle, db2, sqlite, duckdb, bigquery).For Oracle,
get_optimal_type(driver, "json")inspects the server version and selects nativeJSONon Oracle 21c+,BLOBon Oracle 12c–19c, andCLOBon older versions.get_statisticswrapsadbc_get_statisticsand maps standard ADBC statistic keys (0–6) to canonicaladbc.statistic.*names; drivers without statistics support raiseOperationalError.
Framework and AI Extension Stores#
AdbcConfig integrates with all SQLSpec synchronous extension stores across every
supported relational and analytical dialect (postgres, oracle, db2,
sqlite, duckdb, bigquery, snowflake):
Litestar Sessions:
ADBCStoreDurable Event Queue:
AdbcEventQueueStoreGoogle ADK Sessions & Events:
AdbcADKStoreGoogle ADK Memory:
AdbcADKMemoryStore
API Reference#
Configuration#
- class sqlspec.adapters.adbc.AdbcConfig[source]#
Bases:
NoPoolSyncConfig[Connection,AdbcDriver]ADBC configuration for Arrow Database Connectivity.
ADBC provides an interface for connecting to multiple database systems with Arrow-native data transfer.
Supports multiple database backends including PostgreSQL, SQLite, DuckDB, BigQuery, and Snowflake with automatic driver detection and loading.
- driver_type#
alias of
AdbcDriver
- connection_type#
alias of
Connection
- __init__(*, connection_config=None, connection_instance=None, migration_config=None, statement_config=None, driver_features=None, bind_key=None, extension_config=None, observability_config=None, **kwargs)[source]#
Initialize configuration.
- Parameters:
connection_config¶ (
AdbcConnectionParams|dict[str, typing.Any] |None) -- Connection configuration parametersconnection_instance¶ (
Connection|None) -- Pre-created connection instance to use instead of creating new onemigration_config¶ (
dict[str, typing.Any] |None) -- Migration configurationstatement_config¶ (
StatementConfig|None) -- Default SQL statement configurationdriver_features¶ (
AdbcDriverFeatures|dict[str, typing.Any] |None) -- Driver feature configuration (AdbcDriverFeatures)bind_key¶ (
str|None) -- Optional unique identifier for this configurationextension_config¶ (
dict[str,dict[str,Any] |LitestarConfig|FastAPIConfig|StarletteConfig|SanicConfig|FlaskConfig|ADKConfig|EventsConfig|OpenTelemetryConfig|PrometheusConfig] |None) -- Extension-specific configurationobservability_config¶ (
ObservabilityConfig|None) -- Adapter-level observability overrides for lifecycle hooks and observers**kwargs¶ (
Any) -- Additional keyword arguments passed to the base configuration.
- property supports_migration_schemas: bool#
Migration schema support is only available for PostgreSQL-backed ADBC drivers.
- create_connection()[source]#
Create and return a new connection using the specified driver.
- Return type:
Connection- Returns:
A new connection instance, or
connection_instancewhen configured.- Raises:
ImproperConfigurationError -- If the connection could not be established.
- provide_session(*_args, statement_config=None, **_kwargs)[source]#
Provide a driver session context manager.
On first call with a PostgreSQL backend, detects pgvector/paradedb extensions and updates the dialect accordingly.
Connection Parameters#
Driver Features#
- class sqlspec.adapters.adbc.AdbcDriverFeatures[source]#
Bases:
TypedDictADBC driver feature configuration.
Controls optional type handling and serialization behavior for the ADBC adapter. These features configure how data is converted between Python and Arrow types.
- json_serializer#
JSON serialization function to use. Callable that takes Any and returns str (JSON string). Default: sqlspec.utils.serializers.to_json
- enable_cast_detection#
Enable cast-aware parameter processing. When True, detects SQL casts and applies appropriate serialization. Currently used for PostgreSQL JSONB handling. Default: True
- enable_strict_type_coercion#
Enforce strict type coercion rules. When True, raises errors for unsupported type conversions. When False, attempts best-effort conversion. Default: False
- strict_type_coercion#
Alias for enable_strict_type_coercion.
- enable_arrow_extension_types#
Enable PyArrow extension type support. When True, preserves Arrow extension type metadata when reading data. When False, falls back to storage types. Default: True
- arrow_extension_types#
Alias for enable_arrow_extension_types.
- enable_pgvector#
Enable automatic pgvector extension detection. When True and the resolved dialect is PostgreSQL, queries
pg_extensionon the first connection to check for thevectorextension. Defaults to True when thepgvectorPython package is installed.
- enable_paradedb#
Enable ParadeDB (pg_search) extension detection. When True and the resolved dialect is PostgreSQL, queries
pg_extensionon the first connection to check for thepg_searchextension. Defaults to True. Independent of enable_pgvector.
- enable_pg_textsearch#
Enable pg_textsearch extension detection for BM25 search. When True and the resolved dialect is PostgreSQL, queries
pg_extensionon the first connection to check for thepg_textsearchextension. Defaults to True.
- enable_events#
Enable database event channel support. Defaults to True when extension_config["events"] is configured. Provides pub/sub capabilities via table-backed queue (ADBC has no native pub/sub). Requires extension_config["events"] for migration setup.
- on_connection_create#
Callback executed when a connection is created. Receives the raw ADBC connection for low-level driver configuration.
- events_backend#
Event channel backend selection. Only option: "poll_queue" (durable table-backed queue with lease-based retries and acknowledgements). ADBC does not have native pub/sub, so poll_queue is the only backend. Defaults to "poll_queue".
- enable_statement_profiling#
Enable DuckDB JSON statement profiling when using a DuckDB ADBC backend. Defaults to False.
Driver#
- final class sqlspec.adapters.adbc.AdbcDriver[source]#
Bases:
SyncDriverAdapterBaseADBC driver for Arrow Database Connectivity.
Provides database connectivity through ADBC with support for multiple database dialects, parameter style conversion, and transaction management.
- __init__(connection, statement_config=None, driver_features=None, *, dialect=None)[source]#
Initialize driver adapter with connection and configuration.
- Parameters:
connection¶ (
Connection) -- Database connection instancestatement_config¶ (
StatementConfig|None) -- Statement configuration for the driverdriver_features¶ (
dict[str, typing.Any] |None) -- Driver-specific features like extensions, secrets, and connection callbacksobservability¶ -- Optional runtime handling lifecycle hooks, observers, and spans
- property data_dictionary: AdbcDataDictionary#
Get the data dictionary for this driver.
- Returns:
Data dictionary instance for metadata queries
- dispatch_execute(cursor, statement)[source]#
Execute single SQL statement.
- Parameters:
- Return type:
- Returns:
Execution result with data or row count
- dispatch_execute_many(cursor, statement)[source]#
Execute SQL with multiple parameter sets.
- Parameters:
- Return type:
- Returns:
Execution result with row counts
- dispatch_execute_script(cursor, statement)[source]#
Execute SQL script containing multiple statements.
- Parameters:
- Return type:
- Returns:
Execution result with statement counts
- dispatch_select_stream(statement, chunk_size)[source]#
Return a native ADBC stream backed by
fetch_record_batch().- Return type:
Optional[SyncRowStream[dict[str, typing.Any]]]
- begin()[source]#
Begin database transaction.
ADBC connections operate with autocommit disabled and manage the active transaction internally, so no explicit statement is issued.
- Return type:
- create_savepoint(name)[source]#
Create a savepoint within the current transaction.
- Raises:
NotImplementedError -- If the connected database does not support savepoints.
- Return type:
- release_savepoint(name)[source]#
Release a previously created savepoint.
- Raises:
NotImplementedError -- If the connected database does not support savepoints.
- Return type:
- rollback_to_savepoint(name)[source]#
Roll back the current transaction to a previously created savepoint.
- Raises:
NotImplementedError -- If the connected database does not support savepoints.
- Return type:
- set_migration_session_schema(schema)[source]#
Set the PostgreSQL search path for migration SQL when using ADBC PostgreSQL.
- Return type:
- set_migration_non_transactional_schema(schema)[source]#
Set the PostgreSQL search path for non-transactional migration SQL.
- Return type:
- reset_migration_session_schema()[source]#
Reset PostgreSQL search path after non-transactional migration SQL.
- Return type:
- has_schema(schema)[source]#
Return whether a PostgreSQL schema exists when using ADBC PostgreSQL.
- Return type:
- with_cursor(connection)[source]#
Create context manager for cursor.
- Parameters:
connection¶ (
Connection) -- Database connection- Return type:
AdbcCursor- Returns:
Cursor context manager
- handle_database_exceptions()[source]#
Handle database-specific exceptions and wrap them appropriately.
- Return type:
AdbcExceptionHandler- Returns:
Exception handler context manager
- select_to_arrow(statement, /, *parameters, statement_config=None, return_format='table', native_only=False, batch_size=None, arrow_schema=None, **kwargs)[source]#
Execute query and return results as Apache Arrow (ADBC native path).
ADBC provides zero-copy Arrow support via cursor.fetch_arrow_table(). This is 5-10x faster than the conversion path for large datasets.
- Parameters:
statement¶ -- SQL statement, string, or QueryBuilder
*parameters¶ -- Query parameters or filters
statement_config¶ -- Optional statement configuration override
return_format¶ -- "table" for pyarrow.Table (default), "batch" for RecordBatch, "batches" for list of RecordBatch, "reader" for RecordBatchReader
native_only¶ -- Ignored for ADBC (always uses native path)
batch_size¶ -- Batch size hint (for future streaming implementation)
arrow_schema¶ -- Optional pyarrow.Schema for type casting
**kwargs¶ -- Additional keyword arguments
- Returns:
ArrowResult with native Arrow data
- select_to_storage(statement, destination, /, *parameters, statement_config=None, partitioner=None, format_hint=None, telemetry=None, **kwargs)[source]#
Stream query results to storage via the Arrow fast path.
- load_from_arrow(table, source, *, partitioner=None, overwrite=False, telemetry=None)[source]#
Ingest an Arrow payload directly through the ADBC cursor.
- Return type:
- load_from_storage(table, source, *, file_format, partitioner=None, overwrite=False)[source]#
Read an artifact from storage and ingest it via ADBC.
- Return type:
- resolve_rowcount(cursor)[source]#
Resolve rowcount from ADBC cursor for the direct execution path.
- Return type:
- prepare_driver_parameters(parameters, statement_config, is_many=False, prepared_statement=None)[source]#
Prepare parameters with cast-aware type coercion for ADBC.
For PostgreSQL, applies cast-aware parameter processing using metadata from the compiled statement. This allows proper handling of JSONB casts and other type conversions. Respects driver_features['enable_cast_detection'] configuration.
- Parameters:
- Return type:
- Returns:
Parameters with cast-aware type coercion applied
Data Dictionary#
- class sqlspec.adapters.adbc.data_dictionary.AdbcDataDictionary[source]#
Bases:
SyncDataDictionaryBaseADBC multi-dialect data dictionary.
- dialect: ClassVar[str] = 'generic'#
Dialect identifier. Must be defined by subclasses as a class attribute.
- get_version(driver)[source]#
Get database version information based on detected dialect.
Inlines the version cache check to avoid cross-module method calls across MyPyC compilation boundaries.
- Return type:
- get_feature_flag(driver, feature)[source]#
Check if database supports a specific feature.
- Return type:
- get_optimal_type(driver, type_category)[source]#
Get optimal database type for a category.
- Return type:
- get_metadata_capabilities(driver, domains=None)[source]#
Report ADBC transport metadata capabilities without implying DDL fidelity.
- Return type:
- get_columns(driver, table=None, schema=None)[source]#
Get column information for a table or schema.
- Return type:
- get_indexes(driver, table=None, schema=None)[source]#
Get index information for a table or schema.
- Return type:
- get_statistics(driver, table, schema=None, *, approximate=True)[source]#
Get native driver statistics for a table via ADBC GetStatistics.
- Return type:
list[TableStatisticsMetadata]
- get_constraints(driver, table=None, schema=None)[source]#
Get lossy ADBC constraint shells from GetObjects.
- Return type:
- get_ddl(driver, object_name, schema=None, *, object_type='table', include_dependencies=True, prefer_native=True, redact=True)[source]#
Fail closed because ADBC metadata is not a lossless DDL source.
- Return type:
DDLResult