• Synopsis
• Support
• Installation
• Usage
• API Reference
• Contributors
• Stack

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";

const entity = new Entity("consumers", [
  {
    key: "id",
    type: "integer",
    min: 0,
    max: 120,
    typeCheck: true,
    filter: true,
    methods: ["GET", "PUT", "DELETE"],
    operations: ["SELECT", "UPDATE", "DELETE"],
    required: true,
    safe: true,
    sanitize: true,
    normalize: true,
    validate: true,
    sanitizer: null,
    normalizer: null,
    validator: null,
  },
  {
    key: "firstName",
    type: "string",
    min: 0,
    max: 255,
    typeCheck: true,
    filter: false,
    methods: ["GET", "POST", "PUT", "DELETE"],
    operations: ["SELECT", "UPDATE", "DELETE"],
    required: true,
    safe: true,
    sanitize: true,
    normalize: true,
    validate: true,
    sanitizer: null,
    normalizer: normalizeName,
    validator: null,
  },
  {
    key: "lastName",
    type: "string",
    min: 0,
    max: 255,
    typeCheck: true,
    filter: false,
    methods: ["GET", "POST", "PUT", "DELETE"],
    operations: ["SELECT", "UPDATE", "DELETE"],
    required: true,
    safe: true,
    sanitize: true,
    normalize: true,
    validate: true,
    sanitizer: null,
    normalizer: normalizeName,
    validator: null,
  },
  {
    key: "nickname",
    type: "string",
    min: 0,
    max: 255,
    typeCheck: true,
    filter: true,
    methods: ["GET", "POST", "PUT", "DELETE"],
    operations: ["SELECT", "UPDATE", "DELETE"],
    required: true,
    safe: true,
    sanitize: true,
    normalize: true,
    validate: true,
    sanitizer: null,
    normalizer: normalizeNickname,
    validator: null,
  },
]);

router.get("/", ..., entity.get);
router.post("/", entity.normalizeArray, entity.validateArray, ..., entity.add);
router.put("/", entity.normalizeArray, entity.validateArray, ..., entity.update);
router.put("/archive", ..., entity.archive);
router.delete("/", ..., entity.delete);
router.delete("/archived", ..., entity.deleteArchive);

API Reference

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

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;
}

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

  query: {
    select: (
      paginate: boolean,
      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;

}

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>;

get(), add(), update(), archive(), delete() and deleteArchive() 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.

• 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. Expects req.body.date to be a Date object.

Match modes

List of possible match modes :

| Name | alias | types | Description | | :---------- | :---- | :---------------------- | :-------------------------------------------------------- | | startsWith | | string | Whether the value starts with the filter value | | contains | | string | Whether the value contains the filter value | | endsWith | | string | Whether the value ends with the filter value | | notContains | | string | Whether the value does not contain filter value | | equals | | string | number | Whether the value equals the filter value | | notEquals | | string | number | Whether the value does not equal the filter value | | in | | string[] | number[] | Whether the value contains the filter value | | lt | | string | number | Whether the value is less than the filter value | | lte | | string | number | Whether the value is less than or equals to the filter value | | gt | | string | number | Whether the value is greater than the filter value | | gte | | string | number | Whether the value is greater than or equals to the filter value | | is | | date | boolean | null | Whether the value equals the filter value, alias to equals | | isNot | | date | boolean | null | Whether the value does not equal the filter value, alias to notEquals | | before | | date | Whether the date value is before the filter date | | after | | date | Whether the date value is after the filter date | | between | | date[2] | number[2] | Whether the value is between the filter values | | st_contains | | geometry | Whether the geometry completely contains other geometries | | st_dwithin | | geometry | Whether geometries are within a specified distance from another geometry |

Types

List of compatible match modes for each property types

| Name | Match modes | | :---------- | :---------------------- | | string | startsWith,contains,endsWith,notContains,equals,notEquals,lt,lte,gt,gte | | number | equals,notEquals,lt,lte,gt,gte | | date | is,isNot,before,after | | boolean | is,isNot | | string[] | in | | number[] | in,between | | date[] | between | | geometry | st_contains,st_dwithin |

List of secondary types :

| Name | equivalent | | :----------------- | :--------- | | integer | number | | float | number | | even | number | | odd | number | | positive | number | | negative | number | | powerOfTwo | number | | ascii | number | | array | any[] | | jwt | string | | symbol | string | | email | string | | password | string | | regex | string | | ipAddress | string | | slug | string | | hexadecimal | string | | date | date | | timestamp | date | | function | string | | htmlElement | string | | htmlEventAttribute | string | | node | string | | json | object | | object | object |

Available options for a property

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

| Name | Type | Description | Default value |
| :-------------- | :------------------------ | :------------------------------------------------ | :-------------- | | key | string | Name of the property | | type | Type | Type of the property | | min | number | Date | Minimum value | 0 | 1900-01-01 | max | number | Date | Maximum value | 999999999 | 2200-12-31 | required | boolean | Property is required during validation | false | safe | boolean | Property is sent in the response | true | typeCheck | boolean | Type is checked during validation | false | filter | boolean | property is filterable in a SELECT operation | true | methods | Method[] | property is validated for the listed methods only | [ "GET", "POST", "PUT", "DELETE" ] | operations | Operation[] | SQL DML operations for the property | [ "SELECT", "INSERT", "UPDATE", "DELETE" ] | sanitize | boolean | Sanitize the property if true | true | normalize | boolean | Normalize the property if true | false | validate | boolean | validate the property if true | true | sanitizer | ((v:any) => any) | null | Custom sanitizer function if sanitize is true | null | normalizer | ((v:any) => any) | null | Custop Normalizer function if normalize is true | null | validator | ((v:any, min:number, max:number, typeCheck:boolean) => any) | null | validator function if validate is true | null

• 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 |