npm mysql2: Too many connections when using promises and a connection pool

Question

Using the mysql2/promise module, I'm creating a connection pool like this:

import mysql from 'mysql2/promise';

async function getConnection() {
    let pool = await mysql.createPool({
        connectionLimit: 10,
        host:'localhost',
        user: 'root',
        database: 'customers'
    });

    return pool.getConnection();
}

export  {
    getConnection
};

In another file, I am access this functionality like this:

import {getConnection} from '../config/database/mysql';

async function getCustomerAddresses(id){
    let conn = await getConnection();

    let [rows, fields] = await conn.execute(
        `SELECT *
        FROM addresses WHERE customer = ?`, [id]);

    conn.release();

    return rows;
}

After calling the function a couple of times, I'm getting the following error:

Too many connections

What am I doing wrong? And is there a more elegant way so I don't have to include the getConnection() call in every function?

Answer 1

Based on my experience, I believe that your problems is this code

conn.release();

In my case, I changed it to

conn.destroy();

I just read the documentation and I think the difference is explained in the documentation by this:

Connections are lazily created by the pool. If you configure the pool to allow up to 100 connections, but only ever use 5 simultaneously, only 5 connections will be made. Connections are also cycled round-robin style, with connections being taken from the top of the pool and returning to the bottom.

Whereas destroy is this

This will cause an immediate termination of the underlying socket. Additionally destroy() guarantees that no more events or callbacks will be triggered for the connection.

Hope this helps.

Answer 2

You are creating new pool each time you want new connection, and though pool is eventually is GCed connection is still open unless you manually call .close() on it. What you should really do is to have one single pool and it will manage connections lifetime for you:

import mysql from 'mysql2/promise';

const pool = mysql.createPool(params);  
const getConnection = () => pool.getConnection();
export { getConnection } 

Answer 3

In my opinion, there's an even more elegant way by using pool.promise().

import mysql from 'mysql2';

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    database: 'users',
});

const asyncPool = pool.promise();

export default asyncPool;

That async pool can then be queried using async/await like this:

const [row] = await pool.query(`SELECT * FROM users WHERE uname = ?;`, ['john']);