How to Fix 'Too Many Database Connections Opened' in Prisma with Next.js Hot Reload

When developing a Next.js application with Prisma as your ORM, you might encounter an issue where the application throws the error, “Too many database connections opened: FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute.” This error usually arises during development, especially when Next.js is in watch mode and the application is restarted frequently. Each restart can create a new database connection while leaving the previous one in memory, which eventually leads to the exhaustion of available connection slots.

Thank me by sharing on Twitter 🙏

This post will explain the root cause of the error and how to resolve it by implementing a Prisma client singleton to ensure only one instance of the Prisma client is used throughout the application’s lifecycle in development mode.

Error Description

The error message you’ll typically see in your logs looks like this:

Plaintext

Too many database connections opened: FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute

This happens because Prisma opens a new connection to your database each time your Next.js app restarts in development. Without proper handling, old connections are left open, eventually causing PostgreSQL to run out of available connection slots.

Cause of the Error

The root cause of this issue lies in how Next.js handles hot reloading in development mode. When the app watches for file changes and restarts, it may recreate new database connections without properly closing the old ones. Over time, this exhausts the available connections in PostgreSQL, leading to the error.

The Nvidia Way: Jensen Huang and the Making of a Tech Giant

(40)

$26.16 (as of December 21, 2024 19:39 GMT +00:00 - )

etguuds USB to USB C Cable 3ft, 2-Pack USB A to Type C Charger Cord Fast Charging for Samsung Galaxy A15 A25 A35 A55 A54, S24 S23 S22 S21 S20 S10 S10E, Note 20 10, Moto G, for iPhone 16 15, Gray

(68048)

$6.99 (as of December 21, 2024 08:38 GMT +00:00 - )

USB Type-C to A Cable 5pack 6ft Braided Fast Charging 3A Quick Charger Cord, 6 Foot Compatible iPhone 16/15,Samsung Galaxy S10 S9 S8 Plus, Note 10 9 8, LG V50 V40 G8 G7(Grey)

(58400)

$8.54 (as of December 21, 2024 08:38 GMT +00:00 - )

In production, this isn’t typically a problem, as the app starts only once per deployment. However, in development, where restarts are frequent, it’s important to manage your database connections properly.

Solution

To resolve this error, the key is to ensure that Prisma maintains only one instance of the PrismaClient during development. This can be done by using a singleton pattern that stores the client in the globalThis object. Here’s how to implement this:

Step 1: Create the Prisma Singleton

TypeScript

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

const prismaClientSingleton = () => {
  return new PrismaClient();
};

// Ensure the global object is extended to store the Prisma client
declare const globalThis: {
  prismaGlobal: ReturnType<typeof prismaClientSingleton>;
} & typeof global;

// Use the existing Prisma client if it exists, or create a new one
export const prisma = globalThis.prismaGlobal ?? prismaClientSingleton();

if (process.env.NODE_ENV !== 'production') {
  // Store the Prisma client in globalThis to reuse in development
  globalThis.prismaGlobal = prisma;
}

Step 2: Explanation of the Code

Singleton Design: The prismaClientSingleton function creates an instance of PrismaClient. We store this instance in the globalThis object, so it can be reused between hot reloads.
Global Object: By extending the global object, we ensure that Prisma’s client persists across module reloads, preventing the creation of multiple connections.
Production vs Development: The prismaGlobal object is only used in development (NODE_ENV !== 'production'). In production, a new instance of PrismaClient is created per deployment, which is fine in serverless environments like Vercel.

Step 3: Integrate the Singleton

You’ll want to use the singleton prisma instance in your application wherever you perform database queries. For example, in an API route:

TypeScript

import { prisma } from '@/lib/prisma';

export default async function handler(req, res) {
  const users = await prisma.user.findMany();
  res.json(users);
}

Verification

To verify that the solution works, you should no longer experience the error after making multiple changes to your app and triggering hot reloads. The app will continue restarting without exhausting the database connection limit.

Here’s how to confirm that the error is resolved:

Make several changes to your code to trigger multiple restarts in development mode.
Ensure that the error no longer appears in the logs.
The app should continue working without hitting the connection limit, even after several restarts.

Conclusion

Managing database connections properly in development is crucial when working with Prisma and Next.js. By creating a Prisma client singleton and storing it in the global object, you can avoid the “Too many database connections” error and ensure that your app runs smoothly during development, even when it restarts frequently. This approach keeps your database connections efficient and prevents the exhaustion of available connection slots.

Tags/Keywords

Prisma
Next.js
PostgreSQL
Database Connections
Prisma Singleton
Hot Reloading

How to Fix ‘Too Many Database Connections Opened’ in Prisma with Next.js Hot Reload