postgresql-query

Querying PostgreSQL with Node.js made easy.

Created by: Lasha Tavartkiladze at Elva
License: MIT

Install

npm install postgresql-query

config()

Require and prepare module for querying.

let db = require('postgresql-query');

db.config({
    username: '',
    password: '',
    host: '',
    database: '' 
});

query()

Query a database and get results in a callback function.

db.query(sql, values, callback);

db.query('SELECT * FROM albums WHERE artist_id = $1', 47, function (err, albums) {
    
});

Or run multiple queries in specified order and get all results in a finalCallback functions.

db.query(queries, finalCallback);

db.query([
    ['SELECT * FROM albums WHERE artist_id = $1', 47],
    ['SELECT * FROM genres WHERE artist_id = $1 AND mood = $2', [47, 'sad']],
    ['SELECT * FROM comments WHERE artist_id = $1', [47]]
], function (err, albums, genres, comments) {
        
});

Or use async/await

db.query(sql, values);

(async function () {
    try {
        let albums = await db.query('SELECT * FROM albums WHERE artist_id = $1', 47);
        let genres = await db.query('SELECT * FROM genres WHERE artist_id = $1 AND mood = $2', [47, 'sad']);
        let comments = await db.query('SELECT * FROM comments WHERE artist_id = $1', [47]);
    } catch (err) {
        console.log(err);
    }
})();

queryOne()

Query a database and use only the first row.

db.queryOne(sql, values, callback);

db.queryOne('SELECT * FROM artists WHERE id = $1', 47, function (err, artist) {
    // artist.name
});

db.queryOne(sql, values);

(async function () {
    await artist = db.queryOne('SELECT * FROM artists WHERE id = $1', 47);
})().catch(function (err) {});

insert()

Helper function to make it easy writing INSERT queries.

db.insert(obj, callback);

db.insert({
    table: 'artists',
    fields: {
        first_name: 'John',
        last_name: 'Doe',
        country: 'Italy'
    },
    returnValue: '*'
}, function (err, insertedRow) {
    
});

Above is the same as this:

db.query('INSERT INTO artists (first_name, last_name, country) VALUES ($1, $2, $3) RETURNING *', ['John', 'Doe', 'Italy'], function (err, insertedRows) {
    
});

update()

Helper function to make it easy writing UPDATE queries.

db.update(obj, callback);

db.update({
    table: 'artists',
    fields: {
        first_name: 'Mister',
        last_name: 'Smith',
        country: 'Spain'
    },
    where: {
        id: 38
    },
    returnValue: '*'
}, function (err, updatedRow) {
    
});

Above is the same as this:

db.query('UPDATE artists SET first_name = $1, last_name = $2, country = $3 WHERE id = $4 RETURNING *', ['Mister', 'Smith', 'Spain', 38], function (err, updatedRows) {
    
});

transaction()

Helper function to make it easy dealing with transactions. It takes only a single parameter - a callback function and passed transaction object to it.

db.transaction(function (transaction) {
});

Or async/await

let transaction = await db.transaction();

Transaction object has three methods:

transaction.query();
transaction.commit();
transaction.rollback();

transaction.query() is similar to db.query and can run multiple queries one after another. It also supports db.insert() and db.update() syntax, so handy way of writing INSERT/UPDATE queries can be used here as well.

Example below starts a transaction, inserts a new artist and if that query was successfull adds two albums on that artist and then commits the transaction.

(async function () {
    let transaction;
    try {
        tranaction = await db.transaction();
        let artist = await transaction.query({
            table: 'artist',
            fields: {
                first_name: 'Something'
            },
            returnValue: '*'
        });
        await transaction.query([
            {
                table: 'albums',
                fields: {
                    artist_id: insertedArtist.id,
                    title: 'Best Songs',
                    release_year: 2017
                }
            }, {
                table: 'albums',
                fields: {
                    artist_id: insertedArtist.id,
                    title: 'New Century',
                    release_year: 2016
                }
            }
        ]);
        transaction.commit();
    } catch (err) {
        console.log(err);
        transaction.rollback();
    }
})();