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'"
}

Answer 1

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.