I have a file which creates the database connection pool called pool.js like this
import mysql from 'mysql2';
import dotenv from 'dotenv';
dotenv.config();
const pool = mysql.createPool({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
port: process.env.DB_PORT,
connectionLimit: 5
}).promise();
export default pool;
A Base model class which handles query execution like this
import pool from './pool.js'
class BaseModel{
async runSQL(sql){
const [rows] = await pool.query(sql, this.params)
return rows
}
}
And a User model class which calls the base model database method like this
import BaseModel from "../helpers/BaseModel.js"
class User extends BaseModel{
async create(props){
const response = await this.runSQL(sql)
return response
}
}
export default new User
Does this implementation satisfy the database connection pooling usage?
Yes, you are using connection pooling in the right way.
If you need more control on connection pool, you can play with a couple of more params like maxIdle
or idleTimeout
.
connectionLimit: 10,
maxIdle: 10, // max idle connections, the default value is the same as connectionLimit
idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
You can find more infos here.
Just remember that (from the linked docs)
The pool does not create all connections upfront but creates them on demand until the connection limit is reached.