5

I have a small hobby web app built on this template: https://github.com/tomsoderlund/nextjs-pwa-graphql-sql-boilerplate

It uses ElephantSQL’s free tier (5 connections), runs on Zeit Now v2 serverless functions – and keeps running out of Postgres connections (“too many connections for role "djsktctf"”).

I’m using the death NPM to close connections - this is in /api/graphql/index.js:

const { ApolloServer, gql } = require('apollo-server-micro')
const { config } = require('../../config/config')

// Postgres (pg)
const { Pool } = require('pg')
const onProcessDeath = require('death')
const pool = new Pool({ connectionString: config.databaseUrl })
let client

const initDatabaseClient = async () => {
  if (!client) client = await pool.connect()
}
initDatabaseClient()

onProcessDeath((signal, err) => {
  client && client.release()
})

const typeDefs = gql`
  ${require('../../graphql/font/schema')}
`

const resolvers = {
  ...require('../../graphql/font/resolvers')(pool)
}

const server = new ApolloServer({
  typeDefs,
  resolvers,
  introspection: true,
  playground: true
})

module.exports = server.createHandler({ path: config.graphqlPath })

Then inside resolvers it looks like this:

module.exports = (pool) => ({
  Query: {
    async articles (parent, variables, context, info) {
      const sqlString = `SELECT * FROM article LIMIT 100;`
      const { rows } = await pool.query(sqlString)
      return rows
    }
}

How can I improve this to avoid running out of connections?

Update

After getting the answer below, I updated my code with client.end() and NOT using a shared client:

// const results = await runDatabaseFunction(async (pool) => { ... })
const runDatabaseFunction = async function (functionToRun) {
  // Connect db
  const client = await pool.connect()
  // Run function
  const results = await functionToRun(client)
  // Release db
  await client.end()
  await client.release()
  return results
}
Tom Söderlund
  • 4,743
  • 4
  • 45
  • 67
  • just curious about your pool config - mind sharing it? Do you have max, min, acquire & idle set? Have you experimented with retry? – headwinds May 31 '21 at 14:12

1 Answers1

2

I think that problem comes from client = await pool.connect()

It seems to hold your DB. So you need to disconnect something between you and DB.
If you add await client.end() after your last data fetching, then the error may go away.

In my case, this way solved the problem.

lupin
  • 138
  • 1
  • 8
  • Thank you, I'm now using both `client.end()` AND `client.release()`: `const client = await pool.connect(); const results = await client.query(sqlString, paramArray); await client.end(); await client.release();` although the official docs seem to state that `end()` is for the entire pool: https://node-postgres.com/features/pooling – Tom Söderlund Sep 21 '20 at 20:28