Expressions and operators

Predicates and sort keys are trees of Expr<T> nodes. You build them from the generated ColumnRefs, combine them with and / or / not, and SqlCompiler turns the tree into SQL with $n parameters.

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

final Expr<bool> visible = PostTable.publishedAt.isNotNull
    .and(PostTable.title.ilike('%dart%').or(PostTable.authorId.inList([1, 2])))
    .and(PostTable.body.isNull.not);

Every node compiles wrapped in parentheses, so combining expressions never changes precedence.

Operator summary

Dart	SQL	Available on
col.eq(v)	col = $n (IS NULL when v is null)	every ColumnRef<T>
col.neq(v)	col <> $n (IS NOT NULL when v is null)	every ColumnRef<T>
col.gt(v), gte, lt, lte	>, >=, <, <=	every ColumnRef<T>
col.eqExpr(e), neqExpr(e)	col = <expr>, col <> <expr>	every ColumnRef<T>
col.inList(values)	col IN ($1, ...); FALSE when empty	every ColumnRef<T>
col.between(lo, hi)	col BETWEEN $1 AND $2	every ColumnRef<T>
col.isNull, col.isNotNull	IS NULL, IS NOT NULL	every ColumnRef<T>
col.like(p), col.ilike(p)	LIKE, ILIKE	ColumnRef<String>, ColumnRef<String?>
col.field(k), col.text(k)	col -> $n, col ->> $n	ColumnRef<Map<String, dynamic>>
col.contains(values)	col @> $n	ColumnRef<List<T>>
col.overlaps(values)	col && $n	ColumnRef<List<T>>
col.l2Distance(v), cosineDistance, innerProduct, distance	<->, <=>, <#>	ColumnRef<Vector>, ColumnRef<Vector?>
a.and(b), a.or(b)	AND, OR	any Expr<bool>
a.not (getter)	NOT a	any Expr<bool>

Expr<T>

abstract class Expr<T> {
  const Expr();
  R accept<R>(ExprVisitor<R> visitor);
  Expr<bool> and(Expr<bool> other);
  Expr<bool> or(Expr<bool> other);
  Expr<bool> get not;
}

Base class of every node. T is the SQL value type the expression produces.

When to use: as the type of a reusable predicate (Expr<bool>) or sort key (Expr<dynamic>). You rarely subclass it.

Member	Description
and(other)	(this AND other). Call it only on boolean expressions.
or(other)	(this OR other).
not	(NOT this). A getter, not a method.
accept(visitor)	Visitor entry point used by SqlCompiler.

Example

Expr<bool> publishedBy(int authorId) =>
    PostTable.authorId.eq(authorId).and(PostTable.publishedAt.isNotNull);

final rows = await Query<Post>(PostTable.metadata)
    .where(publishedBy(3))
    .all(db.context());

ColumnRef

class ColumnRef<T> extends Expr<T> {
  const ColumnRef({required String column, required String table});
  final String column;
  final String table;
}

A typed reference to "table"."column". The generator emits one per column on each <Model>Table class.

When to use: use the generated ones. Construct one yourself for a column the schema doesn’t describe, such as a joined table’s column or an alias.

Method	Signature	SQL
eq	Expr<bool> eq(T value)	=; IS NULL for null
neq	Expr<bool> neq(T value)	<>; IS NOT NULL for null
gt, gte, lt, lte	Expr<bool> gt(T value)	>, >=, <, <=
eqExpr, neqExpr	Expr<bool> eqExpr(Expr<T> other)	compare with another expression (no parameter)
inList	Expr<bool> inList(Iterable<T> values)	IN (...); an empty list compiles to (FALSE)
between	Expr<bool> between(T lower, T upper)	BETWEEN lower AND upper (inclusive)
isNull, isNotNull	getters returning Expr<bool>	IS NULL, IS NOT NULL

Example

final orderUserId = const ColumnRef<int>(table: 'orders', column: 'user_id');
final big = const ColumnRef<int>(table: 'orders', column: 'total');

final buyers = await Query<User>(UserTable.metadata)
    .join('orders', UserTable.id.eqExpr(orderUserId))
    .where(big.gte(10000))
    .distinct()
    .all(db.context());

Note

Values are bound as driver parameters with no conversion, apart from the special cases in SqlCompiler.bind. A Dart enum isn’t converted to its label, so ReviewTable.verdict.eq(ReviewVerdict.approved) fails when the statement runs. Compare enum columns through RawSql: RawSql<bool>('"reviews"."verdict" = \$1::review_verdict', [ReviewVerdict.approved.sqlValue]).

String operators

extension StringColumnRefOps on ColumnRef<String> {
  Expr<bool> like(String pattern);
  Expr<bool> ilike(String pattern);
}
extension NullableStringColumnRefOps on ColumnRef<String?> { /* same */ }

When to use: pattern matching. ilike is case-insensitive. The pattern is a bound parameter, but % and _ in it are still wildcards; escape them if the text comes from users and must match literally.

Query<Author>(AuthorTable.metadata).where(AuthorTable.email.ilike('%@example.com'));

JSON operators

extension JsonColumnRefOps on ColumnRef<Map<String, dynamic>> {
  Expr<Map<String, dynamic>?> field(String key); // column -> 'key'
  Expr<String?> text(String key);                // column ->> 'key'
}

Caution

Generated json columns are typed ColumnRef<Object> / ColumnRef<Object?>, so this extension doesn’t apply to them, and its results are plain Exprs without eq. Filter JSON columns with RawSql: RawSql<bool>("\"users\".\"prefs\" ->> 'theme' = \$1", ['dark']).

Array operators

extension ArrayColumnRefOps<T> on ColumnRef<List<T>> {
  Expr<bool> contains(Iterable<T> values); // column @> $n
  Expr<bool> overlaps(Iterable<T> values); // column && $n
}

When to use: tag-style filtering on varchar[], integer[] and other array columns declared is_null: false. A nullable array column is ColumnRef<List<T>?>, which this extension doesn’t cover; use RawSql there.

Query<Post>(PostTable.metadata).where(PostTable.tags.contains(['dart']));

Vector operators

extension VectorColumnRefOps on ColumnRef<Vector> {
  Expr<double> l2Distance(Vector value);     // column <-> $n::vector
  Expr<double> cosineDistance(Vector value); // column <=> $n::vector
  Expr<double> innerProduct(Vector value);   // column <#> $n::vector (negative inner product)
  Expr<double> distance(Vector value, {VectorDistance metric = VectorDistance.l2});
}
extension NullableVectorColumnRefOps on ColumnRef<Vector?> { /* same */ }

When to use: nearest-neighbour search with pgvector. Pass the distance to orderBy (ascending gives nearest first) and add a limit. Match the metric to the column’s index distance so Postgres can use the index.

final nearest = await Query<Document>(DocumentTable.metadata)
    .orderBy(DocumentTable.embedding.distance(query, metric: VectorDistance.cosine))
    .limit(5)
    .all(db.context());

See Vector.

RawSql

class RawSql<T> extends Expr<T> {
  const RawSql(String sql, [List<Object?> params = const []]);
  final String sql;
  final List<Object?> params;
}

A hand-written SQL fragment inside an otherwise typed query.

When to use: a predicate or sort key the operators can’t express: functions (lower(...), full-text search), subqueries (EXISTS (...)), casts, JSON paths, enum comparisons. For whole statements, use db.execute instead.

Rule	Detail
Placeholders	$1, $2, … counted within the fragment. They are renumbered to fit the surrounding statement.
Checking	Every parameter must be referenced and no placeholder may exceed params.length; otherwise compiling throws ArgumentError.
?	Plain SQL (for example the JSONB key-exists operator), not a placeholder.
Wrapping	The fragment is wrapped in parentheses, so a OR b stays one operand.
Identifiers	Not quoted for you. Write "posts"."title" when the name needs quoting.

final q = Query<Post>(PostTable.metadata)
    .where(PostTable.publishedAt.isNotNull)
    .where(RawSql<bool>(
      'EXISTS (SELECT 1 FROM comments c WHERE c.post_id = "posts"."id" AND c.body ILIKE \$1)',
      ['%thanks%'],
    ));

Danger

The sql string is inserted verbatim. Put user input only in params.

SqlCompiler

class SqlCompiler implements ExprVisitor<String> {
  List<Object?> get params;
  String bind(Object? value);
  String compile(Expr<dynamic> expr);
}

Walks an expression tree and produces a SQL fragment plus a flat parameter list. Each Query compile uses one instance, so SET values and WHERE values share one $n sequence.

When to use: to compile a standalone predicate, for example to reuse a typed filter inside a hand-written statement.

bind(value) appends a parameter and returns its placeholder, converting a few types first:

Value	Bound as	Placeholder
Vector	[v1,v2,...] text	$n::vector
Point, Box, Circle, Lseg	Postgres text form	$n::point, $n::box, $n::circle, $n::lseg
List	Postgres array literal text {a,b}	$n
anything else	unchanged	$n

Because a List becomes an array literal, a JSON array value must be encoded before you bind it; see encodeJsonColumn.

Example

final c = SqlCompiler();
final where = c.compile(PostTable.authorId.eq(3).and(PostTable.publishedAt.isNotNull));
final rows = await db.execute(
  'SELECT count(*)::int AS n FROM "posts" WHERE $where',
  parameters: c.params,
);

AST node classes

You normally get these from the helpers above. They are public so you can build nodes the helpers don’t offer.

Class	Constructor	SQL
Literal<T>	const Literal(T? value)	a bound parameter
BinOp<T>	const BinOp(Expr left, String op, Expr right)	(left op right)
UnaryOp<T>	const UnaryOp(String op, Expr operand)	(op operand)
FuncCall<T>	const FuncCall(String name, List<Expr> args, {bool infix = false})	name(a, b), or (a name b) when infix with two args
InList<T>	const InList(ColumnRef<T> column, List<T> values)	(col IN (...))
Between<T>	const Between(ColumnRef<T> column, Expr<T> lower, Expr<T> upper)	(col BETWEEN lo AND hi)
NullCheck	const NullCheck(Expr operand, bool isNull)	(x IS NULL) / (x IS NOT NULL)
VectorLiteral	const VectorLiteral(Vector value)	$n::vector
ExprVisitor<R>	interface	one visit... method per node type

Example: a case-insensitive equality the helpers don’t provide.

final byEmail = BinOp<bool>(
  FuncCall<String>('lower', [AuthorTable.email]),
  '=',
  Literal<String>(email.toLowerCase()),
);
final author = await Query<Author>(AuthorTable.metadata).where(byEmail).first(db.context());

Danger

BinOp.op, UnaryOp.op and FuncCall.name are written into the SQL verbatim. Use constant strings only.

See also: Query builder, Filter and paginate, Use raw SQL.