What is the difference between pool.query and client.query with the Node pg library?

Question

I am trying to understand pool.query and client.query and the differences between the two and when to use which.

I have an express application that runs the following code at a certain end-point.

// I am just using params for to a quick setup
router.get("/:username/:password", (req, res) => {
  const { username, password } = req.params;
  // crypt & gen_salt function from pgcrypto
  client // *** here ***
    .query(
      ` SELECT username, email, password FROM users 
        WHERE users.username = $1
        AND users.password = crypt($2, password)
      `, [username, password]
    )
    .then(user => {
      console.log("Users ==>", user.rows);
      const userCount = user.rows.length;
      if (userCount < 1) {
        res.status(204).json({ userFound: "No User Found" });
      } else {
        res.status(200).json(user.rows[0]);
      }
    })
    .catch(err => console.log(err));
});

// index.js (starting point of application)
require("dotenv").config({ debug: true });
const app = require("./middleware");


// Port Listener
app.listen(process.env.PORT, () =>
  console.log(
    "Server running on PORT <======= " + process.env.PORT + " =======>"
  )
);

I hit the API endpoints up and Both ways return the exact same result, and I have read that using pooling over instantiating a new client instance is better, but I'm honestly not sure if that is true since Node should keep the same client instance because of the way it's being exported as it is beyond my current knowledge base. The readings I found on this topic were kind of sparse, so if any one has any articles they would recommend then I'd be happy to check them out as well.

Answer 1

I believe both are the same. But pool.query will allow you to execute a basic single query when you need to execute from a client that would be accessed from the pool of client threads.

So pool.query could be used to directly run the query rather than acquiring a client and then running the query with that client.

Acquiring Client from Pool

const { Pool } = require('pg')

const pool = new Pool()

pool.connect((err, client, release) => {
  if (err) {
    return console.error('Error acquiring client', err.stack)
  }
  client.query('SELECT NOW()', (err, result) => { // Default client query which is same as that used when connect to DB with one client.
    release()
    if (err) {
      return console.error('Error executing query', err.stack)
    }
    console.log(result.rows)
  })
})

Same as above except without pool

const { Client } = require('pg').Client
const client = new Client()
client.connect()
client.query('SELECT NOW()', (err, res) => {
  if (err) throw err
  console.log(res)
  client.end()
})

Directly calling query on pool

const { Pool } = require('pg')

const pool = new Pool()

// Direct query without acquiring client object.
pool.query('SELECT $1::text as name', ['brianc'], (err, result) => {
  if (err) {
    return console.error('Error executing query', err.stack)
  }
  console.log(result.rows[0].name) // brianc
})