CODEWITHSUNDEEP
node.jspostgresqlexpresspgpoolnode-pg-pool
Philip Jay Fry
Philip Jay Fry

Reputation: 115

1 db connection for all functions in API call

I have a route that handles API calls for timepunches. One of the calls is to "clock_in".

router.route('/clock_in').post(managerCheck, startTimeCheck, isClockedIn, clockIn);

Each of these functions will perform it's own db connection, query the db for some info, then respond to the user or go to the next() function.

I'm using pool from 'pg-poll'.

My connection looks like this.

export const **isClockedIn** = (request, response, next) => {

  const query = `select * from....`;

  const values = [value1, value2];

  pool.connect((err, client, release) => {

    client.query(query, values, (err, result) => {
    //do stuff
    }

and the connection is essentially the same for all functions.

What i'd like to do is have only 1 instance of pool.connect then each function in the api call will use that connection to do their client.query. I'm just not sure how i'd set that up.

Hopefully my question is clear. All my code works, it's just not efficient since it's making multiple db connections for 1 api call.

Upvotes: 1

Views: 1139

Answers (1)

Philip Jay Fry
Philip Jay Fry

Reputation: 115

I learned a lot by watching my db connections as I made calls from my API.

When you make your first call with pg.pool a connection will be made to the db. After your query finishes the connection is placed into an idle state, if another pg.pool command is run, it will use that idle connection. The connection will close after 10 seconds of being idle (you can configure this).

You can also set a max amount of connections (default 10). So if you run 10 queries at the same time, they will all open a connection and run. Their connections will be left idle after completion. If you run another 10 at the same time, they will reuse those connections.

if you want to force only 1 connection ever that never closes (not saying you want to do this), you set idle timeout to 0, and max 1 connection. Then if you run 10 queries at once, they will line up and run one at a time.

const pool = new pg.Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'database',
  password: 'password',
  port: 5000,
  idleTimeoutMillis: 0,
  max: 1,
});

This page is super helpful, although I didn't understand much of it until I watched the database connection as my API ran.

https://node-postgres.com/api/pool

Note: The above code should be in it's own js file and all connections should reference it. If you create new pg.Pools I believe those will open their own connections which may not be what you want.

Upvotes: 1

Related Questions