@dwtechs/antity-pgsql

!Jest:coverage

- 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

``bash
$ npm i @dwtechs/antity-pgsql
`

Usage


`javascript

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


`javascript

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[],
consumerId?: number | string,
consumerName?: string) => {
query: string;
args: unknown[];
};
insert: (
rows: Record[],
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;
update: (req: Request, res: Response, next: NextFunction) => Promise;
archive: (req: Request, res: Response, next: NextFunction) => Promise;
delete: (req: Request, res: Response, next: NextFunction) => Promise;
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;


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