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 provides typed queries and foreign-key relations, explicit includes, raw SQL, transactions with savepoints, and locks. Each repository call is one SQL statement; related rows never load lazily. Statement shapes are prepared once per connection, just as for plain reads.

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 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 dart run dartway_cli: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.

For edits or derived values shared before and after insertion, use the draft as a value snapshot. A project-written toDraft() extension copies every column from the stored row; shared logic takes New<Entity>Row. Update with the edited snapshot's withId(current.id) after reading the current row under a transaction lock, preserving columns the command does not own. A read-only extension on the draft is also available through stored.toDraft(). See the complete shared-edit example.

"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 on a draft built only from the command 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]) when the draft carries every value to write. For a partial edit, read the row and preserve its other columns; upsert replaces every non-key column.

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, unbounded})	the matching rows; without orderBy the order is whatever the database returns, so paging needs one. unbounded: says why a read without limit stays small ("a board's few columns"); dartway check warns about a request handler's find with neither (unboundedRead), and the repository refuses it beside a limit or blank
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])	on tables without state columns: 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])	on tables without state columns: 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 ordinary columns by id; retains and guards states. Throws DwRowNotFound for a missing id; DwStaleState for an existing row whose state no longer matches
updateWhere({where, set})	sets ordinary columns on every matching row; returns the count; refuses state assignments
updateWhereReturning({where, set})	the same, answering the rows as updated — what to publish, without a second read
transition(id, column: …, to: …)	the changed row or null when missing/disallowed; one guarded state update
transitionWhere(where: …, column: …, to: …)	only the rows that moved; compare returned ids with requested ids
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.

Relations and includes

Import the generated dw_schema.dart to use the relation extensions. The generator derives a to-one from an FK field (sessionId → session) and a reverse to-many from the referencing entity's plural name. When two FKs reference the same table, name both directions on each: @DwForeignKey('user_profile', relation: 'authorProfile', reverse: 'authoredMessages'). Ambiguous pairs and relation names colliding with fields, id or reserved words warn and emit no relation in either direction until named with relation: and reverse:. Generation continues. A referenced row field named tablePath, include or includeSpec remains a generation error; rename the Dart field and retain its SQL name with @DwColumnName. Only row classes in the project's generation input participate; FKs to framework, module or SQL-only tables remain valid and get no relation in either direction.

final rows = await ctx.db.sessionBookings.find(
  where: (t) => t.session.startsAt.gte(day),
  orderBy: (t) => [t.session.startsAt.asc(), t.id.asc()],
  limit: page.fetchLimit,
  include: (t) => [
    t.session.include((s) => [s.service, s.coachProfile]),
    t.clientProfile,
    t.sessionReviews.take(1),
  ],
);
const t = SessionBookingRow.tableDef;
for (final row in rows) {
  final ClubSessionRow session = row.one(t.session);
  final List<SessionReviewRow> reviews = row.many(t.sessionReviews);
  // Map the explicitly loaded rows to the project's DTOs.
}

find, findFirst and findById keep returning ordinary generated rows. row.one(token) returns the relation's row type (Row? for a nullable FK); row.many(token) returns List<Row>. Both throw StateError when the relation was not included. An included empty collection is [], an included missing nullable to-one is null. Inclusion belongs to that returned row instance; copyWith, drafts, later reads and writes do not inherit it. No casts or string keys are needed. Nested includes select their own relations explicitly and may extend beyond three levels.

Each to-many include needs .take(n, where: ..., orderBy: ..., include: ...) or .unbounded('<domain reason>', where: ..., orderBy: ..., include: ...). A bare to-many token is refused; dartway check also warns with unboundedRead at a handler's include site. A parent limit does not bound its children. Nonnegative child limits are bound parameters. Without a nonempty orderBy, take(n) orders children by their primary key. Columns reached through a nullable to-one have nullable Dart types, including grouping keys and aggregate values; grouped reads retain the NULL group. lock: together with include: is refused, even inside a transaction; keep locked reads separate.

Related columns work in every repository where callback, including count, exists, updateWhere and aggregates. Chains such as t.session.coachProfile.firstName work in filters and orders. Reverse collections have no asc/desc: use any, none, every, or a filtered count(where: ...) with equals/gt/gte/lt/lte:

where: (t) => t.sessionBookings.any((b) => b.status.equals(BookingStatus.booked))
where: (t) => t.sessionBookings.none((b) => b.status.equals(BookingStatus.cancelled))
where: (t) => t.sessionBookings.every((b) => b.status.equals(BookingStatus.attended))
where: (t) => t.sessionBookings.count(where: (b) => b.status.equals(BookingStatus.booked)).gte(2)

any is EXISTS, none is NOT EXISTS, and every is NOT EXISTS of a child whose predicate IS NOT TRUE: it includes parents without children and treats a null comparison as false. Counts are correlated subqueries. To-one paths use LEFT JOIN; mutations with such paths select matching parent ids within the same statement. Ordinary mutations keep their direct SQL. To-many includes aggregate each collection separately, avoiding row multiplication and cartesian products. Bounded or filtered parent reads use a lateral aggregate over a limited, ordered child subquery; reads with no parent limit, offset or filter group unbounded child collections once and join them by FK. Parent limit/offset counts parents. Related JSON rows pass through the same generated codec as ordinary reads, with exact timestamp microseconds, calendar dates, durations, bytes, doubles, enums, state columns, collections and nulls. The calendar-day-in-jsonb restriction for user-declared collection columns does not apply to included date columns.

Postgres does not create an index on an FK column. Generation warns if no unique constraint or index starts with that column, including FKs to external tables. Add DwTableIndex(['fieldId']) to the row's @DwSqlTable(indexes: [...]), regenerate and create a migration. A composite index starting with the FK already satisfies the warning.

Includes are reads, not an access path: the handler's access: / DwAccessRule.resource still owns authorization. findByIds remains useful when the caller already has the ids; raw SQL, transactions and locks keep their existing meanings.

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 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 or aggregate outside the typed relation API, 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.

State columns

A lifecycle status is a state column. Declare its initial values and directed moves as const data beside the enum, and annotate the non-null enum field on its row:

enum WorkStatus { open, running, completed, cancelled }

const workTransitions = DwStateTransitions<WorkStatus>(
  initial: {WorkStatus.open},
  moves: {
    WorkStatus.open: {WorkStatus.running, WorkStatus.cancelled},
    WorkStatus.running: {WorkStatus.completed, WorkStatus.cancelled},
  },
);

// In WorkItemRow:
@DwStateColumn(workTransitions)
final WorkStatus status;

Run dartway generate. A missing source entry is terminal; a self move is allowed only if declared. The table is ordinary data: tests can read initial, moves, allows and sourcesFor.

final moved = await ctx.db.workItems.transition(
  id,
  column: (t) => t.status,
  to: WorkStatus.running,
);
if (moved == null) ctx.refuse(AppRefusal.workNotOpen);

final changed = await ctx.db.workItems.transitionWhere(
  where: (t) => t.id.inList(requestedIds),
  column: (t) => t.status,
  to: WorkStatus.cancelled,
);
final unchangedIds = requestedIds.toSet().difference(changed.map((r) => r.id).toSet());

Each transition uses one guarded UPDATE ... WHERE ... AND status IN (...) RETURNING ..., with parameters for all values. A concurrent writer rechecks the guard against the stored row after waiting for its lock. transition returns null for a missing row or a disallowed move; transitionWhere returns only the rows that moved, leaving all other rows untouched. An empty predecessor set returns immediately. There is no preliminary read. Select the column explicitly, including on rows with multiple state fields.

All insert paths validate the initial set before writing, including the whole batch of insertAll. update(row) writes ordinary fields only and guards every state against the supplied row: a stale state on an existing row throws the catchable DwStaleState. A missing id throws DwRowNotFound on every table. Catch DwStaleState and translate it into the feature's conflict, or let the server answer dw.conflict (HTTP 409) without reporting an incident. Re-read before deciding whether to retry. Generated stored-row copyWith refuses an intentional state change with DwStateWriteException; passing the same row's state unchanged is allowed. A reconstructed row has no original state to compare: the repository guards the supplied state and never writes it. A state-only row has no ordinary update and is refused. updateWhere and updateWhereReturning refuse assignments to state columns. upsert and upsertAll on state tables are refused; use insert/tryInsert and transitions instead. These refusals are programming errors, not domain refusals: commands translate a transition's null/unchanged result into their own refusal.

dartway check reports stateWrittenDirectly for direct state writes through row copyWith, ordinary updates, upserts or raw SQL in server lib/. It reads formatted source rather than resolved types: typed rows and rows inferred from repository calls are recognized, including a local repository alias; arbitrary data flow or dynamically assembled SQL is outside this source check. The runtime guards still hold every repository write. Raw SQL is a privileged escape and must not be used to bypass state rules. A schema migration may rewrite stranded values through m.backfill; that is allowed only in lib/src/migrations/.

The framework repository's booking feature demonstrates the full path: booked → cancelled or attended, through its commands, publications and acceptance tests.