@dwtechs/antity-pgsql

v0.9.1

Published

2 days ago

Open source library to add PostgreSQL support to @dwtechs/Antity entities.

0High
0Medium
0Low

dwtechs

entities

Synopsis

Antity-pgsql.js adds PostgreSQL features to Antity.js library.

🪶 Very lightweight
🧪 Thoroughly tested
🚚 Shipped as EcmaScrypt module
📝 Written in Typescript

Support

node: 22

This is the oldest targeted versions. The library should work properly on older versions of Node.js but we do not support it officially.

Installation

$ npm i @dwtechs/antity-pgsql

Usage


import { SQLEntity } from "@dwtechs/antity-pgsql";
import { normalizeName, normalizeNickname } from "@dwtechs/checkard";

// Create entity with default 'public' schema
const entity = new SQLEntity("consumers", [
  // properties...
]);

// Or specify a custom schema
const customEntity = new SQLEntity("consumers", [
  // properties...
], "myschema");

// Example with all properties
const entity = new SQLEntity("consumers", [
  {
    key: "id",
    type: "integer",
    min: 0,
    max: 120,
    typeCheck: true,
    filter: true,
    need: ["PUT"],
    operations: ["SELECT", "UPDATE"],
    send: true,
    sanitizer: null,
    normalizer: null,
    validator: null,
  },
  {
    key: "firstName",
    type: "string",
    min: 0,
    max: 255,
    typeCheck: true,
    filter: false,
    need: ["POST", "PUT"],
    operations: ["SELECT", "UPDATE"],
    send: true,
    sanitizer: null,
    normalizer: normalizeName,
    validator: null,
  },
  {
    key: "lastName",
    type: "string",
    min: 0,
    max: 255,
    typeCheck: true,
    filter: false,
    need: ["POST", "PUT"],
    operations: ["SELECT", "UPDATE"],
    send: true,
    sanitizer: null,
    normalizer: normalizeName,
    validator: null,
  },
  {
    key: "nickname",
    type: "string",
    min: 0,
    max: 255,
    typeCheck: true,
    filter: true,
    need: ["POST", "PUT"],
    operations: ["SELECT", "UPDATE"],
    send: true,
    sanitizer: null,
    normalizer: normalizeNickname,
    validator: null,
  },
]);

router.get("/", ..., entity.get);

// Using substacks (recommended) - combines normalize, validate, and database operation
router.post("/", ...entity.addArraySubstack);
router.put("/", ...entity.updateArraySubstack);

// Or manually chain middlewares
router.post("/manual", entity.normalizeArray, entity.validateArray, ..., entity.add);
router.put("/manual", entity.normalizeArray, entity.validateArray, ..., entity.update);

router.put("/archive", ..., entity.archive);
router.delete("/", ..., entity.delete);
router.delete("/archived", ..., entity.deleteArchive);
router.get("/history", ..., entity.getHistory);

API Reference


type Operation = "SELECT" | "INSERT" | "UPDATE";

type MatchMode =  
  "startsWith" | 
  "endsWith" |
  "contains" |
  "notContains" |
  "equals" |
  "notEquals" |
  "between" |
  "in" |
  "lt" |
  "lte" |
  "gt" |
  "gte" |
  "is" |
  "isNot" |
  "before" |
  "after" |
  "st_contains" |
  "st_dwithin";


type Filter = {
  value: string | number | boolean | Date | number[];
  subProps?: string[];
  matchMode?: MatchMode;
}

type ExpressMiddleware = (req: Request, res: Response, next: NextFunction) => void;
type ExpressMiddlewareAsync = (req: Request, res: Response, next: NextFunction) => Promise<void>;
type SubstackTuple = [ExpressMiddleware, ExpressMiddleware, ExpressMiddlewareAsync];

class SQLEntity {
  constructor(name: string, properties: Property[], schema?: string);
  get name(): string;
  get table(): string;
  get schema(): string;
  get unsafeProps(): string[];
  get properties(): Property[];
  set name(name: string);
  set table(table: string);
  set schema(schema: string);

  // Middleware substacks (combine normalize, validate, and operation)
  get addArraySubstack(): SubstackTuple;
  get addOneSubstack(): SubstackTuple;
  get updateArraySubstack(): SubstackTuple;
  get updateOneSubstack(): SubstackTuple;

  query: {
    select: (
      first?: number,
      rows?: number | null,
      sortField?: string | null,
      sortOrder?: "ASC" | "DESC" | null,
      filters?: Filters | null) => {
        query: string;
        args: (Filter["value"])[];
      };
    update: (
      rows: Record<string, unknown>[],
      consumerId?: number | string,
      consumerName?: string) => {
        query: string;
        args: unknown[];
    };
    insert: (
      rows: Record<string, unknown>[],
      consumerId?: number | string,
      consumerName?: string,
      rtn?: string) => {
        query: string;
          args: unknown[];
      };
    delete: (ids: number[]) => {
      query: string;
      args: number[];
    };
    deleteArchive: () => string;
    return: (prop: string) => string;
  };
  get: (req: Request, res: Response, next: NextFunction) => void;
  add: (req: Request, res: Response, next: NextFunction) => Promise<void>;
  update: (req: Request, res: Response, next: NextFunction) => Promise<void>;
  archive: (req: Request, res: Response, next: NextFunction) => Promise<void>;
  delete: (req: Request, res: Response, next: NextFunction) => Promise<void>;
  deleteArchive: (req: Request, res: Response, next: NextFunction) => void;
  getHistory: (req: Request, res: Response, next: NextFunction) => Promise<void>;

}

function filter(
  first: number,
  rows: number | null,
  sortField: string | null,
  sortOrder: Sort | null,
  filters: Filters | null,
): { filterClause: string, args: (Filter["value"])[] };

function execute(
  query: string, 
  args: (string | number | boolean | Date | number[])[], 
  client: any,
): Promise<PGResponse>;

Middleware Methods for Express.js

get(), add(), update(), archive(), delete(), deleteArchive() and getHistory() methods are made to be used as Express.js middlewares. Each method will look for data to work on in the req.body.rows parameter.

Schema Qualification

All SQL queries generated by Antity-pgsql use schema-qualified table names (e.g., schema.table). This provides:

Security: Protection against search_path manipulation attacks, especially important when using SECURITY DEFINER functions
Clarity: Explicit schema references make queries more readable and maintainable
Flexibility: Easy multi-schema support within the same application

The default schema is 'public' but can be customized via the constructor's third parameter or the schema setter.

Middleware Substacks

Substacks are pre-composed middleware chains that combine normalization, validation, and database operations:

addArraySubstack: Combines normalizeArray, validateArray, and add. Use this for POST routes with req.body.rows containing multiple objects.
addOneSubstack: Combines normalizeOne, validateOne, and add. Use this for POST routes with req.body containing a single object.
updateArraySubstack: Combines normalizeArray, validateArray, and update. Use this for PUT routes with req.body.rows containing multiple objects.
updateOneSubstack: Combines normalizeOne, validateOne, and update. Use this for PUT routes with req.body containing a single object.

Using substacks simplifies your route definitions and ensures consistent data processing.

Query Methods

query.select(): Generates a SELECT query. When the rows parameter is provided (not null), pagination is automatically enabled and the query includes COUNT(*) OVER () AS total to return the total number of rows. The total count is extracted from results and returned separately from the row data.
delete(): Deletes rows by their IDs. Expects req.body.rows to be an array of objects with id property: [{id: 1}, {id: 2}]
deleteArchive(): Deletes archived rows that were archived before a specific date using a PostgreSQL SECURITY DEFINER function. Expects req.body.date to be a Date object.
getHistory(): Retrieves modification history for rows from the log.history table. Expects req.body.rows to be an array of objects with id property. Returns all historical records for the specified entity IDs.

Match modes

List of possible match modes :

Types

List of compatible match modes for each property types

List of secondary types :

Available options for a property

Any of these can be passed into the options object for each function.

Min and max parameters are not used for boolean type
TypeCheck Parameter is not used for boolean, string and array types

Contributors

Antity.js is still in development and we would be glad to get all the help you can provide. To contribute please read contributor.md for detailed installation guide.

Stack

| Purpose | Choice | Motivation | | :-------------- | :------------------------------------------: | -------------------------------------------------------------: | | repository | Github | hosting for software development version control using Git | | package manager | npm | default node.js package manager | | language | TypeScript | static type checking along with the latest ECMAScript features | | module bundler | Rollup | advanced module bundler for ES6 modules | | unit testing | Jest | delightful testing with a focus on simplicity |