February 7, 2016

New npm package: Rapid-prototyping CRUD REST-to-SQL

Last week, I discovered how implementing a simple CRUD REST-to-SQL web server with Node.js + Hapi + Bookshelf.js still requires an uncomfortable amount of manual work. This week I decided to do something about it. I hereby present the hapi-bookshelf-crud npm package.

Given that you have a Hapi instance server and a Bookshelf instance bookshelf, you can define your models like so:

const models = {
  Customer: bookshelf.Model.extend({
    tableName: 'customer',
    schema: {
      name: Joi.string().regex(/^[A-Za-z ]*$/).required(),
      employmentStatus: Joi.string().default('Unemployed'),
      payments: hapiCrud.empty(),
  Payment: bookshelf.Model.extend({
    tableName: 'payment',
    schema: {
      amount: Joi.number().positive(),
      date: Joi.date().format('YYYY-MM-DD').allow(null),

And define your CRUD REST endpoints like so:

const hapiCrud = require('hapi-bookshelf-crud')(server);

  bookshelfModel: models.Customer,
  basePath: '/customers',

  bookshelfModel: models.Payment,
  basePath: '/customers/{customerId}/payments',
  baseQuery: function(request) {
    return {customerId: request.params.customerId}

That’s it. Now you have:

  • CRUD operations on the DB at the respective REST service endpoints, ready for use with Restangular (or any other REST consumer)
  • Validation error handling with I18N message support based on validation constraints on the model
  • DB error handling with I18N message support
  • Date format conversion based on model declaration
  • To-Many-relationship handling
  • And a few more handy things.
All in all it’s just a no-brainer super-rapid prototyping solution.

For more information about how to use it and in order to get started, visit its GitHub repository or its npm package page.

Read on to learn more about the motivation behind this project and what problems it is designed to solve, and how it does that.

The foundations

In 2016, Node.js is the platform of choice if we want to write a reliable server in a few lines of code. There’s no denying that a Java EE-based solution comes with more boilerplate.

If really all I want is a CRUD REST server, and I want it in as few lines as possible, Java EE is no match. Therefore, it makes sense to realize this project on top of Node.js.

Hapi is an industry-proven REST server abstraction, and Bookshelf.js is the most complete O/R mapper; it thus makes sense to use these packages as the basis.

Problem n° 1: Compact CRUD

The central idea of hapi-bookshelf-crud is to provide a simple API to build all REST service operations for full CRUD at once for a given model rather than building each route separately, as is the case with vanilla Hapi. hapi-bookshelf-crud borrows the idea of each model belonging to a “base path” from Java EE to build an API which can build CRUD with nothing more than model and base path information.

Problem n° 2: Validation / Error handling

I want to have declarative validation constraints on my model. In fact, my model definition should hardly consist of anything else than validation constraints; those just are the most important part of its definition.

Unfortunately, although Bookshelf comes with its own validation framework called Checkit, it doesn’t support out-of-the-box support for declarative validation; it need to be coded explicitly. Also Checkit’s API shows that the number of available constraints and the information yielded in case of a validation error is very limited.

On the other hand, Hapi provides a strong, elaborate validation framework called Joi. Therefore, I used this framework to do the model validations. I explicitly call the validation logic in a handler every REST endpoint access has to pass through rather than relying on Bookshelf’s callback hooks.

The validation rules can be specified in the model’s schema property. This is quite a lightweight solution which matches well Joi’s own naming and doesn’t interfere with anything from Bookshelf.js.

Joi comes with a huge amount of elaborate validation functionality, including cross-field validation and setting default values. It’s a joy (pun!) to see that those now work implicitly with hapi-bookshelf-crud.

The same goes with error handling which is implemented centrally in hapi-bookshelf-crud.

Problem n° 3: Table column mapping

SQL’s table columns which come in snake_case need to be matched against the model’s camelCased properties (camelCase is JSON object de-facto standard). Bookshelf doesn’t do this by default, and it needs a hook.

hapi-bookshelf-crud applies this hook to the model upon initialization.

Problem n° 4: Querying for nested resources

One of the most powerful aspects of REST is that its resources can be nested like a breadcrumb-navigation. For the “find all” and “find by id” GET queries, we thus need to incorporate any parent resources’ ids as an additional WHERE filter.

In the hapiCrud.crud API, the user must explicitly provide a map with the id-to-param mapping.

We could probably automate this step by regexing the basePath in a later release.

In the same way, we need to put all the foreign keys back into a model by extracting them from the query String before saving the model, otherwise we would risk overriding them with null. Typically, we can’t rely on the foreign keys being preserved in the model; but they’re guaranteed to be part of the (nested) resource URI. So we reverse above logic to put that information back into the model.

Problem n° 5: Relationship handling

hapi-bookshelf-crud handles to-one and to-many relations:
  • to-one relations set the foreign key id. This happens implicitly and matches well Bookshelf’s defaults and SQL’s expectations.
  • to-many relationships however must be deleted from a model before save because otherwise, Bookshelf tries to insert the array containing the “many” objects as a single JSON LOB into the respective column which of course must fail miserably. In order to mark a property as a to-many field which must be emptied, we use a special marker for that property in the validation schema (hapiCrud.empty()).
In fact, cascading save operations to children doesn't seem to make sense in a strict REST architecture, as a sub-model would really be addressed by another (nested) REST endpoint.

With hapi-bookshelf-crud there is hence no need for any of Bookshelf’s special “relation” declarations on the model.

(Cascading delete operations however remain an open issue for now.)

Problem n° 6: Miscellaneous mapping problems

Bookshelf includes some additional O/R mapping problems hapi-bookshelf-crud is designed to solve:
  • numbers should be initialized with 0 in order to trigger i.e. Joi positive() constraint violations rather than not-null constraints on the DB. Here, hapi-bookshelf-crud happily relies on the model schema’s definitions to find numeric model properties.
  • dates should be initialized as date and formatted appropriately when returned from the DB. Again, hapi-bookshelf-crud takes the model schema’s definitions of date()s and their format()s into account.

See it in action

See a complete demo server implementation here.

For the client, we can simply retake the Restangular-based example client application I built for use with a vanilla Node.js + Hapi + Bookshelf.js server and use it with a Node.js server built on top of hapi-bookshelf-crud.


In my last blog post comparing Java EE 7 with Node.js, I concluded that the Node.js ecosystem is “no match for a Java EE 7 tech stack” when it comes to building a REST-to-SQL server. Does this magically change with the hapi-bookshelf-crud package? No. There are still other concerns which don’t quite persuade me to make the change to a Node.js solution, as discussed in that article.

However, I think the hapi-bookshelf-crud package may actually make for a good solution when it comes to rapid-prototyping a CRUD REST-to-SQL backend. I’m especially happy that it supports declarative validation already and gets rid of almost all the technical boilerplate code. I would gladly use it to quickly build a REST backend in earlier iterations of a web app development project.

Please note that this npm package clearly is still very experimental and unfinished. If this has awaken your interest and you have Node.js / Hapi / Bookshelf.js knowledge, please head over to its GitHub repository to learn how to contribute.

Also, let me know your thoughts about this project in the comments section below. Please be nice; this is the first npm package I ever released…

Update March 2, 2016: hapi-bookshelf-crud 0.2.0 is now officially released.

No comments:

Post a Comment