Next.js API routes with pg-promise

Question

I’m using the excellent pg-promise library inside a Next.js app to interface with a Postgres database deployed on AWS. Specifically, I’m using the API routes feature, where folders inside /pages/api are mapped to corresponding endpoints. This has vastly simplified my code and allowed me to remove a custom server.js file. The problem is that pg-promise throws this warning:

WARNING: Creating a duplicate database object for the same connection.

The author has addressed this before, but despite following the advice, the warning persists.

I initialize the database connection only once, in database.js:

const pgp = require('pg-promise')();

const connection = { ... };

const db = pgp(connection);

module.exports = db;

And then pass it along to my APIs in pages/api, in this case, users.js:

import db from ‘../database.js;

export default async function handler(req, res) {
  try {
    const users = await db.any('SELECT * FROM table);
    res.json(users)
  } catch (error) {
    res.status(error.status || 500).end(error.message)
  }
}

The data eventually makes its way to a getInitialProps call.

What is causing the warning? Is there a pattern I'm missing or a design flaw in my handling of the connection object? I've experimented with various configs and middlewares, but the warning remains.

Admin · Answer

Establishing a connection is made via the getStaticProps function.

see the following:next/getStaticProps

You must import the pg-promise library within this function, just as you would within a server framework like express.

user_8780756 · Answer

I think you can use noWarnings: true as from this tutorial: https://techsolutionshere.com/next-js-with-postgresql/

const pgp = require('pg-promise')({
    noWarnings: true
})

const db = pgp(`postgres://User:Password@localhost:5432/product-test`)


export default async (req, res) => {
    try {
        const { name, price,  imageUrl, description } = req.query

        if(!name || !price || !imageUrl || !description){
            return res.status(422).send({error: ['Missing one or more fields']})
        }

        const product = await db.one('INSERT INTO products(name, price, imageUrl, description) VALUES($1, $2, $3, $4) RETURNING *', [name, price, imageUrl, description])

        res.status(200).json(product)

    } catch (error) {
        // console.error(error);
        res.status(500).send({message: ["Error creating on the server"], error: error})
    }
}

Next.js API routes with pg-promise

2 Answers