QuackDB

QuackDB is an Elixir client for remote DuckDB analytics over DuckDB's experimental Quack protocol.

It gives Elixir applications a DBConnection-first way to query, stream from, and append to DuckDB without embedding DuckDB inside the BEAM. Use it directly for analytical SQL, add Ecto when you want query composition and migrations, hand results to Explorer or Livebook, and let QuackDB supervise a local DuckDB server for development and notebooks.

[!WARNING] QuackDB targets DuckDB's experimental Quack protocol and is not production-ready yet. Public APIs, result shapes, Ecto adapter behavior, and supported protocol coverage may still change as DuckDB and QuackDB evolve. Validate behavior against your DuckDB version before relying on it for critical workloads.

What you can build with it

QuackDB is useful when your Elixir system needs DuckDB's analytical engine, but you want DuckDB to live as a separate process:

• local analytics services backed by DuckDB files;
• Livebook and Explorer workflows with supervised DuckDB;
• ingestion pipelines that append row, column, or dataframe batches;
• Ecto-powered analytical queries and setup migrations;
• spatial data exploration with DuckDB Spatial and optional Geo/WKB conversion;
• full-text search over DuckDB tables with BM25 ranking;
• querying Parquet, CSV, JSON, XLSX, Delta, and Iceberg sources from local paths or object stores.

Highlights

Installation

Add QuackDB to your dependencies:

def deps do
  [
    {:quackdb, "~> 0.3.0"}
  ]
end

Optional integrations are enabled when their packages are present:

def deps do
  [
    {:quackdb, "~> 0.3.0"},
    {:ecto_sql, "~> 3.13"},
    {:explorer, "~> 0.11"},
    {:geo, "~> 4.1"}
  ]
end

DuckDB's Quack protocol is experimental. For local development, use DuckDB 1.5.3 or newer with the quack extension.

Quick start with a supervised local DuckDB

For development, tests, examples, and notebooks, QuackDB can supervise DuckDB's CLI process and start a matching client pool. child_specs/1 generates one random token and injects it into both children.

children =
  QuackDB.Server.child_specs(
    server: [
      name: MyApp.DuckDB,
      duckdb: :managed,
      endpoint: "quack:localhost:9494"
    ],
    client: [
      name: MyApp.QuackDB,
      pool_size: System.schedulers_online()
    ]
  )

duckdb: :managed downloads DuckDB's official CLI binary on first use, verifies known checksums for QuackDB's pinned DuckDB version, and caches it. QuackDB never downloads DuckDB during dependency compilation. Use QUACKDB_BINARY_PATH, QUACKDB_BINARY_CACHE_DIR, duckdb: "/path/to/duckdb", or run the quackdb.install Mix task when you want explicit control. See the managed DuckDB guide.

You can also start DuckDB manually:

duckdb -interactive -init /dev/null \
  -cmd "LOAD quack; CALL quack_serve('quack:localhost', token='super_secret');"

quack:localhost often binds on IPv6 localhost, so examples use http://[::1]:9494.

Direct queries

{:ok, conn} =
  QuackDB.start_link(
    uri: "http://[::1]:9494",
    token: "super_secret"
  )

{:ok, result} = QuackDB.query(conn, "SELECT ? AS name, ? AS n", ["duck", 42])

result.columns
#=> ["name", "n"]

result.rows
#=> [["duck", 42]]

Use QuackDB.stream/4 for large result sets, or QuackDB.columns/4 when a column-oriented shape is more convenient for analytics tooling.

Native append writes

QuackDB can write through DuckDB's native append protocol instead of generating huge INSERT VALUES statements.

QuackDB.insert_rows!(conn, "events", [
  [id: 1, name: "duck", tags: ["bird", "wetland"]],
  [id: 2, name: "goose", tags: ["bird", "loud"]]
])

QuackDB.insert_columns!(conn, "measurements", [
  id: [1, 2, 3],
  temperature: [12.5, 13.0, 12.8]
])

Append supports explicit types, batching, scalar DuckDB values, and nested LIST, STRUCT, ARRAY, and MAP values. Explorer dataframes can be appended with QuackDB.Explorer.insert_dataframe/4 when Explorer is installed.

Ecto adapter

QuackDB includes an optional Ecto SQL adapter for applications that want Ecto query composition, schema-based reads/writes, migrations, and raw SQL through Repo.query/3.

defmodule MyApp.AnalyticsRepo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.QuackDB
end

import Ecto.Query

MyApp.AnalyticsRepo.all(
  from event in "events",
    where: event.score > ^10,
    group_by: event.category,
    select: %{category: event.category, events: count()}
)

For DuckDB-specific analytical helpers, spatial fragments, and normal Ecto query imports together:

defmodule MyApp.Analytics do
  use QuackDB.Ecto

  def median_scores do
    from event in "events",
      group_by: event.category,
      select: %{category: event.category, median_score: median(event.score)}
  end
end

The adapter currently covers:

• raw SQL via Repo.query/3;
• schema-backed full selects and Repo.get!/2;
• analytical reads with joins, filters, grouping, windows, CTEs, combinations, locks, and fragments;
• Repo.insert/2, Repo.insert_all/3, RETURNING, ON CONFLICT DO NOTHING, and common DO UPDATE upserts;
• explicit native append fast path via insert_method: :append;
• update_all, delete_all, schema update/delete, and transaction usage;
• Ecto.Adapters.SQL.explain/4;
• basic migration DDL through Ecto migrator: create/drop/alter tables, columns, references, indexes, primary keys, check constraints, and renames.

DuckDB-specific SQL that Ecto cannot model cleanly should still use Repo.query/3. See the Ecto coverage matrix.

Query files, object stores, and lakehouse tables

DuckDB can query data where it lives. QuackDB provides small helpers that generate DuckDB table-function SQL while leaving credentials and file access to DuckDB.

alias QuackDB.{Extension, Secret, Source}

QuackDB.query!(conn, Extension.install(:httpfs))
QuackDB.query!(conn, Extension.load(:httpfs))
QuackDB.query!(conn, Secret.create(:s3, provider: :credential_chain))

source = Source.parquet("s3://bucket/events/*.parquet", hive_partitioning: true)

MyApp.AnalyticsRepo.all(
  from event in source,
    group_by: event.category,
    select: %{category: event.category, events: count()}
)

QuackDB does not upload local files for you. The DuckDB server must be able to see the path, URL, object store, or lakehouse catalog. See the sources guide.

Spatial workflows

DuckDB Spatial works well through raw SQL, QuackDB expression helpers, or Ecto fragments.

alias QuackDB.Spatial

QuackDB.query!(conn, Spatial.install())
QuackDB.query!(conn, Spatial.load())

QuackDB.query!(conn, [
  "SELECT ",
  Spatial.as_geojson(Spatial.point(13.405, 52.52)),
  " AS berlin"
])

GEOMETRY values decode as WKB-compatible bytes for tested DuckDB Spatial values. QuackDB.Geometry can convert to/from %Geo.*{} structs when the optional :geo package is installed. See the spatial guide.

Full-text search

DuckDB's FTS extension can index text columns and rank matches with BM25. QuackDB wraps the setup pragmas and search expressions:

alias QuackDB.FTS

QuackDB.query!(conn, FTS.install())
QuackDB.query!(conn, FTS.load())
QuackDB.query!(conn, FTS.create_index("documents", :id, [:title, :body], overwrite: true))

score = FTS.match_bm25(~s|"id"|, "duckdb analytics", schema: FTS.schema_name("main.documents"))
QuackDB.query!(conn, ["SELECT id, title, ", score, " AS score FROM documents ORDER BY score DESC"])

Use QuackDB.Ecto.FTS or use QuackDB.Ecto for Ecto query expressions. See the full-text search guide.

Explorer, Table.Reader, and Livebook

When Explorer is installed, QuackDB can move data between DuckDB and dataframes:

alias Explorer.DataFrame
alias QuackDB.Explorer, as: QuackExplorer

frame = DataFrame.new(id: [1, 2], name: ["duck", "goose"])
QuackExplorer.insert_dataframe!(conn, "events", frame)

result = QuackDB.query!(conn, "SELECT * FROM events")
DataFrame.new(result)

QuackDB.Result and QuackDB.Columns implement Table.Reader, so they can be consumed by Livebook and other Table-aware tooling. See the Explorer guide and the Livebook example.

Observability

QuackDB emits telemetry spans for query, append, and fetch operations:

• [:quackdb, :query, :start | :stop]

• [:quackdb, :append, :start | :stop]

• [:quackdb, :fetch, :start | :stop]

Metadata includes connection/session information, command details, append batch counts, and client query IDs. Params are not included unless you opt in with telemetry_params: true. See the telemetry guide.

Architecture

Elixir application
  ├─ QuackDB / DBConnection query and stream APIs
  ├─ Ecto adapter, analytics helpers, and migration DDL
  ├─ Native row, column, and dataframe append APIs
  ├─ Explorer and Table.Reader integrations
  ├─ Spatial helpers and optional Geo/WKB bridge
  ├─ Source, extension, secret, DDL, and DML SQL helpers
  ├─ Telemetry spans and client query IDs
  └─ QuackDB.Server for local DuckDB supervision
        │
        ▼
DuckDB + quack extension

Each DBConnection process owns one Quack session and one persistent Mint HTTP connection. This matches Quack's sessionful protocol: prepared statements, fetch cursors, append requests, and disconnect messages all belong to a DuckDB connection id.

For local supervised DuckDB, QuackDB defaults to performance-conscious settings:

settings: [threads: System.schedulers_online()],
global_settings: [quack_fetch_batch_chunks: 4]

For heavy analytical scans, prefer a smaller client pool_size such as 1..4 because DuckDB parallelizes internally. For many small concurrent queries, System.schedulers_online() is a reasonable starting point.

Type and protocol coverage

QuackDB decodes common DuckDB scalars and nested values, including:

• booleans and integers through huge integers;
• floats and decimals;
• UUID, enum, blob, varchar, bit, and BIGNUM;
• date/time/timestamp families, including nanosecond values and TIME WITH TIME ZONE;
• intervals;
• LIST, STRUCT, ARRAY, and MAP;
• DuckDB Spatial GEOMETRY as WKB-compatible bytes.

The protocol implementation is intentionally explicit about unsupported features. Remaining low-level gaps, conformance fixtures, and unsupported vector/logical types are tracked in docs/protocol/coverage.md and guides/type-support.md.

Examples

The repository includes runnable scripts and a Livebook notebook:

• examples/query_observability.exs — attach telemetry handlers and print query, append, and fetch timings.
• examples/dataframe_analytics.exs — derive DDL from an Ecto schema, append an Explorer dataframe, query with Ecto DSL, and return a dataframe.
• examples/livebook_analytics.livemd — interactive analytics with DuckDB SQL, Explorer, Table.Reader, VegaLite, and telemetry.
• examples/spatial_wms/ — a minimal Ash + Ecto + Plug/Bandit app serving DuckDB Spatial rows through a WMS-like GeoJSON endpoint.
• examples/append_benchmark.exs — compares SQL inserts, native row/column append, Explorer append, and Ecto insert paths.
• examples/support/quackdb_demo.exs — shared demo boot helper that starts QuackDB.Server unless QUACKDB_URI is set.

Run scripts from outside the Mix project so Mix.install/2 can load the local package:

cd /tmp
elixir /path/to/quackdb/examples/dataframe_analytics.exs

Current boundaries

QuackDB is already broad, but intentionally not a complete DuckDB or Postgrex replacement:

• the Quack wire protocol is experimental and may change;
• unsupported vector/logical types raise explicit protocol errors;
• Ecto coverage focuses on analytical workflows and common write/setup paths, not every relational adapter feature;
• QuackDB does not stage/upload local files to a remote server;
• Arrow IPC / zero-copy columnar handoff is research for now;
• managed DuckDB binary downloads currently cover Linux/macOS targets, not Windows.

Development

mix deps.get
mix ci

See CONTRIBUTING.md for local checks, example smoke tests, package audit steps, and release dry-run notes.

Integration tests are skipped by default. To run them against a Quack server:

QUACKDB_TEST_URI='http://[::1]:9494' \
QUACKDB_TEST_TOKEN=super_secret \
mix test --include integration

Useful docs:

• Getting started
• Type support
• Examples
• Managed DuckDB
• Explorer
• Sources
• Spatial
• Full-text search
• Telemetry
• Protocol coverage
• Ecto coverage