pg-promise

What is pg-promise?

pg-promise is a Node.js library for interfacing with PostgreSQL databases. It provides a powerful and flexible API for executing SQL queries, managing transactions, and handling connections. The library is built on top of the 'pg' module and adds a layer of promise-based functionality, making it easier to work with asynchronous operations.

What are pg-promise's main functionalities?

Basic Query Execution

This feature allows you to execute basic SQL queries. The example demonstrates how to select all active users from a 'users' table.

const pgp = require('pg-promise')();
const db = pgp('postgres://username:password@host:port/database');

db.any('SELECT * FROM users WHERE active = $1', [true])
  .then(data => {
    console.log(data);
  })
  .catch(error => {
    console.error(error);
  });

Parameterized Queries

This feature allows you to use parameterized queries to prevent SQL injection. The example shows how to insert a new user into the 'users' table and return the new user's ID.

const pgp = require('pg-promise')();
const db = pgp('postgres://username:password@host:port/database');

const query = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING id';
const values = ['John Doe', '[e-mail]'];

db.one(query, values)
  .then(data => {
    console.log('New User ID:', data.id);
  })
  .catch(error => {
    console.error(error);
  });

Transactions

This feature allows you to manage transactions, ensuring that a series of queries either all succeed or all fail. The example demonstrates how to insert a user and their profile in a single transaction.

const pgp = require('pg-promise')();
const db = pgp('postgres://username:password@host:port/database');

db.tx(t => {
  return t.batch([
    t.none('INSERT INTO users(name, email) VALUES($1, $2)', ['John Doe', '[e-mail]']),
    t.none('INSERT INTO profiles(user_id, bio) VALUES((SELECT id FROM users WHERE email = $1), $2)', ['[e-mail]', 'Bio for John Doe'])
  ]);
})
  .then(data => {
    console.log('Transaction successful');
  })
  .catch(error => {
    console.error('Transaction failed:', error);
  });

Connection Management

This feature allows you to manage database connections explicitly. The example shows how to establish and release a connection.

const pgp = require('pg-promise')();
const db = pgp('postgres://username:password@host:port/database');

db.connect()
  .then(obj => {
    obj.done(); // success, release the connection;
    console.log('Connection successful');
  })
  .catch(error => {
    console.error('Connection failed:', error);
  });

Other packages similar to pg-promise

pg

The 'pg' package is a low-level PostgreSQL client for Node.js. It provides basic functionality for connecting to a PostgreSQL database and executing queries. Unlike pg-promise, it does not include built-in promise support, so you need to handle asynchronous operations manually or use a promise library.

sequelize

Sequelize is an ORM (Object-Relational Mapper) for Node.js that supports multiple SQL dialects, including PostgreSQL. It provides a higher-level API for defining models and relationships, making it easier to work with complex data structures. Unlike pg-promise, which is focused on SQL query execution, Sequelize abstracts much of the SQL away.

knex

Knex.js is a SQL query builder for Node.js that supports multiple database types, including PostgreSQL. It provides a flexible and chainable API for building SQL queries programmatically. While pg-promise focuses on executing raw SQL queries, Knex.js allows you to construct queries using JavaScript methods.

README

Getting started

1. Load the library

var pgpLib = require('pg-promise');

2. Configure connection parameters

var config = {
    host: 'localhost',
    port: 5432,
    database: 'my_db_name',
    user: 'postgres',
    password: 'bla-bla'
};

NOTE: The library itself doesn't use this object at all, just passing it on to PG to interpret and use. See ConnectionParameters in PG package for details.

3. Initialize the library

var pgp = pgpLib(config);

Only one global instance should be used as shown above. Now you are ready to use it.

Usage

The basics

In order to eliminate the chances of unexpected query results and make code more robust, each request is parametrized with the expected/supported return result, using type queryResult as shown below:

queryResult = {
    one: 1,
    many: 2,
    none: 4
};

In the following generic-query example we indicate that the call must return either no records or multiple records:

pgp.query("select * from users", queryResult.none | queryResult.many);

This usage pattern is facilitated through result-specific methods that can be used instead of the generic query:

pgp.many("select * from users"); // multiple records are expected
pgp.one("select * from users limit 1"); // one record is expected
pgp.none("update users set active=TRUE where id=1"); // no records expected

There are also mixed-result ones, called oneOrNone and manyOrNone. And when the expected and actual results do not match, the call will be rejected.

Each of the query calls returns a Promise object to be used in the standard way:

pgp.many("select * from users").then(function(data){
    console.log(data); // printing the data received
},function(reason){
    console.log(reason); // printing the reason why the call was rejected
});

Functions and Procedures

In PostgreSQL stored procedures are just functions that usually do not return anything.

Suppose we want to call a function to find audit records by user id and maximum time stamp. We can call a corresponding function as shown below:

pgp.func('findAudit', [
    123,
    new Date()
]);

We passed it user Id = 123, plus current Date/Time as the time stamp. We assume that the function signature matches the the parameters that we passed. All values passed are serialized automatically to comply with PostgreSQL requirements. In the same way you can call pgp.proc, which doesn't care about the returned result. Both methods return a promise object.

Transactions

Every call shown in the chapter above would acquire a new connection from the pool and release it when done. In order to execute a transaction on the same connection, a transaction class is to be used.

Example:

var promise = require('promise');

var tx = new pgp.tx(); // creating a new transaction object
tx.exec(function(){
    return promise.all([
        tx.none("update users set active=TRUE"),
        tx.one("insert into audit(event) values('user changed') returning id")
    ]);
}).then(function(data){
    console.log(data); // printing successful transation output
}, function(reason){
    console.log(reason); // printing the reason why the transaction was rejected
});

In the above example we create a new transaction object and call its method exec, passing it a call-back function that must do all the queries needed and return a promise object. In the example we use promise.all to indicate that we want both queries inside the transaction to succeed to consider it a success; otherwise the transaction is to be rolled back.

Note that while inside a transaction, we make calls to the same-named methods as outside of transactions, except on the transaction object instance, instead of the global pgp object, which gives it access to the shared transaction connection object. The same goes for calling functions and procedures within transactions, using tx.func and tx.proc accordingly.

Type Helpers

The library provides several basic helper functions to convert a basic type into proper PostgreSQL presentation that can be used to pass directly into queries or functions as parameters. All of such helper functions are located within namespace pgp.as:

pgp.as.bool(value); // reuturns proper postgresql boolean presentation
pgp.as.text(value); // reuturns proper postgresql text presentation, fixing quota symbols
pgp.as.date(value); // reuturns proper postgresql date/time presentation

These helpers are not associated with a connection, and can be called from inside or outside a transaction.

Advanced

Work in progress. This chapter will be written just as the first version is finished.