Trigger custom SQL queries with FeathersJS
The protagonists
If you already use Feathers you can skip to the next section.
In French, the Express catchline “Fast, unopinionated, minimalist web framework for Node.js” is literally translated as “Fast, flexible, minimalist web infrastructure for Node.js”.
Yes, not framework, but infrastructure. And I really like this translation because in my opinion Express is far too low level to be considered a framework.
Express is nice if you need to build a big API for zillions of users, but what if you are SME or a startup and you just want a nice API? What’s the point in setting up by yourself what any backend would need anyway, like authentication, a basic CRUD structure, and so on? In this case, you may need FeathersJS instead.
Feathers is a framework. I won’t describe it in details, but let’s just say that it is very fast, very thin, very scalable, and very complete while still being pluggable. I use it mostly as a wrapper around Express (or Socket.io) that provides the basic features to build a full-fledged REST or real-time API, nothing less, nothing more.
In my last project, I was confronted to a technical constraint I hadn’t encountered in years: directly working with SQL queries. Yes, you heard me, no sequelize, no waterline, no ORM, only neat handwritten SQL queries.
Why though?
For some people the “real backend” is not the server, but the database. This actually makes sense when you think about it. For most app, the server is only a program for conveniently manipulating data, in order to get the last news, to book an airbnb and so on.
So why should some server code impose the database structure? Would you want the frontend dev to impose the Node server architecture? No, when writing a Node server, you’ll want to setup your server as you wish, and then provide a public API. And thus, sometimes, it may be more convenient to build your SQL database… in SQL, not in Node.js.
In this precise project, the database structure was very complex and the team involved a database expert. Therefore manually writing the SQL queries was clearly a better idea than reproducing the whole db structure using sequelize or similar in order to auto-generate them.
First try: customizing a Knex service through Hooks
Knex is low level query builder which easily integrates with Feathers. It also includes a raw mode where you can send your own queries. In our case, Knex simply acts as a convenient database client and a query sanitizer.
So, Knex was definitely a fit for my usage, both for integrating the queries I was provided, or to quickly generate my own using its helpers. Now the question was how to use it correctly.
My first idea was to build an usual full fledged Knex based service, and then customize it. Feathers provides a CLI for generating such services, so it was the most natural solution. This service would include default CRUD methods, and use Knex under the hood to query the database.
The customization step is done through hooks. If you don’t know Feathers, hooks are simply reusable middlewares, similar to those used in Express. You can totally bypass the standard behaviour by creating the query, triggering it, and then setting up the context.result.
But then, why using a Knex service, since we totally bypass its default behaviour in our hook? In addition, Feathers generated services are usually tied to a precise table, which you don’t want if the requests are already provided. Instead you would rather need a service per domain entity, which makes more sense from the business standpoint.
Second try : custom service, but with the Knex client
I eventually figured out that when you first create a Knex service, it will setup the client. But then, no need to create any additional Knex service: simply create a custom service, and pass it the knex client!
Having Feathers to generate stuffs for you sounds great, but let’s not forget why light frameworks are good: they let you do whatever you want.
Here is the final result:
As simple as that! It is much cleaner, as we actually write a custom service for our custom need instead of bypassing an auto-generated generic service.
Final thoughts : fully custom service?
You may have noticed that with custom services, we don’t need Knex anymore. We could directly use any database connector and use sql-template-strings to write the queries.
Also, for methods that require multiple queries (usually some SELECT to query additionnal data before an INSERT or UPDATE), we could write reusable hooks as usual.
Voilà, we are done with integrating our SQL queries into our Feathers backend. I hope this article will help the SQL hardcore fans among us ;)
I am the co-founder of the French computer science and innovation office Lebrun Burel Knowledge Engineering — https://www.lebrun-burel.com
Always happy to talk about code, machine learning, innovation and entrepreneurship!
