Dynamic queries

Elixir’s Ecto was designed around an expressive query API that leverages macros to pre-compile queries for performance and safety. In Ecto you choose between a keyword syntax and a pipe-based one, and values from the surrounding code are interpolated with ^:

Post
|> where([p], p.author == "José" and p.category == "Elixir")
|> where([p], p.published_at > ^minimum_date)
|> order_by([p], desc: p.published_at)

lode has no macros, so none of that machinery exists — and none of it is needed. There is exactly one syntax: plain function calls that build a Query value. There are no [p] binding lists (bindings are positional integers carried by typed field accessors), and there is no ^ interpolation (a value from the surrounding code is just an argument). The same query reads:

import lode/query
import lode/query/expr.{type Expr, FieldRef}
import lode/types/primitive
import lode/types/temporal
import gleam/option.{type Option, None, Some}
import gleam/time/timestamp.{type Timestamp}

// Typed accessors for the `posts` source at binding 0. Hand-written here;
// `lode/codegen` emits these from the schema spec (see the
// getting-started.html guide).
fn post_author() -> FieldRef(Post, String) {
  FieldRef(binding: 0, name: "author", lode_type: primitive.string())
}

fn post_category() -> FieldRef(Post, String) {
  FieldRef(binding: 0, name: "category", lode_type: primitive.string())
}

fn post_published_at() -> FieldRef(Post, Timestamp) {
  FieldRef(binding: 0, name: "published_at", lode_type: temporal.utc_datetime())
}

fn post_public() -> FieldRef(Post, Bool) {
  FieldRef(binding: 0, name: "public", lode_type: primitive.boolean())
}

pub fn recent_elixir_posts(minimum_date: Timestamp) -> query.Query {
  query.from(source: "posts", alias: "p")
  |> query.where(expr.and_(
    left: expr.eq(field: post_author(), to: "José"),
    right: expr.eq(field: post_category(), to: "Elixir"),
  ))
  |> query.where(expr.gt(field: post_published_at(), to: minimum_date))
  |> query.order_by([query.desc(expr.col(post_published_at()))])
}

Queries compose the same way Ecto’s do — each call to query.where, query.order_by, and so on returns a new Query, so abstracting the published_at filtering and sorting into a function is ordinary code:

pub fn most_recent_from(q: query.Query, minimum_date: Timestamp) -> query.Query {
  q
  |> query.where(expr.gt(field: post_published_at(), to: minimum_date))
  |> query.order_by([query.desc(expr.col(post_published_at()))])
}

This shows queries can be built and composed at a high level. Sometimes, however, you want the contents of the where or the order_by themselves to be decided at runtime. The classic example is a web application offering search over posts: the user may specify any combination of criteria — author name, category, a publication date cutoff, the sort order.

Ecto solves this with two extra mechanisms: data structures as query inputs and the dynamic/2 macro. Let’s look at how each maps here.

Focusing on data structures

Ecto lets you ditch the binding and write where(author: "José", category: "Elixir") — keyword lists are accepted as first-class query inputs and can be built dynamically.

Gleam has no keyword lists, but the deeper point needs no special support: in lode everything is already a data structure. A Query is a record, an Expr is a plain tree of constructors, and order_by takes a List(query.OrderItem). Any of them can be produced by ordinary code. The equivalent of feeding a runtime-built keyword list to where is mapping a list of name/value pairs onto equality expressions with the raw expr.Col/expr.Lit constructors:

import lode/value
import gleam/list

let conditions = [
  #("author", value.VString("José")),
  #("category", value.VString("Elixir")),
]
let order = [query.desc(expr.col(post_published_at()))]

let by_fields =
  list.map(conditions, fn(c) {
    expr.BinOp(expr.Eq, expr.Col(0, c.0), expr.Lit(c.1))
  })

list.fold(by_fields, query.from(source: "posts", alias: "p"), query.where)
|> query.where(expr.gt(field: post_published_at(), to: minimum_date))
|> query.order_by(order)

The trade-offs mirror Ecto’s exactly. A pair list only encodes equality, so order-based comparisons such as published_at > minimum_date are still written with the typed combinators. And just as Ecto’s keyword lists are checked only at runtime, dropping to expr.Col(0, "author") bypasses the typed accessor layer — a typo’d column name or a wrongly-typed Value won’t be caught by the compiler. Prefer the typed FieldRef accessors wherever the field is known statically; the raw constructors are the deliberate escape hatch for truly name-driven inputs (see schemaless-queries.html).

Dynamic fragments

For filters that can’t be expressed as data structures, Ecto provides the dynamic/2 macro: it captures a query fragment as a value that can be built conditionally and interpolated into a query later, e.g. dynamic([p], p.published_at > ^date or p.public).

lode deliberately has no dynamic — because it doesn’t need one. Ecto needs dynamic/2 to escape the macro world: a where argument is normally consumed at compile time, so turning a fragment into a runtime value takes a dedicated construct. In lode a query expression already is a runtime value — Expr is a plain data type you can build in a function, store in a variable, branch on, and pass around (see DESIGN.md in the repository). The dynamic example above is simply:

let recent_or_public =
  expr.or_(
    left: expr.gt(field: post_published_at(), to: date),
    right: expr.col(post_public()),
  )

Note expr.col(post_public()) on its own: a reference to a boolean column is itself a valid boolean expression, just like p.public in Ecto. Also note the date comparison: post_published_at() is a FieldRef(Post, Timestamp) whose lode_type is temporal.utc_datetime(), so expr.gt only accepts a gleam/time Timestamp — passing a string is a compile error. At render time the value dumps through the field’s type into a $n parameter (p."published_at" > $1) that the Postgres adapter sends as a native timestamp, never as interpolated text.

So where Ecto conditionally builds a dynamic, you conditionally build an Expr. Suppose posts may optionally be filtered by publication date. You can of course branch on the query itself:

let q =
  query.from(source: "posts", alias: "p")
  |> query.order_by(order)

let q = case published_before {
  Some(date) -> query.where(q, expr.lt(field: post_published_at(), to: date))
  None -> q
}

But you can also decouple the parameter handling from the query generation by producing an Option(Expr) first and applying it later:

fn maybe_where(q: query.Query, condition: Option(Expr)) -> query.Query {
  case condition {
    Some(e) -> query.where(q, e)
    None -> q
  }
}

let filter_published_before = case published_before {
  Some(date) -> Some(expr.lt(field: post_published_at(), to: date))
  None -> None
}

query.from(source: "posts", alias: "p")
|> maybe_where(filter_published_before)
|> query.order_by(order)

(Ecto’s dynamic(true) seed has a literal translation too — expr.Lit(value.VBool(True)) is an expression that renders as an always-true parameter — but Option(Expr) skips the redundant clause entirely.)

This decoupling is what makes complex search endpoints tractable. Let’s see a fuller example.

Building dynamic queries

Back to the original problem: a search function where the user configures how to traverse all posts — sort order, author and category filters, and a “published after” cutoff.

In Ecto the raw web params (a string-keyed map) are reduced directly into a dynamic. Gleam has no string-keyed magic, so the idiomatic boundary is a record of Option fields that the web layer decodes raw params into (parsing the date with timestamp.parse_rfc3339, dropping unknown keys — the typed analogue of upstream’s “not a where parameter” clause). From there, the problem breaks into the same small functions, each returning plain data:

import gleam/list
import gleam/option

pub type PostSearch {
  PostSearch(
    order_by: String,
    author: Option(String),
    category: Option(String),
    published_after: Option(Timestamp),
  )
}

pub fn filter(params: PostSearch) -> query.Query {
  query.from(source: "posts", alias: "p")
  |> query.order_by(filter_order_by(params.order_by))
  |> maybe_where(filter_where(params))
}

pub fn filter_order_by(order: String) -> List(query.OrderItem) {
  case order {
    "published_at_desc" -> [query.desc(expr.col(post_published_at()))]
    "published_at" -> [query.asc(expr.col(post_published_at()))]
    _ -> []
  }
}

pub fn filter_where(params: PostSearch) -> Option(Expr) {
  [
    option.map(params.author, fn(name) {
      expr.eq(field: post_author(), to: name)
    }),
    option.map(params.category, fn(c) {
      expr.eq(field: post_category(), to: c)
    }),
    option.map(params.published_after, fn(date) {
      expr.gt(field: post_published_at(), to: date)
    }),
  ]
  |> option.values
  |> list.reduce(expr.and_)
  |> option.from_result
}

Because the problem is broken into small functions over regular data, all of Gleam’s tools apply. filter_order_by pattern matches on the requested order. filter_where is the analogue of upstream’s Enum.reduce over an empty dynamic: each present filter becomes an Expr, option.values keeps the ones that apply, and list.reduce(expr.and_) folds them into one combined condition (Error(Nil) from the empty list becomes None — no WHERE at all). Swapping expr.and_ for expr.or_ would make the filters alternatives instead.

Testing also becomes simpler, and here the value-based design pays off twice. Ecto’s guide asserts on inspect(dynamic) strings; an Expr is plain data with structural equality, so you assert on the value directly:

pub fn filter_where_published_after_test() {
  let empty =
    PostSearch(order_by: "", author: None, category: None, published_after: None)
  // No filters -> no WHERE expression at all.
  assert filter_where(empty) == None

  let assert Ok(date) = timestamp.parse_rfc3339("2010-04-17T00:00:00Z")
  assert filter_where(PostSearch(..empty, published_after: Some(date)))
    == Some(expr.BinOp(
      expr.Gt,
      expr.Col(0, "published_at"),
      expr.Lit(value.VTimestamp(date)),
    ))
}

You can also go one level further and assert on the rendered SQL plus its parameters with the renderer from lode_sql (import lode/query/sql):

pub fn filter_renders_test() {
  let assert Ok(date) = timestamp.parse_rfc3339("2010-04-17T00:00:00Z")
  let params =
    PostSearch(
      order_by: "published_at_desc",
      author: Some("José"),
      category: None,
      published_after: Some(date),
    )
  let #(rendered, values) = sql.to_sql(filter(params))
  assert rendered
    == "SELECT * FROM \"posts\" AS p"
    <> " WHERE ((p.\"author\" = $1) AND (p.\"published_at\" > $2))"
    <> " ORDER BY p.\"published_at\" DESC"
  assert values == [value.VString("José"), value.VTimestamp(date)]
}

Running the query is then repo.all(repo: r, schema: post_schema(), query: filter(params)).

Dynamic and joins

Even joins can be tackled dynamically. Let’s make the two modifications from the upstream guide: allow sorting by author name ("author_name" and "author_name_desc"), and move authors into a separate table, which means the author filter in filter_where now goes through a join.

In Ecto this is where named bindings (as: :authors) come in, so that dynamics built far from the query can refer to the join. lode’s bindings are positional integers instead: the from source is binding 0 and each join takes the next index, and every FieldRef carries its binding. So referring to the joined table from a helper function just means using accessors built at binding 1:

fn post_author_id() -> FieldRef(Post, Int) {
  FieldRef(binding: 0, name: "author_id", lode_type: primitive.integer())
}

// Accessors for the `authors` source at binding 1 (the first join).
fn author_id() -> FieldRef(Author, Int) {
  FieldRef(binding: 1, name: "id", lode_type: primitive.id())
}

fn author_name() -> FieldRef(Author, String) {
  FieldRef(binding: 1, name: "name", lode_type: primitive.string())
}

The final solution looks like this:

pub fn filter(params: PostSearch) -> query.Query {
  query.from(source: "posts", alias: "p")
  // 1. Add the join — `authors` becomes binding 1
  |> query.join(
    kind: query.InnerJoin,
    source: "authors",
    alias: "a",
    on: expr.eq_col(left: post_author_id(), right: author_id()),
  )
  |> query.order_by(filter_order_by(params.order_by))
  |> maybe_where(filter_where(params))
}

// 2. Order items may now reference the join binding
pub fn filter_order_by(order: String) -> List(query.OrderItem) {
  case order {
    "published_at_desc" -> [query.desc(expr.col(post_published_at()))]
    "published_at" -> [query.asc(expr.col(post_published_at()))]
    "author_name_desc" -> [query.desc(expr.col(author_name()))]
    "author_name" -> [query.asc(expr.col(author_name()))]
    _ -> []
  }
}

// 3. The author clause now filters through the join
pub fn filter_where(params: PostSearch) -> Option(Expr) {
  [
    option.map(params.author, fn(name) {
      expr.eq(field: author_name(), to: name)
    }),
    option.map(params.category, fn(c) {
      expr.eq(field: post_category(), to: c)
    }),
    option.map(params.published_after, fn(date) {
      expr.gt(field: post_published_at(), to: date)
    }),
  ]
  |> option.values
  |> list.reduce(expr.and_)
  |> option.from_result
}

Two honest differences from Ecto here. First, the binding agreement is by convention, not by name: author_name() hard-codes binding 1, so it is only correct when filter actually adds the authors join first — the same contract Ecto spells as: :authors, but without a build-time check. If the join is missing, the renderer falls back to a synthetic t1 alias and Postgres rejects the query, whereas Ecto raises on a missing named binding when the query is built. When composing joins programmatically, query.last_binding(q) returns the index the most recent join received, and because FieldRef is an ordinary record you can re-bind an accessor with a record update (FieldRef(..author_name(), binding: n)) — generated accessors all start at binding 0, so this is also how you point codegen output at a join. Second:

Association-derived joins. Ecto’s join(:inner, [p], assoc(p, :authors)) derives the join’s source and ON from the declared association. association.join(query, schema, name, kind, alias) does the same in lode — it reads the metadata off the association and appends the join, returning a Result (the schema is the query’s from binding). You can still spell query.join with an explicit source:/on: by hand when you want a join the associations don’t describe. #assoc-joins

Adding more filters in the future is a matter of adding a field to PostSearch and one entry to the list in filter_where — the fold does the rest. For the full map of what else differs from Ecto’s query layer (subqueries, named bindings, and friends), see Divergences from Ecto.