Node-mysql2: Too many connections with connection pool

[PolymathWhiz]

I'm getting too many connections error when I make exactly 30 insert queries on my node app.

I have a connect.js file like this:

async function connection() {
  try {
    const mysql = require('mysql2');

    const pool = mysql.createPool({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DATABASE,
      connectionLimit: 10,
      waitForConnections: true,
      queueLimit: 0
    });

    const promisePool = pool.promise();

    return promisePool;
  } catch (error) {
    return console.log(`Could not connect - ${error}`);
  }
}

module.exports = {
  connection
};

and another file that I import the connect.jslike this:

let connection = require('connect');

let db = await connection();

then sql queries with db.execute(); follows.

Please help. Thanks

[sidorares]

you are creating new pool each time instead of borrowing connection from the same pool. Change to something like this:

function createPool() {
  try {
    const mysql = require('mysql2');

    const pool = mysql.createPool({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DATABASE,
      connectionLimit: 10,
      waitForConnections: true,
      queueLimit: 0
    });

    const promisePool = pool.promise();

    return promisePool;
  } catch (error) {
    return console.log(`Could not connect - ${error}`);
  }
}

const pool = createPool();

module.exports = {
  connection: async () => pool.getConnection()
  execute: (...params) => pool.execute(...params)
};

const db = require('./db');
const conn = await db.connection();
const results = conn.execute('select 1+1');
conn.release();

// or
const results = db.execute('select 1 + 1');

[PolymathWhiz]

Thanks so much! It works with the second result = db.execute('select 1 + 1');. Nice work!

[rildomar]

module.exports = { connection: async () => pool.getConnection() execute: (...params) => pool.execute(...params) }; doesnt work for me..

if i export only pool const pool = createPool();, works fine.. however, will I have to use this on every page that I will use connection to?

[PolymathWhiz]

function connection() {
  try {
    const mysql = require('mysql2');

    const pool = mysql.createPool({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DATABASE,
      timezone: 'utc',
      connectionLimit: 10,
      waitForConnections: true,
      queueLimit: 0
    });

    const promisePool = pool.promise();

    return promisePool;
  } catch (error) {
    return console.log(`Could not connect - ${error}`);
  }
}

const pool = connection();

module.exports = {
  connection: async () => pool.getConnection(),
  execute: (...params) => pool.execute(...params)
};

In another file

const db = require('path to file');

Then to run a query

const [query] = await db.execute(/*query here*/);

[domneedham]

How would I go about doing something like the following ?

const [users] = await db.execute("SELECT * FROM users where full_name LIKE %?%", [req.params.id]);

As it just gives me a syntax error.

[PolymathWhiz]

@domneedham

const id = "%" + req.params.id +"%"; // you can change this to backticks style concat

const [users] = await db.execute("SELECT * FROM users where full_name LIKE ?", [id]);

[Kennytian]

SET GLOBAL wait_timeout = 60;

run this command in mysql client command terminal, the timeout unit is seconds

[craigcosmo]

SET GLOBAL wait_timeout = 60;

run this command in mysql client command terminal, the timeout unit is seconds

what does this do ?

[craigcosmo]

function createPool() {
  try {
    const mysql = require('mysql2');

    const pool = mysql.createPool({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DATABASE,
      connectionLimit: 10,
      waitForConnections: true,
      queueLimit: 0
    });

    const promisePool = pool.promise();

    return promisePool;
  } catch (error) {
    return console.log(`Could not connect - ${error}`);
  }
}

const pool = createPool();

module.exports = {
  connection: async () => pool.getConnection()
  execute: (...params) => pool.execute(...params)
};

what supposed to be "./db"?