Advanced Error Handling

Tables with Foreign Keys

When a table references an entry in another table, this can open up an endpoint to more room for errors.

Consider a database with a table for species which contains entries like 'dog', 'cat' etc, and then a table for pets where each pet has a species property that references the species table.

For an endpoint like GET /api/pets?species=[speciesHere], where the user wants all of the pets that match the provided species, there are a few situations to consider:

GET /api/pets?species=iDontExist - where the species we are filtering by does not exist in our database. This should give a 404 response because the resource does not exist.

GET /api/pets?species=iguana - here is a scenario where the species we are filtering by does exist in our species table, however no pets reference this species. This should give a 200 and an empty array.

Sending the correct response

If the database receives either of the species queries in the examples above, it will always respond with an empty array. This is because, in both cases, the database has responded with all the data matching the query, aka none!

We must then differentiate between when an empty array is appropriate to send back (when the query is valid but no resources associated with it) vs when we should instead send back an error (when the query is invalid).

This will require a separate request to the database to verify if the referenced resource does indeed exist in the database or if it is an invalid query. It may be useful to extract this out into its own function.

const checkSpeciesExists = async (species) => {
  const dbOutput = await db.query(
    'SELECT * FROM species WHERE species_name = $1;',
    [species]
  );
 
  if (dbOutput.rows.length === 0) {
    // resource does NOT exist
    return Promise.reject({ status: 404, msg: 'Species not found' });
  }
};

If the Promise.reject is triggered because there is no resource found in the database, this will immediately send us to the controller's catch block with the 404 error object that we have created. If the rejection is not triggered, then the query was valid and we should indeed send an empty array back to the user.

Querying variable database columns

It would be good if this 'checkExists' function was reusable, as this may be a check that we want to make for different resources on our server.

It would be ideal to pass in the table and column as variables and passing those into our database query.

We can either pass these table/column variables into the query string using template literals, or make use of the pg-format package as below.

Important note: using template literals opens the query up to SQL injection, so it is very important that the table and column variables are not provided by the external user. Instead, we the developers should pass in the strings for the table and column ourselves and sanitise them if necessary.

pg-format can be used to insert identifiers (opens in a new tab) such as table or column names into our queries using %I.

Snippet 1: making checkExists reusable

// in utils.js
const format = require('pg-format');
 
const checkExists = async (table, column, value) => {
  // %I is an identifier in pg-format
  const queryStr = format('SELECT * FROM %I WHERE %I = $1;', table, column);
  const dbOutput = await db.query(queryStr, [value]);
 
  if (dbOutput.rows.length === 0) {
    // resource does NOT exist
    return Promise.reject({ status: 404, msg: 'Resource not found' });
  }
};

Snippet 2: Using checkExists

// in model.js
const selectPets = async ({ species }) => {
  // getting the pets from the db here
  if (!pets.length) {
    await checkExists('species', 'species_name', species); // We, the devs, pass in the table and column name
  }
};