Can you keep a PostgreSQL connection alive from within a Next.js API?

Question

I'm using Next.js for my side project. I have a PostrgeSQL database hosted on ElephantSQL. Inside the Next.js project, I have a GraphQL API set up, using the apollo-server-micro package.

Inside the file where the GraphQL API is set up (/api/graphql), I import a database helper-module. Inside that, I set up a pool connection and export a function which uses a client from the pool to execute a query and return the result. This looks something like this:

// import node-postgres module
import { Pool } from 'pg'

// set up pool connection using environment variables with a maximum of three active clients at a time
const pool = new Pool({ max: 3 })

// query function which uses next available client to execute a single query and return results on success
export async function queryPool(query) {
    let payload

    // checkout a client
    try {
        // try executing queries
        const res = await pool.query(query)
        payload = res.rows
    } catch (e) {
        console.error(e)
    }

    return payload
}

The problem I'm running into, is that it appears as though the Next.js API doesn't (always) keep the connection alive but rather opens up a new one (either for every connected user or maybe even for every API query), which results in the database quickly running out of connections.

I believe that what I'm trying to achieve is possible for example in AWS Lambda (by setting context.callbackWaitsForEmptyEventLoop to false).

It is very possible that I don't have a proper understanding of how serverless functions work and this might not be possible at all but maybe someone can suggest me a solution.

I have found a package called serverless-postgres and I wonder if that might be able to solve it but I'd prefer to use the node-postgres package instead as it has much better documentation. Another option would probably be to move away from the integrated API functionality entirely and build a dedicated backend-server, which maintains the database connection but obviously this would be a last resort.

Answer 1

I haven't stress-tested this yet, but it appears that the mongodb next.js example, solves this problem by attaching the database connection to global in a helper function. The important bit in their example is here.

Since the pg connection is a bit more abstract than mongodb, it appears this approach just takes a few lines for us pg enthusiasts:

// eg, lib/db.js


const { Pool } = require("pg");

if (!global.db) {
  global.db = { pool: null };
}

export function connectToDatabase() {
  if (!global.db.pool) {
    console.log("No pool available, creating new pool.");
    global.db.pool = new Pool();
  }
  return global.db;
}

then in, eg, our API route, we can just:

// eg, pages/api/now


export default async (req, res) => {
  const { pool } = connectToDatabase();
  try {
    const time = (await pool.query("SELECT NOW()")).rows[0].now;
    res.end(`time: ${time}`);
  } catch (e) {
    console.error(e);
    res.status(500).end("Error");
  }
};