The database: how do rows, queries and transactions work?

The server reads and writes PostgreSQL through dartway_orm, which dartway_core_server re-exports. It is small on purpose: typed single-table queries, raw SQL when that is not enough, transactions with savepoints, and locks. There are no joins and no lazy relations (D-011) — related rows load with findByIds, one query per relation for a whole list, so there is no N+1 to fall into and nothing an ORM hides behind a getter.

Row classes

A table is declared by a row class in the server package, in the rows file of the feature that owns it (lib/src/<feature>/<feature>_rows.dart). From example/dartway_example_server/lib/src/bookings/bookings_rows.dart:

import 'package:dartway_core_server/dartway_core_server.dart';
import 'package:dartway_example_shared/dartway_example_shared.dart';

part 'bookings_rows.dw.dart';

@DwSqlTable(
  'session_booking',
  indexes: [
    DwTableIndex(['sessionId', 'status']),
    DwTableIndex(['clientProfileId', 'createdAt']),
  ],
)
final class SessionBookingRow extends DwTableRow with _$SessionBookingRow {
  const SessionBookingRow({
    required this.id,
    required this.sessionId,
    required this.clientProfileId,
    required this.status,
    required this.createdAt,
  });

  @override
  final int id;

  @DwForeignKey('club_session', onDelete: DwOnDelete.cascade)
  final int sessionId;

  @DwForeignKey('user_profile', onDelete: DwOnDelete.cascade)
  final int clientProfileId;

  final BookingStatus status;
  final DateTime createdAt;

  static const tableDef = SessionBookingTable();
}

The rules, each enforced by dart run dartway_cli:dartway generate:

• the class is named <Entity>Row and extends DwTableRow — a row and the data object clients see (SessionBooking) never share a name, and a row never leaves the server;
• @override final int id; with required this.id — the bigserial primary key the database assigned: a row is what is stored, so its id is never missing and never needs a !;
• static const tableDef = <Entity>Table(); and part '<file>.dw.dart';;
• columns are the constructor's fields; nullability is the field's type.

Annotation	Meaning
@DwSqlTable(name, indexes: […])	the table name; indexes name Dart fields, not columns
DwTableIndex(fields, {unique, name})	an index; named <table>_<columns>_idx (_key when unique) unless name is given
@DwForeignKey(table, onDelete: …)	a foreign key to that table's id; the field is int or int?. DwOnDelete.noAction (default), restrict, cascade, setNull
@DwUniqueColumn()	a single-column unique constraint
@DwColumnName(name)	the SQL column name; snake_case of the field otherwise
@DwDefaultValue(sql), DwDefaultValue.now()	a database default

A database default applies only to rows written without the column — rows that existed when the column was added, and raw SQL inserts. A repository insert always writes every column, so the value a new row gets in Dart comes from the constructor's default (this.bookedCount = 0).

Column types

Dart field	Column	Notes
int	bigint
double	double precision
bool	boolean
String	text
DateTime	timestamp with time zone	read back in UTC; connections run in UTC
DwCalendarDay	date	canonical YYYY-MM-DD; Gregorian years 0001–9999; no time or zone
Duration	bigint	microseconds: exact and sortable
Uint8List	bytea
an enum	text	its name (D-008): adding a value needs no migration
List<E>	jsonb	E is int, double, String or bool, nullable or not
List<E> of an enum	jsonb	an array of the values' names; E not nullable. A name no value has any more fails the read (DwDecodeException)
Map<String, V>	jsonb	the same element types

A data object is not a column type: a row holds ids, and the handler builds the object.

What dartway generate writes

• <file>.dw.dart: ==, hashCode, toString, a copyWith (nullable fields take a DwFieldPatch — keep, set or clear; the id is kept, a copy of a stored row is the same row), the draft New<Entity>Row and the table class <Entity>Table extends DwTableDef, whose getters are the typed columns (t.status, t.createdAt).
• lib/generated/dw_schema.dart: the project's DwDatabaseSchema (the migration tools and the server's startup check compare against it) and one getter per table on DwDatabaseHandle, named by the plural of the entity:

extension DartwayExampleDb on DwDatabaseHandle {
  DwTableRepository<SessionBookingRow, SessionBookingTable>
  get sessionBookings => repository(SessionBookingRow.tableDef);
  // one per table
}

Rows and drafts

A row not stored yet has no id, so it is not a row: it is its draft. New<Entity>Row is generated beside every row class — the same constructor without id, the same defaults — and it is what the inserting methods take; each answers the stored row, id included:

final booking = await ctx.db.sessionBookings.insert(
  NewSessionBookingRow(
    sessionId: session.id,
    clientProfileId: me.id,
    status: BookingStatus.booked,
    createdAt: DateTime.now(),
  ),
);
booking.id; // an int, assigned by the database

A draft is a value like the row: it compares, hashes and prints by its columns, and has a copyWith of its own. withId(id) turns it into the row stored under an id already known.

"Create or save" by an optional id takes one of two shapes, decided by what the command carries:

• The command carries every column of the row — an admin form over the whole of a table with no owner, no creation time, no counter the command does not set. The draft is the whole row, and withId saves it (update of an id that is gone throws DwRowNotFound):

  final draft = NewClubServiceRow(title: command.title.trim(), /* every column */);
  final saved = switch (command.id) {
    null => await ctx.db.clubServices.insert(draft),
    final id => await ctx.db.clubServices.update(draft.withId(id)),
  };

• Otherwise — the usual case — the row has columns the command does not own: its owner, when it was created, what other commands set. Read the stored row, by its id and its owner, and change what the command owns; withId would overwrite the rest with the draft's defaults:

  final current = await ctx.db.invoices.findFirst(
    where: (t) => t.id.equals(id) & t.ownerProfileId.equals(me.id),
    lock: DwRowLock.forUpdate,
  );
  if (current == null) ctx.refuse(DwCoreRefusal.notFound);
  final saved = await ctx.db.invoices.update(
    current.copyWith(note: DwFieldPatch.set(command.note)),
  );

  A row keyed by a unique column rather than an id (one settings row per member) is upsert(draft, conflictOn: (t) => [t.profileId]).

Ids are always the database's: there is no insert with an id of one's own, which would also leave the bigserial sequence behind the rows and fail the next insert.

So ctx.db.sessionBookings is a DwTableRepository inside a handler, server.db.sessionBookings next to a running server, and tx.sessionBookings inside a transaction body. Changing a row class means regenerating and writing a migration (migrations).

DwTableRepository

Every method is one statement and one round trip (two inside a transaction). The statement's text depends only on the shape of the call — which columns, which operators — never on the values, so each shape is prepared once per connection and reused.

Method	Returns
find({where, orderBy, limit, offset, lock})	the matching rows; without orderBy the order is whatever the database returns, so paging needs one
findFirst({where, orderBy, lock})	the first row or null
findById(id, {lock})	the row or null
findByIds(ids)	the rows with those ids, in one statement whatever their number; missing ids are absent, the order unspecified — index the result by id
count({where, distinct})	how many rows — or, with distinct: (t) => t.column, how many distinct non-null values among them (members, not their entries)
countBy(group, {where, distinct})	Map of the group column's value → count; a group without rows is absent
sum(of, {where}), sumBy(group, of, {where})	the sum in the column's own type, 0 over no rows
max(of, {where}), min(of, {where})	the extreme value of a comparable column, null over no rows or only null cells
maxBy(group, of, {where}), minBy(group, of, {where})	Map of the group's value → extreme; a group whose cells are all null is absent
findFirstPer(group, {orderBy, where})	Map of the group's value → its first row in orderBy order: the latest plan of each member, in one statement (DISTINCT ON)
exists({where})	whether any
insert(draft)	the row as stored, with its id and every value read back
tryInsert(draft, onConflict: …)	the stored row, or null when it conflicted
upsert(draft, conflictOn: (t) => [t.key])	inserts, or writes every other column over the row with the same unique key; the stored row either way, in one statement with no race between "not there" and "insert"
upsertAll(drafts, conflictOn: (t) => [t.slug])	every row inserted, or written over the row with the same key — unless that row already holds exactly these values — in one statement; the count of rows inserted or changed, 0 for a second run with the same rows. What DwSeedRows runs
insertAll(drafts)	the rows as stored, in order, in one statement
update(row)	writes every column by id; throws DwRowNotFound when no row has that id — an update that changed nothing is a failure, not a quiet success
updateWhere({where, set})	sets columns on every matching row; returns the count
updateWhereReturning({where, set})	the same, answering the rows as updated — what to publish, without a second read
delete(id)	1, or 0 when there was none
deleteWhere({where})	the count

Conditions and order

where and orderBy receive the table and return conditions built from its columns:

final alreadyBooked = await ctx.db.sessionBookings.exists(
  where: (t) =>
      t.sessionId.equals(session.id) &
      t.clientProfileId.equals(me.id) &
      t.status.equals(BookingStatus.booked),
);

On a column	Conditions
any	equals(v) (null matches null cells), notEquals(v), isNull(), isNotNull(), asc(), desc(), set(v) for updateWhere
non-null values	inList(values), notInList(values) — one array parameter, so one prepared statement for every list length
comparable (int, double, String, DateTime, DwCalendarDay, Duration)	gt, gte, lt, lte, between(low, high) (inclusive)
String	like(pattern), ilike(pattern)
non-null int or double	increment(n) for updateWhere: column = column + n, a negative n decrements
nullable	setIfNull(v) for updateWhere: column = COALESCE(column, v) — fills a null cell, keeps a value
a list (jsonb array)	isEmptyList(), isNotEmptyList(), contains(v), containsAny(values) — an enum list matches by the names it stores; a null cell is neither empty nor not

Conditions combine with &, | and .not(). Expressions that make no sense for a type do not compile: no gt on a bool, no like on a DateTime. Null follows Dart, not SQL's three-valued logic: a comparison against a null cell is false, and its not() is true.

Values are always bound parameters; nothing passed to a condition is spliced into SQL.

DwCalendarDay is a date, not midnight UTC and not a DateTime. It can be used directly as a row field, including nullable fields, and supports equality, ordering, range queries, inList, and indexes like other scalar columns. Lists and maps of DwCalendarDay are not supported as jsonb columns because JSONB reads back plain JSON values rather than framework value objects. Choose the intended calendar zone in application code, then pass its year, month, and day to the constructor.

A repository insert binds every column, so a SQL DEFAULT CURRENT_DATE does not supply a missing date through the generated repository. Pass the day explicitly or use SQL that omits the column. Existing timestamp with time zone columns remain instants. Converting one is an explicit project migration: choose the civil zone, for example ALTER TABLE events ALTER COLUMN starts_at TYPE date USING (starts_at AT TIME ZONE 'America/Los_Angeles')::date;. The selected zone determines the day.

tryInsert and DwOnConflict

A unique constraint is the only race-free "insert unless it exists". tryInsert skips the conflicting row and returns null:

final inserted = await ctx.db.sessionReviews.tryInsert(
  NewSessionReviewRow(
    bookingId: booking.id,
    rating: command.rating,
    text: command.text,
    createdAt: ctx.now,
  ),
  onConflict: DwOnConflict.doNothing((t) => [t.bookingId]),
);
if (inserted == null) ctx.refuse(DartwayExampleRefusal.alreadyReviewed);

The target columns name the unique constraint the conflict is expected on; an empty list accepts a conflict on any of them. Checking with exists and then inserting does not work: two calls both see nothing and both insert, or the second fails on the constraint.

updateWhere

A conditional update is a compare-and-set in one statement. The example's read positions (example/dartway_example_server/lib/src/chat/logic/chat_lookups.dart) move forward only:

await db.chatReadPositions.updateWhere(
  where: (t) =>
      t.profileId.equals(profileId) &
      t.channelId.equals(message.channelId) &
      (t.sentAt.lt(message.sentAt) |
          (t.sentAt.equals(message.sentAt) & t.messageId.lt(message.id))),
  set: (t) => [t.messageId.set(message.id), t.sentAt.set(message.sentAt)],
);

A counter moves in the statement, so concurrent changes all count — reading it, adding one and writing it back would lose the increments another transaction made in between:

await db.chatUnreads.updateWhere(
  where: (t) => t.channelId.equals(channelId) & t.profileId.notEquals(authorId),
  set: (t) => [t.count.increment(1)],
);

increment exists only on a non-null number column: on a nullable one NULL + 1 is NULL, and treating null as zero is the project's decision to write, not the ORM's to hide.

Transactions

Future<R> transaction<R>(
  Future<R> Function(DwDatabaseHandle tx) body, {
  DwIsolationLevel? isolation,
});

• The body gets tx, a handle bound to one connection; the transaction commits when the body completes and rolls back when it throws.
• Inside a transaction, transaction opens a savepoint: a failure rolls back only the nested part. Only the outermost transaction chooses isolation.
• A statement error aborts the whole Postgres transaction, so catching it inside the body does not save the transaction: the body goes on, and at the end the transaction is rolled back and the error rethrown — Postgres would silently turn that COMMIT into a rollback, and a commit that did not happen must not look like one. To survive an expected conflict, use tryInsert, or run the risky statement in a nested transaction (a savepoint) and catch around that.
• A body that returns while its statements are still running is rolled back with a StateError: await them. A handle used after its transaction ended, or an outer handle used while a nested one is open, throws.
• DwIsolationLevel.serializable conflicts surface as DwSerializationFailure; the caller retries the whole transaction.

In a handler, use ctx.transaction: it also ties the call's publications and jobs to the commit (handlers). A top-level ctx.transaction — the one that really opens a transaction, wherever it is opened: a transactional command, a route, a non-transactional command, a job, a startup step — is retried on a serialization failure or a deadlock, up to three attempts, with the per-call memo cleared between them. A nested one (a savepoint) is not retried by itself: the conflict rises to the top-level transaction, which runs again. So a ctx.transaction body may run up to three times and must do nothing outside the database that a retry would repeat. transaction called on a handle rather than on ctx is not retried for you.

Row locks

final session = await ctx.db.clubSessions.findById(
  command.sessionId,
  lock: DwRowLock.forUpdate,
);

DwRowLock.forUpdate waits for rows another transaction holds; DwRowLock.forUpdateSkipLocked returns only rows nobody holds — how concurrent workers claim distinct rows from one queue. A lock is allowed only inside a transaction and throws StateError outside one, where it would end with the statement: it would read like protection and be none. The example's BookSession locks the session row, so the capacity check and the insert of every booking of that session queue behind each other.

Advisory locks

Some rules have no row to lock: "one code request per identifier at a time", "one import per account". advisoryLock and tryAdvisoryLock lock a pair of 32-bit integers instead:

Future<void> advisoryLock(int namespace, int key);   // waits for the lock
Future<bool> tryAdvisoryLock(int namespace, int key); // false when it is held

Both are transaction-scoped — released at commit or rollback, never leaked — and throw StateError outside a transaction. namespace keeps unrelated features apart; the framework uses the 0x4457xxxx range for its own locks (identifiers, idempotency keys, pending uploads), so keep project namespaces clear of it. A string key must be hashed to a 32-bit integer by the project.

advisoryLock waits and holds a pooled connection while it waits; tryAdvisoryLock answers at once, for work where "someone else is doing it" is itself the answer.

Settings

The app's settings are one data object, read and written through ctx.settings — a table of string keys and values is refused by dart run dartway_cli:dartway check (settingsKeyValueTable). Every reader of such a table parses text and supplies its own default (== 'true'), every first save of a key races another, and a value nobody reads is never noticed.

A settings object is a data object of the shared package with a default for every field and a fixed id (template/dartway_starter_shared/lib/src/settings.dart):

final class AppSettings extends DwDataObject with _$AppSettings {
  const AppSettings({this.appName = 'DartwayStarter', this.signUpEnabled = true});

  @override
  String get id => 'app';

  final String appName;
  final bool signUpEnabled;
}

Call	What it does
ctx.settings.read<AppSettings>()	the stored value, or the defaults while nothing is stored — never null; a read writes nothing
ctx.settings.save(value)	stores the whole value in one upsert and returns it: two first saves at once both succeed
ctx.settings.update<AppSettings>((current) => …)	reads, applies the change and stores the result with the row locked in between, so two edits of different fields both land; a change that leaves the value equal writes nothing

The value is stored in the framework's dw_setting table under the object's wire name, as the JSON the wire carries — which holds only what differs from the defaults. How it survives the object changing under it:

• a field added reads as its default;
• a field removed is ignored by reads, kept by update, dropped by save;
• a stored field that no longer decodes — an enum value that was removed, a field whose type changed — reads as that field's default, the rest as stored, and the server logs it once. A read never fails over what is stored. update writes such a field back as it was unless the change sets it — the version of the server that wrote it, the one a rollback returns to, still reads it — and save replaces the whole value, such fields included;
• a default changed changes every value that equalled the old default, since nothing of it was stored — say so in the change if that is not what is meant;
• the class renamed resets it to its defaults: the area is its wire name.

A nullable field is changed by a DwFieldPatch field of the command, applied with the helpers, so it can be cleared — never an empty string meaning none.

Values kept elsewhere before — a key/value table, a single-row table of the project's own — are carried across two production releases, following the same expand/contract rule as a rename. Release N carries the settings and stops reading the old table, keeping it so a rollback to the previous image can still pass its declared-table check. A project never writes dw_* tables itself; m.carrySettings is the one statement that does, merging one JSON object over what is stored:

await m.carrySettings('ClubSettings', fromSql: '''
  SELECT jsonb_strip_nulls(jsonb_build_object(
    'clubName', (SELECT value FROM app_setting WHERE key = 'clubName'),
    'bookingEnabled',
      (SELECT lower(value) = 'true' FROM app_setting WHERE key = 'bookingEnabled')))''');

Release N+1 drops the old table, once no running code relies on it:

await m.dropTable('app_setting');

A settings object is published like any data object (ctx.publish(AppChannels.settings, saved)) and read by the app with a DwSingleRequest. The type argument names the object: left to inference it is DwDataObject, and the call throws StateError.

A preference per member is not a settings object: it stays a row of the member's own table, which a job can query across members, and a reader without the row maps to the data object's own defaults.

Raw SQL

When a statement is not one table's — a WITH, a join the batch loaders cannot express, a UNION — write SQL. An aggregate over one table is not a reason: countBy, sumBy, maxBy and findFirstPer keep enum values typed, where a literal like 'accepted' in SQL breaks silently on a rename.

final rows = await db.query(
  'SELECT i.status, count(*) AS n FROM invoice i '
  'JOIN customer c ON c.id = i.customer_id '
  'WHERE c.region = @region GROUP BY i.status',
  params: {'region': 'north'},
);

• query(sql, params: {…}) returns List<DwResultRow>; parameters are @name, types inferred by the server — add a cast (@id::int8, @ids::int8[]) where it cannot infer one.
• DwResultRow is a read-only map: row['name'], row.get<T>('name') which throws DwDecodeException for a missing column or a value of another type (including null for a non-nullable T), and row.decode(column) through a table column's type.
• execute(sql, params: {…}) returns the affected row count; without params the text may hold several statements, sent in one round trip.
• notify(channel, payload) sends a Postgres notification, delivered at commit inside a transaction.

Never interpolate values into the SQL text: every distinct text is a separate prepared statement in the cache, and an injected value is an injection.

Errors

Driver errors are mapped, so a caller never needs package:postgres to tell a constraint from a lost connection:

Exception	When
DwUniqueViolation	SQLSTATE 23505; constraint names it
DwForeignKeyViolation	23503; constraint
DwSerializationFailure	40001
DwRowNotFound	update addressed an id that does not exist
DwDecodeException	a value does not fit the Dart type reading it — the schema and the row class drifted
DwDatabaseException	the base: code (SQLSTATE, or null when the server was never reached), message, detail

An exception that escapes a handler is a failure: 500, an incident id, an alert (alerts). A conflict the user caused is a refusal the handler decides on — prefer tryInsert over catching DwUniqueViolation, which also aborts the transaction.

The pool

DwPostgresDatabase.open(config) opens the pool and proves the database is reachable with one real connection. DwDatabaseConfig:

Field	Default
host, port, name, user, password	port 5432
ssl	true	encrypted, certificate not verified
caFile	unset	verifies the server's certificate against this CA instead (verify-full); refused together with ssl: false. The certificate needs a subjectAltName for the connecting host — no fallback to the deprecated CN field, unlike libpq — and extendedKeyUsage: serverAuth; see deploy
maxConnections	10	the ceiling of concurrent statements
applicationName	dartway
connectTimeout	15 s	also how long a caller waits for a free connection
queryTimeout	5 min
statementCacheSize	256	prepared statements kept per connection

Connections are kept until they break, with their prepared statements, and the most recently used one is handed out first so its cache stays warm. A cached statement invalidated by a schema change is prepared again and, outside a transaction, the statement is run again once. The server builds the pool from DwAppServer(database:); a script opens its own and closes it (template/dartway_starter_server/bin/seed_dev.dart).

Related

• Migrations — how a row class change reaches the database.
• Handlers and the call context — ctx.db and ctx.transaction.
• Jobs — SKIP LOCKED claims and LISTEN/NOTIFY in the framework's own queue.