New Programs Makers

    pg-query-exec
    TypeScript icon, indicating that this package has built-in type declarations

    1.1.0 • Public • Published

    pg-query-exec

    NPM

    Build Status

    Overview

    Pleasant wrapper for pg that supports named parameters and transactions

    Install

    $ npm install pg-query-exec --save
    

    Dependencies

    None directly but include pg as a peer dependency.

    Features

    • Pleasant API for issuing queries.
    • Named parameter support.
    • Implicit transaction management.

    Usage

    Create a QueryExecutor

    You should do this once in a config module then reuse the executor throughout your code:

    import pg = require('pg');
    import { createQueryExecutor } from 'pg-query-exec';
    const pool = new pg.Pool();
    const db = createQueryExecutor(pool);

    Query for multiple rows

    // rows is an array
    const rows = await db.query('SELECT * FROM some_table');

    Query with numbered parameters

    // rows is an array
    const rows = await db.query('SELECT * FROM some_table WHERE name = $1', ["test"]);

    Query with named parameters

    // rows is an array
    const params = {
        name: 'test',
    }
    const rows = await db.query('SELECT * FROM some_table WHERE name = :name', params);

    Query for a single row

    // rows is an object or null
    const row = await db.queryOne('SELECT * FROM some_table WHERE id = 123');

    Query for a field

    // name is an any or null
    const name = await db.queryOne('SELECT name FROM some_table WHERE id = 123', [], 'name')

    Query for a field and specify type via generic

    // name is a string or null
    const name = await db.queryOne<string>('SELECT name FROM some_table WHERE id = 123', [], 'name')

    Perform DML

    // count is the number of rows effected
    const count = await db.update('INSERT INTO foo (name) VALUES (:name)', {name: 'test'});

    Transform a query result

    interface FooRow {
        id: number;
        name: string;
    }
    class Foo {
        constructor(readonly id: number, readonly name: string) {}
    }
    function rowToFoo(row) {
        return new Foo(row.id, row.name);
    }
    // foo is an instance of Foo or null
    const foo = await db.queryOne<Foo,FooRow>('SELECT * FROM foo WHERE id = :id', {id: 123}, rowToFoo);

    Transactions

    Transactions managed using Domains. This allows transaction demarcation to occur outside of the functions that are perform the transactional work. Queries executed within a transaction on the same QueryExecutor will automatically join the in flight transaction and share the same client.

    // db.ts
    const db: QueryExecutor = ...
    export { db };
    
    // Foo.ts
    import { db } from './db';
    async function createFoo(name: string) {
        return db.queryOne<string>('INSERT INTO foo (name) VALUES (:name) RETURNING id', {name}, 'id')
    }
    
    // Audit.ts
    import { db } from './db';
    async function save(type: string, detail: object) {
        await db.update('INSERT INTO audit (type, detail) VALUES (:type, :detail)', {type, message});
    }
    
    // controller.ts
    import { db } from './db';
    import Foo = require('./Foo');
    import Audit = require('./Audit');
    async function someRoute(req: Request, res: Response) {
        const name: string = req.body.name;
        const fooId = await db.tx(async () => {
            const id = await Foo.createFoo(name);
            await Audit.save('foo.create', {id});
        });
        res.send({ fooId });
    }

    If you want to ensure that a given operation must execute within a transaction then use the Tx(...) suffixed functions. They check to ensure that a transaction is in flight and if not throw an Error:

    • queryTx(...)
    • queryOneTx(...)
    • updateTx(...)

    By default an error is thrown if multiple queries are executed concurrently in a transaction. This means that you should not use the implicit pg query queue with transactions. Concurrent usage of non-transactional queries is fine as each will pull a random client from the pool.

    Hooks

    You can optionally add beforeQuery(...) or afterQuery(...) hooks to the QueryExecutor upon creation to be executed before and after each query is executed. This can be used to do things like log query times (probably a good idea) or transform the query results (probably a bad idea).

    const db = createQueryExecutor(pool, {
        afterQuery: (opts) => {
            if (opts.elapsed > 100) {
                console.log('Slow query sql=%j elapsed=%s', opts.sql, opts.elapsed);
            }
        },
    });

    Building and Testing

    To build the module run:

    $ make
    

    Then, to run the tests run:

    $ make test
    

    License

    ISC. See the file LICENSE.

    Install

    npm i pg-query-exec

    DownloadsWeekly Downloads

    4

    Version

    1.1.0

    License

    ISC

    Unpacked Size

    51.4 kB

    Total Files

    15

    Last publish

    Collaborators

    • sehrope