Error Too many connections with Drizzle + MySQL2

I have my DB setup as follow:

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import * as schema from "./schema";

const poolConnection = mysql.createPool({
    host: "localhost",
    user: "root",
    database: "nextjs",
});

const db = drizzle(poolConnection);

export { db, schema };

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import * as schema from "./schema";

const poolConnection = mysql.createPool({
    host: "localhost",
    user: "root",
    database: "nextjs",
});

const db = drizzle(poolConnection);

export { db, schema };

I keep getting the error Too many connections after refreshing the page several times. How can I fix it?

10 Replies

nqhtrung•9mo ago

it’s a problem with hot reload in NextJS. Try using singleton for your connection pool.

B33fb0n3•9mo ago

do you have any examples?

nqhtrung•9mo ago

Here is what I meant, you check if there is any existing drizzle instance before creating one.

B33fb0n3•9mo ago

Where does „globalThis“ comes from and would that also work in edge functions? Serverless functions?

Kairu•9mo ago

this is my way for avoiding connection exhaustion in HMR

type PostgresDB = ReturnType<typeof postgres> | undefined;
let postgresDb: PostgresDB = undefined;

if (!postgresDb) {
  postgresDb = postgres(databaseUrl);
}

export const db = drizzle(postgresDb, { schema });

type PostgresDB = ReturnType<typeof postgres> | undefined;
let postgresDb: PostgresDB = undefined;

if (!postgresDb) {
  postgresDb = postgres(databaseUrl);
}

export const db = drizzle(postgresDb, { schema });

afaik it wont work on serverless which is why serverless drivers and the drizzle http proxy exists, but i dont get enough traffic per second to exhaust connections

B33fb0n3•9mo ago

that's also a good way to prevent multiple sessions. I just set the limit to unlimited, because there is also not that much traffic and then close them automatically via this: https://stackoverflow.com/questions/12391174/how-to-close-idle-connections-in-postgresql-automatically/69528572#69528572

Stack Overflow

How to close idle connections in PostgreSQL automatically?

Some clients connect to our postgresql database but leave the connections opened. Is it possible to tell Postgresql to close those connection after a certain amount of inactivity ? TL;DR IF you're...

Kairu•9mo ago

i should probably check what mine is set to, presumably the default

B33fb0n3•9mo ago

but what's the default? xD

Kairu•9mo ago

0/disabled I believe haven’t encountered any errors so im probably fine for now

B33fb0n3•9mo ago

oh xD I think I end idling connections after like 15 minutes. I guess that's a good number. Or maybe even 5 minutes..

Gaming

Programming

Error Too many connections with Drizzle + MySQL2