3

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
g_auge19
  • 55
  • 5
  • good question, got any good solution yet? Br – user1665355 Oct 12 '20 at 21:14
  • @user1665355 sadly no. there is a mysql-serverless module which is supposed to take care of this problem for MySQL database connections but unfortunately, there's no similar module available for Postgres. i really hope someone will create one in the future but until then we will have to use a second, dedicated db connection server. – g_auge19 Oct 14 '20 at 13:24

1 Answers1

5

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");
  }
};
Parker
  • 83
  • 1
  • 6