ADBC Scanner

Install the driver and extension

DuckDB needs the SQLite ADBC driver as well as the extension. Install dbc, then run this in your terminal:

dbc install sqlite

This installs the driver and its manifest so driver 'sqlite' resolves in the SQL example. Run DuckDB from the same terminal environment, especially if you use a virtual environment or set ADBC_DRIVER_PATH.

The example above was checked with DuckDB 1.5.6 and ADBC Scanner community build 9723c3c. A fresh INSTALL adbc_scanner FROM community installs the extension; to replace an existing copy, run:

FORCE INSTALL adbc_scanner FROM community;

Close any DuckDB sessions that already loaded the extension. In a new session, load it and check the build:

LOAD adbc_scanner;

SELECT extension_version
FROM duckdb_extensions()
WHERE extension_name = 'adbc_scanner';

Then run the complete SQLite example at the top of this page. It needs no separate SQLite server or database file. If DuckDB cannot find sqlite, run dbc list in the same environment and check that the driver is installed there.

API version and migration

These examples use the attached-database API, introduced in 7e5210c and included in both the build checked above and the DuckDB 2.0 build. Older installations may expose the previous connection-handle API; update the extension before using these examples.

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, because ROLLBACK could not undo it. 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.

A driver without transactions (one that cannot turn autocommit off) can still be written to outside BEGIN … COMMIT, through adbc_execute, adbc_insert, INSERT INTO db.… and CREATE TABLE db.… AS. Each statement runs in the driver’s autocommit, so one that fails part-way can leave some rows written.

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, and a reference to one (or to a backend system column such as PostgreSQL’s ctid) fails to bind with Referenced column "rowid" not found. 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.

An unqualified name (db.orders, or orders after USE db) resolves to the attachment’s default schema: the schema the connection reports as current (PostgreSQL’s public, for example), else the database’s only schema, else main. Set it explicitly with the default_schema ATTACH option: ATTACH '…' AS db (TYPE adbc, driver '…', default_schema 'sales'). Catalog writes such as INSERT INTO db.other.t and CREATE TABLE db.other.t AS land in the schema they name.

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 DuckDB 2.0, whole-query pushdown can go beyond individual scans. For drivers with a structured-query dialect profile (PostgreSQL, MySQL, SQLite and DuckDB), filters, projections, aggregates, supported joins, ordering, limits and nested queries against an attached catalog run as one remote statement. Anything unsupported, including a reference to a column the remote table does not have, falls back to the regular scan path. Repeated result column names in a pushed-down query, such as two count(*) columns, are renamed rather than raising a binder error. EXPLAIN shows the generated statement under Remote SQL in an Adbc Remote Query operator. Turn pushdown off per attachment with query_pushdown false when comparing plans or troubleshooting:

ATTACH 'postgresql://user:password@host/database' AS pg (
    TYPE adbc,
    driver 'postgresql',
    query_pushdown false
);

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.

CONNECT on DuckDB 2.0

DuckDB 2.0’s CONNECT statement routes every following statement, queries and commands alike, to an attached ADBC database as raw SQL in the backend’s own dialect, until DISCONNECT:

ATTACH ':memory:' AS db (TYPE adbc, driver 'sqlite');
CONNECT db;
SELECT sqlite_version();
CREATE TABLE notes (id INTEGER, body TEXT);
INSERT INTO notes VALUES (1, 'runs on SQLite');
SELECT * FROM notes;
DISCONNECT;
DETACH db;

A command that the driver describes with result columns but that returns none when executed (DataFusion’s CREATE TABLE … AS SELECT, for example) returns those columns with no rows.

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.

When appending to an existing table, the extension asks the driver for the target table’s schema and marks the columns it declares NOT NULL as non-nullable in the ingest stream, so drivers that compare schemas strictly (DataFusion, for example) accept the append. A NULL in such a column fails with NOT NULL constraint failed: <table>.<column>. When the driver cannot report the table’s schema, every column is sent as nullable.

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;