Announcing the DuckDB ADBC Extension

The Missing Link Between DuckDB and External Databases

DuckDB has surged in popularity thanks to its ease of use, speed, and feature richness. But not all of the world’s data lives in DuckDB, and eventually DuckDB needs to talk to the outside world. To connect DuckDB to other systems, the DuckDB community has developed vendor-specific extensions for individual systems such as SQLite, PostgreSQL, and Snowflake.

Although these vendor-specific extensions address a real need, each one adds another interface, feature set, and collection of quirks for DuckDB users to learn. More importantly, DuckDB lacks dedicated extensions for many widely used systems such as Databricks, Redshift, and Oracle, because building and maintaining a separate extension for every system outpaces what the DuckDB community can sustain.

Enter ADBC

ADBC (Arrow Database Connectivity) is a universal data access API that supports 30+ query systems, including:

• Transactional databases: PostgreSQL, MySQL, MariaDB, SQLite, Oracle Database, Microsoft SQL Server, CockroachDB, YugabyteDB, TiDB
• Analytical databases: DuckDB, ClickHouse, Exasol
• Data warehouses: Snowflake, Databricks, BigQuery, Amazon Redshift, Teradata
• Lakehouse engines: Trino, Dremio, StarRocks, Apache Doris
• Time-series databases: InfluxDB, TimescaleDB, GreptimeDB

ADBC is built on Apache Arrow, an efficient, columnar data format that nearly every modern data system speaks natively. Building on Arrow, ADBC enables:

• Fast, zero-copy data transfer between column-oriented analytical databases, skipping the slow column-to-row and row-to-column conversions that legacy row-based APIs like ODBC and JDBC force on you.
• Seamless interoperability across the large and growing ecosystem of Arrow-compatible systems.

The ADBC Extension for DuckDB

DuckDB is no stranger to ADBC. It already supports other systems connecting to it through ADBC: the DuckDB shared library doubles as an ADBC driver (which you can install with dbc install duckdb), and there’s a separate ADBC driver for Quack, DuckDB’s client-server protocol (dbc install --pre quack). Those sit alongside the many other ways DuckDB already speaks Apache Arrow.

Today, we’re announcing the ADBC extension for DuckDB, which turns the connection around: instead of other systems reaching into DuckDB, DuckDB reaches out to the entire ADBC ecosystem.

Using this new extension, you can query ADBC databases directly with read_adbc, or ATTACH to an ADBC database and run SELECT, INSERT, COPY, and CTAS statements as if it were local to DuckDB.

Rusty Conover deserves credit as the first to connect DuckDB out to the ADBC ecosystem, with his adbc_scanner extension. We took a different approach that integrates more easily with ADBC connection profiles, offers broader database compatibility, and adds automatic connection pooling, automatic metadata caching, and memory-efficient INSERT and CTAS statements through streaming bulk ingest.

We plan to contribute this open source extension to the Arrow project so it can become an official ADBC library.

What the ADBC Extension Can Do

Before we can demonstrate the extension, we need to set up a database to connect to and an ADBC driver that connects to it.

Install an ADBC Driver and Create a Connection Profile

To showcase the extension, we use a SQLite games database that you can download at this link or in your terminal by running:

curl -o games.sqlite "https://data.columnar.tech/games.sqlite"

To connect to a new system, you install its ADBC driver. We built dbc to make that as easy as a single dbc install command. If you don’t already have dbc, install it, then run:

dbc install sqlite

Next, create an ADBC connection profile: a simple TOML file that stores the connection information for a database. A profile named mydb.toml that connects to the SQLite games database looks like this:

profile_version = 1
driver = "sqlite"

[Options]
uri = "./games.sqlite"

You can create this file yourself, or click here to download it.

The last step is to move mydb.toml into the default directory for ADBC connection profiles on your system.

# Linux
mv mydb.toml ~/.config/adbc/profiles/

# macOS
mv mydb.toml ~/Library/Application Support/ADBC/Profiles/

# Windows
move "mydb.toml" "%LOCALAPPDATA%\ADBC\Profiles\"

You can also do this in your file manager (Finder, Explorer, and so on) by moving mydb.toml into the path shown above for your OS.

With the profile in place, you’re ready to install and load the ADBC extension and start using it in DuckDB. You’ll work in a DuckDB SQL interface—the DuckDB CLI is the easiest, though any of DuckDB’s SQL clients will do. (The exception is DuckDB WASM, such as the online shell, which the extension isn’t designed to work with.)

Read data with read_adbc

The simplest way to use the extension is to call read_adbc with a profile URI and a SQL query. It runs the query on the remote database and returns the results to DuckDB.

-- Install the extension
D INSTALL adbc FROM community;

-- Load it
D LOAD adbc;

-- Read from the ADBC database using read_adbc
D SELECT * FROM read_adbc('profile://mydb', 'SELECT * FROM games');
┌───────┬────────────┬─────────────────────┬─────────┬─────────┬─────────────┬─────────────┬────────────┐
│  id   │    name    │      inventor       │  year   │ min_age │ min_players │ max_players │ list_price │
│ int64 │  varchar   │       varchar       │ varchar │  int64  │    int64    │    int64    │  varchar   │
├───────┼────────────┼─────────────────────┼─────────┼─────────┼─────────────┼─────────────┼────────────┤
│     1 │ Monopoly   │ Elizabeth Magie     │ 1903    │       8 │           2 │           6 │ 19.99      │
│     2 │ Scrabble   │ Alfred Mosher Butts │ 1938    │       8 │           2 │           4 │ 17.99      │
│     3 │ Clue       │ Anthony E. Pratt    │ 1944    │       8 │           2 │           6 │ 9.99       │
│     4 │ Candy Land │ Eleanor Abbott      │ 1948    │       3 │           2 │           4 │ 7.99       │
│     5 │ Risk       │ Albert Lamorisse    │ 1957    │      10 │           2 │           5 │ 29.99      │
└───────┴────────────┴─────────────────────┴─────────┴─────────┴─────────────┴─────────────┴────────────┘

Data comes back as a regular DuckDB table that you can work with however you like — filter it, aggregate it, write it to Parquet, or join it with local DuckDB tables, to name a few.

ATTACH to an ADBC database

To create a persistent connection to an ADBC database, run the ATTACH command and then query the database as if it were local to DuckDB. We currently support catalog lookups, along with SELECT, INSERT, COPY, and CREATE TABLE AS (SELECT ...) (CTAS) statements.

-- Create a persistent connection to the SQLite database
D ATTACH 'profile://mydb' AS mydb (TYPE adbc);

-- Set the default schema
D USE mydb.main;

-- Display all tables in the attached ADBC database
D SHOW ALL TABLES;
┌──────────┬─────────┬─────────┬───────────────────────────────┬────────────────────────────────┬───────────┐
│ database │ schema  │  name   │         column_names          │          column_types          │ temporary │
│ varchar  │ varchar │ varchar │           varchar[]           │           varchar[]            │  boolean  │
├──────────┼─────────┼─────────┼───────────────────────────────┼────────────────────────────────┼───────────┤
│ mydb     │ main    │ games   │ [id, name, inventor, year,    │ [BIGINT, VARCHAR, VARCHAR,     │ false     │
│          │         │         │  min_age, min_players,        │  VARCHAR, BIGINT, BIGINT,      │           │
│          │         │         │  max_players, list_price]     │  BIGINT, VARCHAR]              │           │
└──────────┴─────────┴─────────┴───────────────────────────────┴────────────────────────────────┴───────────┘

-- Read directly from the attached ADBC table
D SELECT * FROM games;
┌───────┬────────────┬─────────────────────┬─────────┬─────────┬─────────────┬─────────────┬────────────┐
│  id   │    name    │      inventor       │  year   │ min_age │ min_players │ max_players │ list_price │
│ int64 │  varchar   │       varchar       │ varchar │  int64  │    int64    │    int64    │  varchar   │
├───────┼────────────┼─────────────────────┼─────────┼─────────┼─────────────┼─────────────┼────────────┤
│     1 │ Monopoly   │ Elizabeth Magie     │ 1903    │       8 │           2 │           6 │ 19.99      │
│     2 │ Scrabble   │ Alfred Mosher Butts │ 1938    │       8 │           2 │           4 │ 17.99      │
│     3 │ Clue       │ Anthony E. Pratt    │ 1944    │       8 │           2 │           6 │ 9.99       │
│     4 │ Candy Land │ Eleanor Abbott      │ 1948    │       3 │           2 │           4 │ 7.99       │
│     5 │ Risk       │ Albert Lamorisse    │ 1957    │      10 │           2 │           5 │ 29.99      │
└───────┴────────────┴─────────────────────┴─────────┴─────────┴─────────────┴─────────────┴────────────┘

-- Insert into the ADBC database
D INSERT INTO games (SELECT 6, 'Battleship', 'Clifford Von Wickler', 1931, 7, 2, 2, 12.99);
D SELECT * FROM games;
┌───────┬────────────┬──────────────────────┬─────────┬─────────┬─────────────┬─────────────┬────────────┐
│  id   │    name    │       inventor       │  year   │ min_age │ min_players │ max_players │ list_price │
│ int64 │  varchar   │       varchar        │ varchar │  int64  │    int64    │    int64    │  varchar   │
├───────┼────────────┼──────────────────────┼─────────┼─────────┼─────────────┼─────────────┼────────────┤
│     1 │ Monopoly   │ Elizabeth Magie      │ 1903    │       8 │           2 │           6 │ 19.99      │
│     2 │ Scrabble   │ Alfred Mosher Butts  │ 1938    │       8 │           2 │           4 │ 17.99      │
│     3 │ Clue       │ Anthony E. Pratt     │ 1944    │       8 │           2 │           6 │ 9.99       │
│     4 │ Candy Land │ Eleanor Abbott       │ 1948    │       3 │           2 │           4 │ 7.99       │
│     5 │ Risk       │ Albert Lamorisse     │ 1957    │      10 │           2 │           5 │ 29.99      │
│     6 │ Battleship │ Clifford Von Wickler │ 1931    │       7 │           2 │           2 │ 12.99      │
└───────┴────────────┴──────────────────────┴─────────┴─────────┴─────────────┴─────────────┴────────────┘

-- Create a local table in DuckDB of the inventors of each game
D CREATE TABLE memory.inventors AS (SELECT id, inventor FROM games);

-- Create a new table in the attached ADBC database (SQLite) of the inventors
D CREATE TABLE game_inventors(id, inventor) AS (SELECT * FROM memory.inventors);
D SELECT * FROM game_inventors;
┌───────┬──────────────────────┐
│  id   │       inventor       │
│ int64 │       varchar        │
├───────┼──────────────────────┤
│     1 │ Elizabeth Magie      │
│     2 │ Alfred Mosher Butts  │
│     3 │ Anthony E. Pratt     │
│     4 │ Eleanor Abbott       │
│     5 │ Albert Lamorisse     │
│     6 │ Clifford Von Wickler │
└───────┴──────────────────────┘

Limitations

Autocommit Only. The ADBC extension operates in autocommit mode, which means that queries take effect immediately, and multi-statement transactions aren’t supported yet.

No Predicate or Projection Pushdown (for ATTACH). When you query an attached table, the extension fetches all of its columns and rows, then DuckDB applies predicates and projections locally. For large tables, push filters and projections down with read_adbc instead.

D CREATE MACRO read_mydb(query) AS TABLE SELECT * FROM read_adbc('profile://mydb', query);
D SELECT inventor FROM read_mydb('SELECT inventor FROM games WHERE name = ''Monopoly''');
┌─────────────────────┐
│      inventor       │
│       varchar       │
├─────────────────────│
│ Elizabeth Magie     │
└─────────────────────┘

Predicate and projection pushdown for attached tables are on our roadmap; see issues #1 and #2.

Key Features

Streaming Bulk Ingest. For an INSERT or CREATE TABLE AS (SELECT ...) statement, the extension inserts rows in batches (by default, ~2 million rows at a time) using ADBC’s bulk ingest API. Streaming rows in batches keeps memory usage low, even for datasets larger than main memory. Tune the batch size by setting adbc_insert_buffer_size.

Connection Pooling. Once you’ve attached an ADBC database, the extension reuses connections across subsequent SQL statements rather than opening and closing a new one each time, avoiding redundant overhead and improving performance. Tune the pool size by setting adbc_connection_pool_size.

Metadata Caching. After you attach an ADBC database, it’s convenient to browse its schemas and tables with SHOW TABLES. The extension caches schema and table metadata locally so it doesn’t re-query the remote database for the same information. To clear the cache and pick up the latest metadata after a remote schema change, run CALL adbc_clear_cache().