Relation API Guide

View Source

Relations are lazy query builders. They build SQL but do not execute until you call a fetch method.

Create Relations

{:ok, conn} = DuckdbEx.Connection.connect(:memory)
rel = DuckdbEx.Connection.table(conn, "orders")
rel = DuckdbEx.Connection.sql(conn, "SELECT * FROM orders")
rel = DuckdbEx.Connection.values(conn, [1, "a"])
rel = DuckdbEx.Connection.values(conn, [{1, "a"}, {2, "b"}])

Build Queries

rel =
  rel
  |> DuckdbEx.Relation.filter("amount > 100")
  |> DuckdbEx.Relation.project(["customer", "amount"])
  |> DuckdbEx.Relation.order("amount DESC")
  |> DuckdbEx.Relation.limit(10)

Use an offset when you need to skip rows:

rel |> DuckdbEx.Relation.limit(10, 5)

Execute

{:ok, rows} = DuckdbEx.Relation.fetch_all(rel)

Rows are tuples in column order.

Aggregations

rel =
  rel
  |> DuckdbEx.Relation.aggregate(
    ["sum(amount) as total", "count(*) as cnt"],
    group_by: ["region"]
  )

Joins & Set Operations

joined = DuckdbEx.Relation.join(rel1, rel2, "rel1.id = rel2.id", type: :left)
unioned = DuckdbEx.Relation.union(rel1, rel2)

Create/Insert/Update

rel = DuckdbEx.Connection.sql(conn, "SELECT 1 AS a, 'x' AS b")
DuckdbEx.Relation.create(rel, "table_from_rel")
DuckdbEx.Relation.create_view(rel, "view_from_rel")

table = DuckdbEx.Connection.table(conn, "table_from_rel")
DuckdbEx.Relation.insert(table, [2, "y"])
DuckdbEx.Relation.update(table, %{"b" => "z"}, "a = 2")