Node - pg with native promises (pattern)

README.md

Use Promises with PostgreSQL in Node

This pattern is a pattern I use for all my Node projects that need PostgreSQL. I work a lot with Promises, and ended-up designing this db.js file.

Put this file wherever you want in your app directory. Edit the connectionInfo string and voilà!

Call the execute method passing a callback function. Your callback function will be called with a function as first parameter which will allow you to execute SQL queries retourning Promises. Execute as much queries as you want, but don't forget to return a Promise that must be resolved once all your queries are done. After that, the database connection will be properly closed automatically.

db.js

var pg = require('pg')



var connectionInfo = 'postgres://username:password@localhost/database'

var self = {
    /**
     * Open a connection and execute the callback passing the connection as the first argument.
     *
     * @note Your callback must return a Promise.
     */
    execute: function(callback)
    {
        return new Promise(function(resolve, reject)
        {
            pg.connect(connectionInfo, function(error, connection, closeConnection)
            {
                if (error)
                {
                    error.message = 'Database connection failed: ' + error.message
                    reject(error)
                }
                else
                {
                    callback(function()
                    {
                        var param = []
                        for (var i = 0; i < arguments.length; ++i)
                        {
                            param.push(arguments[i])
                        }
                        
                        return new Promise(function(resolve, reject)
                        {
                            param.push(function(error, result)
                            {
                                if (error)
                                {
                                    reject(error)
                                }
                                else
                                {
                                    resolve(result)
                                }
                            })
                            connection.query.apply(connection, param)
                        })
                    }, connection)

                    .then(function(result)
                    {
                        closeConnection()
                        resolve(result)
                    })
                    .catch(function(error)
                    {
                        closeConnection()
                        reject(error)
                    })
                }
            })
        })
    }
}

module.exports = self

usage.js

var db = require('./db')

db.execute(function(query /*, connection*/)// You can get the connection as 2nd argument for more
                                           // complex queries (e.g. with cursor)
{
    // Insert something.
    return query(
        'INSERT INTO myTable(column1, column2) VALUES($1, $2)',
        [ 'value1', 'value2' ]
    )
    .then(function(result)
    {
        // ...
        
        return Promise.resolve()
    })

    // Select something.
    .then(function()
    {
        return query(
            'SELECT * FROM myTable'
        )
        .then(function(result)
        {
            // ...
            
            return Promise.resolve()
        })
    })
})

// Database connection auto-close here before executing what's in the next then:

.then(function()
{
    // Do something else here without the database...
})

.catch(function(error)
{
    console.error(error)
})

Have you seen pg-promise?

@vitaly-t pg-promise rely on a library for promises. Native promises do not have the "finally" block. So if you use native Promises, you must always have a catch & a then block to be sure to close the connection. With this code, you can wrap all your query in a function, and the database connection will be closed automatically. That's what I was looking for when I wrote this: autoclose the connection.

That is incorrect. Closing the connection is optional, and done only once, in the end of the application, requests do not have to deal with connections at all. See the examples: Learn by Example

And ES6 Promise is the default library used by pg-promise.

Great. I should give it a try in a new project. You say you use a single connection within the application? In my code, I'm rather using a connection per request. Is that OK to keep a single connection for the whole server?

pg-promise uses the connection pool implemented by node-postgres, which in turn redirects it into generic-pool. with pg-promise you do need to worry about keeping the connection at all.

P.S. It is awkward writing here, because posts here do not provide any notification, no way of knowing when somebody has replied.