Skip to content

Versioned Data Access Layer for Node web apps, targeting Postgres

License

Notifications You must be signed in to change notification settings

permacommons/rev-dal

Repository files navigation

rev-dal

rev-dal is a TypeScript-first PostgreSQL data access layer with built-in revision support and rich model typing (including multilingual string helpers).

It is currently internally used in the Permacommons project family for two projects:

The need for consistent wiki-style revision metadata motivated the creation of a custom DAL.

Install

npm install github:permacommons/rev-dal

Quick Start

import createDataAccessLayer, { setDebugLogger, setLanguageProvider } from 'rev-dal';
import languages from './locales/languages.js';
import debug from './util/debug.js';

setLanguageProvider(languages);
setDebugLogger(debug);

const dal = createDataAccessLayer();
await dal.connect();

Language Provider

mlString relies on a language provider with these methods:

interface DalLanguageProvider {
  getValidLanguagesAndUndetermined(): string[];
  getFallbacks(lang: string): string[];
}

If you don't call setLanguageProvider, rev-dal uses a superset of the language lists currently used in lib.reviews and agpwiki. Apps that require strict language validation should set their own provider.

Debug Logger

setDebugLogger wires DAL logs into your app's logging system. The logger must expose db and error functions; any additional arguments are ignored.

SQL Grants Script

setup-db-grants.sql is parameterized. Example:

psql -v app_db=libreviews -v app_user=libreviews_user \
     -v test_db=libreviews_test -v test_user=libreviews_user \
     -f setup-db-grants.sql

Testing

rev-dal uses Node's built-in test runner with tsx to execute TypeScript tests.

npm test

Test Database Setup

Integration tests require PostgreSQL with the pgcrypto extension available. The test harness creates a fresh schema per run and cleans it up afterwards.

Provide a connection string via REV_DAL_TEST_DATABASE_URL:

export REV_DAL_TEST_DATABASE_URL="postgres://rev_dal_test_user:rev_dal_password@localhost:5432/rev_dal_test"
npm test

If your test user cannot create extensions, run this once as a superuser:

psql -d rev_dal_test -c 'CREATE EXTENSION IF NOT EXISTS "pgcrypto";'

Type Tests

Type-level checks live under test/types/ and are compiled with a dedicated config:

npm run test:types

Core Building Blocks

  • DataAccessLayer (src/lib/data-access-layer.ts) – Owns the shared pg.Pool, manages migrations, and keeps a per-instance ModelRegistry so constructors are isolated between DALs (useful for fixtures/tests).
  • Model runtime (src/lib/model.ts) – Implements camelCase ↔︎ snake_case mapping, validation/default handling, change tracking, and persistence primitives consumed by every manifest-driven model.
  • Manifest system (src/lib/create-model.ts, src/lib/model-manifest.ts) – Declarative manifests define schema, relations, revision support, and custom methods. defineModel returns a lazy proxy constructor whose types are inferred from the manifest.
  • Query builder (src/lib/query-builder.ts) – Builds SQL fragments for predicates, joins, ordering, pagination, and deletes. filterWhere wraps it with typed predicates for day-to-day usage.
    • Chainables include orderBy/limit/offset, whereIn, getJoin (auto-selects inline or batch join based on cardinality), whereRelated (predicate on a related table via manifest metadata), and chronologicalFeed for date-backed limit+1 pagination.
  • Revision helpers (src/lib/revision.ts) – Adds static/instance helpers (createFirstRevision, newRevision, etc.) to models flagged with hasRevisions: true.
  • Type helpers (src/lib/type.ts) – Fluent schema builders that feed manifest inference, including virtual field descriptors and multilingual string support via mlString.

Bootstrap & Lifecycle

import createDataAccessLayer from 'rev-dal';
import { initializeManifestModels } from 'rev-dal/lib/create-model';

const dal = createDataAccessLayer();
await dal.connect();
initializeManifestModels(dal); // registers every manifest that was imported during bootstrap

The DAL is initialised once at startup. Tests and fixtures may spin up isolated instances; disconnecting a DAL clears its registry so cached constructors do not leak across runs.

Defining Models

Models are split across two directories to avoid circular imports:

  • models/manifests/ – Schema declarations, types, validation helpers, and cross-model reference functions
  • models/ – Runtime behavior (complex static/instance methods that depend on other models)

Basic Structure

// models/manifests/user.ts - Schema, types, validation helpers
import dal from 'rev-dal';
import type { ManifestInstance, ManifestModel } from 'rev-dal/lib/create-model';
import { referenceModel } from 'rev-dal/lib/model-handle';
import type { ModelManifest } from 'rev-dal/lib/model-manifest';

const { types } = dal;

// Model-specific options and helpers
const userOptions = {
  maxChars: 128,
  illegalChars: /[<>;"&?!./_]/,
  minPasswordLength: 6,
};

export function canonicalize(name: string): string {
  return name.toUpperCase();
}

function containsOnlyLegalCharacters(name: string): true {
  if (userOptions.illegalChars.test(name)) {
    throw new Error(`Username ${name} contains invalid characters.`);
  }
  return true;
}

const userManifest = {
  tableName: 'users',
  hasRevisions: false as const,
  schema: {
    id: types.string().uuid(4),
    displayName: types
      .string()
      .max(userOptions.maxChars)
      .validator(containsOnlyLegalCharacters)
      .required(),
    suppressedNotices: types.array(types.string()),
    urlName: types
      .virtual()
      .returns<string | undefined>()
      .default(function (this: ModelInstance) {
        const displayName = this.getValue('displayName');
        return displayName ? encodeURIComponent(String(displayName).replace(/ /g, '_')) : undefined;
      }),
  },
  camelToSnake: {
    displayName: 'display_name',
    suppressedNotices: 'suppressed_notices',
  },
  relations: [
    {
      name: 'meta',
      targetTable: 'user_metas',
      sourceKey: 'userMetaID',
      targetKey: 'id',
      hasRevisions: true,
      cardinality: 'one',
    },
  ] as const,
} as const satisfies ModelManifest;

export type UserInstance = ManifestInstance<typeof userManifest>;
export type UserModel = ManifestModel<typeof userManifest>;

// For models WITH relations, use intersection pattern for strong typing:
// export type UserInstance = ManifestInstance<typeof userManifest> & {
//   meta?: UserMetaInstance;
//   teams?: TeamInstance[];
// };

// Export reference helper for other models to use
export function referenceUser(): UserModel {
  return referenceModel(userManifest) as UserModel;
}

export { userOptions };
export default userManifest;

Relation Definition

Relations are defined as plain objects in the manifest's relations array:

interface RelationDefinition {
  name: string;                     // Field name on the instance
  targetTable?: string;             // Target table name, OR use target()
  target?: () => unknown;           // Lazy model reference (has tableName)
  sourceKey?: string;               // Column on this model (default: 'id')
  targetKey?: string;               // Column on target model (default: 'id')
  cardinality?: 'one' | 'many';     // Affects join strategy when using `true`
  hasRevisions?: boolean;           // Apply revision guards to joined records
  through?: {                       // For many-to-many via junction table
    table: string;
    sourceForeignKey?: string;
    targetForeignKey?: string;
  };
}

Example with a many-to-many relation:

relations: [
  {
    name: 'teams',
    targetTable: 'teams',
    sourceKey: 'id',
    targetKey: 'id',
    hasRevisions: true,
    through: {
      table: 'team_members',
      sourceForeignKey: 'user_id',
      targetForeignKey: 'team_id',
    },
    cardinality: 'many',
  },
]
// models/user.ts - Runtime behavior
import { defineModel, defineStaticMethods } from 'rev-dal/lib/create-model';
import userManifest, { type UserInstance, type UserModel } from './manifests/user.ts';
import { referenceTeam } from './manifests/team.ts';

// Safe cross-model reference - no circular import!
const Team = referenceTeam();

const userStaticMethods = defineStaticMethods(userManifest, {
  async findByEmail(this: UserModel, email: string) {
    return this.filterWhere({ email }).run();
  },

  async getWithTeams(this: UserModel, id: string) {
    const user = await this.get(id);
    if (user) {
      user.teams = await Team.filterWhere({ /* ... */ }).run();
    }
    return user;
  }
});

export default defineModel(userManifest, { staticMethods: userStaticMethods });

Type Inference

Manifests drive all type inference:

  • Low-level helpers (internal):
    • InferData and InferVirtual extract stored and virtual fields from schema builders
    • InferInstance switches between ModelInstance and VersionedModelInstance based on hasRevisions
    • InferConstructor produces the typed model constructor with CRUD methods
  • Convenience helpers (recommended for manifest files):
    • ManifestInstance<Manifest, InstanceMethods> - cleaner than InferInstance<MergeManifestMethods<...>>
    • ManifestModel<Manifest, StaticMethods, InstanceMethods> - cleaner than InferConstructor<MergeManifestMethods<...>>
  • Bundle helpers for manifests:
    • ManifestTypes<Manifest, StaticMethods, InstanceMethods, Relations> packages the data, virtual, instance, and model types into a single object.
    • ManifestExports<Manifest, Options> builds on ManifestTypes with a single options object (relations, statics, instances) and additionally returns typed StaticMethods/InstanceMethods mappings. Use this to keep manifest exports short when declaring both types and methods.
  • Method mapping helpers:
    • StaticMethodsFrom/InstanceMethodsFrom map plain method signatures to manifest-aware this types so authors don't need to annotate every method with this: ModelType or this: InstanceType. The generics are ordered as manifest, method map, then relation fields (plus instance methods for static methods) to match the call graph.
  • Static/instance methods declared via defineStaticMethods/defineInstanceMethods receive correctly typed this via contextual ThisType

Example using ManifestExports to keep manifests terse:

type ThingRelations = { files?: FileInstance[] };

type ThingTypes = ManifestExports<
  typeof thingManifest,
  {
    relations: ThingRelations;
    statics: { getWithData(id: string): Promise<ThingTypes['Instance']> };
    instances: { populateUserInfo(user: UserAccessContext | null | undefined): void };
  }
>;

type ThingInstance = ThingTypes['Instance'];
type ThingStaticMethods = ThingTypes['StaticMethods'];
type ThingInstanceMethods = ThingTypes['InstanceMethods'];

Method implementations can then omit explicit this annotations while still receiving the fully-typed model/instance context in the body.

Relation Field Typing: Use the intersection pattern to add strongly-typed relation fields:

// If you have static/instance methods, define base types first
type UserInstanceBase = ManifestInstance<typeof userManifest, UserInstanceMethods>;

// Add relation types via intersection - fields are optional since they're
// only populated when explicitly loaded via getJoin() or custom queries
export type UserInstance = UserInstanceBase & {
  meta?: UserMetaInstance;
  teams?: TeamInstance[];
};

This pattern avoids circular type errors when two models reference each other (e.g., Thing ↔ Review).

Cross-Model References

Use referenceModel() to safely import other models without circular dependencies:

// In models/thing.ts
import { referenceReview, type ReviewInstance } from './manifests/review.ts';

const Review = referenceReview();

// Can now call Review.filterWhere(...) safely
const reviews = await Review.filterWhere({ thingID: thing.id }).run();

The manifest exports a typed reference function that returns a lazy proxy. The actual model is resolved at runtime after bootstrap completes.

What Goes Where?

Manifests (models/manifests/) contain:

  • Schema definitions as plain objects with as const satisfies ModelManifest
  • Type exports (UserInstance, UserModel, etc.)
  • Validation functions used in schema validators
  • Model-specific constants and options
  • Cross-model reference functions (referenceUser(), etc.)
  • Simple helper functions with no external model dependencies

Runtime models (models/) contain:

  • Complex static methods that query other models
  • Instance methods that interact with related models
  • Business logic that requires calling multiple models
  • Methods that need fully-initialized DAL helpers

Rule of thumb: If it needs to call another model's methods, put it in models/. If it's pure validation, types, or schema, put it in models/manifests/.

Querying Data

Every manifest-based model ships a typed query entry point:

  • Model.filterWhere(literal) – Typed builder defined in rev-dal/lib/filter-where. Features include:
    • Typed predicate literals keyed by manifest fields.
    • Operator helpers exposed via Model.ops (neq, gt/gte/lt/lte, in/notIn, between/notBetween, containsAll, containsAny, jsonContains, not).
    • Automatic revision guards (_old_rev_of IS NULL, _rev_deleted = false) with opt-outs (includeDeleted(), includeStale()).
    • Fluent chaining (and, or, revisionData, orderBy, orderByRelation, limit, offset, getJoin, whereRelated, whereIn, chronologicalFeed, delete, count, average, groupBy, aggregateGrouped).
    • Promise-like behaviour so await Model.filterWhere({ ... }) works without .run().

Example:

const { containsAll, neq } = Thing.ops;
const things = await Thing.filterWhere({ urls: containsAll(targetUrls) })
  .and({ createdBy: neq(blockedUserId) })
  .orderBy('created_on', 'DESC')
  .limit(25)
  .run();

// Aggregates reuse the same revision-safe predicates
const averageRating = await Review.filterWhere({ thingID }).average('starRating');
const reviewCount = await Review.filterWhere({ thingID }).count();

// Atomic counters for numeric schema fields (throws on non-numeric columns)
const { rows } = await User.filterWhere({ id: someUser }).increment('inviteLinkCount', {
  by: 1,
  returning: ['inviteLinkCount'],
});

// Or decrement the same field atomically
await User.filterWhere({ id: someUser }).decrement('inviteLinkCount', { by: 1 });

// Grouped aggregations using GROUP BY
const { in: inOp } = Review.ops;
const reviewCounts = await Review.filterWhere({ thingID: inOp(thingIds) })
  .groupBy('thingID')
  .aggregateGrouped('COUNT');
// Returns: Map<string, number> { 'thing-id-1' => 5, 'thing-id-2' => 3, ... }

// Average rating per category
const avgPrices = await Product.filterWhere({})
  .groupBy('category')
  .aggregateGrouped('AVG', { aggregateField: 'price' });
// Returns: Map<string, number> { 'electronics' => 299.99, 'books' => 19.99, ... }

Grouped Aggregations

Use groupBy() and aggregateGrouped() for batched aggregations:

// Batch fetch review counts for multiple things in a single query
const { in: inOp } = Review.ops;
const counts = await Review.filterWhere({ thingID: inOp(thingIds) })
  .groupBy('thingID')
  .aggregateGrouped('COUNT');

things.forEach(thing => {
  thing.reviewCount = counts.get(thing.id) ?? 0;
});

Supports COUNT, AVG, SUM, MIN, MAX. Returns Map<string, number> keyed by the first groupBy field. For aggregates other than COUNT, specify aggregateField option.

Batch Loading Relations

The DAL provides Model.loadManyRelated() for efficiently loading many-to-many associations through junction tables. This is especially useful when hydrating lists of records with their related data (the "N+1 query" problem).

Basic Usage

// Load teams for multiple reviews in a single query
const reviewIds = reviews.map(r => r.id);
const reviewTeamMap = await Review.loadManyRelated('teams', reviewIds);

// Populate each review with its teams
reviews.forEach(review => {
  review.teams = reviewTeamMap.get(review.id) || [];
});

How It Works

loadManyRelated() uses the relation metadata defined in your manifest to:

  1. Build a single SQL query that joins through the junction table
  2. Apply revision system guards (_old_rev_of IS NULL, _rev_deleted = false) when needed
  3. Group results by source ID
  4. Return a Map<sourceId, TargetInstance[]> for easy assignment

Before (manual SQL):

const query = `
  SELECT rt.review_id, t.* FROM teams t
  JOIN review_teams rt ON t.id = rt.team_id
  WHERE rt.review_id IN (${placeholders})
    AND (t._old_rev_of IS NULL)
    AND (t._rev_deleted IS NULL OR t._rev_deleted = false)
`;
const result = await dal.query(query, reviewIds);
// Manual grouping logic...

After (DAL helper):

const reviewTeamMap = await Review.loadManyRelated('teams', reviewIds);

Requirements

The relation must be defined in your model's manifest with:

  • A through object specifying the junction table
  • Proper sourceForeignKey and targetForeignKey columns
  • Optional hasRevisions flag for revision-aware filtering

Example manifest configuration:

const reviewManifest = {
  tableName: 'reviews',
  hasRevisions: true as const,
  schema: { /* ... */ },
  relations: [
    {
      name: 'teams',
      targetTable: 'teams',
      sourceKey: 'id',
      targetKey: 'id',
      hasRevisions: true,
      through: {
        table: 'review_teams',
        sourceForeignKey: 'review_id',
        targetForeignKey: 'team_id',
      },
      cardinality: 'many',
    },
  ] as const,
} as const satisfies ModelManifest;

Edge Cases

  • Empty input: Returns empty Map when given []
  • No matches: Returns empty Map when no associations exist
  • Deleted records: Automatically excludes soft-deleted related records when hasRevisions: true
  • Type safety: Results are typed as Map<string, ModelInstance<JsonObject, JsonObject>[]>, requiring a cast to the specific target type when needed

Writing Many-to-Many Associations

The DAL provides Model.addManyRelated() for batch-inserting associations into many-to-many junction tables. This complements loadManyRelated() by handling the write side of many-to-many relationships.

Basic Usage

// Associate a thing with multiple files
const Thing = dalFixture.getModel('things');
await Thing.addManyRelated('files', thingId, [file1.id, file2.id]);

// Associate a review with teams
const Review = dalFixture.getModel('reviews');
await Review.addManyRelated('teams', reviewId, teamIds);

How It Works

addManyRelated() uses the relation metadata defined in your manifest to:

  1. Build a parameterized INSERT query for the junction table
  2. Apply schema namespace prefixing for test isolation
  3. Handle duplicate associations gracefully via ON CONFLICT DO NOTHING (default)
  4. Validate inputs and provide helpful error messages

Before (manual SQL):

const runtime = this.constructor as Record<string, any>;
const dalInstance = runtime.dal as Record<string, any>;
const junctionTable = dalInstance.schemaNamespace
  ? `${dalInstance.schemaNamespace}thing_files`
  : 'thing_files';
const insertValues: Array<string | undefined> = [];
const valueClauses: string[] = [];
let paramIndex = 1;

validFiles.forEach(file => {
  valueClauses.push(`($${paramIndex}, $${paramIndex + 1})`);
  insertValues.push(this.id, file.id);
  paramIndex += 2;
});

if (valueClauses.length) {
  const insertQuery = `
    INSERT INTO ${junctionTable} (thing_id, file_id)
    VALUES ${valueClauses.join(', ')}
    ON CONFLICT DO NOTHING
  `;
  await dalInstance.query(insertQuery, insertValues);
}

After (DAL helper):

const Thing = this.constructor as ThingModel;
await Thing.addManyRelated('files', this.id!, validFiles.map(f => f.id!));

Requirements

The relation must be defined in your model's manifest with:

  • A through object specifying the junction table
  • Proper sourceForeignKey and targetForeignKey columns
  • cardinality: 'many'

Example manifest configuration:

const thingManifest = {
  hasRevisions: true as const,
  tableName: 'things',
  relations: [
    {
      name: 'files',
      targetTable: 'files',
      sourceKey: 'id',
      targetKey: 'id',
      hasRevisions: true,
      through: {
        table: 'thing_files',
        sourceForeignKey: 'thing_id',
        targetForeignKey: 'file_id',
      },
      cardinality: 'many',
    },
  ] as const,
} as const satisfies ModelManifest;

Conflict Handling

By default, duplicate associations are silently ignored:

// Associate once
await Review.addManyRelated('teams', reviewId, [teamId]);

// Associate again - no error, no duplicate row
await Review.addManyRelated('teams', reviewId, [teamId]);

For strict validation, use { onConflict: 'error' }:

await Review.addManyRelated('teams', reviewId, [teamId], { onConflict: 'error' });
// Throws if association already exists

Edge Cases

  • Empty input: Returns immediately when given [] with no database query
  • Invalid relation: Throws helpful error listing available relations
  • Non-junction relation: Throws error if relation doesn't have a through table
  • Invalid source ID: Throws error for empty or non-string source IDs
  • Schema isolation: Automatically handles test namespace prefixing

Instance Method Pattern

When writing instance methods that create associations, follow this pattern:

async addFilesByIDsAndSave(this: ThingInstance, fileIDs: string[]): Promise<ThingInstance> {
  // 1. Validate and fetch related records
  const { in: inOp } = File.ops;
  const validFiles = await File.filterWhere({ id: inOp(fileIDs as [string, ...string[]]) }).run();

  if (!validFiles.length) {
    return this;
  }

  // 2. Insert associations using static helper
  const Thing = this.constructor as ThingModel;
  await Thing.addManyRelated('files', this.id!, validFiles.map(f => f.id!));

  // 3. Update in-memory representation
  this.files = [...(this.files ?? []), ...validFiles];

  return this;
}

Revisions

Models with hasRevisions: true gain revision metadata fields and helpers:

  • Static helpers (createFirstRevision, getNotStaleOrDeleted, revision-aware filterWhere, etc.).
  • Instance helpers (newRevision, deleteAllRevisions).
  • filterWhere.revisionData() exposes typed predicates for _rev* columns when querying revision metadata.

_revSummary is disabled by default. If you want it, add the column in your schema and call setRevisionSummaryEnabled(true) at bootstrap.

Multilingual Strings

The DAL provides runtime-validated multilingual string schemas via mlString (imported from rev-dal/lib/ml-string). These enforce a security model that distinguishes plain text from HTML content at write time.

Storage Format

Text fields store HTML-safe text with entities escaped (e.g., My &amp; Co, I &lt;3 JS) but HTML tags rejected. This preserves users' literal input including angle brackets while allowing safe rendering in HTML templates.

  • User types A & B → stored as { en: "A &amp; B" } → browser displays A & B
  • Adapter returns <b>Title</b> → stripped to Title → stored as { en: "Title" }
  • For plain text contexts (emails, exports), decode entities with decodeHTML() from the entities package

Schema Methods

Plain Text (HTML-safe) – For labels, titles, names, descriptions

import mlString from 'rev-dal/lib/ml-string';

const thingManifest = {
  hasRevisions: true as const,
  schema: {
    label: mlString.getSafeTextSchema({ maxLength: 256 }),
    aliases: mlString.getSafeTextSchema({ array: true, maxLength: 256 }),
  },
  // ... other fields
} as const satisfies ModelManifest;

Rejects HTML tags at write time via ValidationError.

HTML Content – For cached rendered markdown output

const reviewManifest = {
  hasRevisions: true as const,
  schema: {
    html: mlString.getHTMLSchema(),
  },
  // ... other fields
} as const satisfies ModelManifest;

Permits full HTML markup. Use only for pre-sanitized content.

Rich Text (Nested) – For fields storing both markdown source and cached HTML

const teamManifest = {
  hasRevisions: true as const,
  schema: {
    description: mlString.getRichTextSchema(),
    // Expects: { text: { en: "markdown" }, html: { en: "<p>HTML</p>" } }
  },
  // ... other fields
} as const satisfies ModelManifest;

Validates nested structure: text must be HTML-safe, html may contain tags.

Template Rendering

{{!-- Plain text (entity-escaped at storage) --}}
<h1>{{mlSafeText review.title}}</h1>
<meta content="{{mlSafeText thing.label false}}" />

{{!-- Pre-rendered HTML --}}
<div>{{{mlHTML review.html}}}</div>
<div>{{{mlHTML team.description.html}}}</div>

Both helpers support language fallbacks and optional language indicators.

Security Model

Defense in depth against XSS:

  1. Input sanitization – Adapters/forms escape entities at write time
  2. Runtime validation – Schemas reject <script> in text fields
  3. Template safety – Explicit mlSafeText vs mlHTML helpers
  4. User feedback – Flash middleware converts validation errors to localized messages

Helper Methods

// Resolve translation with fallbacks
const resolved = mlString.resolve('de', thing.label);
// Returns: { str: "Label text", lang: "en" }

// Resolve with metadata (preferred languages + available languages)
const resolvedWithMetadata = mlString.resolveWithMetadata(['de', 'en'], thing.label);

// Strip HTML from external data
const cleaned = mlString.stripHTML({ en: '<b>Bold</b> text' });
// Returns: { en: 'Bold text' }

// Build JSONB query predicates
const query = mlString.buildQuery('label', 'en', searchTerm, 'ILIKE');
// Returns: "label->>'en' ILIKE $1"

Directory Reference

  • src/index.ts – Public entry point that re-exports constructors, types, and helpers.
  • src/lib/ – Core implementation (connection management, manifests, query builder, filters, revision system, schema types).
  • setup-db-grants.sql – Grants applied to shared environments.

About

Versioned Data Access Layer for Node web apps, targeting Postgres

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published