A PostgreSQL adapter for the JSON Rules Engine that allows evaluating rules directly against a PostgreSQL database.
- 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.)
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 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 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" } );
-
Basic comparisons:
equal,notEquallessThan,lessThanInclusivegreaterThan,greaterThanInclusive
-
String operations:
startsWith,endsWithstringContains,stringDoesNotContain
-
Date operations:
dateEqual,dateNotEqualdateLessThan,dateLessThanInclusivedateGreaterThan,dateGreaterThanInclusivedateWithin(requires start and end dates)
-
Array operations:
in,notIncontains,doesNotContain
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"], } );
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
The adapter is written in TypeScript and includes type definitions for:
- Rule structure
- Evaluation options
- Table mapping
- Date ranges
npm install json-rules-engine-pg
- Node.js 20 or later
- PostgreSQL 14 or later
- Clone the repository
- Install dependencies:
npm install
# Run tests once npm test # Run tests in watch mode npm run test:watch
pg-adapter.ts- Main adapter implementationpg-adapter.spec.ts- Integration tests.github/workflows/test.yml- GitHub Actions workflow for CI
MIT