Node.js - PostgreSQL (pg) : Client has already been connected. You cannot reuse a client

Question

I am just trying to write simple register/login system. I am trying to find if username exists. Here is the steps :

1. Go localhost:3000/users/register page
2. Fill in all fields and click register button
3. Checking my command line if username exists it should print it with console.log

Everything works fine until now.

When I go back to the register page, I fill in all fields again and click register button. Then it throws it in command line :

Error: Client has already been connected. You cannot reuse a client.
at Client._connect (C:\Users\Hasan\Desktop\Projeler\node-ogreniyorum\node_modules\pg\lib\client.js:91:17)
at C:\Users\Hasan\Desktop\Projeler\node-ogreniyorum\node_modules\pg\lib\client.js:310:10
at new Promise (<anonymous>)
at Client.connect (C:\Users\Hasan\Desktop\Projeler\node-ogreniyorum\node_modules\pg\lib\client.js:309:10)
at Object.module.exports.findUserById (C:\Users\Hasan\Desktop\Projeler\node-ogreniyorum\database\register_sql.js:8:22)
at C:\Users\Hasan\Desktop\Projeler\node-ogreniyorum\routes\users.js:37:29
at Layer.handle [as handle_request] (C:\Users\Hasan\Desktop\Projeler\node-ogreniyorum\node_modules\express\lib\router\layer.js:95:5)
at next (C:\Users\Hasan\Desktop\Projeler\node-ogreniyorum\node_modules\express\lib\router\route.js:137:13)
at next (C:\Users\Hasan\Desktop\Projeler\node-ogreniyorum\node_modules\express\lib\router\route.js:131:14)
at Route.dispatch (C:\Users\Hasan\Desktop\Projeler\node-ogreniyorum\node_modules\express\lib\router\route.js:112:3)  

I dont understand because I already end my client after I call my method.

register_sql.js :

module.exports.findUserById =(async (username) =>{
    try {
        await client.connect();
        console.log('Connected successfuly');
        const result = await client.query("select * from users where username = ($1)", [username]);
        console.log(result.rows[0]['username']);
        await client.end();
        console.log('Client disconnected...');
    } catch (err) {
        console.log(err);
    }
});

I call register_sql.js in users.js. Here is users.js :

const router = express.Router();
const registerSQL = require('../database/register_sql');
router.route('/register')
    .get((req, res, next) => {
        res.render('register');
    })
    .post((req, res, next) => {
            const {
                username,
                password,
                password2
            } = req.body;
            let errors = [];
            if (!username || !password || !password2) {
                errors.push("Please fill in all fields!");
            }
            if (password != password2) {
                errors.push("Passwords do not match!");
            }
            if (password.length < 6) {
                errors.push("Password has to be at least 6 characters!");
            }
            if (errors.length > 0) {
                res.render('register', {
                    errors
                });
            } else {
                registerSQL.findUserById(username);
                res.redirect('/');
            }
});

module.exports = router;  

Thank you for helping!

Answer 1

With node-postgres (which is the one you're using) I've only made it work using the pool do manage the connections.

const pg = require('pg')
const pool = new pg.Pool();

pool.connect(function(err, client, done) {
    if(err) {
      return console.error('connexion error', err);
    }
    client.query("select * from users where username = ($1)", [username], function(err, result) {
      // call `done()` to release the client back to the pool
      done();
  
      if(err) {
        return console.error('error running query', err);
      }
      console.log(result.rows[0]['username'])
    });
});

Answer 2

I managed to fix this problem without using pool. Maybe that's not the most correct solution, but it works. First create a separate js file, where you connect the client and export it:

const pg = require('pg')
const client = new pg.Client('your connection string')
client.connect()
module.exports = client

Then you just use the exported client, which has already been connected, so it won't reconnect again on each request. Be sure to import the client from the js file where you connect it.

    const client = require('../db')
    const register = async (req, res) => {
        const {email, password, username} = req.body
        const hashedPassword = await bcrypt.hash(password, 10)
        const command = `insert into users(email, username, password, created) VALUES ('${email}', '${username}', '${hashedPassword}', current_timestamp)`
        await client.query(command, (err, result) => {
err ? res.json({error: err.detail}) : res.json({message: 'User created!'})
})
    }

Answer 3

well the problem occur because you haven't closed the connection to database. Remember you have to close the connection before you sent something to client like that:

try {
  await client.connect();
  const result = await client.query(query, values);
  await client.end();
  res.status(201).send({
    result,
  });
} catch (err) {
  return res.send({
    error: err.detail,
      message: "Can't create a new user, please check your info again!",
    });
  }

Answer 4

Pool approach is better practice but if someone want to connect with Client approach then this solution will work.

Code which will work with Client approach :

client.connect();

client.query(`select * from users where username = ($1)`, (err, result)=>{
  try{
          console.log("Test", result)
           res.send(result)
        } catch{
          console.log("err", err)
          res.status(500).json(err);
    
        }

    client.end();

})

Answer 5

I had the same problem, dont create the new Client outside the function.

- const client = new pg.Client(connection);
-
 function test() {
+    const client = new pg.Client(connection);
+
     client.connect(err => {
         if (err) {
             console.log(err);
             return;
         }
         client.query('select 123', [], (err, data) => {
             if (err) {
                 console.log(err);
             } else {
                 console.log('DATA:', data.rows[0]);
             }
             client.end();
         });
     });
 }