CODEWITHSUNDEEP
node.jspostgresqlpostman
Steven Klinger
Steven Klinger

Reputation: 284

Pool is draining and cannot accept work : twice the same call in the same session?

I have a last issue with my Node.JS code. I want to insert a user into my database (see below). When I test it with Postman the first time : Ok it works. BUT if I try again with different values, it doesn't and throw me this :

POST /users/create_account 500 3.735 ms - 1642
Error: pool is draining and cannot accept work

So I don't understand why. There is the code below : (I Think there is no need to link my SQL code, if you need it, ask me :)) I'm note using AWS by the way.

var express = require('express');
var router = express.Router();
const { Pool, Client } = require('pg');
const bodyParser = require('body-parser');

const client = new Client({
  user: process.env.DB_DBUSER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASS,
  port: process.env.DB_PORT,
})

const pool = new Pool({
  user: process.env.DB_DBUSER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASS,
  port: process.env.DB_PORT,
})

pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err)
  process.exit(-1)
})

//Third-party middelware
router.use(bodyParser.json());
router.use(bodyParser.urlencoded({
  extended: true
}));

/* CREATE an account */
router.post('/create_account', function (req, response, next) {

  // Grab data from http request
  const data = {
    username: req.body.username,
    name: req.body.name,
    firstname: req.body.firstname,
    email: req.body.email,
    location: req.body.location
  };

  const values = [
    data.username,
    data.name,
    data.firstname,
    data.email,
    data.location
  ];

  pool.connect((err, client, done) => {
    if (err)
      throw err;

    client.query('INSERT INTO users(username, name, firstname, email, location) values($1, $2, $3, $4, $5)', values, (err, res) => {
      done();

      if (err) {
        throw err
      }
      response.send('USER' + res.rows[0] + 'SAVED');
      console.log('user:', res.rows[0]);
      pool.end()
    })
  });
});

module.exports = router;

Oc, in my postman body, I have this:

{
    "username" : "Username",
    "name" : "Bob",
    "firstname" : "Paul",
    "email" : "[email protected]",
    "location" : "Here"
}

And my dependencies:

  "dependencies": {
    "body-parser": "^1.18.3",
    "cookie-parser": "~1.4.3",
    "debug": "~2.6.9",
    "dotenv": "^6.0.0",
    "express": "~4.16.0",
    "http-errors": "~1.6.2",
    "morgan": "~1.9.0",
    "pg": "^6.1.0",
    "pg-hstore": "^2.3.2"
  },
  "devDependencies": {
    "chai": "^4.1.2",
    "mocha": "^5.2.0"
  }

The SQL:

const queryUser = client.query(
  'CREATE TABLE IF NOT EXISTS Users('
  + 'user_id SERIAL,'
  + 'username VARCHAR(40) NOT NULL UNIQUE,'
  + 'name VARCHAR(40) NOT NULL,'
  + 'firstname VARCHAR(40) NOT NULL,'
  + 'email VARCHAR(60) UNIQUE,'
  + 'location Varchar(60) NOT NULL,'
  + 'CONSTRAINT pk_user PRIMARY KEY (user_id))',

  (err, res) => {
    if (err) throw err
    console.log("TABLE USERS : " + res)
  });

Upvotes: 1

Views: 6478

Answers (1)

Eddie Jaoude
Eddie Jaoude

Reputation: 1708

I would suggest using an ORM to handle the connections for you, something like Sequelize https://www.npmjs.com/package/sequelize

Simple usage...

const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
   host: 'localhost',
   dialect: 'mysql'|'sqlite'|'postgres'|'mssql',

   pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000
   },
});

const User = sequelize.define('user', {
   username: Sequelize.STRING,
   birthday: Sequelize.DATE
});

sequelize.sync()
   .then(() => User.create({
      username: 'janedoe',
      birthday: new Date(1980, 6, 20)
   })) 
   .then(jane => {
      console.log(jane.toJSON());
 });

Upvotes: 3

Related Questions