Query building

What it is

A query in gisila_orm is a plain Dart object you assemble, then run against a DbContext:

final q = Query<Post>(PostTable.metadata)   // which table, how to build a Post
    .where(PostTable.publishedAt.isNotNull) // an Expr<bool> tree
    .orderBy(PostTable.publishedAt, desc: true)
    .limit(20);

final rows = await q.all(db.context());     // compile, execute, hydrate

Three pieces cooperate:

Piece	Comes from	Job
`TableMeta<T>`	generated `PostTable.metadata`	Table name, column list, `fromRow`
`ColumnRef<T>` and `Expr<T>`	generated `PostTable.title` etc.	Typed predicates and sort keys
`Query<T>`	you	Collects clauses; compiles and runs them

When to use it

Reads and writes against one table where you want the result as models.
Filters assembled at runtime from optional inputs (search forms, API query parameters).
Anything where a typo in a column name should be a compile error, not a 500.

When not to use it

Reports that return computed columns: sums, averages, window functions, GROUP BY with aggregates. The builder hydrates rows into T, so it can’t return shapes that aren’t a model. Use raw SQL or compile(overrideSelect: ...).
Multi-table results (a post with its author’s name in one row). Use preload to load related models, or raw SQL for a flat shape.
CTEs, UNION, INSERT ... SELECT, UPDATE ... FROM, SET col = col + 1.

How it works

Typed columns

Each generated ColumnRef<T> carries the Dart type of its field. Operators take a T, so mismatched values fail at compile time, and type-specific operators (like, contains, cosineDistance) only appear on columns of the right type.

An expression tree, not a string

PostTable.title.ilike('%dart%').and(PostTable.authorId.eq(3)) doesn’t produce SQL; it produces a small tree:

BinOp(AND)
├── BinOp(ILIKE)  ColumnRef(posts.title)  Literal('%dart%')
└── BinOp(=)      ColumnRef(posts.author_id)  Literal(3)

When you run the query, SqlCompiler walks the tree and writes:

(("posts"."title" ILIKE $1) AND ("posts"."author_id" = $2))   -- params: ['%dart%', 3]

Every literal becomes a $n parameter and every identifier is quoted, so values can’t inject SQL and reserved words like order work as column names. eq(null) becomes IS NULL, and an empty inList becomes FALSE rather than a syntax error.

Builders are mutable

Clause methods modify the builder and return it. That makes conditional filters natural:

final q = posts();
if (onlyPublished) q.where(PostTable.publishedAt.isNotNull);
if (authorId != null) q.where(PostTable.authorId.eq(authorId));

It also means a builder you keep around accumulates clauses. Create a new one (posts()) for each query rather than sharing one.

One query pipeline

Every terminal operation compiles to a CompiledSql (SQL plus parameter list) and calls DbContext.execute. count() reuses the same WHERE and joins without ordering or paging; exists() wraps it in SELECT EXISTS(...); one() and first() add a limit on a copy. Writes reuse the WHERE of the query they start from.

Contexts decide where it runs

Terminal operations take a DbContext. The same query runs on the pool (db.context()), inside a transaction (tx), or on a pinned connection, with no change to the query code. Write data-access functions to take a DbContext and let the caller choose.

Example

A search endpoint with optional filters, keyset pagination and a total:

Future<({List<Post> items, int total})> search(
  DbContext db, {
  String? text,
  int? authorId,
  int? after,
  int pageSize = 20,
}) async {
  Query<Post> base() {
    final q = posts().where(PostTable.publishedAt.isNotNull);
    if (text != null && text.isNotEmpty) q.where(PostTable.title.ilike('%$text%'));
    if (authorId != null) q.where(PostTable.authorId.eq(authorId));
    return q;
  }

  final total = await base().count(db);
  final items = await base().after(PostTable.id, after).limit(pageSize).all(db);
  return (items: items, total: total);
}