§reference

PostgreSQL

Native UUID, JSONB, RETURNING, advisory locks, and TLS when the server offers it. Running the framework on PostgreSQL is the cleanest fit: every feature the framework wants, with no workaround.

Quick reference

dialectDialect::Postgres
driversqlx’s PostgreSQL driver
urlpostgres://host:5432/db; in a config file jdbc:postgresql://host:5432/db
loginusername and password, never in the url
uuidUUID, native
jsonJSONB
timestampsTIMESTAMP (without time zone), holding UTC
RETURNINGyes, on inserts and updates
keyed lockSqlKeyedLockProvider: pg_advisory_lock
migration lockan advisory lock, per database
TLSused when the server offers it (sslmode=prefer); verify-full checks the certificate
tested againstpostgres:17-alpine; TLS against postgres:17

Migration order

The framework’s two event tables live in a schema of their own, eventlog, inside the database you connect to. The first migration creates the schema and then the tables in it:

migrations/
  V0001__eventlog.sql           # CREATE SCHEMA IF NOT EXISTS eventlog; events + event_notifications + indexes
  V0002__scheduled_tasks.sql    # only with distributed jobs or the local event handler
  V0003__wallets.sql            # your own tables

No grant is needed: the connecting user can normally create a schema in its own database. That is why PostgreSQL is the least work of the three. Copy the framework’s files from wallet-example/migrations/postgres; each table’s DDL is on its page under Framework tables.

Your own tables

A table a repository’s ready-made methods work on has id, version and state columns - schemagen writes the Table and TableRecord code only for such a table. A model’s table also has created_date and updated_date.

columntyperole
idUUID PRIMARY KEYread as ekbatan::DbUuid
versionBIGINT NOT NULLoptimistic locking: updates carry WHERE version = ? and raise it
stateVARCHAR(24) NOT NULLDELETED marks a soft-deleted row; reads skip it
created_dateTIMESTAMP NOT NULLUTC, from the model
updated_dateTIMESTAMP NOT NULLUTC, from the model

Things to know

JSONB, not JSON, for the framework’s JSON columns. The binary form compares by value and can be indexed; JSON is stored text.

A schema, not a database. eventlog is a schema inside the connected database, so every database - every shard - has its own event tables, even when several shards share a server. Hand-written SQL names them eventlog.events and eventlog.event_notifications.

Plain TIMESTAMP for your columns, never TIMESTAMPTZ. The framework writes UTC digits into zone-less columns and reads them back as UTC. Run the server in UTC (TZ=UTC, or timezone = 'UTC') so NOW() and column defaults agree. See Always UTC.

Except in scheduled_tasks. db-scheduler’s layout has timestamp with time zone there, and the job registry reads those columns only in that type. Copy that file as it is.

Partial indexes suit this workload. The examples index undelivered events with WHERE delivered = FALSE and due notifications with WHERE state IN ('PENDING', 'FAILED'): the index holds only the rows still waiting, and stays small however large the tables grow.

Many ids in one read are sent as one array parameter: id = ANY($1). So find_all_by_ids sends the same statement however many ids it is given.

CREATE INDEX CONCURRENTLY refuses to run inside a transaction. Mark its migration file with -- no-transaction as its first line, and write it so it can run twice (IF NOT EXISTS): see Migrations.

Advisory locks are per database. A keyed lock on one shard’s database does not block the same key on another’s, even on one server. A pooler that changes connections between statements (PgBouncer in transaction mode) breaks session locks: point the lock pool and the migrator straight at the database.

TLS. The image’s own certificate is self-signed, so verify-full refuses it; give the CA in sslrootcert. The trusted CAs are Mozilla’s list, built in; the operating system’s store is not read. See Connecting.

Driver settings are sqlx’s: sslmode, sslrootcert, sslcert, sslkey, statement-cache-capacity, application_name. A Java driver’s ApplicationName is refused; write application_name.

See also