Too many connections Nodejs + mysql2/promise

Question

I am working in a proyect with nodejs + mysql2 and some times I got the error Too many connections.

My connection:

import {createPool, Pool} from 'mysql2/promise';

export async function connect(): Promise < any > {
  const connection: Pool = await createPool({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: '',
    database: 'mendozarq',
    connectionLimit: 10
  });
  return connection;
}

Controller:

import { Response, Request } from 'express';
import { FieldPacket, Pool } from 'mysql2/promise';

import { connect } from './../../classes/database';
import { Personal } from './../../models/personal.interface';

export const getAllPersonal = async (req: Request, res: Response) => {
    try {
        const conn: Pool = await connect();

        const [personal]: [any[], FieldPacket[]] = await conn.query('SELECT * FROM personal ORDER BY creadoEn DESC');
        conn.end();

        return res.status(200).json(personal);

    } catch (error) {
        return res.status(400).json({
            error
        });
    }
}

So, my question is I have to use createConnection instead of createPool or I just have to create one instance of my conn.

How many times does you app call `connect()`? it should only call it once (it looks like you are calling it for every request), and be selecting a connection from the pool. — Get Off My Lawn, Jan 20 '21 at 16:49
I am calling connect() for every request, so I just have to call connect() one time for each controller or just one for every Controllers?. Please I would like to get some example. — Bladimir Medrano Vargas, Jan 20 '21 at 16:57

Get Off My Lawn · Accepted Answer · 2023-01-04T14:49:47.950

You should only be calling createPool once, then select from the pool every time thereafter.

So, it should look something like this. Create a constant that stores the pool. Then in the function if the pool already exists return the pool without calling createPool. If it doesn't exist, then you would call createPool and return the already created pool.

import {createPool, Pool} from 'mysql2/promise';

const globalPool: Pool | undefined = undefined;

export async function connect(): Promise <Pool> {

  // If the pool was already created, return it instead of creating a new one.
  if(typeof globalPool !== 'undefined') {
    return globalPool;
  }

  // If we have gotten this far, the pool doesn't exist, so lets create one.
  globalPool = await createPool({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: '',
    database: 'mendozarq',
    connectionLimit: 10
  });
  return globalPool;
}

Thanks for your explanation! – Bladimir Medrano Vargas Jan 20 '21 at 17:01 — Bladimir Medrano Vargas, Jan 20 '21 at 17:01

Too many connections Nodejs + mysql2/promise

1 Answers1