DuckDB

Sync DuckDB adapter with full Arrow integration, extension management, and secret configuration. DuckDB excels at analytical workloads and can query Parquet, CSV, and JSON files directly.

UUID parameters and results

driver_features={"enable_uuid_conversion": False} passes UUID parameters to DuckDB unchanged. The default converts these parameters to strings. This setting does not change result types: UUID columns return Python UUID objects, including on cache hits and in row streams. VARCHAR columns remain strings, even when their values look like UUIDs. Arrow results keep their native Arrow representation.

Native object-store transfers

select_to_storage() can write a remote Parquet or CSV object with DuckDB COPY. load_from_storage() can append a remote Parquet object with INSERT ... SELECT ... FROM read_parquet(). Each successful native transfer executes one statement on the driver's existing connection and avoids materializing the data through Python Arrow buffers.

Configure the required extension and secret through driver_features before opening a session. Native routing uses the settings successfully applied to that connection. A failed optional extension or secret does not enable the route. Pool recreation resets this record. Filesystems registered through on_connection_create are discovered after the callback runs and participate without requiring a cloud extension.

import os

from sqlspec.adapters.duckdb import DuckDBConfig

config = DuckDBConfig(
    driver_features={
        "extensions": [{"name": "httpfs", "install": True, "required": True}],
        "secrets": [{
            "name": "reports",
            "secret_type": "s3",
            "provider": "config",
            "required": True,
            "value": {
                "key_id": os.environ["AWS_ACCESS_KEY_ID"],
                "secret": os.environ["AWS_SECRET_ACCESS_KEY"],
                "region": "us-east-1",
            },
        }],
    },
)
try:
    with config.provide_session() as session:
        session.select_to_storage(
            "SELECT :day AS report_day",
            "s3://example-bucket/reports/daily.parquet",
            day="2026-01-01",
        )
        session.execute("CREATE TABLE reports (report_day VARCHAR)")
        session.load_from_storage(
            "reports", "s3://example-bucket/reports/daily.parquet",
            file_format="parquet",
        )
finally:
    config.close_pool()

Declared secrets are created only when missing. DuckDB secret names are shared by every connection to a database and matched case-insensitively, and persistent secrets outlive the process, so an existing secret is reused and declared values are not applied to it unless replace=True is set, for example after rotating credentials. DuckDB redacts credential values, so an existing secret is compared on its type, provider and the declared settings DuckDB does not redact, such as scope, key_id, region and endpoint. Settings the declaration omits are kept as stored and are not compared. A difference raises for required=True secrets and logs a warning otherwise.

Query values and object addresses remain bound parameters. Native import accepts one table identifier, optionally schema-qualified or quoted; SQL fragments are rejected. Parquet import disables automatic Hive partition columns. The API handles one object and does not expose reader projection or filter pushdown.

Providers and aliases

Native provider prerequisites

URI schemes	Extension	Secret settings
s3	httpfs	secret_type="s3" with static credentials or a configured credential chain.
gs, gcs	httpfs	secret_type="gcs" with HMAC key_id and secret. GCS OAuth and service-account backend options are not interchangeable with HMAC keys.
r2	httpfs	secret_type="r2" with key_id, secret and account_id or the matching R2 endpoint. R2 uses the S3 API through DuckDB's httpfs extension.
gcss	Community gcs	DuckDB's gcp secret configuration, including application default credentials. The original URI is passed to the extension unchanged.
az, azure, abfss	azure	secret_type="azure" with a configured connection_string or supported identity provider. Direct URI resolution also needs an account name in the successful secret configuration.
http, https	httpfs	Parquet reads only, with default backend options.

Existing alias://name/path destinations use the storage registry. Native routing requires a known backend whose credentials, endpoint, region and access options match the configured DuckDB secret. Unknown options and credential mismatches keep the Arrow route. Alias names are not provider names: an alias named gcs can still refer to S3. Custom storage backend classes and custom storage_pipeline_factory implementations retain their existing behavior.

Additional filesystems

DuckDB's Python client supports registered fsspec filesystems, including gcsfs. Register an instance using connection.register_filesystem() in on_connection_create. Its advertised protocols are discovered automatically; eligible transfers use DuckDB's reader or COPY without materializing an Arrow payload in SQLSpec. The filesystem itself can still perform Python I/O.

For additional DuckDB extensions, declare their URI schemes alongside the existing extension configuration, for example {"name": "my_filesystem", "repository": "community", "storage_protocols": ["myfs", "myfs+alias"]}. These schemes become available only after the extension loads successfully. SQLSpec passes direct addresses unchanged; the configured DuckDB filesystem owns authentication and reports transfer errors. No matching fsspec or obstore package is required for these direct routes. Aliases still resolve through SQLSpec's registry, and backend options that cannot be preserved keep the existing storage path.

See DuckDB's fsspec integration and the community GCS extension.

Fallbacks and failures

Local paths, unsupported formats, explicit Arrow conversion options such as arrow_schema, and options that cannot be represented faithfully use Arrow. Import with overwrite=True also uses the existing Arrow truncate-and-insert path. CSV imports always use Arrow: DuckDB's CSV inference differs for values such as leading-zero strings, quoted empty strings and null markers.

Names containing ? or # retain the storage backend's interpretation instead of becoming DuckDB URL settings. Parquet reader names containing glob characters also use the single-object Arrow path. Repeated native exports replace the same object; they do not add a new overwrite argument or produce partitioned files. The partitioner argument continues to attach telemetry metadata.

Routing is decided before the transfer statement. Once native execution starts, authentication, missing-object and database failures propagate through the adapter's exception mapping; they are never retried through Arrow.

Native jobs report row counts, duration, format and backend="duckdb". bytes_processed is omitted when DuckDB does not provide it; no extra object request is made solely to fill that field.

Verification and performance

S3-compatible CSV/Parquet export and Parquet append are tested against local RustFS. Configured GCS, R2 and Azure routing is tested offline; transfers against those cloud services are not verified by the local tests. Extension installation may require access to DuckDB's official extension repository.

tools/scripts/bench_duckdb_storage.py compares native and Arrow Parquet export/import against the same local service. It verifies equal values and records object sizes, median and spread, connection/extension setup time, versions and source revision. Run the opt-in fixture-backed benchmark with:

SQLSPEC_DUCKDB_STORAGE_BENCHMARK=/tmp/duckdb-storage.json uv run pytest \
  tests/integration/adapters/duckdb/duckdb/test_native_storage.py \
  -k native_storage_benchmark

The benchmark uses 100, 1,000 and 10,000 rows with four warmups and eight measured iterations. Results depend on object size, network and service configuration; native transfer is not a universal speedup. CSV import is excluded from native benchmarks because it retains Arrow inference.

Configuration

class sqlspec.adapters.duckdb.DuckDBConfig

Bases: SyncDatabaseConfig[DuckDBPyConnection, DuckDBConnectionPool, DuckDBDriver]

DuckDB configuration with connection pooling.

Supports extensions, secrets, Arrow transfers, and direct file queries. Connections recycle after 24 hours by default; set recycling to 0 to disable. Shared memory databases use :memory:shared_db.

Set driver_features["json_serializer"] to a serializer that returns text for dict/list parameters. The default is sqlspec.utils.serializers.to_json.

driver_features["enable_uuid_conversion"] converts UUID parameters to strings by default. Disable it to pass UUID parameters through unchanged. UUID result columns always return UUID objects; VARCHAR columns remain strings.

driver_type

alias of DuckDBDriver

__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)

Initialize DuckDB configuration.

Parameters:

• connection_config (DuckDBPoolParams | dict[str, typing.Any] | None) -- Connection and pool configuration parameters

• connection_instance (DuckDBConnectionPool | None) -- Pre-created pool instance

• migration_config (dict[str, typing.Any] | None) -- Migration configuration

• statement_config (StatementConfig | None) -- Statement configuration override

• driver_features (DuckDBDriverFeatures | dict[str, typing.Any] | None) -- DuckDB-specific driver features including json_serializer and enable_uuid_conversion options

• bind_key (str | None) -- Optional unique identifier for this configuration

• extension_config (dict[str, dict[str, Any] | LitestarConfig | FastAPIConfig | StarletteConfig | SanicConfig | FlaskConfig | ADKConfig | EventsConfig | OpenTelemetryConfig | PrometheusConfig] | None) -- Extension-specific configuration

• observability_config (ObservabilityConfig | None) -- Adapter-level observability overrides for lifecycle hooks and observers

• **kwargs (Any) -- Additional keyword arguments passed to the base configuration.

create_connection()

Open a standalone connection owned by the caller.

The connection carries the same settings, extensions, and secrets the pool applies, but it is not the pool's thread-local connection, so closing it leaves the pool usable.

Returns:

A newly opened connection.

Return type:

DuckDBConnection

get_signature_namespace()

Get the signature namespace for DuckDB types.

This provides all DuckDB-specific types that Litestar needs to recognize to avoid serialization attempts.

Return type:

dict[str, typing.Any]

Returns:

Dictionary mapping type names to types.

get_event_runtime_hints()

Return polling defaults optimized for DuckDB.

Return type:

EventRuntimeHints

Connection Parameters

class sqlspec.adapters.duckdb.DuckDBConnectionParams

Bases: TypedDict

DuckDB connection parameters.

Mirrors the keyword arguments accepted by duckdb.connect so callers can drive every DuckDB configuration switch directly through SQLSpec. All keys are optional and forwarded verbatim to DuckDB, either as top-level parameters or via the nested config dictionary when DuckDB expects them there.

Pool Parameters

class sqlspec.adapters.duckdb.DuckDBPoolParams

Bases: DuckDBConnectionParams

Complete pool configuration for DuckDB adapter.

Extends DuckDBConnectionParams with lifecycle settings consumed by SQLSpec's thread-local DuckDB connection manager.

Driver Features

extension_flags values are folded into the database startup configuration. DuckDB rejects these settings once the database is running, so an unrecognized flag fails at connect time rather than being ignored.

class sqlspec.adapters.duckdb.DuckDBDriverFeatures

Bases: TypedDict

TypedDict for DuckDB driver features configuration.

extensions

Extensions to install/load when connecting.

secrets

Secrets to create for external services.

on_connection_create

Callback run when connecting.

json_serializer

Serializer for dict/list parameters. Defaults to sqlspec.utils.serializers.to_json.

enable_uuid_conversion

Convert UUID parameters to strings (default True). False passes parameters unchanged. UUID result columns always return UUID objects; VARCHAR columns remain strings.

extension_flags

Startup-only flags; DuckDB rejects changes after startup.

enable_events

Enable table-backed event queues. Defaults to True when extension_config["events"] is set; requires it for migrations.

events_backend

Only "poll_queue" is supported, with lease-based retries and acknowledgements. DuckDB has no native pub/sub.

enable_statement_profiling

Enable per-statement JSON operator tree profiling attached to StatementEvent.profile (default False).

Extensions and Secrets

class sqlspec.adapters.duckdb.DuckDBExtensionConfig

Bases: TypedDict

DuckDB extension configuration for auto-management.

name: str

Name of the extension to install/load.

version: NotRequired[str]

Specific version of the extension.

repository: NotRequired[str]

Repository for the extension (core, community, or custom URL).

repository_url: NotRequired[str]

Custom repository URL for the extension.

force_install: NotRequired[bool]

Force reinstallation of the extension.

install: NotRequired[bool]

Force an explicit install_extension() call even for a name-only config.

required: NotRequired[bool]

When True, install/load failure raises instead of best-effort WARNING.

storage_protocols: NotRequired[Sequence[str]]

Additional URI schemes handled by this extension's configured filesystem.

Addresses using these schemes are passed unchanged to DuckDB after the extension loads. DuckDB owns credential selection and reports transfer errors. Registered Python filesystems are discovered automatically.

class sqlspec.adapters.duckdb.DuckDBSecretConfig

Bases: TypedDict

DuckDB secret configuration for AI/API integrations.

secret_type: str

Type of secret.

name: str

Name of the secret. DuckDB matches secret names case-insensitively.

value: NotRequired[dict[str, Any]]

Secret configuration values, applied only when the secret is created or replace is set.

provider: NotRequired[str]

Secret provider, such as config or credential_chain.

scope: NotRequired[str]

Optional path or storage-prefix scope for the secret.

persistent: NotRequired[bool]

Persist the secret to DuckDB's configured secret directory.

replace: NotRequired[bool]

When True, overwrite an existing secret of the same name, such as after rotating credentials. Default False.

required: NotRequired[bool]

When True, raise instead of skipping the secret with a warning. Default False.

Failures are a creation error, a secret that is not visible after creation, and an existing secret whose type, provider, declared scope or unredacted declared settings such as key_id, region or endpoint differ from the declaration.

Driver

class sqlspec.adapters.duckdb.DuckDBDriver

Bases: SyncDriverAdapterBase

Synchronous DuckDB database driver.

Provides SQL statement execution, transaction management, and result handling for DuckDB databases. Supports multiple parameter styles including QMARK, NUMERIC, and NAMED_DOLLAR formats.

The driver handles script execution, batch operations, and integrates with the sqlspec.core modules for statement processing and caching.

__init__(connection, statement_config=None, driver_features=None)

Initialize driver adapter with connection and configuration.

Parameters:

• connection (DuckDBPyConnection) -- 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: DuckDBDataDictionary

Get the data dictionary for this driver.

Returns:

Data dictionary instance for metadata queries

dispatch_execute(cursor, statement)

Execute single SQL statement with data handling.

Executes a SQL statement with parameter binding and processes the results. Handles both data-returning queries and data modification operations.

Parameters:

• cursor (DuckDBPyConnection) -- DuckDB cursor object

• statement (SQL) -- SQL statement to execute

Return type:

ExecutionResult

Returns:

ExecutionResult with execution metadata

dispatch_execute_many(cursor, statement)

Execute SQL with multiple parameter sets using batch processing.

Uses DuckDB's Arrow bulk insert fast path for simple INSERT batches, falling back to executemany and calculating row counts for both data modification and query operations.

Parameters:

• cursor (DuckDBPyConnection) -- DuckDB cursor object

• statement (SQL) -- SQL statement with multiple parameter sets

Return type:

ExecutionResult

Returns:

ExecutionResult with batch execution metadata

build_statement_result(statement, execution_result)

Build statement result, returning DMLResult when produced by bulk insert.

Return type:

SQLResult

dispatch_execute_script(cursor, statement)

Execute SQL script with statement splitting and parameter handling.

Parses multi-statement scripts and executes each statement sequentially with the provided parameters.

Parameters:

• cursor (DuckDBPyConnection) -- DuckDB cursor object

• statement (SQL) -- SQL statement with script content

Return type:

ExecutionResult

Returns:

ExecutionResult with script execution metadata

dispatch_select_stream(statement, chunk_size)

Return a native DuckDB row stream backed by Arrow record batches.

Return type:

Optional[SyncRowStream[dict[str, typing.Any]]]

begin()

Begin a database transaction.

Return type:

None

commit()

Commit the current transaction.

Return type:

None

rollback()

Rollback the current transaction.

Return type:

None

create_savepoint(name)

Raise because DuckDB does not support savepoints.

Raises:

NotImplementedError -- Always.

Return type:

None

release_savepoint(name)

Raise because DuckDB does not support savepoints.

Raises:

NotImplementedError -- Always.

Return type:

None

rollback_to_savepoint(name)

Raise because DuckDB does not support savepoints.

Raises:

NotImplementedError -- Always.

Return type:

None

set_migration_session_schema(schema)

Set DuckDB search_path for migration SQL.

Return type:

None

has_schema(schema)

Return whether a DuckDB schema exists.

Return type:

bool

with_cursor(connection)

Create context manager for DuckDB cursor.

Parameters:

connection (DuckDBPyConnection) -- DuckDB connection instance

Return type:

DuckDBCursor

Returns:

DuckDBCursor context manager instance

handle_database_exceptions()

Handle database-specific exceptions and wrap them appropriately.

Return type:

DuckDBExceptionHandler

Returns:

Exception handler with deferred exception pattern for mypyc compatibility.

select_to_arrow(statement, /, *parameters, statement_config=None, return_format='table', native_only=False, batch_size=None, arrow_schema=None, **kwargs)

Execute query and return results as Apache Arrow (DuckDB native path).

DuckDB provides native Arrow support via cursor.arrow(). This is the fastest path due to DuckDB's columnar architecture.

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 DuckDB (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)

Persist a query with native object-store COPY when settings are compatible.

load_from_arrow(table, source, *, partitioner=None, overwrite=False, telemetry=None)

Load Arrow data into DuckDB using temporary table registration.

Return type:

StorageBridgeJob

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

Read an artifact from storage and load it into DuckDB.

Return type:

StorageBridgeJob

collect_rows(cursor, fetched)

Collect DuckDB rows for the direct execution path.

Return type:

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

resolve_rowcount(cursor)

Resolve rowcount from DuckDB cursor for the direct execution path.

Return type:

int

Connection Pool

final class sqlspec.adapters.duckdb.DuckDBConnectionPool

Bases: object

Thread-local connection manager for DuckDB.

Uses thread-local storage to ensure each thread gets its own DuckDB connection, preventing the thread-safety issues that cause segmentation faults when multiple cursors share the same connection concurrently.

This design trades traditional pooling for thread safety, which is essential for DuckDB since connections and cursors are not thread-safe.

__init__(connection_config, pool_recycle_seconds=86400, health_check_interval=30.0, extensions=None, secrets=None, on_connection_create=None, enable_statement_profiling=False)

Initialize the thread-local connection manager.

Parameters:

• connection_config -- DuckDB connection configuration

• pool_recycle_seconds -- Connection recycle time in seconds

• health_check_interval -- Seconds of idle time before running health check

• extensions -- List of extensions to install/load

• secrets -- List of secrets to create

• on_connection_create -- Callback executed when connection is created

• enable_statement_profiling -- Whether to enable per-connection JSON statement profiling

new_connection(record_thread_state=False)

Open a standalone connection with extensions and secrets applied.

Unless the pool asks it to, the result is owned by the caller: it is not thread-local, is not tracked for pool shutdown, and leaves the storage setup already published for this thread untouched.

Parameters:

record_thread_state (bool) -- Whether to publish storage setup for this thread.

Returns:

A newly opened, fully configured connection.

Return type:

DuckDBConnection

get_connection()

Get a thread-local connection.

Each thread gets its own dedicated DuckDB connection to prevent thread-safety issues with concurrent cursor operations.

Re-entrant checkouts on the same thread reuse the active connection without committing, rolling back, or closing on inner exits.

Yields:

DuckDBConnection -- A thread-local connection.

Return type:

Generator[DuckDBPyConnection, None, None]

close()

Close every connection this pool opened, on any thread.

Return type:

None

size()

Get current pool size (always 1 for thread-local).

Return type:

int

checked_out()

Get number of checked out connections (always 0 for thread-local).

Return type:

int

acquire()

Acquire a thread-local connection.

Each thread gets its own dedicated DuckDB connection to prevent thread-safety issues with concurrent cursor operations.

Returns:

A thread-local connection

Return type:

DuckDBConnection

release(connection)

Release a connection (no-op for thread-local connections).

Parameters:

connection (DuckDBPyConnection) -- The connection to release (ignored)

Return type:

None

Data Dictionary

class sqlspec.adapters.duckdb.data_dictionary.DuckDBDataDictionary

Bases: SyncDataDictionaryBase

DuckDB-specific sync data dictionary.

dialect: ClassVar[str] = 'duckdb'

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

__init__()

get_version(driver)

Get DuckDB database version information.

Parameters:

driver (DuckDBDriver) -- DuckDB driver instance.

Return type:

VersionInfo | None

Returns:

DuckDB version information or None if detection fails.

get_feature_flag(driver, feature)

Check if DuckDB database supports a specific feature.

Parameters:

• driver (DuckDBDriver) -- DuckDB driver instance.

• feature (str) -- Feature name to check.

Return type:

bool

Returns:

True if feature is supported, False otherwise.

get_optimal_type(driver, type_category)

Get optimal DuckDB type for a category.

Parameters:

• driver (DuckDBDriver) -- DuckDB driver instance.

• type_category (str) -- Type category.

Return type:

str

Returns:

DuckDB-specific type name.

get_tables(driver, schema=None)

Get tables sorted by topological dependency order using DuckDB catalog.

Return type:

list[TableMetadata]

get_columns(driver, table=None, schema=None)

Get column information for a table or schema.

Return type:

list[ColumnMetadata]

get_indexes(driver, table=None, schema=None)

Get index metadata for a table or schema.

Return type:

list[IndexMetadata]

get_foreign_keys(driver, table=None, schema=None)

Get foreign key metadata.

Return type:

list[ForeignKeyMetadata]

get_dependencies(driver, object_name=None, schema=None)

Get DuckDB object dependency metadata from duckdb_dependencies().

Return type:

MetadataResult

Extension Settings

Use these types inside extension_config["adk"].

class sqlspec.adapters.duckdb.adk.DuckdbADKConfig

Bases: ADKConfig

DuckDB-specific ADK extension settings.

Use these keys inside extension_config["adk"] with the DuckDB ADK stores. Shared ADK table and service settings are inherited from ADKConfig.

enable_event_generated_columns: NotRequired[bool]

Create DuckDB virtual generated columns for common ADK event JSON paths.

enable_event_generated_column_indexes: NotRequired[bool]

Index generated event projection columns when generated columns are enabled.

memory_fts_options: NotRequired[DuckdbADKFTSOptions]

DuckDB FTS PRAGMA create_fts_index options for ADK memory search.

class sqlspec.adapters.duckdb.adk.DuckdbADKFTSOptions

Bases: TypedDict

DuckDB FTS PRAGMA create_fts_index options for ADK memory search.

stemmer: NotRequired[str]

Stemmer name. Defaults to "porter".

stopwords: NotRequired[str]

Stopword table/name. Defaults to "english".

ignore: NotRequired[str]

Regular expression ignored by DuckDB tokenization.

strip_accents: NotRequired[bool | int]

Whether DuckDB strips accents during tokenization. Defaults to 1.

lower: NotRequired[bool | int]

Whether DuckDB lowercases text during tokenization. Defaults to 1.

overwrite: NotRequired[bool | int]

Whether initial FTS index creation overwrites an existing index. Defaults to 0.