transactional route connections, but no transaction wrangling anywhere in the business logic

cowboy_trx.js

const express = require('express');
const knex = require('knex')({
  client: 'pg',
  connection: {
    user: 'dev',
    password: 'dev',
    database: 'dev',
  },
});

const app = express();

// in a piece of middleware, we start a transaction and attatch it to the request.
// anywhere we need a connection we use this transaction.
// when the request has finished we commit
app.use(async (req, res, next) => {
  const trx = await knex.transaction();
  req.trx = trx;

  res.on('finish', async function cleanup() {
    res.removeListener('finish', cleanup);
    await req.trx.commit();
  });

  next();
});

// this route works
// insert a "hit" record, then read the table in separate queries, but same transaction
app.get('/hey', async (req, res, next) => {
  try {
    const { trx } = req;
    await trx('hits').insert({});
    const { count } = (await trx('hits').count('id'))[0];
    return res.json({ hits: count });
  } catch (err) {
    return next(err);
  }
});

// this route does not work
// insert a "hit" record then read from a garbage table name in separate transaction
// because transaction, the insert is rolled back
app.get('/ho', async (req, res, next) => {
  try {
    const { trx } = req;
    await trx('hits').insert({});
    const { count } = (await trx('NOT A TABLE').count('id'))[0];
    return res.json({ hits: count });
  } catch (err) {
    return next(err);
  }
});

// roll the transaction back if an error is encountered
app.use(async (err, req, res, next) => {
  console.log('err, rolling back');
  await req.trx.rollback();
  return res.status(400).json({ err: err.message });
});

app.listen('3333', () => {
  console.log('listening on localhost:3333');
});