1

config.js

const mysql = require('mysql2');

const config = {
    host: 'localhost',
    port: '3306',
    user: 'root',
    password: 'root',
    database: 'krometal',
    charset: "utf8mb4_bin",
    multipleStatements: true
};
const connection = mysql.createPool(config);

connection.getConnection(function (err, connection) {
    //connecting to database
    if (err) {
        logger.log('error', err);
        console.log("MYSQL CONNECT ERROR: " + err);
    } else {
        logger.log('info', "MYSQL CONNECTED SUCCESSFULLY.");
        console.log("MYSQL CONNECTED SUCCESSFULLY.");
    }
});

module.exports = {
    connection
}

login.js

const loginUser = async (req, callback) => {
  connection.query(sql, async function (err, rows) => {
      // logic
      callback(result, 401, connection);
  })
}

route.js

users.post('/users/login', async (req, res) => {
    await loginUser(req, async (response, code, connection) => {
        await connection.end();
        res.status(code).send(response);
    });
});

The problem is the first time I try login worked fine, but if I try the same login API again throw the error Error: Pool is closed.

Should I use the

connection.end()

method every time I open connection or mysql2 automatically end connection?

Hussein Mohamed
  • 157
  • 9

1 Answers1

0

Don't run

await connection.end();

After running this function you need to create a new connection using

connection.getConnection();
Simon
  • 1
  • 1
  • 5
  • I removed ```await connection.end();``` and everything worked fine, but on large scale, the connection will reach its limit. – Hussein Mohamed Dec 05 '21 at 10:45
  • and where exactly should i put ```connection.getConnection();``` and why? i think connection should open once I hit any api – Hussein Mohamed Dec 05 '21 at 10:46
  • @HusseinMohamed i think opening a new connection every time a request comes in is not a great idea. Performance would be a big issue. – Simon Dec 05 '21 at 11:13
  • @HusseinMohamed The documentation says, that you can use `pool.query` without `pool.getConnection()`. [https://github.com/mysqljs/mysql#pooling-connections](https://github.com/mysqljs/mysql#pooling-connections) – Simon Dec 05 '21 at 11:16
  • I am using https://github.com/sidorares/node-mysql2 not https://github.com/mysqljs/mysql – Hussein Mohamed Dec 05 '21 at 12:28
  • It should work anyway, without calling getConnection. Just return the created pool from the config.js file. – Simon Dec 05 '21 at 16:13