db-utils-pg v1.0.0

db-utils-pg

NodeJS wrapper for node-postgres. This module provides some useful methods for working with Postgres.

Install

$ npm install db-utils-pg

Usage

Initialize a postgres connection at app start for future requests:

// Initialization
const { PG } = require( 'db-utils-pg' )
await PG.open( {
    "user":"user",
    "password":"secret",
    "database":"dbase",
    "max":80,
    "host":"127.0.0.1"
} )

Then you can use wrapper functions anywhere in project files like this:

// where you need postgres
const { PG } = require( 'db-utils-pg' )
const tuple = await PG.getItem( `SELECT id, title FROM table WHERE id = $1`, [ 2 ] )

Methods

For the following snipets lets say we have a table with structure described below:

    CREATE TABLE books (
        id SERIAL PRIMARY KEY,
        title TEXT,
        author TEXT,
        tags TEXT [] default null,
        meta JSONB default null,
        published INT,
        amount INT
    )

open( config, errorHandler = null )

Initializes PostgreSQL connection pool.

• config {object} - server details
• errorHandler {callBack} - handler of error event of pg.Pool

close()

Closes PostgreSQL pool

query( query, params = [] )

Executes SQL query

• query {string} - SQL query to execute
• params {any[]} - query parameters

Returns {pg.Result}

const tuple = await PG.query( `SELECT id, title FROM books WHERE id = $1`, [ 2 ] )

console.log( tuple.rowCount ) // number of returned rows
console.log( tuple.rows ) // rows returned by the query

insert( table, tuple, postfix = '', conflict_target = false )

Inserts the tuple to the table. If postfix is passed it will be appended to the end of the insert query. Postfix is a good place for RETURNING or ON CONFLICT statements.

If some entities of a tuple object are objects, then they automatically will be converted to a JSON string.

const rowID = await PG.insert( 'books', {
        title: 'Twenty Thousand Leagues Under the Seas',
        author: 'Jules Verne',
        tags: [ 'adventure', 'science fiction', 'novel' ],
        meta: { country: 'France', publisher: 'Pierre-Jules Hetzel' },
        published: 1870,
        amount: 150
    }, 'RETURNING id' );

console.log( rowID ) // contains value of the id field of the inserted row

If conflict_target is passed, then ON CONFLICT statement will be generated automatically and row will be inserted into a table if it does not exists, or updated if it does.

const result = await PG.insert( 'books', {
        id: 3,
        title: 'The Lord of the Rings',
        author: 'J. R. R. Tolkien',
        tags: [ 'adventure', 'fantasy' ],
        meta: { country: 'England', publisher: 'Allen & Unwin' },
        published: 1954,
        amount: 150
    }, '', 'id' );

console.log( result ) // in this case result contains true, because no RETURNING statement was passed.

getValue( query, params = [] )

Returns value of the queried field from the database. Accepts the same arguments as PG.query.

const title = await PG.getValue( `SELECT title FROM books WHERE id = $1`, [ 3 ] )

console.log( title ) // 'The Lord of the Rings'

getItems( query, params = [] )

Executes SQL query and returns result as an array of rows. Accepts the same arguments as PG.query.

const tuple = await PG.getItems( `SELECT id, title FROM books WHERE amount < $1`, [ 1000 ] )

console.log( tuple ) // rows returned by the query

getItem( query, params = [] )

Executes SQL query and return one row as object. Accepts the same arguments as PG.query.

const tuple = await PG.getItem( `SELECT id, title FROM books WHERE id = $1`, [ 2 ] )

console.log( tuple ) // object { id: 2, title: 'some title' }

getColumn( query, params = [] )

Executes SQL query and returns array of values in queried column. Accepts the same arguments as PG.query.

const books = await PG.getColumn( `SELECT title FROM books WHERE ANY(tags) = $1`, [ 'adventure' ] )

console.log( books ) // object [ 'Twenty Thousand Leagues Under the Seas', 'The Lord of the Rings', ... ]

getAssoc( query, params = [] )

Executes SQL query and returns Map object that holds key-value pairs, where keys are elements of the first column and values are corresponding elements of the second column. Accepts the same arguments as PG.query.

const chronology = await PG.getAssoc( `SELECT title, published FROM books ORDER BY published` )
console.log( chronology.get( 'Twenty Thousand Leagues Under the Seas' ) ) // 1870

License

Copyright (c) 2022 Igor Parchinsky

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.