usm-adapter-postgres

Universal Schema Model — the Postgres/knex adapter for usm-core.

Provides the concrete, SQL-facing half of the engine:

• KnexCollectionSearcher — implements the usm-core Searcher contract: compiles Hasura-style where / order_by / limit / offset / distinct_on and aggregates into knex queries and hydrates rows. Relationship filters become correlated EXISTS subqueries; where count predicates become correlated count subqueries; a distinct_on becomes a DISTINCT ON subquery the caller's ordering, limit and aggregates are applied over (so a count counts groups).
• WhereBuilder / OrderByBuilder — the query compiler.
• KnexModelCollection — extends core's ModelCollection, implementing the batched key loader against a Postgres table via knex. App collections extend this and declare static searchFields / searchRelationships.
• knexSearchSchema(model) — reflects the model's collections (via core) and wires them to a knex searcher factory.
• NotifyChangeSource + PostgresDatabaseListener — a LISTEN/NOTIFY change source for near-real-time subscriptions.
• installChangeTriggers(knex, { tables, channel }) — installs statement-level triggers that emit one NOTIFY per mutating statement (the table name as payload). Bulk-load safe.
• connectKnexClient / connectPostgresClient — connection helpers.

Install

npm install usm-adapter-postgres knex pg

knex and pg are peer dependencies.

Usage

A collection declares its whole shape as one static definition; the model type is generated from it, so no hand-written ModelType subclass is needed.

import { KnexModelCollection, knexSearchSchema, connectKnexClient } from "usm-adapter-postgres";

class AuthorCollection extends KnexModelCollection {
    static definition = {
        table: "authors",
        primaryKey: "id",
        fields: { id: "ID", name: "String" },
        relationships: {
            books: {
                to: "Book", type: "many",
                joins: [{ table: "books", column: "id", references: "author_id" }]
            }
        }
    };
}

class Model {
    constructor(knex){
        this.knex = knex;
        this._collections = { Author: new AuthorCollection(this, "Author") /* , ... */ };
        this._searchSchema = knexSearchSchema(this);
    }
    get collections(){ return this._collections; }
    get searchSchema(){ return this._searchSchema; }
    log(){}
}

const knex = await connectKnexClient({ url: process.env.DB_URL });
const model = new Model(knex);
await model.collections.Author.search({ where: { name: { _ilike: "%a%" } }, limit: 10 });

Relationship joins

joins is an ordered list of hops. Each hop's column names a column on the previous table (the collection's own table for the first hop) and references the matching column on that hop's table. The last hop's table is the target, so a junction is just two hops:

categories: {
    to: "Category", type: "many",
    joins: [
        { table: "books_categories", column: "id", references: "book_id" },
        { table: "categories", column: "category_id", references: "id" }
    ]
}

A hop keyed on more than one column uses on instead, listing every pair. All pairs are correlated together, so composite primary keys traverse and filter correctly:

// election_candidacies (race_id, candidate_id) -> election_candidacy_states
states: {
    to: "CandidacyState", type: "many",
    joins: [{
        table: "election_candidacy_states",
        on: [
            { column: "race_id", references: "race_id" },
            { column: "candidate_id", references: "candidate_id" }
        ]
    }]
}

{ column, references } is shorthand for a single-pair on, so the two forms mix freely within one path.

Subscriptions

import { NotifyChangeSource, PostgresDatabaseListener, connectPostgresClient, installChangeTriggers } from "usm-adapter-postgres";

// once, against the DB you own:
await installChangeTriggers(knex, { tables: ["authors", "books"], channel: "app_change" });

// at startup:
const listener = new PostgresDatabaseListener(await connectPostgresClient({ url: process.env.DB_URL }));
const changeSource = new NotifyChangeSource(listener, { channel: "app_change", tables: "*" });
await changeSource.start();
// hand changeSource to a usm-core SubscriptionManager

Local development note

The inter-package dependency on usm-core is declared as file:../usm-core for in-repo development. When publishing, replace it with a version range (e.g. "usm-core": "^0.1.0"), or use a workspace / npm install --install-links.