WittCode💻

node-postgres Connection Pool

By

Learn how to use the node-postgres or pg library connection pool. We will learn how to connect a node application to a postgres database, learn what a connection pool is, when to use a connection pool, the benefits of a connection pool, and how to configure a connection pool.

Table of Contents 📖

What is a Database Connection Pool?

Database connection pooling is a technique that keeps a collection of open connections to a database and reuses them for different database commands/queries. Without connection pooling, a brand new connection is opened and closed for each database operation. Opening and closing database connections carries lots of overhead, decreasing a program's performance.

When to use a Connection Pool?

Smaller applications don't typically suffer too much from the overhead of opening and closing database connections. Therefore, connection pools aren't always needed as keeping a pool of open connections to a database carries its own overhead as well. However, the size of the database connection pool and idle time before a connection shuts down can often be configured, making connection pools applicable to a wide variety of use cases.

Creating a Connection Pool with pg

To create a connection pool with the pg library we need to import and then instantiate a Pool object.

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

The Pool object has no connections to the Postgres database when it is first created. The connections are created as they are needed. The information we need to initially pass to the pool object is connection information to the database. This is best done with environment variables but for this demonstration we will hard code the values.

const pool = new Pool({
    user: 'postgres'',
    host: 'localhost',
    database: 'postgres',
    password: 'postgres',
    port: 5432
});
  • user - The database user. A fresh postgres database will have the predefined user called postgres.
  • host - The location of the PostgreSQL server, here the server is running on our computer or localhost.
  • database - The name of the database. Most Postgres servers have the database called postgres defined by default.
  • password - the password to access PostgreSQL. The default postgres password is postgres though it will be the password used when the PostgreSQL server was created.
  • port - the port that the PostgreSQL server is running on. By default this is port 5432.

Configuring Postgres Pool

The Pool object can be customized with a configuration object. This configuration object, along with the connection information, is passed to every connection within the pool when the connection is created. This configuration object takes 4 optional keys.

  • max - The maximum number of connections the pool can have. The default value is 10.
  • connectionTimeoutMillis - The amount of time, in milliseconds, to wait for a connection to the database to form before timing out. The default value is 0 which means no timeout.
  • idleTimeoutMillis - The amount of time, in milliseconds, a connection sits idle in the pool before it is torn down and discarded. The default value is 10,000 milliseconds or 10 seconds. We can set it to 0 if we do not want our connections to be torn down, but remember that keeping open connectinos to the database has its own overhead aswell.
  • allowExitOnIdle - A boolean that, when set to true, tells the node event loop to exit the process as soon as all connections are idle, even if they are still connected to the postgres server. The default value is false which means the node event loop will only terminate if all the connections are closed or the pool is shut down.
const pool = new Pool({
    user: 'postgres'',
    host: 'localhost',
    database: 'postgres',
    password: 'postgres',
    port: 5432,
    max: 5,
    connectionTimeoutMillis: 20000,
    idleTimeoutMillis: 20000,
    allowExitOnIdle: false
});

Checking out a Connection from the Pool

To query Postgres with a database pool connection, we need to first checkout a connection from the pool. We can checkout a connection from the pool with the connect method. The connect method is asynchronous and returns a promise. Therefore, we can wrap it in an IIFE to use the await keyword.

(async () => {
    // Checkout a connection from the pool
    const client = await pool.connect();
})();

There are three different scenarios possible when the connect method is called, causing it to behave differently.

  • There are idle connections in the pool - one of the idle connections will be returned.
  • Pool is at max capacity and connections are all being used - the next available connection will be returned.
  • Pool is not at max capacity but all connections are being used - a new connection is created and returned

The pool object comes with a few properties to inform the number of connections idle, waiting, and existing in the connection pool.

pool.idleCount; // Number of connections idle in the connection pool
pool.totalCount; // Total number of connections in the pool
pool.waitingCount; // Number of postgres queries/requests waiting on a connection when all the connections are checked out

Querying the Database with a Connection

After the connection has been checked out, we can use it to query the Postgres database with the query method.

const {rows} = await pool.query('SELECT $1 AS food', ['pizza']);
console.log(rows); // [ { food: 'pizza' } ]

The query method, when successful, returns a Result object. The Result object contains properties such as the returned rows, the amount of rows returned, etc.

Returning a Connection to the Pool

Finally, we have to return the connection back into the pool when we are done with it. This is done with the release method. Unlike connect and query, the release method does not return a promise.

client.release();

The release method also accepts an argument. If this argument is a truthy value then it disconnects and destroys the client as opposed to returning it to the pool.

client.release(true);

It is imperative to release the client back into the pool when it has finished its database operations because if no connections are returned to the pool, eventually there will be no idle connections. In other words, there will be no connections to checkout. Checking out a client from an empty pool will either hang indefinitely or timeout depending if connectionTimeoutMillis in the Pool configuration is not set to 0.

Having node-postgres Checkout and Return Connections to the Pool

Another way we can query the database with the Pool is by calling the query method directly on the pool object. This query method takes the query, a string, as its first argument, and an array of values as the second argument. This method is asynchronous and returns a Promise so we can wrap it with an IIFE so we can use the await keyword.

(async () => {
    const {rows} = await pool.query('SELECT $1 AS food', ['pizza']);
    console.log(rows); // [ { food: 'pizza' } ]
})();

Using the query method on the Pool is different than on the connection as the Pool query method automatically checks out and releases a connection for us.

Closing the Pool

We can close down the connection pool entirely by calling the pool.end method.

(async () => {
    await pool.end();
    console.log('The connection pool has been shut down');
})();

Specifically, this method disconnects all active connections in the pool and then tears them down. This method is mainly used when attempting to gracefully shut down the Node process using the pool.

Pool Extends EventEmitter

The pool object also extends the EventEmitter Node class. Therefore, we can tap into events emitted by the connection pool with the on method. We just need to provide it the event type and a callback function. For example, the connect even is emitted whenever the connection pool creates a new connection to Postgres. The callback function receives the new connection as an argument.

pool.on('connect', (connection) => {
    console.log('New connection to postgres!');
});

Here are some other events that are emitted by the connection pool.

  • error - emitted when an error occurs. When emitted, all connections emit the error event.
  • acquire - emitted when a connection is checked out from the pool
  • release - emitted when a connection is returned to the pool
  • remove - emitted when a connection is closed and removed from the pool
node-postgres Connection Pool