Skip to content
Gisila

When to drop to raw SQL

What it is

Section titled “What it is”

The typed builder covers single-table reads and writes. For everything else, gisila_orm gives you three escape hatches, from smallest to largest:

Level API You keep
A fragment RawSql<bool>(...) inside where, orderBy, having or join The rest of the typed query, hydration into models, preloading
A custom SELECT list query.compile(overrideSelect: '...') then db.execute The typed WHERE / joins / grouping; you read raw rows
A whole statement db.execute(sql, parameters: [...]) or tx.execute(...) Parameter binding, typed errors, transactions, query logging

All three still bind values as $n parameters, map errors to PostgresExceptions, and run on the same DbContext, so they mix freely with typed queries inside one transaction.

When to use it

Section titled “When to use it”
  • RawSql for one predicate the operators lack: lower(email) = $1, full-text search, EXISTS (subquery), JSON paths, comparisons on enum columns.
  • overrideSelect for aggregates over a typed filter: counts per group, sums, min and max.
  • db.execute for statements of their own: reports with joins and window functions, CTEs, UPDATE ... SET n = n + 1, INSERT ... SELECT, bulk UPDATE ... FROM (VALUES ...), junction-table writes, row locks (FOR UPDATE), DDL in hand-written tools.

When not to use it

Section titled “When not to use it”
  • For plain single-table filters, sorts and writes. The builder catches column typos at compile time and survives schema renames by failing to compile where raw strings would fail at runtime.
  • To build SQL out of user input. Raw SQL is only safe when values go in parameters. Identifiers that must be dynamic should come from an allow-list and be quoted with safeTk.

How it works

Section titled “How it works”
  • Placeholders are always $1, $2, .... In Dart string literals, escape the dollar sign ('\$1') or use a raw string (r'$1').
  • In RawSql, numbering restarts at $1 within the fragment; the compiler renumbers to fit the statement. Every parameter must be used.
  • db.execute returns a package:postgres Result: iterate it for ResultRows and call toColumnMap(), or read affectedRows.
  • If the result has a model’s columns, hydrate it with Hydrator<T>(Model.fromRow).hydrateAll(result), and even preload relations onto it with Preloader.
  • RawSql doesn’t quote identifiers. Write "posts"."title" where needed; generated tables are quoted in the builder’s own SQL, so lowercase names work unquoted too.

Example

Section titled “Example”

One request, all three levels:

import 'package:gisila_orm/gisila.dart';


Future<Map<String, Object?>> dashboard(DbContext db, String q) async {
  // 1. Typed query with a full-text fragment.
  final matches = await posts()
      .where(PostTable.publishedAt.isNotNull)
      .where(RawSql<bool>(
        "to_tsvector('english', \"posts\".\"body\") @@ plainto_tsquery('english', \$1)",
        [q],
      ))
      .limit(10)
      .all(db);


  // 2. Aggregate over a typed filter.
  final c = posts()
      .where(PostTable.publishedAt.isNotNull)
      .groupBy(PostTable.authorId)
      .compile(overrideSelect: '"posts"."author_id", COUNT(*)::int AS "n"');
  final perAuthor = {
    for (final row in await db.execute(c.sql, parameters: c.params))
      row.toColumnMap()['author_id']: row.toColumnMap()['n'],
  };


  // 3. A whole statement.
  final weekly = await db.execute('''
    SELECT date_trunc('week', published_at) AS week, COUNT(*)::int AS n
    FROM posts
    WHERE published_at > NOW() - make_interval(weeks => \$1)
    GROUP BY 1 ORDER BY 1
  ''', parameters: [12]);


  return {
    'matches': [for (final p in matches) p.toJson()],
    'perAuthor': perAuthor,
    'weekly': [for (final r in weekly) r.toColumnMap()],
  };
}
Section titled “Related”