12. The clients and the ecosystem

One C++ engine, many bindings. This chapter is what each one is good for, and the details that decide whether the Python path is fast or slow — because that is where most DuckDB actually runs.

Python — the one that matters

import duckdb

# the module-level default connection: in-memory
duckdb.sql("SELECT 42")

# an explicit connection
con = duckdb.connect('analytics.duckdb')
con = duckdb.connect()                    # :memory:
con = duckdb.connect('db.duckdb', read_only=True)

The three ways to get results out

rel = con.sql("SELECT country, sum(amount) FROM sales GROUP BY 1")

rel.df()              # pandas DataFrame
rel.arrow()           # pyarrow Table
rel.pl()              # Polars DataFrame
rel.fetchall()        # list of tuples  ← slow for big results
rel.fetchnumpy()      # dict of numpy arrays
rel.record_batch()    # an Arrow RecordBatchReader — STREAMING

rel is lazy. con.sql(...) builds a relation and does not execute it. Nothing runs until you ask for results, which means you can compose:

rel = con.sql("SELECT * FROM 'events.parquet'")
rel = rel.filter("event_type = 'purchase'")
rel = rel.aggregate("country, sum(amount)", "country")
rel.df()              # NOW it runs, as one optimised query

Where zero-copy is real, and where it is not

This is the detail worth knowing, because the difference is often 10×.

   DUCKDB → OUT

   .arrow()      ✓ zero copy. DuckDB's vectors
                   are handed to Arrow as
                   buffers.
   .pl()         ✓ zero copy, via Arrow.
                   Polars IS Arrow.
   .df()         ~ mostly. Numeric columns are
                   zero copy. STRINGS are not —
                   pandas object-dtype strings
                   must be built as Python
                   objects, one per value.
   .fetchall()   ✗ builds a Python tuple per
                   row, and a Python object per
                   value. Never do this for a
                   large result.


   IN → DUCKDB

   pandas        ~ numeric columns read in
                   place from numpy. Object
                   columns are converted.
   Polars        ✓ zero copy.
   Arrow table   ✓ zero copy.
   Arrow dataset ✓ zero copy, and DuckDB
                   pushes filters INTO it.

The rule: Arrow and Polars are free in both directions. pandas is free for numbers and not for strings. fetchall() is never free.

# ✗ 40 seconds on 10 million rows
rows = con.sql("SELECT * FROM big").fetchall()

# ✓ 0.4 seconds
tbl = con.sql("SELECT * FROM big").arrow()

# ✓ constant memory, for streaming
for batch in con.sql("SELECT * FROM big").record_batch(100_000):
    process(batch)

record_batch() is the answer to chapter 10’s “materialising a huge result into pandas” failure mode, and it is underused.

Replacement scans, again

import pandas as pd, polars as pl, pyarrow as pa

df   = pd.read_csv('a.csv')
pdf  = pl.read_parquet('b.parquet')
tbl  = pa.table({'x': [1,2,3]})

duckdb.sql("SELECT * FROM df JOIN pdf USING (id)")
duckdb.sql("SELECT sum(x) FROM tbl")

All three are queryable by variable name (chapter 2). This is what makes DuckDB feel like a natural part of a notebook rather than a database you have to talk to — and it is also why duckdb.sql() picks up variables you did not mean it to, if you happen to have a DataFrame named orders and a table named orders. The catalog wins; the replacement scan only fires when the name is not found.

Parameters, and not building SQL by hand

con.execute("SELECT * FROM t WHERE country = ? AND ts > ?",
            ['DE', '2026-01-01'])

con.execute("SELECT * FROM t WHERE country = $c", {'c': 'DE'})

DuckDB can read files and write files. SQL injection here is not “they read your users table”, it is “they read /etc/passwd or write to your bucket.” Chapter 9’s enable_external_access = false is the other half of this. Parameterise.

The CLI

duckdb                         # in-memory
duckdb analytics.duckdb        # persistent
duckdb -c "SELECT 42"          # one shot
duckdb -json -c "SELECT * FROM 'x.parquet' LIMIT 5"
duckdb < script.sql

It is a single static binary with no dependencies, which makes it a genuinely good general-purpose data tool:

# CSV → Parquet, one line, no Python
duckdb -c "COPY (SELECT * FROM 'in.csv') TO 'out.parquet'"

# look at a Parquet file you were just handed
duckdb -c "SUMMARIZE SELECT * FROM 'mystery.parquet'"
duckdb -c "DESCRIBE SELECT * FROM 'mystery.parquet'"

# query a remote file without downloading it
duckdb -c "SELECT count(*) FROM 'https://example.com/data.parquet'"

Dot commands worth knowing:

   .mode box | markdown | csv | json | line
   .maxrows 40
   .timer on
   .output results.csv
   .read script.sql
   .shell ls
   .databases
   .tables
   .schema

.mode markdown plus .output is how you get a query result into a document, and .mode line is how you read one very wide row without horizontal scrolling.

The other language bindings

   R           excellent, and integrates with
               dplyr via duckplyr — dplyr verbs
               compiled into DuckDB SQL

   Java / JDBC a real JDBC driver, so BI tools
               and JVM applications work

   Node.js     both a callback API and a newer
               neo API

   Rust, Go,   community-maintained to varying
   C#, Julia,  degrees; the C API underneath
   Swift       them is stable

   C / C++     the C API is the one everything
               else wraps. Stable across
               versions.

   ODBC        for tools that only speak ODBC

They are all the same engine and the same file format. A .duckdb written from Python opens in Java. There is no server-side/client-side version skew because there is no server.

WebAssembly — the one nothing else does

   duckdb-wasm

   The whole engine, compiled to WASM,
   running IN THE BROWSER TAB.

   ─ queries Parquet over HTTP with RANGE
     REQUESTS, so it reads only the bytes it
     needs
   ─ no server, no backend, no data leaving
     the browser
   ─ OPFS for persistent storage
   ─ ~4 MB compressed

This has no equivalent in any other engine on this site. It is why tools like Observable, Evidence, Rill and a growing number of “static site with a real query engine” projects exist — you publish Parquet files to a CDN and the browser does the analytics.

   THE ARCHITECTURE

   a static site on a CDN
     + events.parquet (200 MB) on the CDN
     + duckdb-wasm in the page

   user filters a dashboard
     → the browser issues range requests for
       the row groups it needs
     → maybe 3 MB transferred
     → the query runs on the user's CPU

   TOTAL SERVER COST: static hosting.

The constraints are real — browser memory limits, single-threaded unless cross-origin isolation headers are set, and a cold start to fetch the WASM — but for dashboards over a few hundred megabytes it works, and the operational cost is zero.

dbt-duckdb

my_project:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: dev.duckdb
      extensions: [httpfs, parquet]

This is one of the most useful things in the DuckDB ecosystem and it is under-adopted.

   THE PROBLEM IT SOLVES

   A [dbt](/dbt/01-what-and-why/) project
   against Snowflake or BigQuery means:
     ─ every developer needs a warehouse
       account
     ─ every `dbt run` during development
       costs money
     ─ CI needs a warehouse
     ─ tests are slow because they are
       network round trips

   WITH dbt-duckdb:
     ─ `dbt build` runs locally in seconds
     ─ CI is a container with no credentials
     ─ tests run on a sample in milliseconds
     ─ production still targets the warehouse

Same models, same tests, same ref() graph, different adapter. The SQL is not always identical — dialect differences exist — but for a project that keeps to reasonably portable SQL, the development loop improves out of recognition. dbt’s chapter 12 argues for making CI cheap; this is the cheapest CI available.

It is also used the other way round: dbt-duckdb as the production engine, transforming Parquet in S3 into more Parquet in S3, with no warehouse at all. For the right size of project that is a complete, extremely cheap data platform.

MotherDuck

The commercial hosted service, and worth understanding structurally because it is not “DuckDB in the cloud.”

   HYBRID EXECUTION

   your laptop                 MotherDuck
   ┌──────────────┐            ┌──────────────┐
   │ local DuckDB │◄──────────►│ cloud DuckDB │
   │              │            │              │
   │ local files, │            │ shared data, │
   │ local tables │            │ bigger boxes │
   └──────────────┘            └──────────────┘

   ONE QUERY can join a local Parquet file
   to a cloud table, and the planner decides
   which side does what.

What it adds over plain DuckDB: shared data between people, persistent cloud storage, a server that is always up, authentication, and sharing. Which is to say: the things chapter 11 said DuckDB does not have.

It is a reasonable answer to “we love DuckDB and we have outgrown one process,” and it is a commercial dependency, and both of those are true at once.

The tools built on top

   Evidence, Rill,    BI and dashboarding with
   Hex, Observable    DuckDB or duckdb-wasm
                      underneath

   Ibis               a dataframe API that
                      compiles to DuckDB (and
                      to other backends)

   duckplyr           dplyr verbs, executed by
                      DuckDB

   Harlequin          a terminal UI for DuckDB

   sqlmesh, dlt       pipeline tools with
                      first-class DuckDB
                      support

   pg_duckdb          DuckDB as an execution
                      engine INSIDE PostgreSQL,
                      for analytical queries

pg_duckdb is the interesting one to watch. It puts DuckDB’s vectorised engine into a Postgres extension, so an analytical query against Postgres tables runs columnar. It is the same idea as the ClickHouse-versus-Postgres split, resolved by embedding rather than choosing.

What to take from this chapter

Python relations are lazy — con.sql() builds a plan, and nothing runs until you ask for results, so .filter().aggregate() composes into one optimised query.
Zero copy is real for Arrow and Polars in both directions. pandas is free for numeric columns and not for strings. fetchall() is never free — it builds a Python object per value.
record_batch() streams results in constant memory, and it is the fix for materialising a huge result.
Parameterise. SQL injection into an engine that can read and write files is worse than the usual kind; pair it with enable_external_access = false.
The CLI is a single static binary and a genuinely good general data tool — COPY a CSV to Parquet, SUMMARIZE a file you were handed, query a URL without downloading it. .mode markdown, .timer on, .output.
All bindings are the same engine and the same file format, with no client/server version skew.
duckdb-wasm has no equivalent anywhere else: the whole engine in a browser tab, reading Parquet from a CDN by range request, with static hosting as the total server cost.
dbt-duckdb is under-adopted — local development and CI in seconds with no warehouse credentials, and for small projects a complete production platform over Parquet in S3.
MotherDuck adds exactly what chapter 11 said was missing — sharing, persistence, an always-on server — as a commercial dependency, with hybrid local/cloud execution.
pg_duckdb puts the engine inside Postgres, resolving the row-store/column-store split by embedding rather than choosing.

Next: tuning and operations — the settings that matter, and running DuckDB in containers, CI and Lambda.

Written by Sepuri Sai Krishna