ES6 Async/Await, ExpressJS and Postgres transactions

Question

REVISED QUESTION

I've revised the question, in the hope of getting a clearer answer.

---

I'm trying to process data in ExpressJS, based on the incoming req.body and the existing data in the table.

I'm receiving a req.body that contains a JSON list of updated fields. Some of those fields are stored as JSONB in Postgres. If an incoming field is JSONB, then the form (external code) that is making the request has already run a jsonpatch.compare() to generate the list of patches, and it is these patches and not the full values that are being passed in. For any non-JSONB values, incoming values just need to be passed through to the UPDATE query.

I have a working version, as below, that pretends that the existing JSONB values in the table ARE NULL. Clearly, this is NOT what is needed. I need to pull the values from the db. The non-querying-of-current-values version and a bare minimum router, looks like this:

const express = require('express')
const bodyParser = require('body-parser')
const SQL = require('sql-template-strings')
const { Client } = require('pg')
const dbConfig = require('../db')
const jsonpatch = require('fast-json-patch')

const FormRouter = express.Router()

I have some update code:

````javascript
const patchFormsRoute = (req, res) => {
  const client = new Client(dbConfig)
  const { id } = req.body
  const parts = []
  const params = [id]

  // list of JSONB fields for the 'forms' table
  const jsonFields = [
    'sections',
    'editors',
    'descriptions',
  ]

  // list of all fields, including JSONB fields in the 'forms' table
  const possibleFields = [
    'status',
    'version',
    'detail',
    'materials',
    ...jsonFields,
  ]

  // this is a DUMMY RECORD instead of the result of a client.query 
  let currentRecord = { 'sections':[], 'editors':[], 'descriptions':[] }

  possibleFields.forEach(myProp => {
    if (req.body[myProp] != undefined) {
      parts.push(`${myProp} = $${params.length + 1}`)
      if (jsonFields.indexOf(myProp) > -1) {
        val = currentRecord[myProp]
        jsonpatch.applyPatch(val, req.body[myProp])
        params.push(JSON.stringify(val))
      } else {
        params.push(req.body[myProp])
      }
    }
  })

  const updateQuery = 'UPDATE forms SET ' + parts.join(', ') + ' WHERE id = $1'

  client.connect()
  return client
    .query(updateQuery, params)
    .then(result => res.status(200).json(result.rowCount))
    .catch(err => res.status(400).json(err.severity))
    .then(() => client.end())
}

FormRouter.route('/')
  .patch(bodyParser.json({ limit: '50mb' }), patchFormsRoute)

exports.FormRouter = FormRouter

I promise, that this is working code, which does almost what I need. However, I want to replace the dummy record with the data already in the table, fetched contemporaneously. My issue, is because multiple clients could be updating a row at the same time (but looking at orthogonal elements of the JSONB values), I need the fetch, calc, and update to happen as a SINGLE TRANSACTIOn. My plan is to:

1. BEGIN a transaction

2. Query Postgres for the current row value, based on the incoming id

3. For any JSONB fields, apply the patch to generate the correct value for that field in the UPDATE statement.

4. Run the UPDATE statement with the appropriate param values (either from the req.body or the patched row, depending on whether the field is JSONB or not)

5. COMMIT the transaction, or ROLLBACK on error.

I've tried implementing the answer from @midrizi; maybe it's just me, but the combination of awaits and plain testing of res sends the server off into Hyperspace... and ends in a timeout.

Answer 1

In case anyone is still awake, here's a working solution to my issue.

TLDR; RTFM: A pooled client with async/await minus the pooling (for now).

const patchFormsRoute = (req, res) => {
  const { id } = req.body
  // list of JSONB fields for the 'forms' table
  const jsonFields = [
    'sections',
    'editors',
    'descriptions',
  ]

  // list of all fields, including JSONB fields in the 'forms' table
  const possibleFields = [
    'status',
    'version',
    'detail',
    'materials',
    ...jsonFields,
  ]
  const parts = []
  const params = [id]

  ;(async () => {
    const client = await new Client(dbConfig)
    await client.connect()
    try {
      // begin a transaction
      await client.query('BEGIN')

      // get the current form data from DB
      const fetchResult = await client.query(
        SQL`SELECT * FROM forms WHERE id = ${id}`,
      )

      if (fetchResult.rowCount === 0) {
        res.status(400).json(0)
        await client.query('ROLLBACK')
      } else {
        const currentRecord = fetchResult.rows[0]

        // patch JSONB values or update non-JSONB values
        let val = []

        possibleFields.forEach(myProp => {
          if (req.body[myProp] != undefined) {
            parts.push(`${myProp} = $${params.length + 1}`)
            if (jsonFields.indexOf(myProp) > -1) {
              val = currentRecord[myProp]
              jsonpatch.applyPatch(val, req.body[myProp])
              params.push(JSON.stringify(val))
            } else {
              params.push(req.body[myProp])
            }
          }
        })

        const updateQuery =
          'UPDATE forms SET ' + parts.join(', ') + ' WHERE id = $1'

        // update record in DB
        const result = await client.query(updateQuery, params)

        // commit transaction
        await client.query('COMMIT')

        res.status(200).json(result.rowCount)
      }
    } catch (err) {
      await client.query('ROLLBACK')
      res.status(400).json(err.severity)
      throw err
    } finally {
      client.end()
    }
  })().catch(err => console.error(err.stack))
}