pg-dbhelper

Db helper is a Postgres DAL (Database Abstraction Layer) that connects postgres SQL databses to javascript code. Db helper allows us to communicate with a postgres SQL database without the need to write SQL queries, instead, we work with javascript objects to access and manipulate data.

The first step to work with db helper is to install the library:

npm install @utt/pg-dbhelper

The second step to work with db helper is to require the library:

const DbHelperCJS = require('pg-dbhelper'); // commonJS
import DbHelperES from 'pg-dbhelper'; // ES module

After that we have access to the following public API of db helper:

• static method: createInstance(dbConnectionObject) - Create a working instance of db hepler.
• instance async iterator: import(importObject) - Import rows into the selected table in the database that is connected to the working instance.
• instance async iterator: export(exportObject) - Export rows from the table in the database that is connected to the working instance.
• instance method: updateSchemas(schemas) - Update the schemas information of db helper in case of schema changes.
• instance method: execute(query, memoryCache, dbCache) - Execute a custom SQL query against the database that is connected to the working instance.
• instance method: beginTransaction() - Returns a new instance of db helper that will be used for executing queries as part of a SQL Transaction.
• instance method: commitTransaction() - Used to commit a SQL Transaction Can be used only on the db helper instance that returns from the beginTransaction function
• instance method: rollbackTransaction() - Used to rollback a SQL Transaction Can be used only on the db helper instance that returns from the beginTransaction function
• instance method: insert(insertObject) - Inserts a row into the selected table in the database that is connected to the working instance.
• instance method: update(updateObject) - Update records in the selected tables in the database that is connected to the working instance.
• instance method: upsert(upsertObject) - Upserts a row in the selected table in the database that is connected to the working instance.
• instance method: select(selectObject, memoryCache, dbCache) - Select records from the selected tables in the database that is connected to the working instance.
• instance method: delete(deleteObject) - Delete records from the selected table in the database that is connected to the working instance.
• instance method: createTable(createTableObject) - Creates a table in the database that is connected to the working instance.
• instance method: printQuery(queryType, queryObject) - Returns the SQL query generated by db helper for the provided query object.
• instance method: getFieldType(fieldTypeObject) - Returns the name of the Javascript data type that is mapped for a specific column in the database.

Creating Instance

const dbConnectionObject = {
    host: 'host connection url',
    user: 'username',
    password: 'password',
    database: 'database name'
    port: portNumber, // (default is: 5432)
    schemas: ['schema name 1','schema name 2', ...], // (default is: ['public'])
    maxConnections: maxConnectionsNumber, // (default is: 100)
    idleTimeoutMillis: milliseconds, // (default is: 5000)
    connectionTimeoutMillis: milliseconds, // (default is: 30000)
};

const instance = await DbHelper.createInstance(dbConnectionObject);

Import Function

const importObject = {
    tableSchema: 'schema name', // (default is: 'public')
    tableName: 'table name',
    tableRows: [
        {
            'column name 1': insert value 1,
            'column name 2': insert value 2,
            ...
        },
        {
            'column name 1': insert value 1,
            'column name 2': insert value 2,
            ...
        },
        ...
    ],
    chunkSize: 'number of rows to insert at once' // (default is: 5000)
};

for await (const iterator of dbHelperInstance.import(importObject)) {
    console.log({ prog: iterator.timePassed, rows: iterator.rowsInserted });
}

Export Function

const exportObject =  {
    tableSchema: 'schema name', // (default is: 'public')
    tableName: 'table name',
    selector = {
        columns:
            [
                'column name 1',
                'column name 2'
                ...
            ],
        where: [
            {
                 columnName: { value: 'value', operator: 'operator' }
                 ...
            }
        ]
    };
    chunkSize: 'number of rows to export at once' // (default is: 5000)
};
for await (const iterator of dbHelper.export(exportObject)) {
    console.log({ prog: iterator.timePassed, rows: iterator.rowsExported, dataChunk: iterator.data });
}

Update Schemas

await dbHelperInstance.updateSchemas(schemas);
// (default schemas is: ['public'])

Execute Function

const executeResult = await dbHelperInstance.execute(query, memoryCache, dbCache); // (default memoryCache is: false)
// (default dbCache is: false)

Begin Transaction

const dbHelperTransactionInstance = await dbHelperInstance.beginTransaction();

Commit Transaction

await dbHelperTransactionInstance.commitTransaction();

Rollback Transaction

await dbHelperTransactionInstance.rollbackTransaction();

Insert Function

const insertObject = {
    tableSchema: 'insert schema name', // (default is: 'public')
    tableName: 'insert table name',
    queryUser: 'username of the insert query executor', // (default is: 'System')
    columns: {
        'insert column name 1': insert value 1,
        'insert column name 2': insert value 2,
        ...
    },
    returning: ['column name 1', 'column name 2', ...] OR '*', // (default is: '*')
    createChangeLog: true / false, // (default is: true)
    extendedRes: true / false // (default is: false)
};

const insertResult = await dbHelperInstance.insert(insertObject);

Update Function

const updateObject = {
    tableSchema: 'update schema name', // (default is: 'public')
    tableName: 'update table name',
    queryUser: 'username of the update query executor', // (default is: 'System')
    columns: {
        'update column name 1': update value 1,
        'update column name 2': update value 2,
        'update column name 3': {
            operator: '||' OR 'array_remove' OR '#-', // used for updating/removing values in array and jsonb postgres data types
            value: update value 3
        }
        ...
    },
    selector: {
        where: [
            {
                'culumn name 1': column value 1,
                'culumn name 2': column value 2,
                ...
            },
            {
                'culumn name 3': column value 3,
                'culumn name 4': column value 4,
                ...
            },
            ...
        ],
        joins: ['join query 1', 'join query 2', ...]
    },
    returning: ['column name 1', 'column name 2', ...] or '*', // (default is: '*')
    createChangeLog: true / false, // (default is: true)
    extendedRes: true / false // (default is: false)
};

const updateResult = await dbHelperInstance.update(updateObject);

Upsert Function

const upsertObject = {
    tableSchema: 'upsert schema name', // (default is: 'public')
    tableName: 'upsert table name',
    queryUser: 'username of the upsert query executor', // (default is: 'System')
    columns: {
        'upsert column name 1': upsert value 1,
        'upsert column name 2': upsert value 2,
        ...
    },
    conflictColumn: 'name of column to check conflict with on insert',
    conflictValue: 'the value of the conflict column used to find the row to update',
    returning: ['column name 1', 'column name 2', ...] OR '*', // (default is: '*')
    createChangeLog: true / false, // (default is: true)
    extendedRes: true / false // (default is: false)
};

const upsertResult = await dbHelperInstance.upsert(upsertObject);

Select Function

const selectObject = {
    tableSchema: 'select schema name', // (default is: 'public')
    tableName: 'select table name',
    selector: {
        where: [
            {
                'culumn name 1': column value 1,
                'culumn name 2': column value 2,
                ...
            },
            {
                'culumn name 3': column value 3,
                'culumn name 4': column value 4,
                ...
            },
            ...
        ],
        joins: ['join query 1', 'join query 2', ...],
        orderBy: {
            columns: ['column name 1', 'column name 2', ...],
            direction: 'ASC' OR 'DESC'
        },
        limit: limitNumber,
        offset: offsetNumber
    },
    extendedRes: true / false // (default is: false)
};

const selectResult = await dbHelperInstance.select(selectObject, memoryCache); // (default memoryCache is: false)
// (default dbache is: false)

Delete Function

const deleteObject = {
    tableSchema: 'delete schema name', // (default is: 'public')
    tableName: 'delete table name',
    selector: {
        where: [
            {
                'culumn name 1': column value 1,
                'culumn name 2': column value 2,
                ...
            },
            {
                'culumn name 3': column value 3,
                'culumn name 4': column value 4,
                ...
            },
            ...
        ]
    },
    returning: ['column name 1', 'column name 2', ...] or '*', // (default is: '*')
    extendedRes: true / false // (default is: false)
};

const deleteResult = await dbHelperInstance.delete(deleteObject);

Create Table Function

const createTableObject = {
    tableSchema: 'delete schema name', // (default is: 'public')
    tableName: 'delete table name',
    columns: [
        {
            name: 'column name',
            type: 'column type',
            required: true / false,
            unique: true / false,
            index: true / false,
            indexFields: ['index field 1', 'index field 2', ...], // in case of index on json/jsonb specific fields inside the json/jsonb
            defaultValue: default column value,
            reference: { // foreign key
                referenceSchema: 'referenced schema name',
                referenceTable: 'referenced table name',
                referenceColumn: 'referenced column name'
            }
        }
        ...
    ]
};

const createTableResult = await dbHelperInstance.createTable(createTableObject);

Print Query

const queryType = 'insert' OR 'update' OR 'upsert' OR 'select' OR 'delete' OR 'createTable';
const queryObject = {...} // the matching query object of the chosen queryType
const query = dbHelperTransactionInstance.printQuery(queryType, queryObject);

Get Field Type

const fieldTypeObject = {
	tableSchema: 'table schema', // (default is: 'public')
	tableName: 'table name',
	columnName: 'column name'
};
const typeName = dbHelperTransactionInstance.getFieldType(fieldTypeObject);

Additional Info On Where Selector

{
    ...
    selector: {
        where: [
            {
                'culumn name 1': column value 1,
                'culumn name 2': column value 2,
                ...
            },
            {
                'culumn name 3': column value 3,
                'culumn name 4': column value 4,
                ...
            },
            ...
        ]
        ...
    }
    ...
};

• When using the where selector array, the relation between column values inside every object is AND, and the relation between different objects in the where array is OR:
• The where selector array supports the following operators:

BETWEEN, NOT BETWEEN, ILIKE, LIKE, NOT ILIKE, NOT LIKE, IN, NOT IN, ~*, NOT ~*, IS, NOT IS, =, !=, <, <=, >, >=, @>, NOT @>, <@, NOT <@ &&, NOT &&, ?, NOT ?

• When a column value is of type Array the default operator is: 'IN'
• When a column value is of type Object it will look for an operator and a value:

'column name 1': {
    operator: 'operator',
    value: value
}

• When a column value is not of type Array and not of type Object the default operator is '='

Examples:

• The equivelent of the where query:

WHERE ((name = 'admin' AND age = 18) OR (name IN ('admin','admin2') AND age >= 18 AND birthdate BETWEEN '01/01/2000' AND '01/01/2020'))

is:

{
    ...
    where: [
        {
            name: 'admin',
            age: 18
        },
        {
            name: ['admin', 'admin2'],
            age: {
                operator: '>=',
                value: 18
            },
            birthDate: {
                operator: 'BETWEEN',
                value: ['01/01/2000','01/01/2020']
            }
        }
    ]
    ...
};

Custom operators @-@, NOT @-@, #-#, NOT #-#, @=#, !@=#, @>#, @<#, #=#, !#=#, #>#, #<#

These oparators are used when we pass an object to query (ex. {"key":"value"}) and we want to search a jsonb column type.
It will look for objects in column that have the same key as the object has, and depending on the symbol provided,
it will query values in the column in the manner of equals, more than, less than, between, ...

OPERATOR    |   DATA TYPE   |   TRANSLATES TO   |                   EXPLANATION
@-@         |      date     |      BETWEEN      |             serching range of dates
NOT @-@     |      date     |    NOT BETWEEN    |   searching range of dates that are not in range
#-#         |     numeric   |      BETWEEN      |           searching range of numbers
NOT #-#     |     numeric   |    NOT BETWEEN    |   searching range of numbers that are not in range
@=#         |      date     |       =           |   searching dates that are equal to the one provided
!@=#        |      date     |       !=          |   searching dates that are not equal to the one provided
@>#         |      date     |       >           |   searching dates that are more than the one provided
@<#         |      date     |       <           |   searching dates that are less than the one provided
#=#         |     numeric   |       =           |   searching numbers that are equal to the one provided
!#=#        |     numeric   |       !=          |   searching numbers that are not equal to the one provided
#>#         |     numeric   |       >           |   searching numbers that are more than to the one provided
#<#         |     numeric   |       <           |   searching numbers that are less than to the one provided

These operators only work when passed from frontend, because they are handled in queryBuilders. They create appropriate query that is passed to postgres
(jsonbColumn->>key) >(or some other symbol) value
```example
Payments
id  |   amounts(jsonb)
1   |   {"Paid": "100", "Total":"300"}
2   |   {"Paid": "50", "Total":"100"}