CODEWITHSUNDEEP
node.jspostgresqlasync-await
Sam Leurs
Sam Leurs

Reputation: 2000

reusing postgresql connection pool in nodejs

I have the following postgresql connection file in nodejs:

// postgresql.js

"use strict";

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

module.exports = new Promise((resolve, reject) => {
  const pool = new Pool({
    host: '127.0.0.1',
    port: 5432,
    user: 'postgres',
    password: 'postgres',
    database: 'postgres',
    connectionTimeoutMillis: 0,
    idleTimeoutMillis: 0,
    min: 10,
    max: 20,
  });

  resolve({ pool });
});

I'm using a promise because later on, I will start using Google Cloud Secret Manager. The secrets are fetched asynchronous so the database connection cannot be established when the server starts.

In my controller files, I'm using it like this:

// apicontroller.js

"use strict";

const postgresql = require('./postgresql');

app.get('/api/test', async (req, res, next) => {
// resolve the postgresql promise
const { pool } = await postgresql;

// resolve the pool.connect() promise
const client = await pool.connect();

// use the client object here
// eg. await client.query(...)
});

The problem is not that it doesn't work, instead, it works like a charm! But I'm questioning myself: am I reusing the connection pool, or am I creating a new connection (pool) every time this route is requested?

Is this the correct way to reuse a database connection only once?

Edit: I included only relevant code parts.

Upvotes: 5

Views: 5443

Answers (2)

hurricane
hurricane

Reputation: 6724

As it is explained in the documentation of node-postgres, I would use pool.query rather than using (handling) the client.

Single query, If you don't need a transaction or you just need to run a single query, the pool has a convenience method to run a query on any available client in the pool. This is the preferred way to query with node-postgres if you can as it removes the risk of leaking a client.

So my code would be like;

postgresql.js

let mainPool = null;

function createPool(){
  const pool = new Pool({
    host: '127.0.0.1',
    port: 5432,
    user: 'postgres',
    password: 'postgres',
    database: 'postgres',
    connectionTimeoutMillis: 0,
    idleTimeoutMillis: 0,
    min: 10,
    max: 20,
  });
  return pool;
}

function getPool(){
  if(!mainPool){
    mainPool = createPool();
  }
  return mailPool;
}

export default { getPool };

controller.js

const { getPool } = require('./postgresql');

app.get('/api/test', async (req, res, next) => {
  getPool().query('SELECT * FROM users', [], (err, res) => {
    if (err) {
       return res.status(500).send(err);
    }
    return res.status(500).send(res);
  });
});

Upvotes: 5

Bergi
Bergi

Reputation: 664494

Yes, you are reusing the same pool object that is stored as the result of the exported promise. There is only one, there's no code that would create a second instance, the module is evaluated only once no matter how often you require it.

There is however a small problem with error handling - if the database pool cannot be created (e.g. the secrets fail to load), you need to handle this somewhere (and probably crash the application) instead of waiting for an unhandledrejection event.

While your code is fine, consider the alternative approach of not starting the http server until the pool (and its connection details) are acquired, then passing the pool (not a promise for it) to all the routes. This also ensures that the http server isn't started at all if its dependencies fail to initialise.

Upvotes: 0

Related Questions