Use raw SQL

You’ll run hand-written statements with bound parameters, embed fragments in typed queries, turn raw rows into models, and write a few statements the builder can’t.

Prerequisites

The blog schema from Define a schema.

Steps

1. Run a statement

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

final result = await db.execute(
  r'SELECT id, title FROM posts WHERE author_id = $1 AND published_at > $2',
  parameters: [authorId, DateTime.utc(2026, 1, 1)],
);
for (final row in result) {
  final m = row.toColumnMap();
  print('${m['id']}: ${m['title']}');
}

Placeholders are $1, $2, … in parameter order. Use a raw string (r'...') or escape the dollar sign ('\$1').
Inside a transaction, call tx.execute(...) instead.
Errors are typed: a unique violation is PostgresUniqueViolationException.

2. Get models back

When the result has a model’s columns, hydrate it:

final result = await db.execute(r'''
  SELECT p.* FROM posts p
  JOIN comments c ON c.post_id = p.id
  GROUP BY p.id
  ORDER BY COUNT(c.id) DESC
  LIMIT 10
''');
final popular = Hydrator<Post>(Post.fromRow).hydrateAll(result);

fromRow expects every non-nullable column, so select p.* or list them all.

3. Add a fragment to a typed query

final matches = await posts()
    .where(PostTable.publishedAt.isNotNull)
    .where(RawSql<bool>(
      r"""to_tsvector('english', "posts"."body") @@ plainto_tsquery('english', $1)""",
      [query],
    ))
    .orderBy(PostTable.publishedAt, desc: true)
    .limit(20)
    .all(db.context());

Number placeholders from $1 within each fragment; they’re renumbered to fit the statement. You can also sort by a fragment:

posts().orderBy(RawSql<double>(
  r"""ts_rank(to_tsvector('english', "posts"."body"), plainto_tsquery('english', $1))""",
  [query],
), desc: true);

4. Aggregate over a typed filter

final c = posts()
    .where(PostTable.publishedAt.isNotNull)
    .groupBy(PostTable.authorId)
    .compile(overrideSelect: '"posts"."author_id", COUNT(*)::int AS "n", MAX("posts"."published_at") AS "latest"');
final rows = await db.execute(c.sql, parameters: c.params);

5. Statements the builder can’t write

// Increment a counter atomically.
await db.execute(r'UPDATE posts SET view_count = view_count + 1 WHERE id = $1', parameters: [id]);

// Copy rows.
await db.execute(r'''
  INSERT INTO archived_posts (id, title, body)
  SELECT id, title, body FROM posts WHERE published_at < $1
''', parameters: [cutoff]);

6. Dynamic identifiers

Never interpolate user input into SQL. When a column or table name must vary, pick it from an allow-list and quote it:

const allowed = {'title', 'published_at'};
if (!allowed.contains(column)) throw ArgumentError.value(column, 'column');
final result = await db.execute(
  'SELECT id FROM posts ORDER BY ${column.safeTk} DESC LIMIT \$1',
  parameters: [limit],
);

7. Log what runs

final db = await Database.connect(
  config,
  onQuery: (sql, params, elapsed, error) =>
      print('${elapsed.inMilliseconds}ms ${error == null ? 'ok' : 'ERR'} $sql'),
);

The observer sees raw statements and builder statements alike.

Verify it works

final c = posts().where(RawSql<bool>(r'"posts"."id" = $1', [1])).compile();
print(c.sql); // ... WHERE ("posts"."id" = $1)

A fragment whose placeholders don’t match its parameters throws ArgumentError when compiled, before anything reaches the database.