Node / Express & Postgresql - when no rows match

Question

Hello I am new to Postgresql and I wanted to learn how one handles 0 results as an error is thrown. Essentially I want to get a user if it doesn't exist, return null if one doesn't, and have an error handler. Below is the current code I am using. Any tips on a better way to do this are appreciated!

var options = {
  // Initialization Options
  promiseLib: promise
};
var pgp = require('pg-promise')(options);
var connectionString = 'postgres://localhost:5432/myDbName';
var db = pgp(connectionString);

function getUser(id) {         
  let user = new Promise(function(resolve, reject) {
    try {
      db.one('select * from users where loginName = $1', id).then(function(data) {
        console.log(data);
        resolve(data); 
      }).catch (function (e) {
        console.log('error: '+e);
        reject(e);
      });
    }
    catch (e) {
      console.log('error: '+e);
      reject(e);
    }
  });
  return user;
}

output in console:

error: QueryResultError {
    code: queryResultErrorCode.noData
    message: "No data returned from the query."
    received: 0
    query: "select * from users where loginName = 'someUserName'"
}

If not finding the row is a normal situation (not an error), you should use method oneOrNone instead, and check the resolved value for null. Use .catch for handling actual errors. — vitaly-t
– vitaly-t, Commented Oct 4, 2016 at 6:46

Community · Accepted Answer · 2020-06-20 09:12:55Z

I am the author of pg-promise.

In the realm of promises one uses .then to handle all normal situations and .catch to handle all error situations.

Translated into pg-promise, which adheres to that rule, you execute a database method that resolves with results that represent all the normal situations, so anything else ends up in .catch.

Case in point, if returning one or no rows is a normal situation for your query, you should be using method oneOrNone. It is only when returning no row is an invalid situation you would use method one.

As per the API, method oneOrNone resolves with the data row found, or with null when no row found, which you can check then:

db.oneOrNone('select * from users where loginName = $1', id)
    .then(user=> {
        if (user) {
            // user found
        } else {
            // user not found
        }
    })
    .catch(error=> {
        // something went wrong;     
    });

If, however, you have a query for which returning no data does represent an error, the proper way of checking for returning no rows would be like this:

var QRE = pgp.errors.QueryResultError;
var qrec = pgp.errors.queryResultErrorCode;

db.one('select * from users where loginName = $1', id)
    .then(user=> {
        // normal situation;
    })
    .catch(error=> {
        if (error instanceof QRE && error.code === qrec.noData) {
            // found no row
        } else {
            // something else is wrong;
        }
    });

Similar considerations are made when choosing method many vs manyOrNone (method any is a shorter alias for manyOrNone).

Type QueryResultError has a very friendly console output, just like all other types in the library, to give you a good idea of how to handle the situation.

Feel a bit honored the author of pg-promise answered the question. Thanks Vitaly-t !

Paul · Accepted Answer · 2016-10-04 03:07:37Z

1

In your catch handler for the query, just test for that error. Looking at pg-promise source code, a code of noData is 0. So just do something like this:

db.one('select * from users where loginName = $1', id).then(function(data) {
        console.log(data);
        resolve(data); 
      }).catch (function (e) {
        if(e.code === 0){
          resolve(null);
        }
        console.log('error: '+e);
        reject(e);
      });

answered Oct 4, 2016 at 3:07

Paul

36.4k12 gold badges98 silver badges124 bronze badges

Collectives™ on Stack Overflow

Node / Express & Postgresql - when no rows match

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related