Theo's Typesafe Cult•15mo ago

Drizzle-ORM+PSQL: sorry, too many clients already

X tRPC failed on school.getAll: sorry, too many clients already I guess this is because the connections stay open? or because it opens a connection per request for some reason? This is my TRPC inner context:

const createInnerTRPCContext = (opts: CreateContextOptions) => {
  return {
    db,
  };
};

const createInnerTRPCContext = (opts: CreateContextOptions) => {
  return {
    db,
  };
};

(Default one provided by T3-Drizzle stack) Why is that happening?

7 Replies

zendev•15mo ago

Dealing with exactly the same issue right now, did some research and it looks like it has to do with the hot module reload (HMR) that you get in dev environment with Next.js. I believe you need to create a singleton to avoid opening a new db connection everytime an HMR refresh happens, but I'm not sure as to the best way to do this, or if there's an accepted best practice. Maybe someone can confirm/correct me here and provide an example solution?

West side ⁉OP•15mo ago

That's what I thought of, should be implemented by T3 by default

zendev•15mo ago

Agreed, let me know if you figure out a solution

Dor•13mo ago

Have you came up with something? I face the same issue (t3-drizzle) Really driving me crazy theowat

George•13mo ago

you can checkout this link for a potential fix: https://www.answeroverflow.com/m/1146224610002600067

Too many clients already (!?) - Drizzle Team

Hey guys, been using Drizzle for a few weeks now and I think since last week this issue has started (look at screenshot). I'm using the postgrespaackge to connect Drizzle to my PostgreSQL database that's deployed to Railway. Drizzle connection code 👇 : ```typescript import { env } from "@/env.mjs"; import { drizzle } from "drizzle-orm/postgre...

George•13mo ago

I used it to fix the same issue with some small modifications:

 import { drizzle } from "drizzle-orm/postgres-js";
import { type PostgresJsDatabase } from "drizzle-orm/postgres-js";
import postgres from "postgres";

import { env } from "~/env.mjs";

import * as schema from "./schema"; // where our db tables are defined

// Fix for "sorry, too many clients already" from:
// https://www.answeroverflow.com/m/1146224610002600067

declare global {
    // eslint-disable-next-line no-var -- only var works here
    var db: PostgresJsDatabase<typeof schema> | undefined;
}

let db: PostgresJsDatabase<typeof schema>;

if (env.NODE_ENV === "production") {
    const client = postgres(env.DATABASE_URL);

    db = drizzle(client, {
        schema,
    });
} else {
    if (!global.db) {
        const client = postgres(env.DATABASE_URL);

        global.db = drizzle(client, {
            schema,
            logger: {
                logQuery: (query) => {
                    // to remove quotes on query string, to make it more readable
                    console.log({ query: query.replace(/\"/g, "") });
                },
            },
        });
    }

    db = global.db;
}


type DbInstance = typeof db;

export { db };
export type { DbInstance };

 import { drizzle } from "drizzle-orm/postgres-js";
import { type PostgresJsDatabase } from "drizzle-orm/postgres-js";
import postgres from "postgres";

import { env } from "~/env.mjs";

import * as schema from "./schema"; // where our db tables are defined

// Fix for "sorry, too many clients already" from:
// https://www.answeroverflow.com/m/1146224610002600067

declare global {
    // eslint-disable-next-line no-var -- only var works here
    var db: PostgresJsDatabase<typeof schema> | undefined;
}

let db: PostgresJsDatabase<typeof schema>;

if (env.NODE_ENV === "production") {
    const client = postgres(env.DATABASE_URL);

    db = drizzle(client, {
        schema,
    });
} else {
    if (!global.db) {
        const client = postgres(env.DATABASE_URL);

        global.db = drizzle(client, {
            schema,
            logger: {
                logQuery: (query) => {
                    // to remove quotes on query string, to make it more readable
                    console.log({ query: query.replace(/\"/g, "") });
                },
            },
        });
    }

    db = global.db;
}


type DbInstance = typeof db;

export { db };
export type { DbInstance };

zendev•13mo ago

The solution George sent above looks good, you basically just create a singleton to use for dev instances and leave it as is for production instances

Gaming

Programming

Drizzle-ORM+PSQL: sorry, too many clients already