Photon.js not usable with Heroku DB and ZEIT Now V2 due do DB connection limit

[nikolasburk]

Note: I'm crossposting a comment from GitHub issue #228 into its own issue so its actionable for engineering. The problem here is based on a stack that many people (especially people starting out) are likely to use:

• DB: Heroku
• Application server: ZEIT Now 2.0

Currently this stack seems to cause problems in that the connection limit on Heroku's DB is reached very quickly and the only way to resolve is by restarting the server altogether. Note that this might be related to #567 which didn't get resolved but the user switched from Heroku to Google Cloud SQL.

Below is the copy of @ScottAgirs's problem description from #228.

Problem

On some occasions (currently only tested in development (local) environment) Heroku database gets flooded with connections that don't close. On a hobby plan the limit is 20 connections which can be max out in just few interactions with the app and results in a fatal error.

The Error occurs (currently only this case is tested), when creating a new user or logging in.

The Error

Error querying the database: db error: FATAL: too many connections for role

The resolver resolver.js:

export const LoginMutation = extendType({
  type: 'Mutation',
  definition(t) {
    t.field('login', {
      type: 'AuthPayload',
      args: {
        email: stringArg({ nullable: false }),
        password: stringArg({ nullable: false }),
      },
      resolve: async (_parent, { email, password }, ctx) => {
        const currentUser = await ctx.photon.users.findOne({
          where: {
            email,
          },
        })

        ..

        const token = "string"

        ctx.response.cookie('token', token, {
          httpOnly: true,
          maxAge: 1000 * 60 * 60 * 24 * 365,
        })

        return { currentUser }
      },
    })
  },
})

Environment

• Prisma Framework (prisma2@2.0.0-preview015, binary version: 20b6dc13949cccccfef5be07c0be7a3d7c858abe) deployed to Zeit Now V2
• Postgres database deployed to Heroku
• Apollo Client 3.0 on the client

Reproduction

Currently have not tried producing more than case in which I get this happens, but the one that shows consistent

Also tried to just wait it out to see if it would close after a while, however, the connections remain active even after prolonged period of time (hours that is).

Solutions?

Currently I need restart the server to fix.
Perhaps there is a function that can explicitly close the connection or something that I've missed?

Fingers crossed for solutions/easy workarounds to this 🤞

[pantharshit00]

I can actually confirm this. Managed pool by a separate server is a good option here in my opinion.

[nikolasburk]

One potential workaround is to manually set the connection limit in your PostgreSQL connection string, e.g. to 1:

postgresql://user:password@host:5432/mydb?connection_limit=1

Right now, it is set to the default value which is calculated according to this formula: num_physical_cpus * 2 + 1. So if the machine that's running your application has 4 CPUs, its connection limit is 9.

UPDATE: I also just learned that apparently Lambda provides 2 CPUs by default, which means the connection limit in that case is 5 per running instance.

[matthewmueller]

Documentation for this issue is being tracked here: prisma/prisma#942