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

Introduction

This library unifies Promise and PG to fascilitate easy-to-read database code that relies on promises:

• Simplistic approach to organizing streamlined database code, thanks to full Promise integration;
• Database connections are managed automatically, in every usage case;
• Functions, Procedures and Transactions are all fully supported;
• Robust approach to handling results from every single query.

Getting started

1. Load the library

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

2. Configure database connection

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

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). This also means that you can pass a connection string instead, it is up to PG then to figure what it is and process accordingly.

For example, the same config could be defined as this instead:

var config = "postgres://postgres:bla-bla@localhost/my_db_name";

It would work exactly the same, though perhaps it then then should be renamed into cnString.

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 mask, using type queryResult as shown below:

queryResult = {
    one: 1,     // single-row result is expected;
    many: 2,    // multi-row result is expected;
    none: 4     // no rows expected.
};

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.

Each of the query calls returns a Promise object, as shown below, to be used in the standard way. And when the expected and actual results do not match, the call will be rejected.

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 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 chapters 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 example above 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 we do it on the transaction object instance now, as opposed to the global pgp object, which gives us access to the shared 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 helper functions to convert a basic javascript type into its proper PostgreSQL presentation that can be passed directly into queries or functions as parameters. All of such helper functions are located within namespace pgp.as:

pgp.as.bool(value); // returns proper postgresql boolean presentation

pgp.as.text(value); // returns proper postgresql text presentation,
                    // fixing single-quote symbols

pgp.as.date(value); // returns proper postgresql date/time presentation

As these helpers are not associated with a connection, they can be called from inside or outside a transaction.

Advanced

Work in progress.