Getting started

QuackDB connects Elixir applications to a DuckDB process through DuckDB's Quack protocol. The client talks to the Quack HTTP endpoint, decodes DuckDB result chunks, and exposes the connection through DBConnection.

Requirements

Elixir 1.19 or newer
DuckDB 2.0 with the quack extension (Quack protocol version 3). Connecting to a DuckDB 1.5 server fails with :unsupported_quack_version; QuackDB 0.5 is the release line for DuckDB 1.5.
A running Quack server

Install

Add :quackdb to your dependencies:

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

Optional integrations are compiled only when their packages are available. Add Explorer when you want dataframe handoff helpers:

def deps do
  [
    {:quackdb, "~> 0.7.0"},
    {:explorer, "~> 0.11"}
  ]
end

Then fetch dependencies:

mix deps.get

Start DuckDB with Quack

For local development, QuackDB can supervise DuckDB's external CLI process for you:

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

child_specs/1 generates one shared random token and injects the matching URI/token into both child specs. Pass :token on either side when you want to provide it yourself.

duckdb: :managed downloads and caches DuckDB's official CLI binary when the local server starts. Managed downloads are checksum-verified for QuackDB.Binary.default_version/0; other DuckDB versions require passing an explicit :sha256. QuackDB.Server runs DuckDB's idempotent INSTALL quack and then LOAD quack by default before serving. It writes generated boot SQL to an Elixir-managed temporary init file by default, so generated local server tokens are not embedded in process arguments. Set install_quack?: false only for locked-down environments that preinstall extensions and forbid startup-time extension installation. For explicit setup, run:

mix quackdb.install
mix quackdb.install --print-path

Use QUACKDB_BINARY_PATH or pass duckdb: "/path/to/duckdb" when you want QuackDB to use a system or custom executable instead. See the managed DuckDB guide for cache, checksum, and target-prefetch options.

Or start DuckDB manually with the quack extension loaded:

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

On some systems, quack:localhost binds to IPv6 localhost. If http://localhost:9494 does not connect, use http://[::1]:9494.

Connect from Elixir

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

QuackDB.start_link/1 starts a DBConnection process. You can pass the connection to QuackDB.ping/2, QuackDB.query/4, QuackDB.prepare_execute/4, QuackDB.stream/4, or DBConnection APIs.

Run a query

:ok = QuackDB.ping(conn)

{:ok, result} = QuackDB.query(conn, "SELECT 1 AS n")

result.columns
#=> ["n"]

result.rows
#=> [[1]]

result.num_rows
#=> 1

rows are row-oriented lists. This shape is convenient for DBConnection and future Ecto integration.

QuackDB formats positional parameters as DuckDB SQL literals client-side because the current Quack request path does not expose server-side bind parameters:

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

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

Placeholders inside strings and comments are ignored while formatting, and unsupported parameter values raise explicit errors.

Decode nested values

DuckDB nested result types decode to ordinary Elixir values:

{:ok, result} =
  QuackDB.query(conn, """
  SELECT
    [1, 2, 3] AS xs,
    {'name': 'duck', 'count': 2} AS obj,
    array_value(1, 2, 3) AS arr,
    map(['a', 'b'], [1, 2]) AS m,
    [{'a': 1}, {'a': 2}] AS nested
  """)

result.rows
#=> [
#=>   [
#=>     [1, 2, 3],
#=>     %{"name" => "duck", "count" => 2},
#=>     [1, 2, 3],
#=>     %{"a" => 1, "b" => 2},
#=>     [%{"a" => 1}, %{"a" => 2}]
#=>   ]
#=> ]

Query files and lakehouse sources

DuckDB can scan files, object stores, and lakehouse table formats directly. QuackDB.Source builds safe table-function fragments that can be used from Ecto queries or direct SQL:

use QuackDB.Ecto

alias QuackDB.Source

source = Source.csv("events.csv", header: true)

MyApp.AnalyticsRepo.all(
  from event in source,
    where: event.id > 1,
    select: %{id: event.id, name: event.name}
)

See guides/sources.md for HTTP(S), S3/R2/GCS, Azure/ADLS, Hugging Face, Delta, Iceberg, extensions, and DuckDB secrets.

Stream large result sets

QuackDB.query/4 materializes the full result. QuackDB fetches additional result chunks when DuckDB reports that more rows are available, but for large analytical results prefer streaming helpers.

Use QuackDB.stream/4 to process %QuackDB.Result{} batches lazily:

row_count =
  conn
  |> QuackDB.stream("SELECT i FROM range(0, 50_000) t(i)")
  |> Enum.reduce(0, fn result, count -> count + result.num_rows end)

row_count
#=> 50_000

Use QuackDB.rows/4 for row-level streaming:

conn
|> QuackDB.rows("SELECT i FROM range(0, ?) t(i)", [50_000])
|> Enum.take(3)
#=> [[0], [1], [2]]

Use QuackDB.maps/4 for row maps keyed by column names. Duplicate column names are disambiguated with suffixes such as _2 and _3:

conn
|> QuackDB.maps("SELECT i AS n FROM range(0, ?) t(i)", [50_000])
|> Enum.take(2)
#=> [%{"n" => 0}, %{"n" => 1}]

Use QuackDB.columnar/4 when an analytical workflow wants vectors plus column order and metadata:

{:ok, columns} = QuackDB.columnar(conn, "SELECT id, name FROM events ORDER BY id")

columns.names
#=> ["id", "name"]

columns["id"]
#=> [1, 2]

QuackDB.columns/4 returns just the column map:

{:ok, columns} = QuackDB.columns(conn, "SELECT id, name FROM events ORDER BY id")

columns
#=> %{"id" => [1, 2], "name" => ["duck", "goose"]}

For large results, QuackDB.columnar_batches/4 streams QuackDB.Columns fetch batches without materializing the whole result set. QuackDB.column_batches/4 returns just the map from each batch:

conn
|> QuackDB.column_batches("SELECT i AS n FROM range(0, 50_000) t(i)", [], max_rows: 1_000)
|> Enum.take(1)
#=> [%{"n" => [0, 1, 2, ...]}]

This is not Arrow IPC yet, but it exposes a column-oriented shape that can back future Arrow integration without changing the query API.

Convert results to Explorer DataFrames

When :explorer is available, QuackDB exposes optional helpers for building Explorer.DataFrame values from query results:

Ecto queries can be passed directly when you already have schemas or source helpers:

import Ecto.Query

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

source = Source.csv("events.csv", header: true)

query =
  from event in source,
    where: event.id > ^1,
    select: %{id: event.id, name: event.name}

{:ok, df} = QuackExplorer.dataframe(conn, query)

The Explorer integration materializes query results in Elixir before constructing a dataframe. It is useful for interactive analysis and downstream Explorer pipelines, but it is not a zero-copy Arrow IPC path yet.

Explorer dataframes can also be appended through Quack's native column-oriented append path:

alias QuackDB.Explorer, as: QuackExplorer

QuackExplorer.insert_dataframe!(conn, "events_copy", df, batch_size: 10_000)

You can also convert existing results:

alias QuackDB.Explorer, as: QuackExplorer

{:ok, result} = QuackDB.query(conn, "SELECT 1 AS id, 'duck' AS name")
{:ok, df} = QuackExplorer.from_result(result)

Session-scoped DuckDB objects

Each DBConnection pool worker is a separate DuckDB session. Temporary tables are visible only to the session that created them. With a pool, do not create one in a standalone call and use it in another:

use QuackDB.Ecto
alias MyApp.AnalyticsRepo, as: Repo
alias QuackDB.DDL

Repo.query!(DDL.create_table("events", [id: :integer], temporary: true))
Repo.all(from event in "events", select: event.id) # may use another session

Keep the complete create/use/drop or create/index/query workflow in one transaction:

Repo.transaction(fn ->
  Repo.query!(DDL.create_table("events", [id: :integer], temporary: true))
  Repo.insert_all("events", [%{id: 1}])
  ids = Repo.all(from event in "events", select: event.id)
  Repo.query!(DDL.drop_table("events"))
  ids
end)

Use a persistent table when the workflow must span calls. Temporary secrets are different: they are shared within the DuckDB instance but are not persisted across restarts. pool_size: 1 can hide session switching but is not a substitute for keeping a session-scoped workflow together. Committing a transaction does not drop temporary tables; drop them explicitly before releasing the connection when they are no longer needed.

Work with command results

DuckDB returns affected counts as a Count result column for DML statements. QuackDB normalizes those into num_rows:

alias QuackDB.{DDL, DML}

{:ok, result} =
  DBConnection.transaction(conn, fn tx ->
    QuackDB.query!(tx, DDL.create_table("events", [id: :integer], temporary: true))
    result = QuackDB.query!(tx, DML.insert_into("events", [[id: 1], [id: 2]]))
    QuackDB.query!(tx, DDL.drop_table("events"))
    result
  end)

result.command
#=> :insert

result.num_rows
#=> 2

result.columns
#=> nil

result.rows
#=> nil

The raw DuckDB count result stays available for debugging:

result.metadata[:duckdb_columns]
#=> ["Count"]

result.metadata[:duckdb_rows]
#=> [[2]]

Append rows

QuackDB.insert_rows/4 uses Quack's append protocol to send a DuckDB DataChunk directly to a table:

alias QuackDB.DDL

QuackDB.query!(conn, DDL.create_table("events", [id: :integer, name: :varchar, active: :boolean]))

{:ok, result} =
  QuackDB.insert_rows(conn, "events", [
    [id: 1, name: "duck", active: true],
    [id: 2, name: "goose", active: false]
  ])

result.command
#=> :insert

result.num_rows
#=> 2

Keyword rows preserve append order and allow QuackDB to infer the columns from the rows: every row's keys become columns, and each column's type fits all of its values. Map rows are also accepted, but pass :columns for stable append order and types. Explicit columns are still required for empty batches or all-null columns. Use batch_size: n to split large inputs across multiple append requests while returning the total inserted row count.

Native append columns can be declared with scalar QuackDB.Type specs and nested specs such as {:list, :varchar}, {:struct, [source: :varchar, count: :integer]}, {:array, :integer, 3}, and {:map, :varchar, :varchar}. Explicit MAP columns accept ordinary Elixir maps:

QuackDB.insert_rows!(conn, "events", [[id: 1, labels: %{env: "prod", region: "eu"}]],
  columns: [id: :integer, labels: {:map, :varchar, :varchar}]
)

Plain Elixir maps without an explicit MAP column spec infer as DuckDB STRUCT values. Temporal append values are normalized through Elixir's Calendar-aware Date, Time, NaiveDateTime, and DateTime conversion APIs before encoding.

Full-text search helpers

DuckDB's fts extension can index text columns and expose BM25 ranking.

alias QuackDB.FTS

MyApp.AnalyticsRepo.query!(FTS.install())
MyApp.AnalyticsRepo.query!(FTS.load())

MyApp.AnalyticsRepo.query!(
  FTS.create_index("documents", :id, [:title, :body], overwrite: true)
)

from doc in "documents",
  where: match_bm25("fts_main_documents", doc.id, ^"duckdb analytics") > 0,
  order_by: [desc: match_bm25("fts_main_documents", doc.id, ^"duckdb analytics")],
  select: %{id: doc.id, title: doc.title}

See the full-text search guide for direct SQL and Ecto usage.

Spatial helpers

DuckDB's spatial extension can be loaded with SQL helpers. Use Ecto spatial helpers when you want to keep spatial expressions inside Ecto queries:

use QuackDB.Ecto

alias QuackDB.Spatial

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

point = %Geo.Point{coordinates: {1.0, 2.0}, srid: nil}

from(place in "places",
  where: intersects(place.geom, ^point),
  select: as_text(place.geom)
)

DuckDB GEOMETRY values decode as WKB-compatible bytes. Add optional {:geo, "~> 4.1"} when you want to convert those bytes to Geo structs with QuackDB.Geometry.decode_wkb!/1, or pass %Geo.*{} structs as Ecto parameters.

Inspect output in IEx

QuackDB implements compact inspection for common structs so manual review stays readable:

QuackDB.query!(conn, "SELECT i FROM range(0, 4) t(i)")
#QuackDB.Result<command: :select, columns: ["i"], rows: 4, preview: [[0], [1], [2], :...], connection_id: "...", needs_more_fetch?: false>

The actual rows are still available through result.rows.

Transactions

QuackDB implements DBConnection transaction callbacks with SQL statements:

alias QuackDB.{DDL, DML}

DBConnection.transaction(conn, fn tx ->
  QuackDB.query!(tx, DDL.create_table("tx_events", [id: :integer], temporary: true))
  QuackDB.query!(tx, DML.insert_into("tx_events", id: 1))
end)

Ecto raw SQL

QuackDB includes an initial Ecto SQL adapter for raw SQL queries. The Ecto adapter is compiled when ecto_sql is available, so add Ecto SQL if your app does not already depend on it:

def deps do
  [
    {:quackdb, "~> 0.7.0"},
    {:ecto_sql, "~> 3.13"}
  ]
end

Then define a repo:

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

Configure the repo with the same options accepted by QuackDB.start_link/1:

config :my_app, MyApp.AnalyticsRepo,
  uri: "http://[::1]:9494",
  token: "super_secret"

Generated DDL and setup-oriented DML can participate in Ecto transactions:

{:ok, :committed} =
  MyApp.AnalyticsRepo.transaction(fn ->
    MyApp.AnalyticsRepo.query!(
      QuackDB.DDL.create_table("events", [id: :integer])
    )

    MyApp.AnalyticsRepo.query!(QuackDB.DML.insert_into("events", [[id: 1], [id: 2]]))
    :committed
  end)

Use Repo.rollback/1 to abort transaction work:

{:error, :rolled_back} =
  MyApp.AnalyticsRepo.transaction(fn ->
    MyApp.AnalyticsRepo.query!(QuackDB.DML.insert_into("events", id: 3))
    MyApp.AnalyticsRepo.rollback(:rolled_back)
  end)

Read-only Ecto queries against table names are also supported, including CTEs, window functions, joins, grouping, having, distinct, aggregate FILTER, arithmetic expressions, in/2, predicates, ordering, limits, aggregates, fragments, and DuckDB analytical helpers:

use QuackDB.Ecto

MyApp.AnalyticsRepo.all(
  from event in "events",
    where: event.id > ^min_id and like(event.name, "d%"),
    group_by: event.category,
    select: %{
      category: event.category,
      median_score: median(event.score),
      p95_score: quantile_cont(event.score, 0.95),
      scores: list(event.score),
      high_score_events: filter(count(event.id), event.score > 100)
    }
)

Build date spines and timestamp buckets with Elixir-native Date.Range and Duration values:

use QuackDB.Ecto

MyApp.AnalyticsRepo.all(
  from day in series(Date.range(~D[2024-01-01], ~D[2024-01-31])),
    left_join: event in "events",
    on: event.occurred_on == day.value,
    group_by: day.value,
    order_by: day.value,
    select: %{day: day.value, events: count(event.id)}
)

interval = Duration.new!(hour: 1)
origin = ~N[2024-01-01 00:00:00]

MyApp.AnalyticsRepo.all(
  from event in "events",
    group_by: selected_as(:bucket),
    select: %{
      bucket: selected_as(time_bucket(^interval, event.occurred_at, origin: ^origin), :bucket),
      events: count()
    }
)

Text and regex helpers keep DuckDB string predicates in the query DSL. DuckDB regexes use RE2; ~r literals are convenient for the syntax shared with Elixir regexes, and contains/2 dispatches obvious text calls to DuckDB contains while spatial helper calls go to ST_Contains. Ambiguous calls raise; use contains_text/2 or st_contains/2 when intent is not obvious:

use QuackDB.Ecto

MyApp.AnalyticsRepo.all(
  from event in "events",
    where: contains(event.name, "duck") and regexp_matches(event.name, ~r/^duck/i),
    select: %{
      slug: regexp_replace(event.name, ~r/\s+/, "-", "g"),
      tags: string_split(event.tags, ",")
    }
)

LIST columns can use helpers for containment, intersection, all-required matching, extraction, sorting, slicing, lambda filtering/transforms/reductions, and unnesting. use QuackDB.Ecto imports list helpers by default; contains_list/2 and intersect_list/2 avoid ambiguity with shared predicate/set-operation names. Lambda helpers accept a constrained Elixir fn subset: lambda variables, pinned values, literals, arithmetic, comparisons, boolean operators, rem/2, is_nil/1, and case_when do ... end. Unsupported calls raise during query compilation instead of producing lossy SQL.

use QuackDB.Ecto

MyApp.AnalyticsRepo.all(
  from fragment in "fragments",
    where: contains_list(fragment.terms, ^term_id) and has_any(fragment.terms, ^optional_term_ids),
    select: %{
      id: fragment.id,
      term_count: list_length(fragment.terms),
      first_term: extract(fragment.terms, 1),
      matching_terms: intersect_list(fragment.terms, ^optional_term_ids),
      large_terms: list_filter(fragment.terms, fn term -> term > ^min_term_id end),
      doubled_terms: list_transform(fragment.terms, fn term -> term * 2 end),
      term_labels:
        list_transform(fragment.terms, fn term ->
          case_when do
            term >= 100 -> "large"
            true -> "small"
          end
        end),
      term_total: list_reduce(fragment.terms, fn total, term -> total + term end, 0),
      term: unnest(fragment.terms)
    }
)

MAP and STRUCT columns can use focused helpers for key/value inspection and field extraction. With use QuackDB.Ecto, use explicit aliases for helpers that would otherwise conflict with list/text/spatial helpers.

use QuackDB.Ecto

MyApp.AnalyticsRepo.all(
  from event in "events",
    where: contains_map(event.labels, ^"env") and contains_struct(event.metadata_tuple, ^"duck"),
    select: %{
      label_keys: map_keys(event.labels),
      env: map_extract_value(event.labels, ^"env"),
      name: struct_extract(event.metadata, ^"name")
    }
)

JSON columns can use Ecto access syntax for string extraction, type/2 for numeric/boolean casts, or explicit DuckDB JSON helpers:

use QuackDB.Ecto

MyApp.AnalyticsRepo.all(
  from event in "events",
    where: event.payload["user"]["name"] == "duck" and type(event.payload["score"], :integer) > 10,
    select: %{
      name: event.payload["user"]["name"],
      active: type(event.payload["active"], :boolean),
      score: json_extract(event.payload, [:scores, 0]),
      has_name: json_exists(event.payload, [:user, :name])
    }
)

DuckDB's QUALIFY clause is not part of Ecto's query AST. Use an Ecto subquery when filtering window function results:

ranked =
  from event in "events",
    windows: [by_category: [partition_by: event.category, order_by: [desc: event.score]]],
    select: %{
      id: event.id,
      category: event.category,
      score: event.score,
      rank: over(row_number(), :by_category)
    }

MyApp.AnalyticsRepo.all(
  from event in subquery(ranked),
    where: event.rank <= 3,
    order_by: [event.category, event.rank]
)

Use case_when for multi-branch DuckDB CASE WHEN expressions inside queries:

use QuackDB.Ecto

MyApp.AnalyticsRepo.all(
  from event in "events",
    group_by: [date_part(:hour, event.occurred_at), selected_as(:tier)],
    select: %{
      hour: date_part(:hour, event.occurred_at),
      tier:
        selected_as(
          case_when do
            event.score >= 90 -> "high"
            event.score >= 50 and event.score <= 89 -> "medium"
            true -> "normal"
          end,
          :tier
        ),
      events: count(),
      distinct_events: count(event.id, :distinct),
      high_events: filter(count(event.id), event.score >= 90),
      average_score: coalesce(avg(event.score), 0),
      precise_score_sum: fsum(event.score),
      mode_score: mode(event.score),
      weighted_score: weighted_avg(event.score, event.weight),
      score_stddev: stddev(event.score),
      score_variance: variance(event.score),
      score_entropy: entropy(event.score),
      score_histogram: histogram(event.score),
      ordered_scores: list(event.score, order_by: [desc_nulls_last: event.score])
    }
)

Profile an Ecto queryable with DuckDB SUMMARIZE when you need a quick statistical overview:

query =
  from event in "events",
    where: event.score > 0,
    select: %{category: event.category, score: event.score}

QuackDB.Ecto.Analytics.summarize!(MyApp.AnalyticsRepo, query)

For table/source names or raw SQL outside Ecto, use the direct SQL helper:

MyApp.AnalyticsRepo.query!(QuackDB.Analytics.summarize("events"))
MyApp.AnalyticsRepo.query!(QuackDB.Analytics.summarize({:query, "SELECT score FROM events"}))

Use QuackDB.Profile when you need DuckDB query-plan/operator timings instead of column statistics:

profile =
  QuackDB.Profile.analyze!(MyApp.AnalyticsRepo,
    "SELECT category, avg(score) FROM events GROUP BY category"
  )

QuackDB.Profile.slowest(profile, 5)
IO.puts(QuackDB.Profile.report(profile))

Inspect DuckDB's physical storage and compression choices with QuackDB.Storage:

QuackDB.Storage.info!(MyApp.AnalyticsRepo, "events")
QuackDB.Storage.compression!(MyApp.AnalyticsRepo, "events")
QuackDB.Storage.database_size!(MyApp.AnalyticsRepo)

info!/2 accepts schema modules as well as table names:

QuackDB.Storage.compression!(MyApp.AnalyticsRepo, MyApp.Event)

Use QuackDB.Meta for catalog metadata:

QuackDB.Meta.tables!(MyApp.AnalyticsRepo)
QuackDB.Meta.tables!(MyApp.AnalyticsRepo, expanded: true)
QuackDB.Meta.table_info!(MyApp.AnalyticsRepo, MyApp.Event)
QuackDB.Meta.databases!(MyApp.AnalyticsRepo)

Ecto insert/2 and insert_all/3 are supported for straightforward row inserts. DuckDB RETURNING works through the SQL insert path:

MyApp.AnalyticsRepo.insert!(%Event{id: 1, name: "duck"})

MyApp.AnalyticsRepo.insert_all(
  "events",
  [[id: 2, name: "goose"]],
  returning: [:id]
)

Use insert_method: :append to stream bulk insert_all rows as binary vectors instead of generated SQL. This path is explicit: ordinary insert_all keeps using Ecto SQL generation unless you pass the option.

MyApp.AnalyticsRepo.insert_all(
  Event,
  [[name: "duck"], [name: "goose"]],
  insert_method: :append,
  chunk_every: 10_000
)

For schema-backed inserts, QuackDB derives append types from the Ecto schema. That means nullable columns can be all nil in a batch without passing manual :columns, and omitted schema fields are filled by DuckDB defaults or generated values. The rows stream into a single INSERT, so on_conflict: :nothing and RETURNING work directly:

MyApp.AnalyticsRepo.insert_all(
  Event,
  [[name: "duck"], [name: "goose"]],
  insert_method: :append,
  returning: [:id]
)

The append insert path does not support query inserts, placeholders, or upserts/conflict targets. For streaming rows outside Ecto, use QuackDB.insert_stream!/4; it can take either a QuackDB connection or a QuackDB-backed Ecto repo.

For temporary analytical setup, QuackDB.DDL.create_table/3 builds quoted DuckDB CREATE TABLE and CREATE TABLE AS statements. Temporary tables are session-scoped; if the table is used by later statements, keep the workflow in one DBConnection.transaction/3 or Repo.transaction/2. Use or_replace: true for DuckDB table rewrites, and pass an Ecto schema as the second argument when you want a temporary staging table with the same columns under a different name:

MyApp.AnalyticsRepo.transaction(fn ->
  MyApp.AnalyticsRepo.query!(
    QuackDB.DDL.create_table("events", [payload: :json, occurred_at: :timestamp], temporary: true)
  )

  source = QuackDB.Source.parquet("s3://bucket/events/*.parquet")

  query =
    from event in source,
      select: %{id: event.id, name: event.name}

  MyApp.AnalyticsRepo.query!(
    QuackDB.DDL.create_table("events_from_parquet", as: query, temporary: true)
  )

  # Run the analysis here, while both temporary tables are visible.
  MyApp.AnalyticsRepo.query!(QuackDB.DDL.drop_table("events_from_parquet"))
  MyApp.AnalyticsRepo.query!(QuackDB.DDL.drop_table("events"))
end)

MyApp.AnalyticsRepo.query!(
  QuackDB.DDL.create_table("sorted_fragment_terms",
    as: from(term in "fragment_terms", distinct: true, order_by: [term.term_id, term.fragment_id]),
    or_replace: true
  )
)

MyApp.AnalyticsRepo.transaction(fn ->
  MyApp.AnalyticsRepo.query!(
    QuackDB.DDL.create_table("temp_fragments", MyApp.Fragment, temporary: true)
  )

  # Populate and use the temporary table here, before the transaction ends.
  MyApp.AnalyticsRepo.query!(QuackDB.DDL.drop_table("temp_fragments"))
end)

DDL.create_table/2 rejects parameterized Ecto queries in :as because DDL helpers return SQL iodata, not {sql, params} tuples.

For staging-table dedupe, keep the materialization step in normal Ecto and pass the query to insert_all/3. Run the following inside the same transaction that creates and populates temp_fragments:

staged_new_rows =
  from row in "temp_fragments",
    as: :row,
    where:
      not exists(
        from target in "fragments",
          where: target.content_hash == parent_as(:row).content_hash,
          select: 1
      ),
    select: %{
      content_hash: row.content_hash,
      ast: row.ast,
      kind: row.kind
    }

MyApp.AnalyticsRepo.insert_all("fragments", staged_new_rows,
  returning: [:id, :content_hash]
)

Ecto support covers analytical reads and common write/setup flows. Repo.query/3, schema-backed reads, combinations, inserts/upserts, insert-from-query with returning, schema update/delete callbacks, update_all / delete_all mutations, EXPLAIN, transactions, and basic migrator-backed DDL work; advanced migration features and DuckDB-specific SQL should still use Repo.query/3.

Runtime filters and exact decimal casts

dynamic/2 is Ecto's built-in expression composition API. Use it to build filters from runtime JSON field names and types. For exact decimal comparisons, QuackDB.Ecto.Decimal retains explicit precision and scale in SQL casts:

use QuackDB.Ecto
alias QuackDB.Ecto.Decimal, as: DuckDecimal

decimal = Ecto.ParameterizedType.init(DuckDecimal, precision: 18, scale: 4)
field = "estimate"
minimum = Decimal.new("9.5")
estimate = dynamic([t], type(t.fields[^field], ^decimal))
predicate = dynamic([t], ^estimate > ^minimum)

from t in "tasks", where: ^predicate

Bare :decimal casts use DuckDB's default DECIMAL(18,3), which rounds 9.5001 to 9.500. Choose precision/scale appropriate to your data; explicit casts still follow DuckDB rounding and overflow rules. Invalid stored values raise conversion errors; missing JSON fields and JSON nulls remain NULL. The parameterized type controls query casts, not migration column definitions; continue specifying migration precision: and scale: explicitly.

For literal substring searches, keep the existing contains name:

from t in "tasks",
  where: contains(t.title, ^search, case_sensitive: false)

contains/3 is text-only and accepts a literal case_sensitive: true or false option. The insensitive form compares DuckDB-lowercased strings, not full Unicode case-folded strings. %, _, and backslashes are literal; an empty search matches and NULL operands produce NULL. Existing contains/2 text/spatial dispatch is unchanged.

Basic Ecto migrations

QuackDB implements the Ecto migration DDL callbacks needed for common analytical setup migrations:

defmodule MyApp.Repo.Migrations.CreateEvents do
  use Ecto.Migration

  def change do
    create table(:events, primary_key: false) do
      add(:id, :integer, primary_key: true)
      add(:name, :string, null: false)
      add(:score, :integer, default: 0)
    end

    create(index(:events, [:name]))
  end
end

Supported DDL includes create/drop/alter table, add/modify/drop columns, references in CREATE TABLE, ordinary and unique indexes, primary keys, composite primary keys, and table/column renames. Inline CHECK constraints belong in the initial CREATE TABLE and can be built safely with QuackDB.DDL.check/1.

DuckDB-incompatible options such as concurrent indexes, covering indexes, exclude constraints, constraint comments, and NOT VALID constraints raise explicit QuackDB errors.

DuckDB does not support adding a column with a CHECK or REFERENCES constraint. QuackDB therefore rejects or propagates the engine error rather than silently dropping the constraint. For Ecto add(:column, ..., null: false), QuackDB emits ADD COLUMN and then SET NOT NULL; the second statement can fail if other tables depend on the altered table. A transactional migration rolls back both statements. Declare required constraints when creating the table, before creating dependent tables.

DuckDB does not support ALTER TABLE ADD CONSTRAINT or DROP CONSTRAINT, so the Ecto create constraint(...) and drop constraint(...) forms raise explicit unsupported-feature errors. Use QuackDB.DDL.check/1 with create_table/3 inside a reversible migration:

import QuackDB.DDL, only: [create_table: 3, drop_table: 1, check: 1]

execute(
  fn ->
    repo().query!(
      create_table(:events,
        [
          {:id, :integer, primary_key: true},
          {:name, :varchar, null: false},
          {:score, :integer, default: 0}
        ],
        check(score >= 0)
      )
    )
  end,
  fn -> repo().query!(drop_table(:events)) end
)

Pass one combined constraint or a list of separate constraints:

import QuackDB.DDL, only: [create_table: 3, check: 1]

maximum = 100

create_table(:events, [score: :integer],
  check(score >= 0 and score < ^maximum)
)

create_table(:events, [score: :integer],
  [check(score >= 0), check(score < ^maximum)]
)

Bare identifiers refer to columns; pin runtime values with ^. Use field("column name") or field(^name) for other identifiers. Comparisons, and, or, not, and is_nil/1 are supported, with identifiers and literal values encoded separately. Table options can follow check values in a list, such as [check(score >= 0), temporary: true]. Raw predicates, arithmetic, arbitrary function calls, and CHECKs on CREATE TABLE AS are rejected. CHECK permits NULL when its expression is unknown; also declare null: false if NULL must be rejected. More complex checks require explicit inline SQL through execute/2.

Adding a column with null: false uses ADD COLUMN followed by SET NOT NULL. DuckDB can refuse the second statement when other tables reference the altered table. QuackDB propagates this dependency error; the default transactional Ecto migration rolls back the added column and does not record the migration as applied. Outside a transaction, the first statement can remain applied. For new tables, declare required columns in the initial CREATE TABLE before creating dependent tables; QuackDB does not silently remove NOT NULL or automatically rebuild existing tables.

Constraint violations currently raise database errors rather than returning changeset errors through unique_constraint/3, foreign_key_constraint/3, or check_constraint/3. DuckDB's violation messages do not reliably identify the constraint name Ecto needs; QuackDB does not guess names.

DuckDB's catalog does not solve this reliably: supplied CHECK names are not preserved, and multiple checks on the same column can share a generated catalog name. A failed write also prevents catalog queries in the same aborted transaction. For these reasons, QuackDB does not automatically rebuild a table to drop a named constraint. Use an explicit SQL migration with a reviewed replacement table definition, preserving data, indexes, and dependent objects.

Current limitations

Server-side bind parameters are not exposed by this Quack client path yet. QuackDB formats supported parameter values as DuckDB SQL literals client-side.
Native appends support row and column batches but not Arrow IPC or automatic local-file/data staging yet.
Ecto coverage focuses on analytical reads and common write/setup workflows, not every relational adapter feature.
QuackDB speaks Quack protocol version 3, which DuckDB 2.0 serves; it refuses servers that select another version. QuackDB pins the DuckDB version its managed binary downloads.

Supervision and connection options

Use QuackDB under your application supervisor when you want a long-lived connection pool:

children = [
  {QuackDB,
   uri: "http://[::1]:9494",
   token: "super_secret",
   name: MyApp.QuackDB,
   pool_size: 5}
]

The client accepts QuackDB options such as :uri, :token, and :transport, plus DBConnection pool options such as :name, :pool_size, :queue_target, :queue_interval, and per-call :timeout.

QuackDB.query(MyApp.QuackDB, "SELECT 1", [], timeout: 10_000)

Decoded strings and blobs of 64 bytes or more are slices of the HTTP response they arrived in, so a process that keeps a few of them from a large result keeps the whole response in memory. Pass copy_binaries: true, per query or in the Repo or connection configuration, to copy them off the response. It about doubles decode time for string columns and costs nothing for other columns:

latest = from(event in Event, order_by: [desc: event.inserted_at], limit: 10, select: event.payload)

MyApp.AnalyticsRepo.all(latest, copy_binaries: true)

See the QuackDB module documentation for the heartbeat, retry, and acknowledgement options.

For local development, tests, or notebooks, QuackDB can also supervise a local DuckDB Quack server process with MuonTrap:

children =
  QuackDB.Server.child_specs(
    server: [
      name: MyApp.DuckDB,
      duckdb: :managed,
      endpoint: "quack:localhost:9494",
      settings: [threads: System.schedulers_online()],
      global_settings: [quack_target_batch_bytes: 8 * 1024 * 1024]
    ],
    client: [name: MyApp.QuackDB, pool_size: System.schedulers_online()]
  )

QuackDB.Server starts the external duckdb executable and serves the Quack protocol. It is a convenience process supervisor, not an embedded DuckDB driver and not required for remote DuckDB servers. Use duckdb: :managed for local development convenience, or omit it to use duckdb from PATH.

The local server defaults to settings: [threads: System.schedulers_online()] and no global settings. DuckDB 2.0 cuts result batches by size: quack_target_batch_bytes (32 MiB by default) bounds one fetched batch, so lower it to bound client memory per fetch, and quack_prepare_inline_rows sets how many rows the first response carries. Use a smaller client pool_size such as 1..4 for heavy analytical scans, because DuckDB parallelizes each query internally. Use System.schedulers_online() for many small concurrent queries.

serve_options passes quack_serve options through. DuckDB 2.0 only listens on localhost unless allow_other_hostname: true, and serves HTTPS for any other host unless disable_ssl: true; ssl_cert_file and ssl_key_file select the certificate. The derived client URI follows the same rules. For anything reachable from other machines, DuckDB recommends a TLS-terminating reverse proxy in front of a localhost server instead.

QuackDB.Server.child_specs(
  server: [endpoint: "quack:10.0.0.5:9494", serve_options: [allow_other_hostname: true, disable_ssl: true]],
  client: [pool_size: 2]
)

The server runs the CLI with -no-agent, so DuckDB 2.0's agent output mode stays off even when the BEAM runs under a coding agent.

Running QuackDB's integration tests

With a server running locally:

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

← Previous Page Contributing

Next Page → Ecto