DuckDB¶
sqlspec.adapters.duckdb wraps the in-process analytical DuckDB engine (duckdb + pyarrow), combining thread-local connection pooling, declarative extension and cloud secret management, hybrid native/Arrow row materialization, zero-copy Arrow transfers, and per-statement JSON execution profiling.
Overview & Capabilities¶
Choose DuckDBConfig for embedded OLAP analytics, local lakehouse queries over Parquet/CSV/JSON on S3 or GCS, fast feature engineering pipelines, and columnar ETL jobs running inside a single Python worker.
| Capability | Details |
|---|---|
| Adapter Config | DuckDBConfig (sqlspec.adapters.duckdb) |
| Driver Extra | sqlspec[duckdb] (duckdb, pyarrow) |
| Execution Mode | Synchronous |
| Pool Architecture | DuckDBConnectionPool (thread-local cursor/connection manager with pool_recycle_seconds, health_check_interval, per-config :memory:sqlspec_<uuid> isolation and private spill cleanup) |
| Extensions & Storage | Declarative DuckDBExtensionConfig, DuckDBSecretConfig, zero-copy Arrow select_to_arrow() / load_from_arrow(), ADK FTS + cosine similarity |
Installation & Quickstart¶
from sqlspec import SQLSpec
from sqlspec.adapters.duckdb import DuckDBConfig
spec = SQLSpec()
config = spec.add_config(
DuckDBConfig(
connection_config={
"database": ":memory:",
"memory_limit": "4GB",
"threads": 4,
"preserve_insertion_order": True,
"pool_recycle_seconds": 86400,
"health_check_interval": 60,
},
driver_features={
"extensions": [{"name": "httpfs"}, {"name": "json"}],
"enable_statement_profiling": False,
},
)
)
For persistent analytical databases, set "database": "var/analytics.duckdb" (and "read_only": True on read-only replica processes).
Connection & Pool Configuration¶
Connection and Pool Parameters¶
[!NOTE]
DuckDBConnectionPoolis a thread-local connection manager configured viapool_recycle_secondsandhealth_check_intervalonDuckDBPoolParams. Whendatabaseis omitted, empty, or set to":memory:",DuckDBConfigassigns an isolated:memory:sqlspec_<uuid>identifier per config instance so pooled checkouts share one catalog within that config without leaking state across separate configs; explicit:memory:<name>identifiers remain shared across configs using the same name. When an in-memoryDuckDBConfigomitstemp_directory,DuckDBConfigallocates a private temporary spill directory and removes it inclose_pool(). In Linux containers,DuckDBConfigalso clamps default memory limits to cgroup v1/v2 container quotas automatically.
Pass DuckDBConnectionParams and DuckDBPoolParams keys inside connection_config={...} on DuckDBConfig:
| Key | Type | Default | Description |
|---|---|---|---|
database |
str |
- | Database file path, ':memory:' / ':memory:<name>' memory identifier, or MotherDuck URI. |
read_only |
bool |
- | Open the database file in read-only mode without taking a write lock. |
config |
dict[str, Any] |
- | Raw DuckDB engine configuration dictionary merged with modeled settings. |
memory_limit |
str |
- | Maximum buffer-manager memory ceiling (for example '4GB'). |
memory_ceiling_fraction |
float |
0.8 |
Fraction of container or host memory used as an upper ceiling when memory_limit is unset or exceeds available memory (defaults to 0.8). |
threads |
int |
- | Number of DuckDB execution worker threads. |
temp_directory |
str |
- | Directory path used when spilling out-of-core intermediates to disk. |
reject_tmpfs_temp_directory |
bool |
False |
Raise ImproperConfigurationError when temp_directory resides on an in-memory tmpfs or ramfs mount (defaults to False). |
max_temp_directory_size |
str |
- | Maximum disk space cap for temp_directory spill files (for example '20GB'). |
autoload_known_extensions |
bool |
- | Automatically load installed DuckDB extensions when referenced by SQL syntax. |
autoinstall_known_extensions |
bool |
- | Automatically download and install known DuckDB extensions on first reference. |
allow_community_extensions |
bool |
- | Permit installing extensions from the DuckDB Community Extensions repository. |
allow_unsigned_extensions |
bool |
- | Permit loading unsigned local or third-party DuckDB extension binaries. |
extension_directory |
str |
- | Custom filesystem directory where DuckDB stores downloaded extensions. |
custom_extension_repository |
str |
- | Custom HTTP or local repository URL searched when installing extensions. |
autoinstall_extension_repository |
str |
- | Repository URL used specifically for automatic on-demand extension installs. |
allow_persistent_secrets |
bool |
- | Allow reading and writing persistent secrets on disk. |
enable_external_access |
bool |
- | Allow SQL statements to access local files, HTTP URLs, and object stores. |
secret_directory |
str |
- | Filesystem directory storing persistent DuckDB secret files. |
enable_object_cache |
bool |
- | Cache parsed Parquet metadata and object-store footers in memory. |
parquet_metadata_cache |
bool |
- | Enable caching of Parquet file footers across repeated scans. |
enable_external_file_cache |
bool |
- | Cache remote HTTP and object-store byte ranges in DuckDB's buffer pool. |
checkpoint_threshold |
str |
- | WAL size threshold triggering an automatic database checkpoint (for example '16MB'). |
enable_progress_bar |
bool |
- | Display interactive terminal progress bars during long-running queries. |
progress_bar_time |
int |
- | Milliseconds elapsed before DuckDB displays the query progress bar. |
enable_logging |
bool |
- | Enable DuckDB internal query and engine logging. |
log_query_path |
str |
- | Filesystem path receiving logged SQL queries when logging is enabled. |
logging_level |
str |
- | Minimum severity level recorded by DuckDB's internal logger. |
preserve_insertion_order |
bool |
- | Preserve input row ordering during table scans and bulk operations. |
default_null_order |
str |
- | Default NULLS FIRST or NULLS LAST ordering when ORDER BY omits null placement. |
default_order |
str |
- | Default ASC or DESC sort direction when ORDER BY omits direction. |
ieee_floating_point_ops |
bool |
- | Follow IEEE-754 floating-point division-by-zero semantics instead of returning NULL. |
binary_as_string |
bool |
- | Interpret untyped Parquet binary columns as VARCHAR strings. |
arrow_large_buffer_size |
bool |
- | Emit 64-bit LargeUtf8 and LargeBinary Arrow buffers on Arrow exports. |
errors_as_json |
bool |
- | Format DuckDB exception payloads as structured JSON strings. |
extra |
dict[str, Any] |
- | Additional DuckDB configuration options merged into config. |
pool_recycle_seconds |
int |
86400 |
Maximum connection lifetime in seconds before recycling (defaults to 86400; 0 disables). |
health_check_interval |
float |
- | Idle seconds between lightweight connection health checks. |
Driver Features¶
Pass DuckDBDriverFeatures keys inside driver_features={...} on DuckDBConfig:
| Key | Type | Default | Description |
|---|---|---|---|
extensions |
Sequence[DuckDBExtensionConfig] |
- | Extensions installed and loaded automatically when each pooled connection opens. |
secrets |
Sequence[DuckDBSecretConfig] |
- | Secrets provisioned on new connections for object stores and external catalogs. |
on_connection_create |
Callable[[DuckDBConnection], DuckDBConnection or None] |
- | Callback invoked when a new DuckDBPyConnection is opened. |
reset_session_on_release |
bool |
False |
Drop temporary objects and detach added databases when a connection is released (defaults to False). |
json_serializer |
Callable[[Any], str] |
sqlspec.utils.serializers.to_json |
Serializer converting dict/list parameters to JSON text (defaults to sqlspec.utils.serializers.to_json). |
enable_uuid_conversion |
bool |
True |
Convert UUID parameters to strings on bind while returning UUID objects from UUID columns (defaults to True). |
extension_flags |
dict[str, Any] |
- | Startup-only extension security flags merged into connection_config before duckdb.connect. |
enable_events |
bool |
- | Enable SQLSpec table-backed event channel support. |
events_backend |
Literal['poll_queue'] |
- | Event queue backend name ('poll_queue'). |
enable_statement_profiling |
bool |
False |
Capture per-statement DuckDB JSON operator profiles on StatementEvent.profile (defaults to False). |
Connection Exit¶
Uncommitted work rolls back when a pooled connection is returned, whether the context exits cleanly or with an exception, so call commit() before leaving the session. Statements outside an explicit transaction run in autocommit mode and are unaffected. Set driver_features["reset_session_on_release"] = True to also drop temporary objects and detach added databases on release. See Session Reset on Release.
Type Mappings & Dialect Notes¶
| Database Type | Python Type | Notes / Feature Flag |
|---|---|---|
INTEGER / BIGINT / HUGEINT / UHUGEINT |
int |
32-bit, 64-bit, and 128-bit integer conversion. |
DOUBLE / DECIMAL |
float / decimal.Decimal |
IEEE 754 floats and exact arbitrary-precision decimals. |
VARCHAR / BLOB / BIT |
str / bytes |
UTF-8 text, binary blobs, and bitstrings. |
JSON / STRUCT / MAP / LIST |
dict / list |
Nested analytical types and JSON values serialized via json_serializer / json_deserializer. |
TIMESTAMP / TIMESTAMPTZ / INTERVAL |
datetime.datetime / datetime.timedelta |
Nanosecond/microsecond timestamps and interval durations. |
UUID |
uuid.UUID / str |
Normalized consistently across native and Arrow execution paths when enable_uuid_conversion=True (default). |
- Hybrid Native/Arrow Materialization (
ARROW_MIN_ROWS = 64): Small point lookups (< 64observed rows) and queries returning Arrow-divergent types (HUGEINT,UHUGEINT,BIT,TIMETZ,TIMESTAMP_NS,INTERVAL,MAP,BIGNUM/VARINT, fixed-size arrays, or nestedUUID) materialize directly viacursor.fetchall()so cache-miss and cache-hit calls always return identical DuckDB-native Python types without paying Arrow conversion overhead on 1-row reads. Larger analytical scans route throughto_arrow_table()automatically. - Declarative Extensions and Cloud Secrets: Configure
extensions(DuckDBExtensionConfigdicts withname,repository,force_install) andsecrets(DuckDBSecretConfigdicts configuring S3, GCS, Azure, R2, or HuggingFace credentials viaCREATE SECRET, including dynamic credential rotation viavalue_providercallables). - Zero-Copy Arrow Reads and Writes: Call
session.select_to_arrow(...)(return_format="table","batches", or"reader") andsession.load_from_arrow(...)to exchange columnar tables with Polars, Pandas, or PyArrow without dictionary allocations. - ADK Full-Text and Cosine Vector Search:
DuckDBADKMemoryStoresupports DuckDB'sftsextension alongsideARRAYcosine similarity for local RAG and agent memory persistence.
Trusted Table Text¶
Builder table text must come from a trusted source. DuckDB resolves table names through replacement scans, so a
quoted name such as from_('"data/report.csv"') reads that file. Function text passed to lateral_join runs as
named, and DuckDB functions such as read_text() and query() read files or run SQL text.
To stop SQL from reaching local files, HTTP URLs, and object stores, disable external access when you configure the connection:
config = DuckDBConfig(
connection_config={"database": "app.duckdb", "enable_external_access": False}
)
File reads then raise PermissionDeniedError. This setting does not limit query(), which can still run SQL against
data inside the database, so lateral function text must stay trusted either way.