Ecto

The optional Ecto SQL adapter gives applications Ecto query composition, schema reads and writes, migrations, and raw SQL through Repo.query/3, all over QuackDB's pooled DBConnection sessions.

Repo

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

Pair the Repo with a supervised server through QuackDB.Server.child_specs/1 (see Managed DuckDB), or configure uri: and token: for a server that runs elsewhere:

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

DuckDB workflows as Ecto queries

use QuackDB.Ecto imports DuckDB's analytical helpers as macros that compose with normal queries.

Analytical aggregates

defmodule MyApp.Analytics do
  use QuackDB.Ecto

  def category_scores do
    from event in "events",
      group_by: event.category,
      select: %{
        category: event.category,
        p95: quantile_cont(event.duration_ms, 0.95),
        median: median(event.duration_ms),
        precise_sum: fsum(event.duration_ms),
        mode: mode(event.duration_ms),
        weighted_average: weighted_avg(event.duration_ms, event.weight),
        values: list(event.duration_ms, order_by: [desc_nulls_last: event.duration_ms]),
        slow_events: filter(count(event.id), event.duration_ms > 1_000),
        distinct_users: count(event.user_id, :distinct),
        average_duration: coalesce(avg(event.duration_ms), 0),
        events: count()
      }
  end
end

Date and timestamp series

use QuackDB.Ecto

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)
  }

Timestamp series use Duration steps:

from bucket in series(
       ~N[2024-01-01 00:00:00],
       ~N[2024-01-02 00:00:00],
       step: Duration.new!(hour: 1)
     ),
  select: bucket.value

Source scans

DuckDB can query data where it already lives. QuackDB source helpers can be used directly as Ecto sources.

use QuackDB.Ecto

alias QuackDB.Source

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

from event in source,
  group_by: event.category,
  select: %{
    category: event.category,
    events: count(),
    avg_score: avg(event.score)
  }

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.

CTAS and full-text search

External data can be materialized with CREATE TABLE AS, indexed with DuckDB FTS, and queried with BM25 from Ecto.

use QuackDB.Ecto

alias QuackDB.{DDL, FTS, Source}

query =
  from doc in Source.parquet("s3://bucket/docs/*.parquet"),
    select: %{
      id: doc.id,
      title: doc.title,
      body: doc.body
    }

MyApp.AnalyticsRepo.query!(DDL.create_table("docs", as: query))
MyApp.AnalyticsRepo.query!(FTS.create_index("docs", :id, [:title, :body], overwrite: true))

schema = FTS.schema_name("main.docs")
search = "duckdb analytics"

from doc in "docs",
  where: bm25(^schema, doc.id, ^search) > 0,
  order_by: [desc: bm25(^schema, doc.id, ^search)],
  limit: 10,
  select: %{
    id: doc.id,
    title: doc.title,
    score: bm25(^schema, doc.id, ^search)
  }

See the full-text search guide.

Text and regex predicates

DuckDB text and RE2 regular-expression helpers compose with Ecto filters and aggregate FILTER clauses. Shared contains/2 dispatches obvious string calls to DuckDB contains and spatial helper expressions to ST_Contains; ambiguous calls raise so contains_text/2 and st_contains/2 are available when you want to be explicit.

use QuackDB.Ecto

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"),
    parts: string_split(event.tags, ",")
  }

DuckDB regexes use RE2, so ~r literals are intended for the syntax subset shared with Elixir regexes.

JSON values and variables

DuckDB 2.0's json_set/3, json_insert/3, json_replace/3, and json_remove/2 change JSON documents in place, so they work in update_all as well as in selects. Paths are lists or JSON path strings, and values are converted with to_json:

use QuackDB.Ecto

from(event in "events",
  where: event.id == ^id,
  update: [set: [payload: json_remove(json_set(event.payload, [:status], "archived"), [:draft])]]
)
|> MyApp.AnalyticsRepo.update_all([])

variable/1 reads a DuckDB variable as $name. Variables belong to one connection, so set and read them in one checkout:

alias QuackDB.SQL

MyApp.AnalyticsRepo.checkout(fn ->
  MyApp.AnalyticsRepo.query!(SQL.set_variable(:min_score, 10))
  MyApp.AnalyticsRepo.all(from event in "events", where: event.score >= variable(:min_score), select: event.id)
end)

VARIANT columns have their own helpers, such as variant_extract/2 and variant_type/1; see Type support.

Pivoting and grouping extensions

DuckDB statement-level syntax such as PIVOT, UNPIVOT, GROUPING SETS, ROLLUP, and CUBE has builders in QuackDB.SQL:

MyApp.AnalyticsRepo.query!(QuackDB.SQL.pivot(:events,
  on: :kind,
  using: [sum: :n]
))

QuackDB.SQL.grouping_sets([[:category, :kind], [:category], []])
QuackDB.SQL.rollup([:category, :kind])
QuackDB.SQL.cube([:category, :kind])

List predicates

DuckDB LIST/ARRAY helpers map directly to common list functions such as list_contains, list_has_any, list_has_all, len, list_extract, list_sort, list_intersect, list_filter, list_transform, list_reduce, and unnest. use QuackDB.Ecto imports non-conflicting list helpers by default; use contains_list/2 and intersect_list/2 to avoid ambiguity with text/spatial contains/2 and Ecto set-operation intersect/2.

use QuackDB.Ecto

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 helpers follow the same pattern: natural names are available from focused imports, while use QuackDB.Ecto exposes explicit aliases for ambiguous helpers.

use QuackDB.Ecto

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")
  }

Spatial queries

DuckDB Spatial works with Ecto queries and %Geo.*{} structs when the optional :geo package is installed.

use QuackDB.Ecto

import QuackDB.Ecto.Spatial

alias QuackDB.Spatial

MyApp.AnalyticsRepo.query!(Spatial.load())

point = %Geo.Point{coordinates: {13.405, 52.52}, srid: nil}

from place in "places",
  where: intersects(place.geom, ^point) and distance(place.geom, ^point) < 1_000,
  select: %{
    id: place.id,
    name: place.name,
    wkt: as_text(place.geom)
  }

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 and the Spatial WMS example.

Coverage

The adapter 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, fragments, and QuackDB helper macros;
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, including schema-backed subset columns/defaults and RETURNING;
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. References and CHECK constraints are supported inline during CREATE TABLE; DuckDB rejects adding a column with those constraints.

Statements that Ecto does not model have Elixir builders: QuackDB.SQL for PIVOT, UNPIVOT, GROUPING SETS, ROLLUP, CUBE, and EXPLAIN; QuackDB.DDL for tables, sequences, and CREATE TABLE AS; QuackDB.DML for inserts, deletes, and MERGE INTO; QuackDB.Analytics for SUMMARIZE. Their output goes to Repo.query/2. See the Ecto coverage matrix.

Migrations

Ecto migrations run through the adapter: create, alter, and drop tables, columns, references, indexes, primary keys, check constraints, and renames. References and CHECK constraints go inline in create table; DuckDB does not add them to an existing column.

DuckDB adds a null: false column in two statements, and the second cannot run in the transaction that ran the first. A migration that adds a NOT NULL column sets @disable_ddl_transaction true; inside the DDL transaction the adapter refuses it with an :ecto_feature_not_supported error that says so.

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

  @disable_ddl_transaction true

  def change do
    alter table(:events) do
      add :archived, :boolean, default: false, null: false
    end
  end
end

DDL that the migration DSL has no form for, such as nested schemas and DuckDB 2.0 triggers, comes from QuackDB.DDL; execute takes its builders directly:

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

  alias QuackDB.DDL

  def up do
    execute DDL.create_schema(:finance)
    execute DDL.create_schema([:finance, :reports])
    execute DDL.create_table({[:finance, :reports], :ledger}, amount: :integer)

    execute DDL.create_trigger(:count_events, :events,
      "UPDATE event_stats SET total = total + (SELECT count(*) FROM inserted)",
      event: :insert,
      referencing: [new_table: :inserted]
    )
  end

  def down do
    execute DDL.drop_trigger(:count_events, :events)
    execute DDL.drop_schema(:finance, cascade: true)
  end
end

Ecto queries reach tables in a nested schema through a dotted prefix, such as @schema_prefix "finance.reports".

Types

UUIDs, binaries, JSON maps, decimals with explicit precision, and the non-finite floats all have documented representations; see Type support.

← Previous Page Getting started

Next Page → Writes