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() and load_from_arrow() transfer Arrow Table and RecordBatchReader payloads without intermediate Python tuple allocation.

  • Standard row & model APIs: select(), select_one(), and select_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

driver_name aliases

URI schemes

Driver package / binary

Dialect & parameter behavior

PostgreSQL

postgres, postgresql, pg

postgresql://, postgres://

adbc_driver_postgresql

postgres dialect; numeric ($1) parameters; auto-detects pgvector, pg_textsearch, and paradedb.

Oracle

oracle, oracledb

oracle://

adbc_driver_oracle

oracle dialect; positional colon (:1) execution parameters; lowercases implicit uppercase column names.

IBM Db2

db2, ibmdb2, ibm_db

db2://

adbc_driver_db2

db2 dialect; positional ? parameters; lowercases implicit uppercase column names.

SQLite

sqlite, sqlite3

sqlite://

adbc_driver_sqlite

sqlite dialect; positional ? parameters; sqlite:// prefixes are normalized to file paths.

DuckDB

duckdb

duckdb://

adbc_driver_duckdb

duckdb dialect; ? and $1 parameters; COPY ... TO/FROM parameter inlining.

DuckDB (Quack)

quack

quack://, quack+tls://

adbc_driver_quack

duckdb dialect; supports local files, :memory:, and MotherDuck (quack:///md:db + token).

BigQuery

bigquery, bq

bigquery://

adbc_driver_bigquery

bigquery dialect; named @param parameters; lifts project_id, dataset_id, and token into db_kwargs.

Snowflake

snowflake, sf

snowflake://

adbc_driver_snowflake

snowflake dialect; ? and $1 parameters.

Flight SQL

flightsql, grpc

grpc://, grpc+tls://

adbc_driver_flightsql

Arrow Flight SQL transport; defaults to sqlite dialect unless overridden.

GizmoSQL

gizmosql, gizmo

gizmosql://, gizmo://

adbc_driver_gizmosql (falls back to adbc_driver_flightsql)

duckdb dialect by default (or sqlite via gizmosql_backend="sqlite"); supports auth_type="external" SSO.

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:

  • username and password: Basic authentication credentials.

  • auth_type: Set to "external" on GizmoSQL to trigger browser-based OAuth/OIDC authentication (mapped to adbc.gizmosql.auth_type when using adbc_driver_flightsql or passed directly to adbc_driver_gizmosql).

  • authorization_header: Custom Authorization header value.

  • tls_skip_verify: Boolean flag to disable TLS certificate verification for local development.

  • tls_root_certs, mtls_cert_chain, and mtls_private_key: PEM certificate paths for TLS and mutual TLS.

  • grpc_max_message_size: Maximum gRPC message size in bytes.

  • query_timeout and fetch_timeout: RPC timeout values in seconds.

  • with_cookie_middleware: Enable Flight SQL cookie middleware (boolean).

  • headers: Dictionary of custom RPC call headers (mapped to adbc.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

create_savepoint(name)

rollback_to_savepoint(name)

release_savepoint(name)

PostgreSQL, SQLite, MySQL

SAVEPOINT <name>

ROLLBACK TO SAVEPOINT <name>

RELEASE SAVEPOINT <name>

Oracle

SAVEPOINT <name>

ROLLBACK TO SAVEPOINT <name>

No-op (Oracle does not support RELEASE SAVEPOINT)

IBM Db2

SAVEPOINT <name> ON ROLLBACK RETAIN CURSORS

ROLLBACK TO SAVEPOINT <name>

RELEASE SAVEPOINT <name>

DuckDB, BigQuery, Snowflake

Raises NotImplementedError

Raises NotImplementedError

Raises NotImplementedError

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 UUID columns as Arrow opaque binary extension types. SQLSpec automatically decodes scalar UUID and UUID[] values to uuid.UUID on row APIs (select, select_one, select_stream) and adds explicit ::uuid / ::uuid[] casts when binding Python uuid.UUID parameters. Set driver_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_extension once and upgrades the dialect to pgvector (when vector is installed), pg_textsearch (when pg_textsearch is installed), or paradedb (when pg_search is installed). Control detection with enable_pgvector, enable_pg_textsearch, and enable_paradedb in driver_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, AdbcDataDictionary falls 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 native JSON on Oracle 21c+, BLOB on Oracle 12c–19c, and CLOB on older versions.

  • get_statistics wraps adbc_get_statistics and maps standard ADBC statistic keys (0–6) to canonical adbc.statistic.* names; drivers without statistics support raise OperationalError.

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: ADBCStore

  • Durable Event Queue: AdbcEventQueueStore

  • Google ADK Sessions & Events: AdbcADKStore

  • Google 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:
property supports_migration_schemas: bool#

Migration schema support is only available for PostgreSQL-backed ADBC drivers.

property pg_textsearch_available: bool#

Return True if the pg_textsearch extension is available.

create_connection()[source]#

Create and return a new connection using the specified driver.

Return type:

Connection

Returns:

A new connection instance, or connection_instance when configured.

Raises:

ImproperConfigurationError -- If the connection could not be established.

provide_connection(*args, **kwargs)[source]#

Provide a connection context manager.

Parameters:
  • *args (Any) -- Additional arguments.

  • **kwargs (Any) -- Additional keyword arguments.

Return type:

AdbcConnectionContext

Returns:

A connection context manager.

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.

Parameters:
  • *_args (Any) -- Additional arguments.

  • statement_config (StatementConfig | None) -- Optional statement configuration override.

  • **_kwargs (Any) -- Additional keyword arguments.

Return type:

AdbcSessionContext

Returns:

A context manager that yields an AdbcDriver instance.

get_signature_namespace()[source]#

Get the signature namespace for AdbcConfig types.

Return type:

dict[str, typing.Any]

Returns:

Dictionary mapping type names to types.

get_event_runtime_hints()[source]#

Return polling defaults suitable for ADBC warehouses.

Return type:

EventRuntimeHints

Connection Parameters#

class sqlspec.adapters.adbc.AdbcConnectionParams[source]#

Bases: TypedDict

ADBC connection parameters.

Driver Features#

class sqlspec.adapters.adbc.AdbcDriverFeatures[source]#

Bases: TypedDict

ADBC 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_extension on the first connection to check for the vector extension. Defaults to True when the pgvector Python package is installed.

enable_paradedb#

Enable ParadeDB (pg_search) extension detection. When True and the resolved dialect is PostgreSQL, queries pg_extension on the first connection to check for the pg_search extension. 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_extension on the first connection to check for the pg_textsearch extension. 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: SyncDriverAdapterBase

ADBC 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 instance

  • statement_config (StatementConfig | None) -- Statement configuration for the driver

  • driver_features (dict[str, typing.Any] | None) -- Driver-specific features like extensions, secrets, and connection callbacks

  • observability -- 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:
  • cursor (Cursor) -- Database cursor

  • statement (SQL) -- SQL statement to execute

Return type:

ExecutionResult

Returns:

Execution result with data or row count

dispatch_execute_many(cursor, statement)[source]#

Execute SQL with multiple parameter sets.

Parameters:
  • cursor (Cursor) -- Database cursor

  • statement (SQL) -- SQL statement to execute

Return type:

ExecutionResult

Returns:

Execution result with row counts

dispatch_execute_script(cursor, statement)[source]#

Execute SQL script containing multiple statements.

Parameters:
  • cursor (Cursor) -- Database cursor

  • statement (SQL) -- SQL script to execute

Return type:

ExecutionResult

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:

None

commit()[source]#

Commit database transaction.

Return type:

None

rollback()[source]#

Rollback database transaction.

Return type:

None

create_savepoint(name)[source]#

Create a savepoint within the current transaction.

Raises:

NotImplementedError -- If the connected database does not support savepoints.

Return type:

None

release_savepoint(name)[source]#

Release a previously created savepoint.

Raises:

NotImplementedError -- If the connected database does not support savepoints.

Return type:

None

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:

None

set_migration_session_schema(schema)[source]#

Set the PostgreSQL search path for migration SQL when using ADBC PostgreSQL.

Return type:

None

set_migration_non_transactional_schema(schema)[source]#

Set the PostgreSQL search path for non-transactional migration SQL.

Return type:

None

reset_migration_session_schema()[source]#

Reset PostgreSQL search path after non-transactional migration SQL.

Return type:

None

has_schema(schema)[source]#

Return whether a PostgreSQL schema exists when using ADBC PostgreSQL.

Return type:

bool

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:

StorageBridgeJob

load_from_storage(table, source, *, file_format, partitioner=None, overwrite=False)[source]#

Read an artifact from storage and ingest it via ADBC.

Return type:

StorageBridgeJob

collect_rows(cursor, fetched)[source]#

Collect ADBC rows for the direct execution path.

Return type:

tuple[list[typing.Any], list[str], int]

resolve_rowcount(cursor)[source]#

Resolve rowcount from ADBC cursor for the direct execution path.

Return type:

int

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:
  • parameters (Any) -- Parameters in any format

  • statement_config (StatementConfig) -- Statement configuration

  • is_many (bool) -- Whether this is for execute_many operation

  • prepared_statement (Any | None) -- Prepared statement containing the original SQL statement

Return type:

Any

Returns:

Parameters with cast-aware type coercion applied

Data Dictionary#

class sqlspec.adapters.adbc.data_dictionary.AdbcDataDictionary[source]#

Bases: SyncDataDictionaryBase

ADBC multi-dialect data dictionary.

dialect: ClassVar[str] = 'generic'#

Dialect identifier. Must be defined by subclasses as a class attribute.

__init__()[source]#
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:

VersionInfo | None

get_feature_flag(driver, feature)[source]#

Check if database supports a specific feature.

Return type:

bool

get_optimal_type(driver, type_category)[source]#

Get optimal database type for a category.

Return type:

str

list_available_features()[source]#

List all features that can be checked via get_feature_flag.

Return type:

list[str]

Returns:

List of feature names this data dictionary supports

get_metadata_capabilities(driver, domains=None)[source]#

Report ADBC transport metadata capabilities without implying DDL fidelity.

Return type:

MetadataCapabilityProfile

get_tables(driver, schema=None)[source]#

Get tables for the current dialect.

Return type:

list[TableMetadata]

get_columns(driver, table=None, schema=None)[source]#

Get column information for a table or schema.

Return type:

list[ColumnMetadata]

get_indexes(driver, table=None, schema=None)[source]#

Get index information for a table or schema.

Return type:

list[IndexMetadata]

get_foreign_keys(driver, table=None, schema=None)[source]#

Get foreign key metadata.

Return type:

list[ForeignKeyMetadata]

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:

MetadataResult

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

get_system_metadata_capabilities(driver, domains=None)[source]#

Get ADBC system metadata capability disclosures.

Return type:

tuple[SystemMetadataCapability, ...]

get_system_metadata(driver, request=None, **kwargs)[source]#

Get ADBC transport metadata through the opt-in system namespace.

Return type:

SystemMetadataResult