1

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.

Philip Jay Fry
  • 105
  • 1
  • 6
  • 1
    Using a connection pool is the right approach. If you have to make any concurrent queries, you'll need multiple connections. The pool optimizes your code by leaving connections open and idle, as connection opening and closing is the expensive part of that deal. If performance _with_ a connection pool is bad, it could be you need more, or fewer, idle connections, or have to tweak some other setting like max idle, max open, ttl, etc. – varontron Jan 16 '20 at 02:33
  • Does this mean that when my app starts it already has connections to the DB open. And when I use "pool" i'm just using one of those already open connections? – Philip Jay Fry Jan 16 '20 at 02:37
  • yessir. assuming you've created the pool during startup. – varontron Jan 16 '20 at 02:39
  • I think that's what i'm missing. I'm looking at this https://www.npmjs.com/package/pg-pool#a-note-on-instances and it think it's pretty much what you just pointed out. Just need to figure out how to create a pool on start up lol. – Philip Jay Fry Jan 16 '20 at 02:43
  • I have this in my app, all my queries import this file. //Local Test const pool = new Pool({ user: 'postgres', host: 'localhost', database: 'database', password: 'password', port: 5000, }); export default pool; – Philip Jay Fry Jan 16 '20 at 02:46

1 Answers1

1

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.

Philip Jay Fry
  • 105
  • 1
  • 6