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.

When you get the warning, it includes the call stack, telling you where the second initialization happens. Just follow that. — vitaly-t, Feb 22 '20 at 19:48
Yes, I forgot to mention, the call stack points to the original initialization in ```database.js``` and to ```__webpack_require__``` in ```users.js```, so to the actual import of the database object. — mkrkelj1, Feb 22 '20 at 21:08
So you do manage to execute it twice, via original call, plus through the webpack's compressed version, somehow, it seems. This should be easy to debug and see where the call happens twice. — vitaly-t, Feb 22 '20 at 22:21
I have the same issue and I'm surprised how poor the documentation of this library is. — user134, May 23 '20 at 08:17

score 1 · Answer 1 · answered Jul 13 '20 at 06:46

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.

score 0 · Answer 2 · answered Feb 15 '21 at 14:41

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 Answers2