Use transactions

You’ll write a “publish post” operation that changes several tables atomically, then add an optional step that may fail without undoing the rest, row locking, and a serializable read.

Prerequisites

The blog schema from Define a schema.
For isolation levels: postgres in your pubspec.yaml (dart pub add postgres).

Steps

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

Future<Post> publish(Database db, int postId) {
  return db.transaction((tx) async {
    final updated = await posts()
        .where(PostTable.id.eq(postId))
        .update({'status': PostStatus.published.sqlValue, 'published_at': DateTime.now().toUtc()})
        .run(tx);
    if (updated.isEmpty) throw StateError('post $postId not found');

    await tx.execute(
      'INSERT INTO audit_log (action, post_id) VALUES (\$1, \$2)',
      parameters: ['publish', postId],
    );
    return updated.single;
  });
}

Use tx for every statement inside the callback.
Whatever the callback returns, transaction returns after COMMIT.
Throwing (here, StateError) rolls everything back and rethrows.

2. Accept a `DbContext` in helpers

Helpers that take a DbContext join whatever transaction the caller is in:

Future<void> logAction(DbContext db, String action, int postId) => db.execute(
      'INSERT INTO audit_log (action, post_id) VALUES (\$1, \$2)',
      parameters: [action, postId],
    );

await db.transaction((tx) async {
  // ...
  await logAction(tx, 'publish', postId);
});

3. Let one step fail without losing the rest

Once a statement fails in a transaction, Postgres rejects everything after it. Wrap the optional step in a savepoint:

await db.transaction((tx) async {
  final post = await posts().insert({'title': 'Hello', 'author_id': authorId}).one(tx);
  try {
    await tx.savepoint((sp) => posts()
        .where(PostTable.id.eq(post.id!))
        .update({'slug': 'hello'})
        .run(sp));
  } on PostgresUniqueViolationException {
    // Slug taken: keep the post without one.
  }
});

A nested db.transaction(...) inside the callback behaves the same way: it becomes a savepoint of the outer transaction.

4. Lock rows you read before writing

await db.transaction((tx) async {
  final rows = await tx.execute(
    'SELECT id FROM posts WHERE id = \$1 FOR UPDATE',
    parameters: [postId],
  );
  if (rows.isEmpty) return;
  // No other transaction can change this post until we commit.
  await posts().where(PostTable.id.eq(postId)).update({'title': 'Edited'}).run(tx);
});

5. Set an isolation level

import 'package:postgres/postgres.dart' show AccessMode, IsolationLevel, TransactionSettings;

final stats = await db.transaction(
  (tx) async => (
    posts: await posts().count(tx),
    comments: await comments().count(tx),
  ),
  settings: TransactionSettings(
    isolationLevel: IsolationLevel.repeatableRead,
    accessMode: AccessMode.readOnly,
  ),
);

Both counts come from the same snapshot. For serializable, retry on SQLSTATE 40001; see the example in Transactions.

6. Roll back without throwing

await db.transaction((tx) async {
  await posts().insert({'title': 'Dry run', 'author_id': authorId}).run(tx);
  await tx.rollback();
});

Useful for dry runs. Throwing an exception is usually clearer.

Verify it works

final before = await posts().count(db.context());
try {
  await db.transaction((tx) async {
    await posts().insert({'title': 'Temp', 'author_id': authorId}).run(tx);
    throw StateError('boom');
  });
} on StateError {
  // expected
}
assert(await posts().count(db.context()) == before);