current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite

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'"
}
share|improve this question
    
which node postgres module are you using? – Paul 22 hours ago
    
pg-promise (added to post in the most recent edit) – Ron I 22 hours ago
    
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 18 hours ago
up vote 2 down vote accepted

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.

share|improve this answer
1  
Feel a bit honored the author of pg-promise answered the question. Thanks Vitaly-t ! – Ron I 11 hours ago
up vote 1 down vote

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);
      });
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.