Depot.Query

Depot.Query: composable query builder for in-memory and SQL execution.

Builds a query value from a Depot.Schema that accumulates operations (where, select, order_by, limit, offset). The query is lazy — no work happens until execute/2 (in-memory) or exec_sql/2 (PostgreSQL) is called.

In-memory usage (tests / in-memory Repo)

let q = Depot.Query.from(schema())
  |> Depot.Query.where_(fn row -> record_get(row, "age") > Some(18))
  |> Depot.Query.order_asc("name")
  |> Depot.Query.limit(10)

let results = Depot.Query.execute(q, all_rows)

SQL usage (PostgreSQL via Connection)

let q = Depot.Query.from(schema())
  |> Depot.Query.where_gt("age", "18")
  |> Depot.Query.order_asc("name")
  |> Depot.Query.limit(10)

match Depot.Query.exec_sql(q, db_conn) do
Ok(rows)  -> rows   -- List of record-like assoc lists
Err(msg)  -> []
end

SQL conditions are added with where_eq, where_ne, where_gt, etc. These are orthogonal to where_ (in-memory predicates) — use whichever suits the execution path.

Multiple where_ / where_eq etc. calls are AND-combined. Order of operations in execute/exec_sql:

Filter (where predicates AND sql conditions, AND-combined)
Sort (order_by fields — SQL path only, via order_parts)
Offset (drop N rows)
Limit (take N rows)
Project (select mapping — in-memory only)

Types

typeDQueryDQuery = DQuery(a, List(b), c, d, e, f, List(String), List(String))#

Functions

fncountcount(query, rows)#

Count the results of a query without materializing them. Only applies where predicates (ignores order_by, limit, offset, select).

  let n = Depot.Query.count(query, rows)

fncount_sqlcount_sql(query, conn)#

Execute a COUNT(*) query and return the integer count.

Uses a SELECT count(*) AS "count" FROM <table> WHERE ... query. Ignores select, order_by, limit, and offset — those don't affect a count.

  match Depot.Query.count_sql(q, db_conn) do
  Ok(n)    -> n
  Err(msg) -> 0
  end

fnexec_sqlexec_sql(query, conn)#

Execute the query against a live PostgreSQL connection.

fnexecuteexecute(query, rows)#

Execute a query against a list of rows (records).

Applies operations in order:

where predicates (AND-combined filter)
offset (drop N rows)
limit (take N rows)
select projection (map)

Note: order_by clauses are SQL-only; use exec_sql for server-side ordering.

  let results = Depot.Query.execute(query, rows)

fnexistsexists(query, rows)#

Returns true if the query would return any results.

  Depot.Query.exists(query, rows)

fnfirstfirst(query, rows)#

Returns the first result of a query, or None.

  Depot.Query.first(query, rows)

fnfromfrom(s)#

Wrap a Depot.Schema into a fresh query with no operations.

  let q = Depot.Query.from(schema())

fnfrom_tablefrom_table(table)#

Create a query from a plain table name string, without a schema.

Useful for unit testing SQL generation or for ad-hoc queries where a full Depot.Schema is not available.

  let q = Depot.Query.from_table("users")
    |> Depot.Query.where_eq("active", "true")
  Depot.Query.to_sql(q)  -- SELECT * FROM "users" WHERE "active" = $1

fnlimitlimit(query, n)#

Set a limit on the number of results. Replaces any previous limit.

  q |> Depot.Query.limit(10)

fnoffsetoffset(query, n)#

Set an offset (skip N rows). Replaces any previous offset.

  q |> Depot.Query.offset(20)

fnorder_ascorder_asc(query, field)#

Add an ascending order_by clause.

  q |> Depot.Query.order_asc("name")

fnorder_byorder_by(query, field, direction)#

Add an order_by clause with explicit direction ("asc" or "desc").

ORDER BY is stored as a pre-compiled SQL snippet and appended to the generated SQL by to_sql / exec_sql. For in-memory execution via execute/2, ordering is not applied.

Multiple calls accumulate: earlier calls have higher priority.

  q |> Depot.Query.order_by("name", "asc")
    |> Depot.Query.order_by("age", "desc")

fnorder_descorder_desc(query, field)#

Add a descending order_by clause.

  q |> Depot.Query.order_desc("name")

fnschemaschema(query)#

Returns the schema associated with this query.

  Depot.Query.schema(q)

fnselectselect(query, projection)#

Set the select projection. Replaces any previous select.

The projection receives a row (record) and returns a new value.

  q |> Depot.Query.select(fn row ->
    { name: record_get(row, "name"), email: record_get(row, "email") }
  )

fnto_paramsto_params(query) : List(String)#

Return the ordered list of parameter values for the SQL generated by to_sql/1.

Values correspond to $1, $2, … in the SQL string in left-to-right order. IS NULL / IS NOT NULL conditions produce no parameter.

  let params = Depot.Query.to_params(q)
  -- ["[email protected]", "18"]

fnto_sqlto_sql(query) : String#

Generate a parameterised SQL SELECT string from the query.

Uses the schema's table name and db_fields (non-virtual columns). SQL conditions added via where_eq / where_gt etc. become the WHERE clause. ORDER BY clauses added via order_asc / order_desc are appended last.

The $N placeholders match the list returned by to_params/1 — pass both to Db.exec/3:

  let sql    = Depot.Query.to_sql(q)
  let params = Depot.Query.to_params(q)
  Db.exec(db_conn, sql, params)

Or use exec_sql/2 which does this in one step.

fnts_matchts_match(query, field, search_term)#

WHERE field @@ plainto_tsquery(search_term) — PostgreSQL full-text search predicate.

Column-type contract: field must already be a tsvector-typed column (e.g. maintained via a trigger or a generated column), NOT a plain text column and NOT a to_tsvector(...) expression. This predicate filters the bare column directly ("field" @@ plainto_tsquery(...)) — Postgres has no text @@ tsquery operator, so calling this against a plain text column is a SQL type error.

Indexing: to make this predicate use a GIN index, index the SAME bare tsvector column with Depot.Migration.create_index(table, [field], { using: "gin" }) — NOT create_index_expr. create_index_expr is for a DIFFERENT use case: indexing a computed to_tsvector(...) expression over a plain text column when you don't have a stored tsvector column. Querying against an expression index requires the WHERE clause to use the identical expression, which ts_match (a bare-column predicate) does not do — pairing ts_match with a create_index_expr index silently falls back to a Seq Scan.

ts_rank_expr/2 ranks the same bare tsvector column, so the two compose directly: ts_match(q, "body_tsv", term) + ts_rank_expr("body_tsv", term).

Uses a raw SQL Fragment; search_term travels as a bound Lit inside the Fragment's own argument list. This predicate compiles correctly via exec_sql/2 (which compiles $N params directly from the query AST). If you build SQL manually via to_sql/1 + to_params/1 instead, note this predicate's param will render correctly in the SQL from to_sql/1 (inlined, via compile_inline) but will NOT appear in to_params/1's list — prefer exec_sql/2 for queries using ts_match.

  Depot.Query.from_table("articles") |> Depot.Query.ts_match("body_tsv", "postgres search")

fnts_rank_exprts_rank_expr(field, search_term)#

Builds a ts_rank(field, plainto_tsquery(search_term)) expression for use with Build.select_as/Build.order_by — ranking is a projection concern, so this returns an Expr, not a query. search_term is a bound Fragment param (same mechanism as ts_match), not a hand-concatenated literal, so it needs no manual SQL-escaping.

Same column-type contract as ts_match: field must already be a tsvector-typed column — pass the SAME field name given to ts_match so the rank reflects the same tsvector data being filtered.

Like ts_match, this Fragment param will NOT appear in to_params/1's list — prefer exec_sql/2 (or Db.exec with Compile.compile_parameterized) for queries built with ts_rank_expr.

  Depot.Query.ts_rank_expr("body_tsv", "postgres search")

fnwhere_where_(query, predicate)#

Add a where predicate. Multiple calls are AND-combined.

The predicate receives a row (record) and must return a Bool.

  q |> Depot.Query.where_(fn row -> record_get(row, "active") == Some(true))

fnwhere_eqwhere_eq(query, field, value)#

WHERE field = $N

fnwhere_gtwhere_gt(query, field, value)#

WHERE field > $N

fnwhere_gtewhere_gte(query, field, value)#

WHERE field >= $N

fnwhere_ilikewhere_ilike(query, field, pattern)#

WHERE field ILIKE $N (case-insensitive pattern match, PostgreSQL extension)

fnwhere_is_not_nullwhere_is_not_null(query, field)#

WHERE field IS NOT NULL (no parameter)

fnwhere_is_nullwhere_is_null(query, field)#

WHERE field IS NULL (no parameter)

fnwhere_likewhere_like(query, field, pattern)#

WHERE field LIKE $N (case-sensitive pattern match)

fnwhere_ltwhere_lt(query, field, value)#

WHERE field < $N

fnwhere_ltewhere_lte(query, field, value)#

WHERE field <= $N

fnwhere_newhere_ne(query, field, value)#

WHERE field <> $N