How Shyp handles Waterline database errors

README.md

A better data access layer for Waterline

This is Shyp's next gen data access layer. Maybe the best way to describe this is what was bad with the old one:

• Waterline's behavior is notably inconsistent. We don't have any flexibility with its interface, or the arguments it accepts.
• Waterline throws dictionaries, not Error objects, which are treated inconsistently
• Stubbing a database call required importing/loading all of Sails, which is slow
• Model objects are globals, which means their behavior is inconsistent
• The old API's had a lot of dangerous behavior - a failure to findOne would return null instead of throwing an error, and calls to update always returned a list, even if you knew you were only updating one record.

So! Here are some nice things about the functions in this folder:

• We can define better interfaces that are safer, shorter and easier to work with.
• You can stub database calls in tests without needing to load Sails.
• A function that's designed to find or update one of something, and doesn't find anything, will throw an error.
• Where we can, we throw instances of DatabaseError objects, which have useful properties and better error messages than PG/Waterline.

For an example, check out the UserDAO file. It contains two methods for accessing Users that we call often - creating a new user or updating a single user by their ID. If a constraint or uniqueness check fails, we catch the error and try to return a nicer one.

[ ... snipped ... ]

Handling Postgres errors

Say you have a table with a unique index, and you try to write a duplicate record to the database. Waterline will throw an object with the following keys. Note this is not an instance of Error; if your tests are reporting a failure and there is no visible stack trace, it's probably a Waterline object without a message key. Come to think of it we should probably patch Mocha to make this error more visible.

• originalError - The underlying Postgres error, with a bunch of useful fields including the failing constraint. This field will always be there for errors raised by Postgres, including type errors, not null failures, check constraints, and unique constraints.

• code - 'E_VALIDATION' or 'E_UNKNOWN'. note that E_VALIDATION does not get raised in some cases where it should, for example check constraint failures, so this field is not reliable

• invalidAttributes - Either undefined or an object that looks something like this:

  { 'lower(username)':
  [ { value: 'michaeljordan',
     rule: 'unique',
     message: 'A record with that `lower(username)` already exists (`michaeljordan`).' } ] }

  NB: this field is undefined for check constraints

• _e - ignore this

• rawStack - Raw stack trace for the error message

• details - Occasionally undefined but when defined, just takes fields from the PG error message. Ignore this.

• reason - may be undefined or the string "N attribute(s) are invalid". Ignore this

• status - for some reason Waterline sets HTTP status codes on error objects. ignore this field.

• model - Occasionally undefined, occasionally the table name. Don't use this field, the originalError will tell you which table failed.

My goal in walking through this is to show you that Waterline errors are untrustworthy. You really want to interact with and introspect on the originalError field, which contains more useful and consistent information about what went wrong. That object looks like this:

{ [error: duplicate key value violates unique constraint "username_unique_lowercase"]
  name: 'error',
  length: 228,
  severity: 'ERROR',
  code: '23505',
  detail: 'Key (lower(username))=(michaeljordan) already exists.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'users',
  column: undefined,
  dataType: undefined,
  constraint: 'username_unique_lowercase',
  file: 'nbtinsert.c',
  line: '398',
  routine: '_bt_check_unique' }

You should be able to pass this error object directly to the DatabaseError constructor. Then you can access the detail, code, constraint fields from there. You'll probably need to write a better error message though; put some thought into whether users will be able to understand what went wrong and how to fix it.

DatabaseError.coffee

# A database error indicates an error raised by Postgres

# Codes: see http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html
CODES =
  checkViolation:  '23514'
  uniqueViolation: '23505'

class DatabaseError
  constructor: (message, opts) ->
    unless @ instanceof DatabaseError
      return new DatabaseError(message, opts)

    @message    = message
    @code       = opts.code
    @severity   = opts.severity
    @table      = opts.table
    @detail     = opts.detail
    @constraint = opts.constraint

    Error.call(@)
    Error.captureStackTrace(@, arguments.callee)
    @name = @constructor.name

  @:: = Object.create Error::
  @::constructor = DatabaseError

DatabaseError.CODES = CODES
module.exports      = DatabaseError

UserDAO.coffee

# Framework-independent interface for the Users table.

Promise = require 'bluebird'
uuid = require 'node-uuid'

DatabaseError = require '../errors/DatabaseError'
InvalidDataError = require '../errors/InvalidDataError'
NotFoundError = require '../errors/NotFoundError'
Validation = require '../services/Validation'
ValueError = require '../errors/ValueError'

CONSTRAINTS = Object.freeze
  usernameTooLong:         'username_length'
  invalidCharacters:       'username_valid_characters'
  uniqueEmail:             'users_email_key'
  uniqueLowercaseUsername: 'username_unique_lowercase'

# Catch a database error raised by Waterline and turn it into an Error object
# if we can do so.
throwErrorObject = (err) ->
  unless err.originalError?
    # Not sure what type of error this is
    throw err

  dbErr = err.originalError
  if dbErr.code is DatabaseError.CODES.checkViolation
    if dbErr.constraint is CONSTRAINTS.usernameTooLong
      throw new DatabaseError 'Username is too long', dbErr
    if dbErr.constraint is CONSTRAINTS.invalidCharacters
      throw new DatabaseError 'Username contains invalid characters. Alphanumeric only', dbErr
  else if dbErr.code is DatabaseError.CODES.uniqueViolation
    if dbErr.constraint is CONSTRAINTS.uniqueLowercaseUsername
      throw new DatabaseError 'This username is already in use', dbErr
    if dbErr.constraint is CONSTRAINTS.uniqueEmail
      throw new DatabaseError 'This email is already in use', dbErr

  throw err

UserDAO =

  # Create a new user. Resolves with the new user or rejects with an error
  # - a DatabaseError if we can create one, or a Waterline error for unexpected
  # database issues.
  create: (data) ->
    unless data.id
      data.id = "#{Users.prefix}_#{uuid.v4()}"
    if data.email? and not Validation.isEmail(data.email)
      return Promise.reject new ValueError "Invalid email address: '#{data.email}'"
    Users.create(data).catch throwErrorObject

  # updateOne sets the `data` on the user with the given `id`.
  #
  # Throws a NotFoundError if no user exists with the given id.
  #
  # Returns a Promise which resolves with the updated user. Will throw a
  # DatabaseError if a constraint is violated, or a Waterline error for
  # unexpected database issues (broken connection etc).
  updateOneById: (id, data) ->
    unless id?
      return Promise.reject new InvalidDataError('Invalid ID')
    if data.email? and not Validation.isEmail(data.email)
      return Promise.reject new ValueError "Invalid email address: '#{data.email}'"
    Users.update().where(id: id).set(data).then (users) ->
      unless users[0]?
        throw new NotFoundError 'User not found', 'user_not_found', instanceUrl: "/users/#{id}"
      users[0]
    .catch throwErrorObject

UserDAO.CONSTRAINTS = CONSTRAINTS
module.exports      = UserDAO