Heroku + Postgres DB Error: Too many connections for role

How to solve the 'too many connections' DB error

If you're using Postgres on the free Heroku tier, you are limited to 20 concurrent connections. If you try to add a 21st connection, you'll get the following error:

FATAL: too many connections for role '[role name]'

If you go to your Heroku account and open the Postgresql datastore, you will see a utilization of 20/20 connections.

To solve this, firstly you need to check what exactly is using up all your connections. If your application is small, 20 concurrent connections isn't normal and suggests a deficiency somewhere in the code. In my case, using Prisma (my database ORM), I was importing and instantiating the PrismaClient() on each page load.

import { Prisma } from '@prisma/client'

export async function getStaticProps(context) {

prisma = new PrismaClient()

/* rest of code */

}

By importing and calling the PrismaClient on each page load, a new connection was opened each time. It didn't take long to reach the maximum of 20 concurrent connections.

Even though the database query is inside getStaticProps (and therefore should only run once, at build time), when using 'next dev', the website is recompiled each time. This therefore won't cause any issues in production, but it does mean we have to make some changes to the code to prevent it from calling the database on each page load when in the dev environment.

To solve the immediate issue, we can kill all the connections through the Heroku CLI. If you haven't already, download the Heroku CLI.

Then, login:

$ heroku login

Your browser will open and you can login using your usual credentials. Then you'll be taken back to the CLI. To kill all connections to the Postgres database, run the following command:

$ heroku pg:killall --app myappname

Replace 'myappname' with the name of the app your Postgres DB is connected to.

Now you have killed all the existing processes, you can continue development, but you'll have to keep on killing the processes each time you reach 20 concurrent connections.

A better solution is to identify the deficiency in the code which is causing all of these connections to be created.

The solution I found was to instantiate PrismaClient in an external file and export it.

import { PrismaClient } from '@prisma/client'

let prisma

prisma = new PrismaClient()

export default prisma

Now I just import it into the pages where I make database queries, and use the variable 'prisma' which was defined in the external file:

import prisma from '../lib/prisma'

export async function getStaticProps(context) {

const profiles = await prisma.profiles.findMany()

/* rest of code */

I hope this helps!

About

I post in English and Spanish mainly about React, NextJS, PHP and SEO.

About

I post in English and Spanish mainly about React, NextJS, PHP and SEO.