Pg-promise: Version 7.x of the driver

[vitaly-t]

Breaking my head over how to support the multi-result sets introduced in node-postgres 7.0.0... 😕

i.e. the driver v7.x now can return an array of results: [Result{}, Result{}, Result{},...], which doesn't fit into the existing architecture.

I'm trying to figure out a way that would make sense. Any suggestions are welcome!

---

Other than that, the driver works fine within branch v7.

[brianc]

It only returns an array of results if the query is a multi-statement simple text query such as:

client.query('SELECT * FROM foo; SELECT * FROM baz;')

For the 99% percent of the time that a single query is sent to client.query the behavior remains the same. The change also doesn't impact prepared statements at all because postgres doesn't allow more than 1 prepared statement to be executed at a time. If you want to revert to the old behavior you could do something like...

var query = client.query
client.query = function(text, values) {
  return query.call(client, text, values).then(result => {
    if (!Array.isArray(result)) {
      // return the same thing as it used to return
      // this is the "normal" case
      return result
    }
    const rows = _.flapMap(result, 'rows')
    const fields = _.flapMap(result, 'fields')
    const finalResult = result.pop()
    finalResult.fields = fields
    finalResult.rows = rows
    return finalResult
  })
}

As an aside: I don't recommend pipelining multiple queries in a single query text unless you have a specific edge type of case. You miss out on parameterized queries and other goodies and its sometimes harder to reason about a query error if 1 of N queries in your bulk of queries fails. I realize it can be helpful to do in certain situations though & the change in 7.0 was to make it more understandable when you received a result so it wasn't all collapsed into a single big array. Sorry it introduced some headache!

Hope this helps! :heart:

[vitaly-t]

@brianc Thank you for following this up!

I don't recommend pipelining multiple queries in a single query text unless you have a specific edge type of case

Most of users that I came across were interested in executing multiple queries just for the sake of maximizing the performance. When you have multiple queries and all can be executed at once, it makes sense to simply concatenate them. Previously developers did this type of optimization only when just one of the queries returned a result. But now that you can get all of them, I think many will want to do just that.

[vitaly-t]

After a careful consideration...

The kind of changes that node-postgres v7 brings to the table are impossible to accommodate within pg-promise, not without a major breaking change of the library. This is all about supporting multi-result-sets.

This all is now being done within branch v7, which will be released as pg-promise v7.x, with substantial breaking changes for the first time ever.

The new release will also bring other long-outstanding breaking changes, to improve the overall architecture.

[vitaly-t]

Branch v7 has been finished, and it all works well. But there will be a lot of test refactoring + documentation updates before it can be merged into the master branch.

In the meantime, anyone can start playing with the new version 7 of the library.

Since documentation for v7 is being written, in the meantime you can use the list of changes within the v7 milestone, as a reference for what was changed.

[vitaly-t]

Updated documentation for version 7 is temporarily published here.

[vitaly-t]

Entire version 7 is likely to be scrapped, and re-done from scratch, as something simpler, because the initial approach seems to make the whole layer too complex. I will try to re-do it in such a way to keep everything simple, and hopefully without breaking changes.

[vitaly-t]

Version 6.7 has been released that now uses the latest 7.x driver, while disabling the breaking change of the 7.x driver. See the Release Notes.

Now any work toward the actual version 7.x of pg-promise will be a breaking change in the protocol to support multiple results. This will happen at a later stage, as it is no longer a priority.

For now when getting multiple results the library will only return the last result on the list, thus providing compatibility with all the previous versions.

[vitaly-t]

Replacing this issue with #405, with focus on what's remaining.