node.js + postgres database transaction management

Question

We have an existing SQL database, and I'm writing a node.js server that accesses it using straight SQL, using this postgres driver module:

https://github.com/brianc/node-postgres

So far I can't find a transaction management node module that works with postgres. Does anyone know of one? Preferably with some real-world use?

Second, at a higher level, we're evaluating whether node.js can actually replace Java as a real-world solution for a server potentially handling volume. Transaction management was one of the issues we'd have to solve. So some insight into that would also be useful.

For the moment, I'm simply issuing a sql BEGIN at the start of a node server request and a ROLLBACK or COMMIT at the end. However, I'm (perhaps obviously) unfamiliar with the real-world issues surrounding SQL transaction management. If someone could briefly explain the issues that the transaction management frameworks solve, I'd find it useful.

EDIT: I'm using the built-in connection pooling mechanism of the postgres driver, and all queries within an http request are issued on the same connection obtained from the pool. First the BEGIN is issued, then whatever the specific http request does, then the COMMIT or ROLLBACK.

Thanks.

Andrew · Accepted Answer · 2012-02-25 01:48:08Z

3

Transaction management is a pretty large subject. For what I imagine you are doing, you will want to use AUTOCOMMIT mode. This basically means that you will rely on PostgreSQL to BEGIN/COMMIT all of your statements (or in other words, that all your statements will run in their own transaction with no relation to each other). An easy way to decide that AUTOCOMMIT mode is right for you is to decide you don't need to use ROLLBACK. A huge benefit of AUTOCOMMIT mode is that even the stupidest connection pooling tool can't screw up.

For the gritty details around transaction management, start by taking a look at http://www.postgresql.org/docs/9.1/static/transaction-iso.html What ever you do, make sure you don't use or write a naive framework that leaves you in "IDLE in transaction" land. And finally, since you mentioned "high volume", I should ask what your balance of reads to writes is. If it is strongly favoring read behavior, then you should consider writing your code to use memcached. The easiest (but far from the most effective) way to do this is to use PQC.

answered Feb 25, 2012 at 1:48

Andrew

1,1972 gold badges12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jake Over a year ago

Thanks for all the info. We do need ROLLBACK, because our requests issue a number of update statements that need to be an atomic unit. I will look at that document and PQC.

vitaly-t · Accepted Answer · 2017-08-16 05:12:42Z

3

pg-promise library takes care of the transaction management nicely:

db.tx(t => {
        return t.batch([
            t.query('UPDATE users SET active = $1 WHERE id = $2', [true, 123]),
            t.query('INSERT INTO audit(event, id) VALUES($1, $2)', ['activate', 123])
        ]);
    })
    .then(data => {
        // success;
    })
    .catch(error => {
        // error;
    });

edited Aug 16, 2017 at 5:12

answered Apr 11, 2015 at 9:16

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

2 Comments

Derek Over a year ago

do you know how to set the transaction level using this method?

vitaly-t Over a year ago

@Derek See Configurable Transactions

Alex Herman · Accepted Answer · 2021-01-06 00:56:01Z

You just need a wrapper function.

I prefer to go with pure pg library, for it already has everything for transactions.

Here is my TypeScript example:

import { PoolClient } from "pg"
import { pool } from "../database"

const tx = async (callback: (client: PoolClient) => void) => {
  const client = await pool.connect();

  try {
    await client.query('BEGIN')
    try {
      await callback(client)
      await client.query('COMMIT')
    } catch (e) {
      await client.query('ROLLBACK')
    }
  } finally {
    client.release()
  }
}

export { tx }

Usage:

let result;

await tx(async client => {
  const { rows } = await client.query<{ cnt: string }>('SELECT COUNT(*) AS cnt FROM users WHERE username = $1', [username]);
  result = parseInt(rows[0].cnt) > 0;
});

yoonghm · Accepted Answer · 2015-09-04 11:34:06Z

0

Look for Sequelize http://docs.sequelizejs.com/en/latest/api/transaction/

The possible isolations levels to use when starting a transaction:

{
  READ_UNCOMMITTED: "READ UNCOMMITTED",
  READ_COMMITTED: "READ COMMITTED",
  REPEATABLE_READ: "REPEATABLE READ",
  SERIALIZABLE: "SERIALIZABLE"
}

Pass in the desired level as the first argument:

return sequelize.transaction({
  isolationLevel: Sequelize.Transaction.SERIALIZABLE
}, function (t) {

 // your transactions

}).then(function(result) {
  // transaction has been committed. Do something after the commit if required.
}).catch(function(err) {
  // do something with the err.
});

answered Sep 4, 2015 at 11:34

yoonghm

4,6851 gold badge40 silver badges53 bronze badges

Collectives™ on Stack Overflow

node.js + postgres database transaction management

4 Answers 4

1 Comment

2 Comments

You just need a wrapper function.

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

You just need a wrapper function.

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related