I’m currently experiencing some issues with my API. It works well for the first few requests, but after a while I end up getting a Too Many Connections Error causing my entire Database to crash as well. Only after stopping my project and doing npm run dev does it fix itself. For certain pages I fetch from multiple endpoints and on some pages I make post, put and delete requests numerous times. Though the one with the most requests in one go doesn’t give me this issue. I will provide the simplified versions underneath.

Connection

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: 100,
});

export default pool;

Endpoint Example

import pool from "@/app/libs/mysql";

export async function GET(request: Request) {
  const db = await pool.getConnection();
  try {
    const getQuery = `select * from roles`;
    const [roles] = await db.execute(getQuery);

    if (JSON.stringify(roles) == "[]") {
      return new Response(
        JSON.stringify({
          message: `No roles Found`,
          data: roles,
        }),
        {
          headers: {
            "Content-Type": "application/json",
          },
          status: 404,
        }
      );
    }

    return new Response(JSON.stringify(roles), {
      headers: {
        "Content-Type": "application/json",
      },
      status: 200,
    });
  } catch (err) {
    console.error("Something Went Wrong", err);

    return new Response(
      JSON.stringify({ message: "Something Went Wrong", error: err }),
      {
        headers: {
          "Content-Type": "application/json",
        },
        status: 500,
      }
    );
  } finally {
    db.release();
  }
}

It doesn’t happen all the time, but having it throw this error is an annoying thing to deal with. I am well aware that my connectionLimit is set to 100, but i figured it’d be enough since I’m the only user now and since I’m releasing them, I thought it’d be fine too. Perhaps I’m misunderstanding how it works, so any help is appreciated. If there’s a better way of returning my responses, I’d be eager to know as well.