pg-pool

What is pg-pool?

The pg-pool npm package is a connection pool manager for PostgreSQL, built on top of the 'pg' library. It allows you to manage multiple database connections efficiently, providing features like connection pooling, transaction management, and error handling.

What are pg-pool's main functionalities?

Connection Pooling

This feature allows you to create a pool of connections to the PostgreSQL database. The pool manages the connections, reusing them for multiple queries to improve performance.

const { Pool } = require('pg');
const pool = new Pool({
  user: 'dbuser',
  host: 'database.server.com',
  database: 'mydb',
  password: 'secretpassword',
  port: 5432,
});

pool.query('SELECT NOW()', (err, res) => {
  console.log(err, res);
  pool.end();
});

Transaction Management

This feature allows you to manage transactions, ensuring that a series of database operations either all succeed or all fail, maintaining data integrity.

const { Pool } = require('pg');
const pool = new Pool();

(async () => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const res = await client.query('INSERT INTO users(name) VALUES($1) RETURNING id', ['brianc']);
    const insertPhotoText = 'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)';
    const insertPhotoValues = [res.rows[0].id, 's3.bucket.foo'];
    await client.query(insertPhotoText, insertPhotoValues);
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
})();

Error Handling

This feature provides robust error handling, allowing you to catch and handle errors that occur during query execution.

const { Pool } = require('pg');
const pool = new Pool();

pool.query('SELECT * FROM non_existent_table', (err, res) => {
  if (err) {
    console.error('Error executing query', err.stack);
  } else {
    console.log(res.rows);
  }
  pool.end();
});

Other packages similar to pg-pool

pg

The 'pg' package is the core PostgreSQL client for Node.js. It provides a simple interface for executing SQL queries and managing database connections. Unlike pg-pool, it does not include built-in connection pooling, but it can be used in conjunction with pg-pool for that purpose.

sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication, and more. While it provides connection pooling, it also offers a higher-level abstraction for database operations compared to pg-pool.

knex

Knex.js is a SQL query builder for PostgreSQL, MySQL, MariaDB, SQLite3, and Oracle. It features both traditional node-style callbacks as well as a promise interface for cleaner async flow. Knex.js includes built-in connection pooling and transaction management, similar to pg-pool, but also provides a more flexible query building experience.

README

The README file is missing

Changelog

v1.0.0

• remove deprecated functionality
  • Callback function passed to pg.connect now requires 3 arguments
  • Client#pauseDrain() / Client#resumeDrain removed
  • numeric, decimal, and float data types no longer parsed into float before being returned. Will be returned from query results as String