Database, contexts and transactions

Database owns the connection pools. Queries don’t run on it directly; they run on a DbContext that it hands out: a pool context for one-off statements, a transaction context inside transaction, or a pinned connection inside withConnection.

import 'package:gisila_orm/gisila.dart';

final db = await Database.connect(await DatabaseConfig.fromEnvironment());
final ctx = db.context();
print(await Query<Author>(AuthorTable.metadata).count(ctx));
await db.close();

Database

class Database {
  static Future<Database> connect(
    DatabaseConfig config, {
    String? defaultConnectionName,
    bool eager = false,
    Future<void> Function(Connection connection)? onOpen,
    QueryObserver? onQuery,
  });
}

When to use: create one per process at startup and share it. It is safe to use from concurrent requests: each statement borrows a pooled connection.

Database.connect

Parameter	Type	Default	Description
config	DatabaseConfig	–	Named connections.
defaultConnectionName	String?	config.defaultConnection.name	Connection used when a call doesn’t name one. Throws DatabaseConfigurationException if unknown.
eager	bool	false	Create every connection’s pool now. Otherwise only the default pool is created up front and the rest on first use.
onOpen	Future<void> Function(Connection)?	null	Runs on every new physical connection. The place for session settings that must survive the pool replacing a connection, such as SET search_path or SET TIME ZONE. Connection is from package:postgres.
onQuery	QueryObserver?	null	Called after every statement with SQL, parameters, duration and error.

Creating a pool doesn’t open a connection, so connect succeeds even when the server is down; the first query fails instead. Call ping at startup to fail fast. For a connection whose ssl_mode is prefer, connect asks the server once whether it supports TLS.

Members

Member	Signature	Description
context	DbContext context([String? connectionName])	A PoolDbContext for the default or named pool. Cheap; create freely.
execute	Future<Result> execute(String sql, {List<Object?> parameters = const [], String? connectionName})	One-off statement with $1, $2, ... parameters. Same as context(name).execute(...).
transaction	Future<R> transaction<R>(Future<R> Function(TxDbContext tx) body, {String? connectionName, TransactionSettings? settings})	See Transactions.
withConnection	Future<R> withConnection<R>(Future<R> Function(ConnectionDbContext connection) body, {String? connectionName})	Runs body on one borrowed connection. See ConnectionDbContext.
ping	Future<void> ping([String? connectionName])	Runs SELECT 1; throws if the database is unreachable.
useConnection	void useConnection(String name)	Changes the default connection name. Throws ArgumentError if unknown.
defaultConnectionName	String	Current default connection name.
connectionNames	List<String>	All configured connection names.
close	Future<void> close()	Closes every pool. Later queries throw StateError. Calling it again does nothing.

Result and ResultRow come from package:postgres. Read a row with row.toColumnMap() (column name → value) or index it positionally.

Example: multiple connections.

final db = await Database.connect(config); // config has 'main' and 'replica'
final report = await Query<Order>(OrderTable.metadata)
    .where(OrderTable.total.gt(1000))
    .all(db.context('replica'));

Transactions

Future<R> transaction<R>(
  Future<R> Function(TxDbContext tx) body, {
  String? connectionName,
  TransactionSettings? settings,
})

Runs body in a Postgres transaction on one connection. If body returns, the transaction commits and transaction returns its value. If it throws, the transaction rolls back and the error is rethrown.

When to use: whenever several writes must succeed or fail together, or reads must see one consistent snapshot (with settings). Use tx for every statement inside body; a statement run on db.context() inside the body uses a different connection and is not part of the transaction.

Parameter	Type	Default	Description
body	Future<R> Function(TxDbContext tx)	–	The work.
connectionName	String?	default connection	Which pool to use.
settings	TransactionSettings?	null	Isolation level, access mode, deferrable. Ignored when the call becomes a savepoint.

Nesting. Calling db.transaction(...) again from code running inside a transaction body, on the same connection name, doesn’t open a second transaction. It runs as a savepoint of the outer one, so it doesn’t need a second connection and stays atomic with the outer work. A throw inside it rolls back only its own work; if you catch the error, the outer transaction carries on.

TransactionSettings and IsolationLevel come from package:postgres, which gisila_orm doesn’t re-export. Add postgres to your pubspec.yaml and import it:

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

final balance = await db.transaction(
  (tx) async {
    final rows = await tx.execute('SELECT balance FROM accounts WHERE id = \$1', parameters: [id]);
    return rows.first.toColumnMap()['balance'] as int;
  },
  settings: TransactionSettings(isolationLevel: IsolationLevel.serializable),
);

DbContext

abstract class DbContext {
  Future<Result> execute(String sql, {List<Object?> parameters = const []});
  Stream<ResultRow> stream(String sql, {List<Object?> parameters = const []});
}

What every query runs on. Query.all, InsertQuery.run and the rest take a DbContext, so the same code works inside and outside a transaction.

When to use: type your repository and service methods as taking a DbContext. Callers then decide whether the work joins a transaction.

Method	Description
execute(sql, {parameters})	Runs one statement. Parameters use $1, $2, ... in list order. Server errors are rethrown as typed PostgresExceptions.
stream(sql, {parameters})	Streams rows without buffering. A statement starting with SELECT, VALUES or TABLE is read through a server-side cursor, 500 rows (cursorBatchSize) per round trip, inside a transaction that holds one connection until the stream finishes or is cancelled. Pausing the subscription stops fetching. Other statements are run and buffered.

Future<Author?> findByEmail(DbContext db, String email) =>
    Query<Author>(AuthorTable.metadata).where(AuthorTable.email.eq(email)).first(db);

await findByEmail(db.context(), 'ada@example.com');          // outside a transaction
await db.transaction((tx) => findByEmail(tx, 'ada@example.com')); // inside one

PoolDbContext

class PoolDbContext implements DbContext {
  PoolDbContext(Pool<Connection> pool, {QueryObserver? observer});
}

Returned by db.context(). Each execute borrows a connection, runs the statement and returns the connection, so consecutive statements may run on different connections. Session state (SET, temp tables, advisory locks) doesn’t carry over; use withConnection or a transaction for that.

TxDbContext

class TxDbContext implements DbContext {
  Future<R> savepoint<R>(Future<R> Function(TxDbContext tx) body);
  Future<void> rollback();
}

The tx passed to a transaction body. Every statement runs in the same transaction.

Member	Description
execute, stream	As on DbContext. stream declares a cursor in the current transaction.
savepoint(body)	Runs body inside SAVEPOINT. A throw rolls back to the savepoint and is rethrown; the outer transaction stays usable. Otherwise the savepoint is released and its work commits with the outer transaction.
rollback()	Rolls the transaction back explicitly.

When to use savepoint: an optional step that may fail without losing the rest of the transaction, such as inserting a row that might violate a unique constraint.

await db.transaction((tx) async {
  final order = await orders().insert({'total': 1200}).one(tx);
  try {
    await tx.savepoint((sp) => coupons().insert({'order_id': order.id, 'code': code}).run(sp));
  } on PostgresUniqueViolationException {
    // Coupon already used; the order insert is kept.
  }
});

Once a statement fails inside a transaction (outside a savepoint), Postgres aborts the transaction and refuses further statements until it ends. Catch errors you want to survive only around a savepoint.

ConnectionDbContext

class ConnectionDbContext implements DbContext {
  Future<R> transaction<R>(Future<R> Function(TxDbContext tx) body);
}

The context passed to db.withConnection(...). Every call uses the same physical connection, so session state carries across statements.

When to use: session-level advisory locks, SET without LOCAL, temporary tables. Undo any session state before returning, because the connection goes back to the pool. MigrationManager uses this to hold its advisory lock.

Member	Description
execute, stream	As on DbContext. A cursor stream opens a transaction on this connection, so other statements wait until it ends.
transaction(body)	A transaction on this connection, or a savepoint when already inside one.

await db.withConnection((c) async {
  await c.execute('SELECT pg_advisory_lock(42)');
  try {
    await c.transaction((tx) async {
      // ... work that must not run twice at once ...
    });
  } finally {
    await c.execute('SELECT pg_advisory_unlock(42)');
  }
});

QueryObserver

typedef QueryObserver = void Function(
  String sql,
  List<Object?> parameters,
  Duration elapsed,
  Object? error,
);

Called after every statement any context of the Database runs, including preloads, transaction statements and migrations run through it. error is non-null when the statement failed.

When to use: logging, metrics and tracing. Keep it fast; it runs inline after each statement. Don’t log parameters blindly, they may hold secrets.

slowQueryLogger

QueryObserver slowQueryLogger(Duration threshold, void Function(String message) log)

A ready-made observer that reports statements at or above threshold, and every failed statement, to log. It leaves parameters out.

final db = await Database.connect(
  config,
  onQuery: slowQueryLogger(const Duration(milliseconds: 200), print),
);

Other exports

Symbol	Description
const cursorBatchSize = 500	Rows fetched per round trip by cursor-backed stream.
runTransaction(owner, runTx, body, {settings, observer})	The function behind Database.transaction and ConnectionDbContext.transaction: opens a transaction, or a savepoint when one is already running for the same owner in the current zone. Use the higher-level methods instead.
Endpoint, SslMode	Re-exported from package:postgres.

See also: Use transactions, Transactions concept, Database configuration.