Cloudflare Extension for DuckDB

January 8, 2026 · View on GitHub

Query Cloudflare D1 databases and R2 SQL Iceberg tables directly from DuckDB with native syntax and automatic query optimization.

Installation

INSTALL cloudflare FROM community;
LOAD cloudflare;

Features

D1 (SQLite Databases)

✅ Natural ATTACH syntax - ATTACH 'database' AS mydb (TYPE d1)
✅ No SECRET parameter needed - Automatically finds D1 secret
✅ Auto-create views - All tables instantly queryable
✅ Transaction batching - Multiple operations in single HTTP request
✅ Query optimization - Automatic filter/LIMIT/projection pushdown

R2 SQL (Apache Iceberg Tables)

✅ Query Iceberg tables - Read Apache Iceberg tables stored in R2 buckets
✅ SHOW commands - List databases, namespaces, and tables
✅ Table introspection - DESCRIBE tables to view schema
✅ Standard SQL - SELECT with WHERE, GROUP BY, ORDER BY, LIMIT

Common

✅ Secret management - Store credentials once with CREATE SECRET

Quick Start - D1

1. Get Credentials

From Cloudflare Dashboard:

Account ID: D1 → Database → Right sidebar
API Token: Profile → API Tokens → Create Token (with D1 permissions)

See detailed guide →

2. Create Secret

CREATE SECRET d1 (
    TYPE d1,
    ACCOUNT_ID 'your-cloudflare-account-id',
    API_TOKEN 'your-cloudflare-api-token'
);

3. Query Databases

-- List databases
SELECT * FROM d1_databases('d1');

-- Attach database (no SECRET needed!)
ATTACH 'my-database' AS mydb (TYPE d1);

-- Query with automatic optimization
SELECT * FROM mydb.users WHERE active = true LIMIT 10;

4. Transaction Batching

Multiple operations → single HTTP request:

BEGIN TRANSACTION;
  INSERT INTO mydb.logs VALUES (1, 'Event A');
  INSERT INTO mydb.logs VALUES (2, 'Event B');
  UPDATE mydb.settings SET value = 'new' WHERE key = 'config';
COMMIT;  -- All 3 statements sent as one batch

Quick Start - R2 SQL

1. Get R2 Credentials

From Cloudflare Dashboard:

Account ID: R2 → Overview → Right sidebar
API Token: Profile → API Tokens → Create Token with permissions:
- Workers R2 SQL Read
- Workers R2 Data Catalog Write
- Workers R2 Storage Write

2. Create R2 Secret

CREATE SECRET r2sql (
    TYPE r2_sql,
    ACCOUNT_ID 'your-cloudflare-account-id',
    API_TOKEN 'your-cloudflare-api-token'
);

3. Query Iceberg Tables

-- List namespaces in bucket
SELECT * FROM r2_sql_databases('r2sql', 'my-bucket');

-- List tables in namespace
SELECT * FROM r2_sql_tables('r2sql', 'my-bucket', 'my_namespace');

-- Describe table schema
SELECT * FROM r2_sql_describe('r2sql', 'my-bucket', 'my_namespace.my_table');

-- Query data
SELECT * FROM r2_sql_query(
    'r2sql',
    'my-bucket',
    'SELECT * FROM my_namespace.my_table WHERE id > 100 LIMIT 10'
);

D1 Functions

Function	Purpose	Example
`d1_databases(secret)`	List all databases	`SELECT * FROM d1_databases('d1')`
`d1_tables(secret, db)`	List tables	`SELECT * FROM d1_tables('d1', 'my-db')`
`d1_query(secret, db, sql)`	Execute query	`SELECT * FROM d1_query('d1', 'my-db', 'SELECT * FROM users')`
`d1_execute(secret, db, sql)`	Execute statement	`SELECT d1_execute('d1', 'my-db', 'INSERT INTO ...')`

R2 SQL Functions

Function	Purpose	Example
`r2_sql_databases(secret, bucket)`	List namespaces	`SELECT * FROM r2_sql_databases('r2sql', 'my-bucket')`
`r2_sql_tables(secret, bucket, [namespace])`	List tables	`SELECT * FROM r2_sql_tables('r2sql', 'my-bucket', 'ns')`
`r2_sql_describe(secret, bucket, table)`	Describe table	`SELECT * FROM r2_sql_describe('r2sql', 'my-bucket', 'ns.table')`
`r2_sql_query(secret, bucket, sql)`	Execute SELECT	`SELECT * FROM r2_sql_query('r2sql', 'bucket', 'SELECT ...')`

Advanced Usage

Multiple Cloudflare Accounts

-- Production
CREATE SECRET prod (TYPE d1, ACCOUNT_ID 'prod-id', API_TOKEN 'prod-token');
ATTACH 'prod-db' AS prod (TYPE d1, SECRET 'prod');

-- Staging
CREATE SECRET staging (TYPE d1, ACCOUNT_ID 'staging-id', API_TOKEN 'staging-token');
ATTACH 'staging-db' AS staging (TYPE d1, SECRET 'staging');

-- Compare environments
SELECT 'prod' as env, COUNT(*) FROM prod.users
UNION ALL
SELECT 'staging', COUNT(*) FROM staging.users;

Export to Parquet

COPY (SELECT * FROM mydb.orders WHERE status = 'completed')
TO 'orders.parquet' (FORMAT PARQUET);

Join D1 with Local Data

SELECT u.name, COUNT(*) as order_count
FROM mydb.users u
JOIN mydb.orders o ON u.id = o.user_id
GROUP BY u.name;

How It Works

Architecture

DuckDB → D1Catalog (custom) → D1TransactionManager (batch buffering)
            ↓
      Auto-created views
            ↓
      d1_scan (table function)
            ↓
      Cloudflare D1 API

Key optimizations:

Filter pushdown - WHERE clauses sent to D1
LIMIT pushdown - LIMIT sent to D1 API
Projection pushdown - Only requested columns fetched
Batch buffering - Multiple writes = one HTTP request

Transaction Batching

BEGIN TRANSACTION
  INSERT statement 1  →  Buffered
  INSERT statement 2  →  Buffered
  UPDATE statement    →  Buffered
COMMIT                →  Single batch HTTP request to D1

Important: D1 uses auto-commit per statement (not true ACID transactions). Best for bulk operations where per-statement atomicity is acceptable.

Documentation

📚 Get Cloudflare Credentials - Account ID & API Token setup
📖 Complete Usage Guide - All features & examples
🏗️ Implementation Details - Architecture & transaction batching
🧪 Test Scripts - Example queries and verification

Performance Tips

✅ Use filter pushdown:

-- Good - filter sent to D1
SELECT * FROM mydb.users WHERE id = 123;

-- Bad - all data fetched then filtered
SELECT * FROM (SELECT * FROM mydb.users) WHERE id = 123;

✅ Batch writes:

-- Good - one HTTP request
BEGIN TRANSACTION;
  INSERT INTO mydb.logs SELECT * FROM read_csv('data.csv');
COMMIT;

✅ Export for heavy processing:

-- Export to local Parquet, process locally
COPY (SELECT * FROM mydb.large_table) TO 'local.parquet';
SELECT * FROM 'local.parquet' WHERE complex_calculation(...);

Limitations

Limitation	Impact	Workaround
No true ACID transactions	Statements auto-commit	Use `d1_execute()` for single statements
No rollback after commit	Can't undo committed data	Plan operations carefully
30 second batch timeout	Large batches may fail	Split into smaller batches
Read-your-writes in txn	Buffered writes invisible until commit	Commit before reading
No DDL via ATTACH	Can't CREATE TABLE	Use `d1_execute()` for DDL

Building from Source

# Clone repository
git clone https://github.com/onnimonni/duckdb-cloudflare.git
cd duckdb-cloudflare

# Initialize submodules
git submodule update --init --recursive

# Build
make release GEN=ninja

# Test
./build/release/duckdb -f test-d1-syntax.sql

Troubleshooting

"D1 attach requires a D1 secret"

Solution: Create a secret first:

CREATE SECRET d1 (TYPE d1, ACCOUNT_ID '...', API_TOKEN '...');

"HTTP request failed with status 401"

Cause: Invalid API token

Solution:

Verify credentials in Cloudflare dashboard
Create new token with D1 permissions
Update secret

"D1 database not found"

Solution: List databases to find correct name:

SELECT name FROM d1_databases('d1');

Contributing

Contributions welcome! Please:

Fork the repository
Create a feature branch
Make your changes
Add tests
Submit a pull request

License

MIT License - see LICENSE file

Made with ❤️ for the DuckDB community

Query Cloudflare D1 from your local database!