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

Following a large-scale project, I had to implement a good wrapper for PG package, to support Promise object for inside and outside transactions, as no other library seems to offer a simple and concise approach.

Log

March 3rd, 2015. I just started working on it, changing to be more suitable for public consumption.