q (equery v0.22.0)
View SourceQuery building DSL.
q is the user-facing composable query builder. Each builder takes either
a query and returns a query (eager form, arity N+1), or returns a partially
applied function qfun() (lazy form, arity N) that can be chained with
pipe/2.
q:pipe(q:from(?USER), [
q:where(fun([#{active := A}]) -> A =:= true end),
q:order_by(fun([#{created := C}]) -> [{C, desc}] end),
q:limit(10)
]).Builders fall into three categories:
- Source:
from/1,using/1,2,with/2,3,recursive/2— define where data comes from. - Refinement:
where/1,2,having/1,2,select/1,2,set/1,2,data/1,2,group_by/1,2,order_by/1,2,limit/1,2,first/2,3,offset/1,2,distinct/0,1,distinct_on/1,2— narrow or shape results. - Joins / locks:
join/2,3,4,lateral_join/2,3,4,lock/1,2,3,4,for_update/0,1.
Statement compilation (SELECT / INSERT / UPDATE / DELETE) lives
in qsql. SQL expression builders (operators, scalar/aggregate functions,
CASE, type casts) live in pg_sql. JSON operators and builders live in
qjson.
Summary
Composition
Compile a nullary fun that returns a DSL closure into the closure itself, applying the parse transform at AST level. Used to pre-build reusable closures from shell-loaded code.
Introspect a built query.
Apply a chain of partially-applied builders to a base query.
Joins
Inner join — short form of join(inner, Info, Fun).
Join with explicit type.
LATERAL join — subquery may reference outer columns. ON defaults to true.
LATERAL join with custom ON condition.
Locking
Shorthand for lock(for_update, wait).
Row-level lock.
Row-level lock with explicit wait policy.
Lock selected tables only. Fun filters the list of real tables — use
lookup_tables/2 to pick by model.
Filter a list of real tables to those matching the given model(s).
Refinement
Rewrite the data context — advanced. Closure receives the list of field maps and returns a new list. Useful for splicing computed columns into the data passed to subsequent builders.
SELECT DISTINCT — distinct over all selected columns.
SELECT DISTINCT ON (cols). Closure returns a list of column atoms.
FETCH FIRST n ROWS [ONLY | WITH TIES] — parameterized.
GROUP BY clause. Closure returns a list of expressions to group by.
HAVING clause — predicate over aggregated rows.
LIMIT n — parameterized. Equivalent to first(N, no_ties) and shares
storage with first/2,3: whichever is set last wins.
OFFSET n — parameterized.
ORDER BY clause.
Project the columns.
Set values for INSERT / UPDATE.
Add a WHERE predicate.
Source
Start a query from a source.
Build a recursive CTE.
Add an additional source to FROM (comma form).
WITH clause (CTE).
Upsert
ON CONFLICT (target) DO ... clause.
ON CONFLICT (target) WHERE filter DO ... — partial-index upsert.
Types
-type conflict_action() :: nothing | conflict_update() | {conflict_update(), qast:ast_node()}.
-type conflict_columns() :: [atom()].
-type conflict_target() :: any | conflict_columns().
-type conflict_update() :: #{atom() => qast:ast_node()}.
-type data() :: [#{atom() => qast:ast_node()}].
-type distinct() :: all | [atom()].
-type join_type() :: inner | left | right | full | {left, outer} | {right, outer} | {full, outer}.
-type order() :: [order_item()].
-type order_item() :: {qast:ast_node(), asc | desc} | {qast:ast_node(), asc | desc, order_nulls()}.
-type order_nulls() :: nulls_first | nulls_last.
-type query() :: #query{schema :: q:schema(), with :: term(), distinct :: q:distinct() | undefined, where :: qast:ast_node() | undefined, data :: q:data(), select :: q:select(), set :: q:set() | #query{}, tables :: [q:real_table() | q:table()], joins :: [{q:join_type(), qast:ast_node(), qast:ast_node()}], group_by :: [qast:ast_node()], having :: qast:ast_node() | undefined, order_by :: q:order(), on_conflict :: #{q:stored_conflict_target() => q:conflict_action()}, limit :: {non_neg_integer(), q:ties_mode()} | undefined, offset :: non_neg_integer() | undefined, lock :: {q:row_lock_level(), [q:real_table()], q:wait_policy()} | undefined}.
-type row_lock_level() :: for_update | for_no_key_update | for_share | for_key_share.
-type select() :: #{atom() => qast:ast_node()} | qast:ast_node().
-type set() :: #{atom() => qast:ast_node()} | query().
-type stored_conflict_target() :: conflict_target() | {conflict_columns(), qast:ast_node()}.
-type table() :: {alias, qast:ast_node(), #{atom() => term()}}.
-type ties_mode() :: no_ties | with_ties.
-type wait_policy() :: wait | nowait | skip_locked.
Functions
-spec distinct(Q) -> Q when Q :: query().
-spec first(non_neg_integer(), ties_mode(), Q) -> Q when Q :: query().
-spec for_update(Q) -> Q when Q :: query().
-spec group_by(fun((data()) -> qast:ast_node()), Q) -> Q when Q :: query().
-spec having(fun((data()) -> qast:ast_node()), Q) -> Q when Q :: query().
-spec limit(non_neg_integer(), Q) -> Q when Q :: query().
-spec lock(row_lock_level(), wait_policy(), fun(([RealTable]) -> [RealTable]), query()) -> query() when RealTable :: real_table().
-spec offset(non_neg_integer(), Q) -> Q when Q :: query().
-spec on_conflict(conflict_target(), fun((data()) -> conflict_action()), Q) -> Q when Q :: query().
-spec on_conflict_where(conflict_columns(), fun((data()) -> qast:ast_node()), fun((data()) -> conflict_action()), Q) -> Q when Q :: query().
-spec where(fun((data()) -> qast:ast_node()), Q) -> Q when Q :: query().
Composition
Compile a nullary fun that returns a DSL closure into the closure itself, applying the parse transform at AST level. Used to pre-build reusable closures from shell-loaded code.
Introspect a built query.
get(schema, Q)— the schema map.get(data, Q)— the list of field maps (one per joined source), as fed to DSL closures.
Apply a chain of partially-applied builders to a base query.
q:pipe(q:from(?USER), [
q:where(fun([#{active := A}]) -> A =:= true end),
q:limit(10)
]).Joins
Inner join — short form of join(inner, Info, Fun).
Fun receives the cumulative data (one map per joined source) and
returns the ON-condition AST.
q:join(?POST, fun([#{id := UId}, #{author_id := AId}]) ->
UId =:= AId
end).Join with explicit type.
JoinType is one of inner, left, right, full, {left, outer},
{right, outer}, {full, outer}.
LATERAL join — subquery may reference outer columns. ON defaults to true.
QFun receives the outer data() and returns the subquery (which can
reference outer fields captured from the closure):
q:lateral_join(left, fun([#{id := UId}]) ->
q:pipe(q:from(?POST), [
q:where(fun([#{author_id := A}]) -> A =:= UId end),
q:limit(3)
])
end).-spec lateral_join(join_type(), fun((data()) -> query()), fun((data()) -> qast:ast_node())) -> qfun().
LATERAL join with custom ON condition.
Locking
-spec for_update() -> qfun().
Shorthand for lock(for_update, wait).
-spec lock(row_lock_level()) -> qfun().
Row-level lock.
RowLockLevel ∈ for_update | for_no_key_update | for_share | for_key_share.
Defaults to wait policy and locks all real tables in the query.
-spec lock(row_lock_level(), wait_policy()) -> qfun().
Row-level lock with explicit wait policy.
WaitPolicy ∈ wait | nowait | skip_locked.
-spec lock(row_lock_level(), wait_policy(), fun(([RealTable]) -> [RealTable])) -> qfun() when RealTable :: real_table().
Lock selected tables only. Fun filters the list of real tables — use
lookup_tables/2 to pick by model.
q:lock(for_update, skip_locked,
fun(Tables) -> q:lookup_tables(?USER, Tables) end).-spec lookup_tables(model() | [model()], [RealTable]) -> [RealTable] when RealTable :: real_table().
Filter a list of real tables to those matching the given model(s).
Used inside lock/3,4 to scope locking to specific tables. Throws
{unknown_table, Model} when a requested model is not present.
Refinement
Rewrite the data context — advanced. Closure receives the list of field maps and returns a new list. Useful for splicing computed columns into the data passed to subsequent builders.
-spec distinct() -> qfun().
SELECT DISTINCT — distinct over all selected columns.
SELECT DISTINCT ON (cols). Closure returns a list of column atoms.
-spec first(non_neg_integer(), ties_mode()) -> qfun().
FETCH FIRST n ROWS [ONLY | WITH TIES] — parameterized.
with_ties returns extra rows that tie with the last one on the
ORDER BY key, so requires an order_by/1,2. Shares storage with
limit/1,2: first(N, no_ties) is equivalent to limit(N), and
whichever of the two is set last wins.
-spec group_by(fun((data()) -> qast:ast_node())) -> qfun().
GROUP BY clause. Closure returns a list of expressions to group by.
-spec having(fun((data()) -> qast:ast_node())) -> qfun().
HAVING clause — predicate over aggregated rows.
Multiple having/1,2 calls compose with andalso.
q:pipe(Q, [
q:group_by(fun([#{name := N}]) -> [N] end),
q:having(fun([#{id := Id}]) -> pg_sql:count(Id) > 1 end)
]).-spec limit(non_neg_integer()) -> qfun().
LIMIT n — parameterized. Equivalent to first(N, no_ties) and shares
storage with first/2,3: whichever is set last wins.
-spec offset(non_neg_integer()) -> qfun().
OFFSET n — parameterized.
ORDER BY clause.
Closure returns a list of order_item():
{Field, asc | desc}— direction only.{Field, asc | desc, nulls_first | nulls_last}— with NULL placement.
q:order_by(fun([#{name := N, id := Id}]) ->
[{N, asc, nulls_last}, {Id, desc}]
end).-spec select(Fun) -> qfun() when Fun :: fun((data()) -> select()) | fun((select(), data()) -> select()).
Project the columns.
Closure either takes only data() and returns the new selection map,
or takes the previous select map and data() to update it
incrementally:
q:select(fun([#{id := Id, name := N}]) -> #{id => Id, name => N} end).
q:select(fun(S, [#{age := A}]) -> S#{age => A} end).Selection may also be a single AST node (returns a scalar column).
Set values for INSERT / UPDATE.
Accepts either a map #{field => ast_or_value} for direct VALUES /
SET clause, or a query() for INSERT ... SELECT ... form.
q:set(fun(_) -> #{name => <<"alice">>, active => true} end).-spec where(fun((data()) -> qast:ast_node())) -> qfun().
Add a WHERE predicate.
Multiple where/1,2 calls compose with andalso. The closure receives
the list of field maps (one per joined source) and returns an AST node
(boolean expression).
q:where(fun([#{name := N}]) -> N =:= <<"alice">> end).Source
-spec from(model() | query() | table() | qast:ast_node()) -> query().
Start a query from a source.
Accepts:
- a schema map
#{table => ..., fields => ..., schema => ...}— schema is optional, when present emits"schema"."table"; - a model module that exports
schema/0; - another
query()— wraps it as a subquery inFROM; - a table alias from
with/3; - an arbitrary AST node with
{model, M, FieldsList}opts.
Build a recursive CTE.
BaseQuery is the anchor; UnionFun receives the CTE reference and
returns the recursive query body. Emits WITH RECURSIVE name AS (anchor UNION ALL recursive) SELECT ....
Add an additional source to FROM (comma form).
Used to bring extra tables into scope for UPDATE ... FROM ...,
DELETE ... USING ..., or for SELECTs joining via subsequent
where/1,2 predicates.
WITH clause (CTE).
Fun receives the CTE's table reference and returns a qfun() that
uses it. The CTE source may be a model, a query, or arbitrary AST
with {model, ...} opts (e.g. an UPDATE ... RETURNING ... AST).
Upsert
-spec on_conflict(conflict_target(), fun((data()) -> conflict_action())) -> qfun().
ON CONFLICT (target) DO ... clause.
ConflictTarget is either any (no target, matches any constraint
violation) or a list of column atoms.
Fun receives data() ++ [Excluded] — the additional Excluded map
provides access to the proposed-but-conflicted row values. Returns
either nothing (DO NOTHING), an update map (DO UPDATE SET), or
{UpdateMap, Cond} (DO UPDATE SET ... WHERE Cond).
q:on_conflict([id], fun([_, Excluded]) -> Excluded end).
q:on_conflict(any, fun(_) -> nothing end).-spec on_conflict_where(conflict_columns(), fun((data()) -> qast:ast_node()), fun((data()) -> conflict_action())) -> qfun().
ON CONFLICT (target) WHERE filter DO ... — partial-index upsert.
Filter is a predicate on the row that scopes the target index.