JSON Rules Engine PostgreSQL Adapter

A PostgreSQL adapter for the JSON Rules Engine that allows evaluating rules directly against a PostgreSQL database.

Features

• Direct SQL query generation from JSON rules
• Support for complex nested conditions (all, any, not)
• Two evaluation modes:
  • List mode: Returns an array of records matching the conditions
  • Check mode: Returns a boolean for a specific record
• Support for various operators:
  • Basic comparisons (equal, notEqual, lessThan, etc.)
  • String operations (startsWith, endsWith, contains, etc.)
  • Date operations (dateEqual, dateWithin, etc.)
  • Array operations (in, notIn, contains, etc.)

Usage

Basic Setup

import { PostgresRuleAdapter } from "./pg-adapter";
import { Pool } from "pg";

const pool = new Pool({
  user: "your_user",
  password: "your_password",
  host: "localhost",
  port: 5432,
  database: "your_database",
});

const tableMap = {
  "user.id": '"users"."id"',
  "user.company": '"users"."company"',
  "user.status": '"users"."status"',
  "user.purchases.count":
    '(SELECT COUNT(*) FROM "purchases" WHERE "purchases"."user_id" = "users"."id")',
  "user.purchases.total":
    '(SELECT SUM("amount") FROM "purchases" WHERE "purchases"."user_id" = "users"."id")',
  "user.tags":
    'ARRAY(SELECT "tag" FROM "user_tags" WHERE "user_tags"."user_id" = "users"."id")',
};

const adapter = new PostgresRuleAdapter({ tableMap, pool });

List Mode

List mode returns an array of records that match the conditions. Use this when you need to find all records that satisfy the rule.

const rule = {
  baseTable: "users",
  conditions: {
    all: [
      {
        fact: "user",
        path: "$.company",
        operator: "equal",
        value: "microsoft",
      },
      {
        fact: "user",
        path: "$.status",
        operator: "in",
        value: ["active", "paid-leave"],
      },
    ],
  },
  event: {
    type: "microsoft-employee",
  },
};

// Returns array of records with specified properties
const results = await adapter.evaluateRule(
  rule,
  {},
  {
    mode: "list",
    properties: ["id", "company", "status"],
  }
);

Check Mode

Check mode returns a boolean indicating whether a specific record matches the conditions. Use this when you need to verify if a particular record satisfies the rule.

const rule = {
  baseTable: "users",
  conditions: {
    all: [
      {
        fact: "user",
        path: "$.company",
        operator: "equal",
        value: "microsoft",
      },
      {
        fact: "user",
        path: "$.purchases.count",
        operator: "greaterThan",
        value: 1,
      },
    ],
  },
  event: {
    type: "frequent-microsoft-buyer",
  },
};

// Returns boolean for specific user ID
const isMatch = await adapter.evaluateRule(
  rule,
  {},
  {
    mode: "check",
    value: 1, // user ID to check
    idField: "id", // optional, defaults to "id"
  }
);

Supported Operators

• Basic comparisons:

  • equal, notEqual
  • lessThan, lessThanInclusive
  • greaterThan, greaterThanInclusive

• String operations:

  • startsWith, endsWith
  • stringContains, stringDoesNotContain

• Date operations:

  • dateEqual, dateNotEqual
  • dateLessThan, dateLessThanInclusive
  • dateGreaterThan, dateGreaterThanInclusive
  • dateWithin (requires start and end dates)

• Array operations:

  • in, notIn
  • contains, doesNotContain

Context Variables

You can use context variables in your rules by prefixing the value with $:

const rule = {
  baseTable: "users",
  conditions: {
    all: [
      {
        fact: "user",
        path: "$.purchases.total",
        operator: "greaterThan",
        value: "$purchaseThreshold",
      },
    ],
  },
};

const results = await adapter.evaluateRule(
  rule,
  {
    purchaseThreshold: 1000,
  },
  {
    mode: "list",
    properties: ["id"],
  }
);

Error Handling

The adapter will throw errors in the following cases:

• Missing required options (properties for list mode, value for check mode)
• Unmapped fields in the table map
• Invalid date ranges for dateWithin operator
• Invalid array values for in/notIn operators
• Database query errors

TypeScript Support

The adapter is written in TypeScript and includes type definitions for:

• Rule structure
• Evaluation options
• Table mapping
• Date ranges

Installation

npm install json-rules-engine-pg

Development

Prerequisites

• Node.js 20 or later
• PostgreSQL 14 or later

Setup

1. Clone the repository
2. Install dependencies:

   npm install

Running Tests

# Run tests once
npm test

# Run tests in watch mode
npm run test:watch

Project Structure

• pg-adapter.ts - Main adapter implementation
• pg-adapter.spec.ts - Integration tests
• .github/workflows/test.yml - GitHub Actions workflow for CI

License

MIT