CODEWITHSUNDEEP
node.jspostgresqlpg-promise
user2846569
user2846569

Reputation: 2832

How to lock table with pg-promise

I have

db.result('DELETE FROM categories WHERE id = ${id}', category).then(function (data) { ...

and

db.many('SELECT * FROM categories').then(function (data) { ...

initially delete is called from one API call and then select on following API call, but callback for db request happens in reverse order, so I get list of categories with removed category.

Is there a way how to lock categories table with pg-promise?

Upvotes: 0

Views: 597

Answers (1)

vitaly-t
vitaly-t

Reputation: 25840

If you want the result of the SELECT to always reflect the result of the previous DELETE, then you have two approaches to consider...

The standard approach is to unify the operations into one, so you end up executing all your dependent queries against the same connection:

db.task(function * (t) {
    yield t.none('DELETE FROM categories WHERE id = ${id}', category);
    return yield t.any('SELECT FROM categories');
})
  .then(data => {
      // data = only the categories that weren't deleted
  });

You can, of course, also use either the standard promise syntax or even ES7 await/async.

The second approach would be to organize an artificial lock inside your service that would hold off on executing any corresponding SELECT until the DELETE requests are all done.

However, this is a very awkward solution, typically pointing at the flaw in the architecture. Also, as the author of pg-promise, I won't be even getting into that solution, as it would be way outside of my library anyway.

Upvotes: 1

Related Questions