PosgreSQL Database library

@pomgui/database-pg is a typescript library that provides an interface to execute the normal operations with a client pg database driver as connect, start transaction, commit, etc.

Advantages:

• All methods return promises.
• It uses query parameters like :id instead of ?.
• The parameters understand object's hierarchy, so it understands parameters like :entry.id.
• The returned rows also are preprocessed to return nested objects if needed (See Usage Example section).
• It maintains the same interface (@pomgui/database) no matter the database, so it helps with the migration from different databases E.g. MySQL to Firebird or to PostgreSQL and vice versa (See @pomgui/database-mysql usage example and compare)

Installation

Use npm to install the library.

npm install @pomgui/database-pg --save

Usage Example

import { PiPgPool } from '@pomgui/database-pg';

const options = {
    connectionString: 'user:pass@host:5432/database'
};

async work(){
    const pool = new PiPgPool(options, 10);
    const db = await pool.get();
    await db.beginTransaction();
    try{
        const param = {entry: {id: 3}};
        const data = await db.query(`
            SELECT 
                e.entry_id "id", e.entry_date, 
                b.benef_id "benef.id", b.name "benef.name"
            FROM ENTRIES e JOIN BENEFICIARIES db ON b.benef_id = e.benef_id
            WHERE entry_id >= :entry.id
            LIMIT 10`, param);
        console.log(data);
        await db.commit();
    }catch(err){
        console.error(err);
        await db.rollback();
    }finally{
        await db.close();
    }
}

This will print:

[{  id: 3, 
    entryDate: 2020-08-01T00:00:00.000Z,
    benef: {
        id: 1,
        name: 'John Doe'
    }
},{  id: 4, 
    date: 2020-08-02T00:00:00.000Z,
    benef: {
        id: 1,
        name: 'Jane Doe'
    }
}, ...
]