I am having trouble with pooling mysql connections with nodeJS. As I understand it, when creating a pool, I should be able to use pool.query() to:
- Get a connection
- Run my query
- Release the connection
However, the issue I am running into is the connections remain open and "sleeps" until the server closes the connection itself (60 seconds). Is there something I am doing wrong here? For context, I am connecting to Heroku clearDB and am viewing the connection on its dashboard.
Example: Upon login, I make a query to check login credentials to authenticate, another query to fetch one set of data, and another query to fetch another set. After logging in, the server is left with 2 connection in "sleep" mode. They do not disconnect until 60 seconds expire.
Here is my db.js file:
const mysql = require("mysql");
const dbConfig = require("./db.config.js");
const connection = mysql.createPool({
host: dbConfig.HOST,
user: dbConfig.USER,
password: dbConfig.PASSWORD,
database: dbConfig.DB,
port: dbConfig.PORT,
multipleStatements: true,
connectionLimit: 10
})
module.exports = connection;
Here is how I am making queries:
const sql = require("./db.js"); //Our db connection
---OMITTED---
return new Promise((resolve, reject) => {
const select_user = "SELECT user_id, first_name, last_name, username, password, is_admin, is_active FROM users WHERE username = ? LIMIT 1"
const params = [username]
const user_pass = password
sql.query(select_user, params, (err, res) => {
if (res) {
if (res.length) {
const user = res[0]
const hash = user.password
const is_active = user.is_active
if (is_active) {
bcrypt.compare(user_pass, hash).then(res => {
if (res) {
resolve({ result: true, info: { fname: user.first_name, lname: user.last_name, username: user.username, is_admin: user.is_admin, user_id: user.user_id } })
} else {
reject({ result: false, msg: "wrong_creds" })
}
}).catch(err => {
reject({ result: false, msg: err })
})
} else {
reject({ result: false, msg: "inactive" })
}
} else {
reject({ result: false, msg: "user_not_exist" })
}
} else {
console.log("MYSQL QUERY, COULD NOT SELECT USER FOR AUTHENTICATION")
reject({ result: false, msg: err })
}
})
})
I'm under the impression that pool.query() will close the connection for me after each query. Is this not the case? Am I missing something once the query has been completed?
I have also tried the manual way:
const select_string = "SELECT * FROM user_backlogs WHERE movie_id = ? AND user_id = ?"
const params = [movie_id, user_id]
sql.getConnection((err, connection) => {
if (err) reject(err)
else {
connection.query(select_string, params, (err, res) => {
connection.release()
if (err) reject(err)
else {
if (res.length) {
resolve([movie_id, "exists", res[0]["added_on"]])
} else {
resolve([movie_id, "not_exists"])
}
}
})
}
})
But still, the connections remain up until the server kicks them off. Any help is greatly appreciated.
