Too many instances of PrismaClient

[heymartinadams]

Bug description

I’m getting the classic FATAL: remaining connection slots are reserved for non-replication superuser connections error for my Postgres DB because there are too many PrismaClient instances going on.

Problem is, I’m using REST API routes (using NextJS) and not GraphQL, so each time there’s an incoming request to a route (and there are many), I’m instantiating a PrismaClient, which results in the aforementioned error.

Is there a way to avoid this?

Environment & setup

• OS: macOS
• Database: PostgreSQL
• Node.js version: 12.6.0
• Prisma version:

2.11.0

[bbbryan14]

The solution that I've seen is create a single instance during app initialization that is used as a dependency for modules to consume. So if you're using NestJS, I would probably just create a module that's initializes prisma client, and then have that being re-used by other services.

[heymartinadams]

@bbbryan14 how, specifically, would it be done with NextJS (not NestJS) REST APIs? As far as I know, each API is called individually at various times. Even if I separate it into modules, won’t a new Prisma Client be instantiated each time an API is called? Like this?

module

// Prisma
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

export default prisma

API 1

e.g. /pages/api/payments/initiate

import prisma from 'lib/utils/prisma'

const ApiPaymentsInitiate = async (req, res) => {
  ...
}

export default ApiPaymentsInitiate

API 2

e.g. /pages/api/payments/confirm

import prisma from 'lib/utils/prisma'

const ApiPaymentsConfirm = async (req, res) => {
  ...
}

export default ApiPaymentsConfirm

[pantharshit00]

@heymartinadams try this: https://github.com/prisma/prisma-client-js/issues/228#issuecomment-618433162

[heymartinadams]

Gave this a try, @pantharshit00, and it works well! With https://github.com/prisma/prisma-client-js/issues/228#issuecomment-618433162 global.prisma is called upon repeated calls instead of a new instance of the Prisma Client. Wish that’d been mentioned more clearly in the docs, though — could have saved me a year of dealing with these error messages.

[vladandrei0]

@heymartinadams sorry to bother u but how did u actually made it work? If I create the module in /public/lib... and import and use it inside getServerSideProps I get:

error - ./node_modules/@prisma/client/runtime/index.js:26261:39
Module not found: Can't resolve 'async_hooks'

🙈

[Sytten]

related to #4393 maybe?

[heymartinadams]

Works like a charm, @vladandrei0.

module

import { PrismaClient } from '@prisma/client'

// See here: https://github.com/prisma/prisma-client-js/issues/228#issuecomment-618433162
let prisma

if (process.env.NODE_ENV === 'production') {
	prisma = new PrismaClient()
}
// `stg` or `dev`
else {
	if (!global.prisma) {
		global.prisma = new PrismaClient()
	}

	prisma = global.prisma
}

export default prisma

API

e.g. /api/payments/create

import prisma from 'lib/utils/prisma'

const ApiPaymentsCreate = async (req, res) => {
  ...
  return await prisma.payment.create({...})
}

export default ApiPaymentsCreate

However, a drawback is that referencing Prisma Client indirectly stops the auto-completion (at least in VS Code). A workaround is to temporarily enable while creating the API:

// import prisma from 'lib/utils/prisma'
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

and then to disable it once done:

import prisma from 'lib/utils/prisma'

[vladandrei0]

yes, all in order now. thanks. I don't even know what I did wrong the first time...

[2color]

Hey @heymartinadams,

Just to verify that I understand the problem correctly – is this only happening when using the next dev development server?

[heymartinadams]

Correct, @2color. I only use next, not sure if the same as next dev. ☺️

[matthewmueller]

Hey Martin, we've documented the next dev workflow here: https://www.prisma.io/docs/support/help-articles/nextjs-prisma-client-dev-practices.

[heymartinadams]

@matthewmueller can we somehow maintain autocompletion (e.g. in VSCode) even if we import from a global variable?

Currently I need to:

// import prisma from 'lib/api/prisma'
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

While working on a file, then remove that before testing.

import prisma from 'lib/api/prisma'

Should I open an issue in prisma/language-tools instead?

[hughcrt]

I get this error when using the code from https://www.prisma.io/docs/support/help-articles/nextjs-prisma-client-dev-practices.
TypeError: Duplicate declaration "prisma"

Which I fixed by replacing let prisma: PrismaClient by let prismaClient: PrismaClient.

I'm not an expert in Typescript but you might need to modify your article

[OliverGilan]

Works like a charm, @vladandrei0.

module

import { PrismaClient } from '@prisma/client'

// See here: https://github.com/prisma/prisma-client-js/issues/228#issuecomment-618433162
let prisma

if (process.env.NODE_ENV === 'production') {
	prisma = new PrismaClient()
}
// `stg` or `dev`
else {
	if (!global.prisma) {
		global.prisma = new PrismaClient()
	}

	prisma = global.prisma
}

export default prisma

API

e.g. /api/payments/create

import prisma from 'lib/utils/prisma'

const ApiPaymentsCreate = async (req, res) => {
  ...
  return await prisma.payment.create({...})
}

export default ApiPaymentsCreate

However, a drawback is that referencing Prisma Client indirectly stops the auto-completion (at least in VS Code). A workaround is to temporarily enable while creating the API:

// import prisma from 'lib/utils/prisma'
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

and then to disable it once done:

import prisma from 'lib/utils/prisma'

I understand how and why this works during development but why is the issue not present for production? Why is it okay to create a new prisma client each time while running in production?

Edit: I read some more and realize now that in a production setting the above solution gets cached the first time the module is loaded so you don't run into the issue. Only during development does NextJS clear the cache for hot reloading purposes. Leaving comment in case others have same confusion.

[sergioengineer]

For those having problems with the autocompletion while using plain JS: just let VSCode know the type of the variable through JSDoc.

/** @type {PrismaClient} */
let prisma

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

prisma.$connect()
export default prisma

[janpio]

Where are you using that plain JS example @sergioengineer? We should probably add it to our documentation equivalent to the Next.js example.

[sergioengineer]

I was specifically answering this comment complaining that he was loosing auto-completion by caching Prisma's instance in a variable.

The solution is to let VS Code know the type of the variable as soon as it is instantiated inside the lib file where people are pasting the workaround.

/** @type {PrismaClient} */
let prisma

This is also a solution to this issue.

[janpio]

Ah ok, so it also applies for TypeScript - then this is probably something we just want to add to the example in the docs for everyone? Should improve the life of many people then.

[robhrt7]

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.

[sergioengineer]

@therobhrt - I've never had this kind of problem before. Do you perhaps use getInitialProps somewhere in your App or Document file?
Do you have a smaller repository, which you could share with us, where we could reproduce the same issue?

Seems like a fun issue to solve.

[robhrt7]

@sergioengineer we don't have a separate Document nor App files, we have a couple of pages in a dynamic Next.js application (with BE runtime) + we pre-build some pages to static on build for caching needs. When I build the project locally on M1 machine, during build I get 15 DB connections open to generate 16 static pages. I'm not sure if the relation comes from the number of pages, or number of cores, as while googling, I found a comment that Next.js build parallize the static page build based on number of cores on CPU.

The problem is that all these 15 connections do not close over time, and after few builds we reach DB connections limit. We also see this on production builds, but seems due to build machines having less cores, it's much less of the connections left open there.

I am using Blitz.js on top of Next.js, it might be something related to that, but as far as I'm aware the Next.js build is not modified by Blitz. If build process spawns multiple build threads, it might be that each process has it's own global, and thus the hot reload fix does not really help, as these are separate build contexts, I'm not entirely sure if this is how it works, as it's hard to debug and requires digging into source code of Next.js build (could not find any docs on this).

Fun times trying to do SSG app with Prisma and Postresql....

[janpio]

A reproduction of what you are seeing and observing always helps. Feel free to create a bug issue for that then or a discussion, so we can take a look and potentially help out.