Managing DB connections for Prisma Client JS (e.g. in serverless environments)

[nikolasburk]

The query engine that's powering the Prisma Client JS API is maintaing a database connection pool. In serverless environments (or when running your application in containers, e.g. using Kubernetes), this connection pool might loose its effectiveness due to the infrastructure it's being deployed on. You can read more about this topic in the docs.

As of now, the recommended workaround is to use a tool like PgBouncer. We are further exploring some options, such as enabling a "DB proxy server" (e.g. using a specific generator that generates a Docker image to host Prisma's query engine) that manages the connection pool for you (similar to the the Prisma 1 architecture).

Also note that there some cloud offerings start to have solutions for connection pooling out-of-the-box, such as AWS Aurora Serverless (Data API).

[ScottAgirs]

Hey folks, here's my chime-in on this

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 🤞

[willmruzek]

Is there documentation yet on how to connect through the AWS Aurora Serverless Data API?

If not, is there an easy way to use https://github.com/jeremydaly/serverless-mysql?

Thanks.

[brandonweiss]

Is it just me or is there some other bug with the connection pool (unrelated to serverless environments)? I seem to be hitting the same problem as @ScottAgirs. I’m using Heroku Postgres (Hobby, 20 connections) and running one instance of my application locally. Intermittently I’ll get a “too many connections” error and the only way to fix it is to kill the server and restart.

Is there some gotcha I missed? It feels like I shouldn’t be hitting the limit 🤔

[timsuchanek]

This is not really a bug, but we just open a bunch of connections in a connection pool.
You can limit them with appending ?connection_limit=1 to your connection string.
Here you can read more about it: https://www.prisma.io/docs/reference/database-connectors/postgresql#connection-url
and
https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-client/connection-management#connection-pool

[brandonweiss]

Huh 🤔 I’m on a MacBook Pro with 4 cores, so num_physical_cpus * 2 + 1 would be 9, which is lower than the number of connections on the Heroku database, yet I’m still getting “too many connections” errors.

I was trying to solve this problem a few days ago and stumbled upon an issue that mentioned the connection_limit param in the connection string, so I tried that and set it to 10, but I still got errors…

It definitely could be something I’m doing wrong on my end. The app is a Next.js app (a few days ago I ported it from Razzle to Next.js). The errors happened with both frameworks. In both cases I’m instantiating a new PrismaClient and then passing it into a graphQL server as “context” so I can make requests in resolvers using the same client. Both Razzle and Next.js have dev servers that detect when a change is made and reload all the app code so you don’t have to restart the server. Is there some gotcha I’m not seeing with my setup? And/or is it possible some state is being shared across instances in the Prisma client? 🤔

[martin-888]

I have same problem on heroku with postgress DB as @brandonweiss. It creates a new connection with every query/mutation, setting up ?connection_limit=5 solves nothing, it just ignores the number.

[nikolasburk]

Hey @brandonweiss, two questions:

I’m instantiating a new PrismaClient and then passing it into a graphQL server as “context” so I can make requests in resolvers using the same client

Are you instantiating a new PrismaClient per request or do you generally only have a single instance? (the latter is the recommended approach)

Also, are you calling disconnect in your code?

[Sytten]

@nikolasburk Not sure the recommended way is to call disconnect since that will kill the query engine process for each request. Generally creating a global PrismaClient with a connection_limit of 1 is sufficient, but you need to limit the number of parallel lambdas that can span (called reserved concurrency in AWS). Otherwise you have to use their new proxy or pgBouncer. That is not a problem related to prisma as much as a general serverless problem.

[nikolasburk]

@nikolasburk Not sure the recommended way is to call disconnect since that will kill the query engine process for each request. Generally creating a global PrismaClient with a connection_limit of 1 is sufficient, but you need to limit the number of parallel lambdas that can span (called reserved concurrency in AWS). Otherwise, you have to use their new proxy or pgBouncer. That is not a problem related to Prisma as much as a general serverless problem.

That all seems correct to me for serverless environments – but I believe in this case we're talking about a long-running Node.js process that's running locally on the user's machine so I believe calling disconnect() when the application terminates is a good practice. Furthermore, it's indeed recommended to only create a single PrisamClient instance rather than creating one for each request. Basically this:

Yes

const prisma = new PrismaClient()
const server = new GraphQLServer({
  schema,
  context: {
    prisma
  },
})

No

const server = new GraphQLServer({
  schema,
  context: {
    prisma: new PrismaClient()
  },
})

[brandonweiss]

@nikolasburk I’m instantiating it once and passing it into the graphQL server context similar to your “yes” example. And I’m not calling disconnect anywhere. I’ll post my exact code:

const prisma = new PrismaClient()

let apolloServer = new ApolloServer({
  context: async ({ req: { session } }) => {
    let userID = session.get("userID")

    return {
      userID,
      prisma,
    }
  },
})

Context is taking a function in order to have access to the request/session, but I’m not instantiating a new client per request—it should always be the same instance, right?

I assume the reason killing/restarting the server works is because Prisma then will call disconnect and flush the connections?

I could be completely misunderstanding how the client and connections works, but if instantiating a new client per-request is bad (because the connections hang around?) … then wouldn’t a dev server reloading essentially recreate that effect? When Next.js trashes the require cache and re-requires the graphQL server, it will create a new client. I assume over time connections must timeout and die, but if someone were to make a lot of changes/reloads really quickly, presumably this would use up all the connections?

If that’s the case, I would sort of expect this to be a bigger problem, but perhaps most people are developing against a local (or remote) database with a much higher connection limit?

[Sytten]

@brandonweiss Yes if not for the lifecycle handling done inside the client itself. The recommended way is to call disconnect to be sure, but the client catches some OS signals and kills the child process. See

prisma/src/packages/engine-core/src/NodeEngine.ts

Line 790 in 3cb0854

process.on('beforeExit', exitHandler())

[nikolasburk]

I assume the reason killing/restarting the server works is because Prisma then will call disconnect and flush the connections?
then wouldn’t a dev server reloading essentially recreate that effect?

We do want to work on automatic disconnect behavior (and most of the time automatic disconnect already should work today) but for now you probably want to be on the safe side and call disconnect explicitly in your code so that it gets. called before your dev server restarts.

[martin-888]

I think that my and @brandonweiss problem with heroku should solve adding pgbouncer buildpack

https://devcenter.heroku.com/articles/on-dyno-postgres-connection-pooling