Skip to main content

postgres

PostgreSQL-backed Store for Webda — persists model instances as JSONB rows in Postgres with automatic table creation, view generation, and full WebdaQL query support.

When to use it​

  • You want a relational database backing store for Webda models with ACID guarantees.
  • You are already running PostgreSQL and want Webda models to live alongside existing tables.
  • You need to query across models using familiar SQL tooling while keeping the Webda model API.

Install​

pnpm add @webda/postgres

Configuration​

{
  "services": {
    "postStore": {
      "type": "PostgresStore",
      "model": "MyApp/Post",
      "postgresqlServer": {
        "host": "localhost",
        "port": 5432,
        "database": "myapp",
        "user": "webda",
        "password": "${POSTGRES_PASSWORD}"
      },
      "autoCreateTable": true
    }
  }
}

Or rely on libpq environment variables (PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD):

{
  "services": {
    "postStore": {
      "type": "PostgresStore",
      "model": "MyApp/Post",
      "autoCreateTable": true
    }
  }
}
ParameterTypeDefaultRequiredDescription
postgresqlServerClientConfig | PoolConfiglibpq env varsNoPostgreSQL connection config (host, port, database, user, password)
usePoolbooleanfalseNoUse a connection pool (pg.Pool) instead of a single client, required by transaction
tablestring—NoTable of every model of the store (subclasses and fallback models included), as in Webda 3
tablesobject{}NoPer-model table overrides: { "MyApp/Model": "my_table" }
autoCreateTablebooleantrueNoAutomatically create the JSONB table if it does not exist
viewPrefixstring""NoPrefix for auto-generated SQL views
viewsstring[]["regex:.*"]NoRegexp list of model names to generate views for
transaction"none" | "operation""none"NoOne transaction per operation, see Transactions
transactionIsolationstring"READ COMMITTED"NoIsolation level of the transactions

Relational store​

Webda/PostgresRelationalStore stores the models you list in typed tables (one column per field, primary and foreign keys, junction tables, indexes) generated from the model metadata by @webda/sql-schema, with the same model API. It is never the Registry and only stores listed models.

{
  "services": {
    "domain": {
      "type": "Webda/PostgresRelationalStore",
      "models": ["MyApp/User", "MyApp/Post", "MyApp/Comment"],
      "postgresqlServer": { "host": "localhost", "database": "myapp" },
      "schemaSync": "alter"
    }
  }
}
ParameterTypeDefaultDescription
schemaSyncnone | create | alter | validatecreateWhat init() does with the schema: nothing, create the missing tables, also add the missing columns / indexes / foreign keys of existing tables (never destructive), or only check
columnNamingpreserve | snake_casepreserveColumn names from the field names
extraColumnstring | false_extraJSONB column of the properties the schema does not declare
foreignKeysbooleantrueCreate the foreign key constraints
foreignKeysToJsonbbooleanfalseAlso a foreign key to the uuid of the JSONB table of a model served by a PostgresStore on the same server and database (table per model)
numericAsDecimalbooleanfalseNUMERIC instead of DOUBLE PRECISION for JSON number
appVersionstringpackage.jsonThe application version recorded in the Webda/SchemaVersion registry
allowIncompatiblestring[][]Elements whose blocking changes against a live version are accepted (BLOCK becomes a forced WARN)
versionHeartbeatnumber60Seconds between two heartbeats of the version, 0 for none
versionTtlnumber600Seconds after its last heartbeat a version still counts as live

Several application versions can run on the same database (blue/green, canary): each one registers its plan as a Webda/SchemaVersion in the Registry, init() refuses a plan incompatible with a live version (SchemaIncompatibleError, 409) and only ever expands the schema (nullable columns, CREATE INDEX CONCURRENTLY, foreign keys NOT VALID then validated). webda postgres schema show [--sql | --diff | --json] prints the plan, schema check [--against <version>] the compatibility table for CI, schema contract [--dry-run] what no live version uses any more, schema retire --version <v> and schema backfill --table t --from a --to b the rest of the expand/contract cycle. webda postgres import --models A,B --source Registry [--purge] promotes models from a JSONB store. @Index / @Unique JSDoc tags on the model attributes become indexes. See the documentation for the mapping, the schema lifecycle, the compatibility rules and the limitations.

Usage​

import { CoreModel, Model, Expose } from "@webda/core";

@Model()
export class Post extends CoreModel {
  @Expose() title: string;
  @Expose() content: string;
  @Expose() publishedAt: Date;
}

// With PostgresStore configured, the standard Webda model API is backed by Postgres:
const post = new Post();
post.title = "Hello Postgres";
await post.save(); // INSERT INTO posts ...

const found = await Post.ref(post.uuid).get(); // SELECT ... WHERE uuid = ?
const results = await Post.query("title CONTAINS 'Hello'"); // filtered query

// The table schema uses JSONB:
//   CREATE TABLE IF NOT EXISTS posts (
//     uuid TEXT PRIMARY KEY,
//     data JSONB NOT NULL
//   );

Transactions​

With "transaction": "operation", every statement of an operation runs in one PostgreSQL transaction: committed when the operation succeeds, rolled back when it throws. An operation writing several objects (a create, the patch of a parent, a counter increment) never leaves them half-written.

{
  "services": {
    "postStore": {
      "type": "PostgresStore",
      "usePool": true,
      "transaction": "operation",
      "transactionIsolation": "READ COMMITTED"
    }
  }
}
ParameterTypeDefaultDescription
transaction"none" | "operation""none""none": autocommit per statement (unchanged behavior). "operation": one transaction per operation
transactionIsolation"READ COMMITTED" | "REPEATABLE READ" | "SERIALIZABLE""READ COMMITTED"Isolation level of BEGIN
  • A pool is required: a transaction needs a connection of its own. "transaction": "operation" with "usePool": false fails at init() with the POSTGRES_TRANSACTION_REQUIRES_POOL error.
  • Lazy: the store takes part in every operation, but checks a connection out of the pool (and runs BEGIN) only on the first statement. An operation that never uses the store costs nothing.
  • Lifecycle: COMMIT runs once the method resolved (a streamed result fully written) and before Webda.OperationSuccess; ROLLBACK runs before Webda.OperationFailure. The connection is always released; a connection whose COMMIT or ROLLBACK failed is destroyed rather than given back to the pool. See the operation lifecycle.
  • Scope: the transaction follows the async call chain of the operation: code running under runAsSystem or in a nested callOperation takes part in it (nested operations join the outermost one, no savepoints). Code outside the operation (another request, a queue worker) does not see the uncommitted writes.
  • A failed statement aborts the transaction (PostgreSQL refuses any further statement): the next statements and the commit fail with SQL_TRANSACTION_ABORTED, even when the operation caught the first error.
  • Opting out: @Operation({ transaction: false }) keeps an operation in autocommit, e.g. a long streaming read that should not hold a connection for its whole duration.
  • Outside operations (CLI, queue workers, tests): store.withTransaction(async () => { ... }) commits when the function resolves and rolls back when it throws, or joins the current transaction. It works whatever transaction is set to.
  • Table creation (autoCreateTable) and createViews() always run on the pool, never in a transaction.
  • Webda/PostgresRelationalStore takes part the same way: inside an operation, a row and its junction rows are written on the connection of the operation transaction instead of a transaction of their own.
  • Concurrent statements of one operation (Promise.all) are safe: they are queued on its connection.
  • Not atomic across stores: each store commits on its own, in registration order; when the commit of a second store fails after the first one committed, the first one stays committed.
  • Events before commit: repository events (Created, Updated...) are emitted when the statement runs, before the commit: a listener may see data that is later rolled back.

Reference​