@cimchd You should indeed refactor it so that the code is using a single instance of the Prisma client. Every new call to new PrismaClient() will spin up a new connection pool.

@macrozone You can try the following code to make sure next js hot reloading doesn’t create a new prisma instance every hot reload:

import { PrismaClient } from "@prisma/client"

let prisma

if (process.env.NODE_ENV === "production") {
  prisma = new PrismaClient()
} else {
  if (!global.prisma) {
    global.prisma = new PrismaClient()
  }

  prisma = global.prisma
}

export default prisma

This snippet was originally posted here: https://github.com/prisma/prisma-client-js/issues/228#issuecomment-618433162

For those having this issue, I solved it by creating a singleton:

import { PrismaClient } from '@prisma/client'

class DBClient {
  public prisma: PrismaClient
  private static instance: DBClient
  private constructor() {
    this.prisma = new PrismaClient()
  }

  public static getInstance = () => {
    if (!DBClient.instance) {
      DBClient.instance = new DBClient()
    }
    return DBClient.instance
  }
}

export default DBClient

Then wherever I used the client I did:

import DBClient from '../../database/client'
const prisma = DBClient.getInstance().prisma

You can then use it like normal.

@chynamyerz I looked at your code and you are creating a new PrismaClient instance per request. That will create a new connection pool per request so it will exhaust the database connection.

These lines of code are creating new client instance per request:

const server = new GraphQLServer({ 
  context: ({ request }) => ({
    prisma: new PrismaClient(),
    request
  }),
  resolvers,
  typeDefs: __dirname + "/schema.graphql",
})

Instead, please invoke the client construct once and reuse the client instance like so:

const prisma = new PrismaClient()

const server = new GraphQLServer({
  context: ({ request }) => ({
    prisma
    request,
  }),
  resolvers,
  typeDefs: __dirname + "/schema.graphql",
});

Anyone else subscribed to this issue, please make sure that your code is not creating a Prisma client instance per request.

import { PrismaClient } from "@prisma/client";

declare global {
    var prisma: PrismaClient;
}

let prisma: PrismaClient;

if (process.env.NODE_ENV === "production") {
    prisma = new PrismaClient();
} else {
    if (!global.prisma) {
        global.prisma = new PrismaClient();
    }

    prisma = global.prisma;
}

export default prisma;

A riff on some of the type safety declarations. https://stackoverflow.com/questions/68481686/type-typeof-globalthis-has-no-index-signature

This is a solution I came across, I don’t see it posted here but hopefully it helps.

import { PrismaClient } from '@prisma/client'

const prismaClientPropertyName = `__prevent-name-collision__prisma`
type GlobalThisWithPrismaClient = typeof globalThis & {
  [prismaClientPropertyName]: PrismaClient
}

const getPrismaClient = () => {
  if (process.env.NODE_ENV === `production`) {
    return new PrismaClient()
  } else {
    const newGlobalThis = globalThis as GlobalThisWithPrismaClient
    if (!newGlobalThis[prismaClientPropertyName]) {
      newGlobalThis[prismaClientPropertyName] = new PrismaClient()
    }
    return newGlobalThis[prismaClientPropertyName]
  }
}

const prisma = getPrismaClient()

export default prisma

@rajuashok this should solve your issue of not being able to get type-completion with the above approach.

@pantharshit00 we use a single instance (we have export const prisma = new PrismaClient() in its own file which we import everywhere)

my guess is, that the hot-reloading / rebuilding of nextjs (or webpack in general) might rebuild and re-initialize this file. Is it possible to kindof destroy the instance (to free the connection pool)? then we could use maybe some re-build hook of webpack to reinitialize

i updated the issue with the solution that solved the problem in my case. If you still have this problem in a different case, I suggest to open a new issue instead of commenting here.

I dont think it would be wise since we extrapolate that one might be able to have multiple databases with different clients in the same project. Though it could use the same engine process under it.

@chynamyerz I looked at your code and you are creating a new PrismaClient instance per request. That will create a new connection pool per request so it will exhaust the database connection.

These lines of code are creating new client instance per request:

const server = new GraphQLServer({ 
  context: ({ request }) => ({
    prisma: new PrismaClient(),
    request
  }),
  resolvers,
  typeDefs: __dirname + "/schema.graphql",
})

Instead, please invoke the client construct once and reuse the client instance like so:

const prisma = new PrismaClient()

const server = new GraphQLServer({
  context: ({ request }) => ({
    prisma
    request,
  }),
  resolvers,
  typeDefs: __dirname + "/schema.graphql",
});

Anyone else subscribed to this issue, please make sure that your code is not creating a Prisma client instance per request.

@pantharshit00 thank you, updated the code and it is now working just fine.

@yassinebridi for nestjs you should be sorted if you write your prisma service as below:

import { Injectable, OnModuleInit, OnModuleDestroy } from ‘@nestjs/common’; import { PrismaClient } from ‘@prisma/client’;

@Injectable() export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy { constructor() { super(); } async onModuleInit() { await this.$connect(); }

async onModuleDestroy() { await this.$disconnect(); } }

import { PrismaClient } from "@prisma/client";

declare global {
    var prisma: PrismaClient;
}

let prisma: PrismaClient;

if (process.env.NODE_ENV === "production") {
    prisma = new PrismaClient();
} else {
    if (!global.prisma) {
        global.prisma = new PrismaClient();
    }

    prisma = global.prisma;
}

export default prisma;

A riff on some of the type safety declarations. https://stackoverflow.com/questions/68481686/type-typeof-globalthis-has-no-index-signature

This worked beautifully, except that it has as side effect that if you forget to make your import of prisma, VS Code won’t trigger any linter error of missing import during development.

For example, if a controller retrieves prisma.user info and misses the import it will still work like this:

// import prisma from '@prisma'  <- If we comment this, it will still work during development
const users = await prisma.users.findMany()
return users

But during build time, it will trigger the console error: ReferenceError: prisma is not defined

I’ve found a solution, we just need to use another name for the PrismaClient that we store in the global scope. That way, VS Code won’t detect that prisma is defined and we can avoid the “too many clients error”:

import { PrismaClient } from '@prisma/client'

declare global {
  // eslint-disable-next-line no-var
  var globalPrisma: PrismaClient  // <- Name it whatever you want, except "prisma".
}

let prisma: PrismaClient

if (process.env.NODE_ENV === 'production') {
  prisma = new PrismaClient()
} else {
  if (!global.globalPrisma) {
    global.globalPrisma = new PrismaClient()
  }

  prisma = global.globalPrisma
}

export default prisma

Hope it helps anyone 👍

In my integration tests, I used the parameter connection_limit=0 and it stopped the error “too many clients already”. Setting this to 0 turns off connection limits

it worked for me also, but I didn’t understand why. Can you explain me? Makes no sense to me lol

@mattgabor This means you have exhausted the number of connections that your database can accept. You first have to grasp what this means. In postgres and other traditional DBMS, each connection takes either a thread or a process thus consuming quite a bit of memory. This means the database system/VM has a maximum number of concurrent connections it can hold and its materialized by a setting (max_connections in postgres). Each instance of your service will open a few connections to the database to improve concurrency thus throughput (otherwise you would only be able to do one DB request at a time, even if you server had hundreds of concurrent incoming requests).

Thus in the following cases, it is possible to “exhaust” the database connections (this is easier to do with postgres since the typical number of connection is around 100):

1. Too many concurrent instances of the service running
2. Each instance holding too many connections in their pool (configured in prisma with the connection params or defaults to NUMBER CORES + 1)
3. Prisma pool still being buggy since the connection reaper is not done properly (I reported the problem a long time ago) so the engine keeps more connections then necessary

Those issues are particularly acute when doing rolling updates in production as the number of instances is 2x for a short period of time. My suggestion is usually to modify your database setting to increase the max number of connections (check current level with SHOW max_connections; in postgres) to 2x your normal level (so if you have 100, you set it to 200) so that the rolling update can work. The alternative is to use a connection pooler like pgbouncer for postgres, but that comes with a lot of caveats in prisma (it is not super well supported from my experience).

Hope this helps

Yes, its also solved for me. Still its worth get mentioned somewhere in the docs, as this is probably a common problem on some dev environement involving hot reloads

Can you create a minimal reproduction project @chynamyerz? For now we are still waiting on that before we can investigate and understand.

Hi @janpio

Yeah sure.

I tried to be as descriptive as possible but if I am clear as mud on the README please don’t hesitate to contact me, will try to respond as quickly as possible.

Here is the link to the repo: https://github.com/chynamyerz/prisma2-too-many-clients-error.git

Has anyone encountered an issue when Next.js build some number of static pages during the build, which require DB calls, and due to (assumed) high parallelization of these static page builds, there are multiple DB connections spawned simultaneously, tightly correlated with the number of pages being built.

The solution for hot reload does not seem to help in such case, as we always had it in our codebase.

Basically if we pre-build say 10 pages into static, through the build process the nextjs builder opens 10 connections to DB. The bigger problem is that these connections seem to stale, and it’s unclear where to edit the code to force terminate them.

A combination of previous responses that worked for me, keeping type safety:

import { PrismaClient } from "@prisma/client";

declare global {
  namespace NodeJS {
    interface Global {
      prisma: PrismaClient;
    }
  }
}

let prisma: PrismaClient;

if (process.env.NODE_ENV === "production") {
  prisma = new PrismaClient();
} else {
  if (!global.prisma) {
    global.prisma = new PrismaClient();
  }

  prisma = global.prisma;
}

export default prisma;

Then just import it with:

import prisma from "../lib/prisma";

Understandable, I figured it would be better to open a new issue so my previous comment should be disregarded.

@pantharshit00 I’m using NestJS with the official example provided here: https://www.prisma.io/nestjs I get the same problem. I wonder if there is a trick to debug this, to see how many connection pools are being created.

I’m also having this problem using Next with Prisma/Nexus in API Routes, using Posgres. I wasn’t able to solve it with previous solutions.

Did the Singleton suggestion from above, but still receive that error on localhost for next.js and postgres.

@pantharshit00 yes I’m using prisma with @nexus/schema, nextjs and graphql serverless api routes deployed on Vercel. Additionally we have a postgres db hosted on AWS RDS.

import { PrismaClient } from '@prisma/client'

let prisma: PrismaClient

if (process.env.NODE_ENV === 'production') {
  if (!prisma) {
    prisma = new PrismaClient()
  }
} else {
  // @ts-ignore
  if (!global.prisma) {
    // @ts-ignore
    global.prisma = new PrismaClient()
  }

  // @ts-ignore
  prisma = global.prisma
}

export { prisma }

We have the same problem in a similiar environment (prisma + nexus). We already increased the max database connections to 200 and tested MariaDB and Postgres with the same problems. It seems that the error occurs randomly. Sometimes directly on start, sometimes after several hours or even days… We also connect to the database directly with a database management tool (HeidiSQL or DBeaver), if this may help…

Here the error message from prisma: And here from the postgres database logs:

@janpio i think i can do that