ADBC Scanner

API version and migration

These examples target the attached-database API on the v1.5 branch, introduced in 7e5210c. An older community package may still expose the previous connection-handle API. Installing from the community repository alone does not establish the source revision.

Every adbc_* function now takes the alias of an ATTACH … (TYPE adbc) database instead of a BIGINT connection handle. adbc_connect, adbc_disconnect, adbc_commit, adbc_rollback, and adbc_set_autocommit are removed:

An unknown alias, or one naming a non-ADBC database, fails at bind with the alias in the message. Aliases resolve case-insensitively, like any catalog name. Scans, inserts, and metadata calls resolve the alias again at execution, so a prepared statement fails after DETACH and follows whatever transaction is current when it runs.

Runtime commands

adbc_execute and adbc_clear_cache are table functions run with a standalone CALL:

Commands run during execution. Binding, PREPARE, and ordinary EXPLAIN do not perform them; EXPLAIN ANALYZE executes the statement. Each prepared execution runs the command again. Keep commands as standalone statements: filters and joins can skip execution when commands are embedded in relational queries. This is not an exactly-once guarantee across network retries.

Transactions and connections

ATTACH 'users.sqlite' AS db (TYPE adbc, driver 'sqlite');
CALL adbc_execute('db', 'CREATE TABLE IF NOT EXISTS users(id INTEGER, name TEXT)');
BEGIN;
CALL adbc_execute('db', 'INSERT INTO users VALUES (1, ''Alice'')');
COMMIT;
DETACH db;

Inside an explicit BEGIN … COMMIT, adbc_execute and adbc_insert use the attachment’s write connection with autocommit disabled, so they commit or roll back together with writes made through the catalog (INSERT INTO db.…). Once the transaction has written, reads (adbc_scan, adbc_scan_table, the metadata functions) see its uncommitted writes. A driver that cannot disable autocommit fails the first write in the transaction rather than silently autocommitting. These transactions control the remote ADBC connection; local DuckDB writes and remote writes are not one distributed transaction.

Outside an explicit transaction, the adbc_* functions use the attachment’s own connection in autocommit, so session state such as a temporary table created by adbc_insert or a SET run by adbc_execute is visible to later calls. Writes to an attachment made with READ_ONLY are rejected, in or out of a transaction.

The write connection is separate from the attachment’s own, so a SQLite :memory: attachment gives a transaction an empty database of its own; use a file.

Only one operation may use an ADBC connection at a time, and overlap fails promptly rather than waiting on a lock. A query that needs two simultaneous adbc_* scans of one database, such as a self-join, should attach it twice. Scans of attached tables (db.schema.table) lease their own pooled connections and are not limited this way.

Result schemas without executing SQL during planning

adbc_scan asks the driver for AdbcStatementExecuteSchema metadata. It never falls back to executing the query merely to discover its columns. For drivers without that method, including the SQLite ADBC driver, supply columns:

ATTACH ':memory:' AS db (TYPE adbc, driver 'sqlite');
SELECT * FROM adbc_scan(
  'db',
  'SELECT 1 AS id, ''Alice'' AS name',
  columns := {'id': 'BIGINT', 'name': 'VARCHAR'}
);
DETACH db;

Declare the complete result in its remote column order, even when an outer DuckDB query selects only a subset. SQLite integer results map to BIGINT. The extension validates the runtime column count and checks types before converting the first nonempty batch.

adbc_scan_table obtains table metadata through AdbcConnectionGetTableSchema, then executes a generated SELECT with supported projection and filter pushdown. It also accepts a columns override. adbc_schema('db', table_name) describes a table, not an arbitrary SQL query. Metadata discovery can contact the backend during planning.

Remote tables expose no rowid column. A query that needs no columns, such as count(*), reads the first real column instead.

Read-only catalogs and shared databases

Use ATTACH ... (TYPE adbc, READ_ONLY, ...) for read-only analytics. Schemas and tables appear under the attachment name. See the cookbook for a file-backed SQLite example.

READ_ONLY rejects writes through that attachment, including adbc_execute and adbc_insert. It does not revoke the underlying database user’s write permissions. Catalog write support without READ_ONLY is driver-dependent; CALL adbc_execute runs any remote SQL.

Each SQLite :memory: connection is its own database, and catalog scans use pooled connections separate from the attachment’s own. Use a file when catalog queries (db.main.users) need to see tables created with adbc_execute.

Fresh catalog queries can see another client’s committed changes according to the backend’s transaction isolation. Data changes do not require reattaching or a cache clear. After remote DDL changes tables or columns, run CALL adbc_clear_cache() in each DuckDB client whose catalog metadata needs refreshing. This clears schema/table metadata for attached ADBC catalogs; it does not reload driver libraries or flush connection pools.

DuckDB treats an http:// or https:// ATTACH path as a remote file and asks for the httpfs extension. Pass such URIs (Trino’s, for example) as the uri option instead: ATTACH '' AS tr (TYPE adbc, driver 'trino', uri 'http://host:8080').

Driver options and secrets

Pass driver options as ATTACH options. VARCHAR and BOOLEAN values use the string option setter, integers the int64 setter, FLOAT/DOUBLE the double setter, and BLOBs the bytes setter. Nested option structures are rejected. Use numeric SQL values for options that require numeric setters, rather than quoted numbers. ATTACH option names are lowercased, so a driver option whose name needs uppercase letters has to come from a secret’s EXTRA_OPTIONS or a connection profile.

Use an ADBC secret to reuse connection details. SCOPE may be omitted when the secret has a non-empty URI; the URI then becomes its lookup scope, so ATTACH '<that uri>' AS db (TYPE adbc) finds it. An explicit SCOPE can match a different or broader prefix, and a secret with neither is rejected. Its EXTRA_OPTIONS map contains string values; supply options requiring numeric or binary setters directly as ATTACH options. Passwords and values from EXTRA_OPTIONS are redacted in DuckDB’s secret display. Other fields, including uri, are not, so avoid embedding credentials there. Secret display redaction does not redact SQL text containing credentials from application logs or query history.

adbc_insert accepts driver-specific statement options through options := {...} (a STRUCT or MAP), applied after the target table and mode. Keys adbc_insert sets itself are rejected; unknown keys surface the driver’s error.

Drivers load as native shared libraries into DuckDB. Install a driver manifest to resolve names such as sqlite, or provide the library path and, when needed, its entrypoint. Installing a Python driver package alone does not guarantee that a manifest is available to DuckDB. Load only libraries you trust.

Arrow streaming and performance

ADBC presents results as Arrow batches, which the extension pulls into DuckDB. The driver’s underlying network protocol and conversion costs depend on the backend; ADBC does not promise an Arrow wire protocol or zero serialization end to end.

Arrow type support also varies by implementation. Our DuckDB VARIANT and Arrow compatibility article follows one such type across C++, Python, Go, Rust, and Java.

Catalog scans and adbc_scan_table can push supported projections and filters into generated SQL. For arbitrary adbc_scan queries, put remote filtering in the SQL you send. Batch-size hints and early stream termination depend on driver support. Use a result-returning scan, with the appropriate schema, when inspecting a remote EXPLAIN result; adbc_execute returns an affected-row count rather than query rows.

On the DuckDB 2.0 development branch, whole-query pushdown can go beyond individual scans. The PostgreSQL pushdown benchmark explains how supported joins and aggregates become one remote SQL statement, what we measured, and when the extension falls back to local execution.

adbc_insert and INSERT / CREATE TABLE AS into an attached catalog pass DuckDB query results to the driver’s Arrow ingestion API through a bounded producer queue. Stream binding and execution run together on the consumer thread, so drivers that read their input during BindStream can ingest without blocking. Ingestion modes and transaction behavior depend on that driver’s capabilities.

The examples below use the attached-database API described in Technical Details. Install the SQLite ADBC driver and its manifest before running examples with driver 'sqlite'; alternatively use its shared-library path.

Create and query a SQLite table

ATTACH ':memory:' AS db (TYPE adbc, driver 'sqlite');
CALL adbc_execute('db', 'CREATE TABLE users(id INTEGER, name TEXT)');
CALL adbc_execute('db', 'INSERT INTO users VALUES (1, ''Alice''), (2, ''Bob'')');

-- SQLite requires explicit result columns for arbitrary SQL.
SELECT * FROM adbc_scan(
  'db',
  'SELECT id, name FROM users WHERE id = ?',
  params := row(1),
  columns := {'id': 'BIGINT', 'name': 'VARCHAR'}
);

-- Scanning a named table can obtain its schema from table metadata.
SELECT * FROM adbc_scan_table('db', 'users');
SELECT * FROM adbc_schema('db', 'users');
DETACH db;

Bulk insert from a DuckDB query

ATTACH ':memory:' AS db (TYPE adbc, driver 'sqlite');
SELECT * FROM adbc_insert(
  'db',
  'users',
  (SELECT * FROM (VALUES (1::BIGINT, 'Alice'), (2::BIGINT, 'Bob')) AS source(id, name)),
  mode := 'create'
);
SELECT * FROM adbc_scan_table('db', 'users');
DETACH db;

Commit and roll back

DuckDB’s own BEGIN, COMMIT, and ROLLBACK control the remote transaction. Use a file: the transaction writes on a separate connection, and each SQLite :memory: connection is its own database.

ATTACH 'users.sqlite' AS db (TYPE adbc, driver 'sqlite');
CALL adbc_execute('db', 'DROP TABLE IF EXISTS users');
CALL adbc_execute('db', 'CREATE TABLE users(id INTEGER, name TEXT)');
BEGIN;
CALL adbc_execute('db', 'INSERT INTO users VALUES (1, ''Alice'')');
COMMIT;
BEGIN;
CALL adbc_execute('db', 'INSERT INTO users VALUES (2, ''Bob'')');
ROLLBACK;
SELECT * FROM adbc_scan_table('db', 'users'); -- Alice only
DETACH db;

Mix catalog writes and remote SQL in one transaction

Catalog writes (INSERT INTO app.main.…) and adbc_execute commit or roll back together. The seed row matters for SQLite: its driver reports column types from stored values, so an empty table’s TEXT column would describe itself as an integer.

ATTACH 'app.sqlite' AS app (TYPE adbc, driver 'sqlite');
CALL adbc_execute('app', 'DROP TABLE IF EXISTS messages');
CALL adbc_execute('app', 'CREATE TABLE messages (id INTEGER, body TEXT)');
CALL adbc_execute('app', 'INSERT INTO messages VALUES (1, ''hello'')');
BEGIN;
CALL adbc_execute('app', 'INSERT INTO messages VALUES (2, ''pending'')');
INSERT INTO app.main.messages VALUES (3, 'also pending');
ROLLBACK; -- discards both writes
SELECT * FROM app.main.messages; -- only the seed row
DETACH app;

Attach an existing SQLite file for analytics

Replace the path with a SQLite file containing your application tables. Each :memory: connection has its own database; use a file when catalog queries need to see the same data.

ATTACH '/data/app.db' AS local_app (TYPE adbc, READ_ONLY, driver 'sqlite');
SHOW TABLES FROM local_app.main;
SELECT * FROM local_app.main.users;
DETACH local_app;