// Proper way to initialize and share the Database object

// Loading and initializing the library:
const pgp = require('pg-promise')({
    // Initialization Options
});

// Preparing the connection details:
const cn = 'postgres://username:password@host:port/database';

// Creating a new database instance from the connection details:
const db = pgp(cn);

// Exporting the database object for shared use:
module.exports = db;

Members

(readonly) $cn :string|object

Type:

• string | object

Source:

• database.js, line 341

See:

• Database

(readonly) $config :object

Type:

• object

Source:

• database.js, line 308

Example

// Using the promise protocol as configured by pg-promise:

const $p = db.$config.promise;

const resolvedPromise = $p.resolve('some data');
const rejectedPromise = $p.reject('some reason');

const newPromise = $p((resolve, reject) => {
    // call either resolve(data) or reject(reason) here
});

(readonly) $dc :*

Type:

• *

Source:

• database.js, line 355

See:

• Database

(readonly) $pool :external:pg-pool

Type:

• external:pg-pool

Source:

• database.js, line 369

See:

• Database pgp.end

Example

// Shutting down the connection pool of this database object,
// after all queries have finished in a run-though process:

.then(() => {}) // processing the data
.catch() => {}) // handling the error
.finally(db.$pool.end); // shutting down the pool

Methods

any(query, valuesopt) → {external:Promise.<Array>}

Parameters:

Source:

• database.js, line 687

See:

• manyOrNone, map, each

Returns:

Type

external:Promise.<Array>

connect(optionsopt) → {external:Promise}

Parameters:

function onLostConnection(err, e) {
    e.client.removeListener('my-event', myHandler);
}

Source:

• database.js, line 122

See:

• Database.task, Database.tx

Returns:

Type

external:Promise

Example

let sco; // shared connection object;

db.connect()
    .then(obj => {
        // obj.client = new connected Client object;

        sco = obj; // save the connection object;

        // execute all the queries you need:
        return sco.any('SELECT * FROM Users');
    })
    .then(data => {
        // success
    })
    .catch(error => {
        // error
    })
    .finally(() => {
        // release the connection, if it was successful:
        if (sco) {
            sco.done();
        }
    });

each(query, valuesopt, cb, thisArgopt) → {external:Promise.<Array.<Object>>}

db.any(query, values)
    .then(data => {
        data.forEach((row, index, data) => {
             // process the row
        });
        return data;
    });

Parameters:

Source:

• database.js, line 998

See:

• any, map, Array.forEach

Returns:

Type

external:Promise.<Array.<Object>>

Example

db.each('SELECT id, code, name FROM Events', [], row => {
    row.code = +row.code; // leading `+` is short for `parseInt()`
})
    .then(data => {
        // data = array of events, with 'code' converted into integer
    })
    .catch(error => {
        // error
    });

func(funcName, valuesopt, qrmopt) → {external:Promise}

Parameters:

Source:

• database.js, line 827

See:

• query, proc

Returns:

Type

external:Promise

many(query, valuesopt) → {external:Promise}

Parameters:

Source:

• database.js, line 546

Returns:

Type

external:Promise

manyOrNone(query, valuesopt) → {external:Promise.<Array>}

Parameters:

Source:

• database.js, line 645

See:

• any, many, none

Returns:

Type

external:Promise.<Array>

map(query, values, cb, thisArgopt) → {external:Promise.<Array>}

db.any(query, values)
    .then(data => {
        return data.map((row, index, data) => {
             // return a new element
        });
    });

Parameters:

Source:

• database.js, line 891

See:

• any, each, Array.map

Returns:

Type

external:Promise.<Array>

Examples

db.map('SELECT id FROM Users WHERE status = $1', ['active'], row => row.id)
    .then(data => {
        // data = array of active user id-s
    })
    .catch(error => {
       // error
    });

db.tx(t => {
    return t.map('SELECT id FROM Users WHERE status = $1', ['active'], row => {
       return t.none('UPDATE Events SET checked = $1 WHERE userId = $2', [true, row.id]);
    }).then(t.batch);
})
    .then(data => {
        // success
    })
    .catch(error => {
        // error
    });

// Build a list of active users, each with the list of user events:
db.task(t => {
    return t.map('SELECT id FROM Users WHERE status = $1', ['active'], user => {
        return t.any('SELECT * FROM Events WHERE userId = $1', user.id)
            .then(events=> {
                user.events = events;
                return user;
            });
    }).then(t.batch);
})
    .then(data => {
        // success
    })
    .catch(error => {
        // error
    });

none(query, valuesopt) → {external:Promise.<null>}

Parameters:

Source:

• database.js, line 440

Returns:

Type

external:Promise.<null>

one(query, valuesopt, cbopt, thisArgopt) → {external:Promise}

Parameters:

Source:

• database.js, line 476

See:

• oneOrNone

Returns:

Type

external:Promise

Examples

// a query with in-line value transformation:
db.one('INSERT INTO Events VALUES($1) RETURNING id', [123], event => event.id)
    .then(data => {
        // data = a new event id, rather than an object with it
    });

// a query with in-line value transformation + conversion:
db.one('SELECT count(*) FROM Users', [], c => +c.count)
    .then(count => {
        // count = a proper integer value, rather than an object with a string
    });

oneOrNone(query, valuesopt, cbopt, thisArgopt) → {external:Promise}

Parameters:

Source:

• database.js, line 583

See:

• one, none, manyOrNone

Returns:

Type

external:Promise

Example

// a query with in-line value transformation:
db.oneOrNone('SELECT id FROM Events WHERE type = $1', ['entry'], e => e && e.id)
    .then(data => {
        // data = the event id or null (rather than object or null)
    });

proc(procName, valuesopt, cbopt, thisArgopt) → {external:Promise}

Parameters:

Source:

• database.js, line 852

See:

• oneOrNone, func

Returns:

Type

external:Promise

query(query, valuesopt, qrmopt) → {external:Promise}

Parameters:

Source:

• database.js, line 257

Returns:

Type

external:Promise

result(query, valuesopt, cbopt, thisArgopt) → {external:Promise}

Parameters:

Source:

• database.js, line 730

Returns:

Type

external:Promise

Examples

// use of value transformation:
// deleting rows and returning the number of rows deleted
db.result('DELETE FROM Events WHERE id = $1', [123], r => r.rowCount)
    .then(data => {
        // data = number of rows that were deleted
    });

// use of value transformation:
// getting only column details from a table
db.result('SELECT * FROM Users LIMIT 0', null, r => r.fields)
    .then(data => {
        // data = array of column descriptors
    });

stream(qs, initCB) → {external:Promise}

Parameters:

Source:

• database.js, line 794

Returns:

Type

external:Promise

task(tag/cb, cbopt) → {external:Promise}

Parameters:

Source:

• database.js, line 1073

See:

• Task, tx, tags, Chaining Queries

Returns:

Type

external:Promise

Examples

db.task('my-task', t => {
        // t.ctx = task context object
        
        return t.one('SELECT id FROM Users WHERE name = $1', 'John')
            .then(user => {
                return t.any('SELECT * FROM Events WHERE userId = $1', user.id);
            });
    })
    .then(data => {
        // success
        // data = as returned from the task's callback
    })
    .catch(error => {
        // error
    });

// using an ES6 generator for the callback:
db.task('my-task', function * (t) {
        // t.ctx = task context object

        let user = yield t.one('SELECT id FROM Users WHERE name = $1', 'John');
        return yield t.any('SELECT * FROM Events WHERE userId = $1', user.id);
    })
    .then(data => {
        // success
        // data = as returned from the task's callback
    })
    .catch(error => {
        // error
    });

tx(tag/cb, cbopt) → {external:Promise}

Parameters:

Source:

• database.js, line 1147

See:

• Task, Database.task, tags, Chaining Queries

Returns:

Type

external:Promise

Examples

db.tx('my-transaction', t => {
        // t.ctx = transaction context object
        
        return t.one('INSERT INTO Users(name, age) VALUES($1, $2) RETURNING id', ['Mike', 25])
            .then(user=> {
                return t.batch([
                    t.none('INSERT INTO Events(userId, name) VALUES($1, $2)', [user.id, 'created']),
                    t.none('INSERT INTO Events(userId, name) VALUES($1, $2)', [user.id, 'login'])
                ]);
            });
    })
    .then(data => {
        // success
        // data = as returned from the transaction's callback
    })
    .catch(error => {
        // error
    });

// using an ES6 generator for the callback:
db.tx('my-transaction', function * (t) {
        // t.ctx = transaction context object

        let user = yield t.one('INSERT INTO Users(name, age) VALUES($1, $2) RETURNING id', ['Mike', 25]);
        return yield t.none('INSERT INTO Events(userId, name) VALUES($1, $2)', [user.id, 'created']);
    })
    .then(data => {
        // success
        // data = as returned from the transaction's callback
    })
    .catch(error => {
        // error
    });

Type Definitions

streamInitCB(stream)

Parameters:

Source:

• database.js, line 1334

Example

const QueryStream = require('pg-query-stream');
const JSONStream = require('JSONStream');

// you can also use pgp.as.format(query, values, options)
// to format queries properly, via pg-promise;
const qs = new QueryStream('SELECT * FROM users');

db.stream(qs, stream => {
        // initiate streaming into the console:
        stream.pipe(JSONStream.stringify()).pipe(process.stdout);
    })
    .then(data => {
        console.log('Total rows processed:', data.processed,
          'Duration in milliseconds:', data.duration);
    })
    .catch(error => {
        // error;
    });