Query builder

Query<T> builds a SELECT against one table and hydrates the rows into T. It is also the entry point for inserts, updates and deletes.

import 'package:gisila_orm/gisila.dart';
import 'models/blog.gisila.g.dart';

final recent = await Query<Post>(PostTable.metadata)
    .where(PostTable.publishedAt.isNotNull)
    .orderBy(PostTable.publishedAt, desc: true)
    .limit(20)
    .all(db.context());

`Query<T>`

class Query<T> {
  Query(TableMeta<T> meta);
}

When to use: every typed read. Use the generated accessor (posts()) or Query<Post>(PostTable.metadata); they are the same. Drop to raw SQL for statements the builder can’t express (aggregates other than COUNT, CTEs, window functions, UNION).

How the builder behaves

Clause methods change the builder and return it, so you can chain them or keep a builder and add clauses conditionally.
Nothing runs until you call a terminal operation, which takes a DbContext: db.context(), a transaction’s tx, or a ConnectionDbContext. Database itself is not a DbContext.
Every value is bound as a $n parameter. Table and column names are quoted with safeTk.
The default SELECT list is TableMeta.columnNames, qualified with the table name.

Clause methods

Method	Signature	Description
`where`	`Query<T> where(Expr<bool> predicate)`	Adds a `WHERE` predicate. Repeated calls are AND-ed.
`orderBy`	`Query<T> orderBy(Expr<dynamic> expr, {bool desc = false, bool nullsFirst = false})`	Adds an `ORDER BY` term. Terms keep call order.
`limit`	`Query<T> limit(int n)`	`LIMIT n`. A later call replaces the earlier one.
`offset`	`Query<T> offset(int n)`	`OFFSET n`. A later call replaces the earlier one.
`after`	`Query<T> after<V>(ColumnRef<V> column, V? value, {bool descending = false})`	Keyset pagination.
`distinct`	`Query<T> distinct()`	`SELECT DISTINCT`.
`join`	`Query<T> join(String table, Expr<bool> on, {JoinType type = JoinType.inner, String? alias})`	Adds a join.
`groupBy`	`Query<T> groupBy(ColumnRef<dynamic> column)`	Adds a `GROUP BY` column.
`having`	`Query<T> having(Expr<bool> predicate)`	Sets `HAVING`. A later call replaces the earlier one.
`select`	`Query<T> select(List<String> columns)`	Replaces the `SELECT` list.
`preload`	`Query<T> preload(List<Relation<dynamic, dynamic>> relations)`	Eager-loads relations after `all`, `one` or `first`. See Relations and preloading.

Terminal operations

Method	Returns	SQL / behaviour
`all(DbContext db)`	`Future<List<T>>`	Runs the query, hydrates every row, then runs any preloads.
`one(DbContext db)`	`Future<T>`	Runs with `LIMIT 2`. Throws `QueryNoRowsException` for zero rows and `QueryMultipleRowsException` for more than one.
`first(DbContext db)`	`Future<T?>`	Runs with `LIMIT 1`; `null` when nothing matches.
`count(DbContext db)`	`Future<int>`	`SELECT COUNT(*)::bigint` with the same `WHERE` and joins. Ignores `ORDER BY`, `LIMIT` and `OFFSET`. With `distinct()` or `groupBy` it counts distinct rows or groups.
`exists(DbContext db)`	`Future<bool>`	`SELECT EXISTS(SELECT 1 ...)`.
`stream(DbContext db)`	`Stream<T>`	Hydrated rows through a server-side cursor, 500 at a time. No preloads. See `DbContext.stream`.
`compile({String? overrideSelect})`	`CompiledSql`	The SQL and parameters without running anything. `overrideSelect` replaces the column list.

one and first work on a copy, so the builder’s own limit is unchanged.

Mutation entry points

Method	Returns	Description
`insert(Map<String, Object?> values)`	`InsertQuery<T>`	`INSERT ... RETURNING *`.
`update(Map<String, Object?> values)`	`UpdateQuery<T>`	`UPDATE ... SET ...`, reusing this query’s `WHERE` as it is at call time.
`delete()`	`DeleteQuery<T>`	`DELETE FROM ...`, reusing this query’s `WHERE` as it is at call time.

`where`

Query<T> where(Expr<bool> predicate)

When to use: to filter rows. Build the predicate from generated columns and the operators on Expressions and operators.

where takes an expression, not a callback.

final q = Query<Post>(PostTable.metadata)
    .where(PostTable.title.ilike('%dart%'))
    .where(PostTable.publishedAt.isNotNull.or(PostTable.authorId.eq(1)));
// WHERE (("posts"."title" ILIKE $1) AND (("posts"."published_at" IS NOT NULL) OR ("posts"."author_id" = $2)))

Add filters conditionally by keeping the builder in a variable:

final q = Query<Post>(PostTable.metadata);
if (search != null) q.where(PostTable.title.ilike('%$search%'));
if (authorId != null) q.where(PostTable.authorId.eq(authorId));
final rows = await q.limit(50).all(db.context());

The search text is bound as a parameter, so this is safe from injection. % and _ inside it still act as LIKE wildcards.

`orderBy`

Query<T> orderBy(Expr<dynamic> expr, {bool desc = false, bool nullsFirst = false})

When to use: whenever you page or show rows to people; without it Postgres returns rows in no defined order.

Parameter	Type	Default	Description
`expr`	`Expr<dynamic>`	–	Usually a `ColumnRef`; any expression works, such as a vector distance.
`desc`	`bool`	`false`	`DESC` instead of `ASC`.
`nullsFirst`	`bool`	`false`	Appends `NULLS FIRST`. When `false` nothing is appended, so Postgres’s default applies (`NULLS LAST` for `ASC`, `NULLS FIRST` for `DESC`).

Query<Document>(DocumentTable.metadata)
    .orderBy(DocumentTable.embedding.cosineDistance(queryVector))
    .limit(10);

`after`

Query<T> after<V>(ColumnRef<V> column, V? value, {bool descending = false})

Keyset (“seek”) pagination: adds column > value (or < when descending) and ORDER BY column. Pass null for the first page.

When to use: paging through large or frequently written tables. A page costs the same at any depth and rows inserted meanwhile don’t shift pages. Use offset only for small tables or when users need to jump to page N.

column must be unique, or the last of a unique ordering (the primary key is the usual choice). Clause order matters: an orderBy added before after sorts first.

int? cursor; // last id from the previous page
final page = await Query<Post>(PostTable.metadata)
    .after(PostTable.id, cursor)
    .limit(50)
    .all(db.context());
cursor = page.isEmpty ? null : page.last.id;

`join`

Query<T> join(String table, Expr<bool> on, {JoinType type = JoinType.inner, String? alias})

When to use: to filter by columns in another table. Joined columns are not selected or hydrated; the result is still List<T>. To load related rows, use preload instead.

Parameter	Type	Default	Description
`table`	`String`	–	Table to join (quoted for you).
`on`	`Expr<bool>`	–	Join condition, usually built with `eqExpr`.
`type`	`JoinType`	`JoinType.inner`	`inner`, `left`, `right` or `full`.
`alias`	`String?`	`null`	Emits `"table" AS "alias"`. Reference its columns with `ColumnRef(table: alias, ...)`.

// Authors with at least one post titled like "%release%".
final authors = await Query<Author>(AuthorTable.metadata)
    .join('posts', AuthorTable.id.eqExpr(PostTable.authorId))
    .where(PostTable.title.ilike('%release%'))
    .distinct()
    .all(db.context());

A one-to-many join repeats the parent row once per match. Add distinct() as above, or use an EXISTS subquery through RawSql.

eqExpr takes an Expr of the same type parameter. Generated primary keys are nullable (ColumnRef<int?>) while a NOT NULL foreign key is ColumnRef<int>, so call it on the nullable side: AuthorTable.id.eqExpr(PostTable.authorId) compiles, the reverse doesn’t.

`groupBy` and `having`

Query<T> groupBy(ColumnRef<dynamic> column)
Query<T> having(Expr<bool> predicate)

When to use: mostly with count() (which counts groups) or with compile(overrideSelect: ...) for aggregate reports. all() still selects every column of the table, which Postgres only accepts next to GROUP BY when you group by the primary key.

// How many distinct authors have published posts?
final n = await Query<Post>(PostTable.metadata)
    .where(PostTable.publishedAt.isNotNull)
    .groupBy(PostTable.authorId)
    .count(db.context());

For sums, averages and other aggregates, compile with your own SELECT list and run it:

final compiled = Query<Post>(PostTable.metadata)
    .where(PostTable.publishedAt.isNotNull)
    .groupBy(PostTable.authorId)
    .compile(overrideSelect: '"posts"."author_id", COUNT(*)::int AS "n"');
final rows = await db.execute(compiled.sql, parameters: compiled.params);
for (final row in rows) {
  final m = row.toColumnMap();
  print('${m['author_id']}: ${m['n']}');
}

overrideSelect is inserted into the SQL verbatim. Never build it from user input.

`select`

Query<T> select(List<String> columns)

Replaces the SELECT list with the given column names (quoted and qualified with the table name).

When to use: rarely. Rows still go through the model’s fromRow, which casts non-nullable fields, so you must include every non-nullable column or hydration throws a TypeError. To fetch a few columns cheaply, prefer compile(overrideSelect: ...) with db.execute.

`CompiledSql`

class CompiledSql {
  const CompiledSql(String sql, List<Object?> params);
  final String sql;
  final List<Object?> params;
}

The output of every compile(). Run it with db.execute(c.sql, parameters: c.params), log it, or assert on it in tests.

final c = Query<Post>(PostTable.metadata).where(PostTable.id.eq(7)).compile();
print(c.sql);    // SELECT "posts"."id", ... FROM "posts" WHERE ("posts"."id" = $1)
print(c.params); // [7]

Other exports

Symbol	Description
`enum SortOrder { asc, desc }`	Direction stored in an `OrderTerm`.
`class OrderTerm`	`OrderTerm(Expr<dynamic> expr, {SortOrder order = SortOrder.asc, bool nullsFirst = false})`: one `ORDER BY` entry. Created by `orderBy`.
`enum JoinType { inner, left, right, full }`	Join kind for `join`.
`class JoinSpec`	`JoinSpec(JoinType type, String table, Expr<bool> on, {String? alias})` with a `keyword` getter (`INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN`, `FULL OUTER JOIN`). Created by `join`.

Query builder

Query<T>

How the builder behaves

Clause methods

Terminal operations

Mutation entry points

where

orderBy

after

join

groupBy and having

select

CompiledSql