Insert, update, delete and upsert

Writes start from a Query<T>: insert(...), update(...) or delete(). Each returns a builder you finish with run (or one for inserts). By default every write ends in RETURNING * and gives you hydrated models back.

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

final ctx = db.context();
final ada = await authors().insert({'name': 'Ada', 'email': 'ada@example.com'}).one(ctx);
await posts().where(PostTable.authorId.eq(ada.id!)).update({'title': 'Renamed'}).run(ctx);
await posts().where(PostTable.authorId.eq(ada.id!)).delete().run(ctx);

Column maps

All three builders take column values as Map<String, Object?>:

• Keys are SQL column names. camelCase keys are converted to snake_case ('createdAt' → created_at), so either style works. Foreign keys use the physical name (author_id or authorId).
• Values are bound as parameters. Vector, geometric types and Lists are converted as described in SqlCompiler.bind.
• Write enum columns as .sqlValue and JSON columns through encodeJsonColumn (a Dart Map also works directly; a JSON array must be encoded).
• Leave out columns that have database defaults (id, created_at DEFAULT NOW()).

InsertQuery

class InsertQuery<T> {
  InsertQuery<T> values(Map<String, Object?> row);
  InsertQuery<T> returning([bool enabled = true]);
  InsertQuery<T> onConflictDoNothing([List<String> target = const []]);
  InsertQuery<T> onConflictDoUpdate(List<String> target, {List<String>? update});
  CompiledSql compile();
  Future<List<T>> run(DbContext db);
  Future<T> one(DbContext db);
}

Created by Query<T>.insert(firstRow). Emits INSERT INTO "table" (cols) VALUES (...), (...) [ON CONFLICT ...] [RETURNING *].

When to use: every insert, including bulk inserts and upserts. For INSERT ... SELECT or COPY, use raw SQL.

Method	Description
values(row)	Adds another row. Every row must have the same set of columns, or compile throws ArgumentError.
returning([enabled])	Toggles RETURNING * (on by default). With it off, run returns an empty list.
onConflictDoNothing([target])	ON CONFLICT [(target)] DO NOTHING. Without target, any unique or exclusion conflict skips the row.
onConflictDoUpdate(target, {update})	Upsert: ON CONFLICT (target) DO UPDATE SET col = EXCLUDED.col. See Upserts.
compile()	The SQL and parameters. Throws InvalidQueryBuilderException if there are no rows.
run(db)	Runs and returns the inserted rows (in VALUES order).
one(db)	Runs and returns the first row. Throws InsertReturnedNoRowsException when nothing came back (returning(false), or DO NOTHING skipped the row).

Example: a bulk insert.

final rows = [
  {'name': 'Ada', 'email': 'ada@example.com'},
  {'name': 'Grace', 'email': 'grace@example.com'},
];
final q = authors().insert(rows.first);
for (final r in rows.skip(1)) {
  q.values(r);
}
final inserted = await q.run(db.context());

Postgres allows at most 65,535 parameters per statement, so split very large batches (rows × columns) into chunks.

Upserts

InsertQuery<T> onConflictDoUpdate(List<String> target, {List<String>? update})

Parameter	Type	Default	Description
target	List<String>	–	Columns of a unique index or constraint, for example ['email']. Must not be empty (ArgumentError).
update	List<String>?	every inserted column except target	Columns to overwrite from the rejected row (EXCLUDED). If nothing is left to set, the clause becomes DO NOTHING.

When to use: “create or update” by a natural key, such as syncing records from another system or idempotent seeding. target must match an existing unique index or constraint exactly, or Postgres rejects the statement.

// Insert, or refresh the name of the author with this email.
final author = await authors()
    .insert({'email': 'ada@example.com', 'name': 'Ada Lovelace'})
    .onConflictDoUpdate(['email'])
    .one(db.context());

// Insert unless the email is taken; skip silently otherwise.
await authors()
    .insert({'email': 'ada@example.com', 'name': 'Ada'})
    .onConflictDoNothing(['email'])
    .returning(false)
    .run(db.context());

With RETURNING *, onConflictDoUpdate returns the row as inserted or as updated. onConflictDoNothing returns nothing for a skipped row, so use run rather than one there.

UpdateQuery

class UpdateQuery<T> {
  UpdateQuery<T> where(Expr<bool> predicate);
  UpdateQuery<T> all();
  UpdateQuery<T> returning([bool enabled = true]);
  CompiledSql compile();
  Future<List<T>> run(DbContext db);
}

Created by Query<T>.update(values). Emits UPDATE "table" SET col = $1, ... [WHERE ...] [RETURNING *].

When to use: set-based updates by predicate. To change one loaded model, filter by its primary key. For expressions in SET (such as count = count + 1), use raw SQL; SET values are always bound parameters.

Method	Description
where(predicate)	AND-s another predicate onto the one copied from the Query.
all()	Allows running with no WHERE at all (touching every row).
returning([enabled])	Toggles RETURNING * (on by default). With it off, run returns an empty list.
compile()	Throws InvalidQueryBuilderException when values is empty, or when there is no WHERE and all() wasn’t called.
run(db)	Runs and returns the updated rows.

There is no one() on updates. Take (await q.run(ctx)).single when you expect exactly one row.

final updated = await posts()
    .where(PostTable.id.eq(postId))
    .update({'title': 'New title', 'publishedAt': DateTime.now().toUtc()})
    .run(db.context());
final post = updated.single;

The update copies the Query’s WHERE when you call update(...); later where calls on the original Query don’t affect it.

DeleteQuery

class DeleteQuery<T> {
  DeleteQuery<T> where(Expr<bool> predicate);
  DeleteQuery<T> all();
  DeleteQuery<T> returning([bool enabled = true]);
  CompiledSql compile();
  Future<List<T>> run(DbContext db);
}

Created by Query<T>.delete(). Emits DELETE FROM "table" [WHERE ...] [RETURNING *].

When to use: deleting by predicate. Rows in other tables follow their foreign keys’ on_delete rule (SET NULL unless the schema says otherwise).

Method	Description
where(predicate)	AND-s another predicate onto the one copied from the Query.
all()	Allows deleting every row.
returning([enabled])	Toggles RETURNING *.
compile()	Throws InvalidQueryBuilderException when there is no WHERE and all() wasn’t called.
run(db)	Runs and returns the deleted rows.

// Delete expired sessions, without fetching them back.
await sessions()
    .where(SessionTable.expiresAt.lt(DateTime.now().toUtc()))
    .delete()
    .returning(false)
    .run(db.context());

// Empty a table on purpose.
await sessions().delete().all().run(db.context());

Affected row counts

run returns the RETURNING rows, or an empty list when RETURNING is off. For just a count, keep RETURNING on and use .length, or run the compiled statement and read affectedRows:

final c = posts().where(PostTable.authorId.eq(3)).delete().returning(false).compile();
final result = await db.execute(c.sql, parameters: c.params);
print(result.affectedRows);

See also: CRUD guide, Errors, Transactions.