SQLBits, a lightweight SQL builder for Node.js built by William Wicks, is a great compromise between using native SQL and going to a full ORM (object relational mapping tool). It is small and focused on doing one job, to help you create parameterized queries. One of its unique and killer features is that it can help you create any combination of filter queries from a single complex query based on the parameters used.

My goal with this podcast is to introduce you to SQLBits and provide some insight as to why it could be a valuable tool in your Node.js toolbox.

Episode Info

• Episode: CW 003


• Published: February 17th, 2014


• Tags: nodejs, database


• Duration: 17:17

Episode Notes

Background - SQL Builder vs ORM

What is a SQL builder and how does that compare to an ORM (Object Relational Mapper)?

A SQL builder like SQLBits is designed to help you to build safe parameterized SQL with a minimal abstraction layer that still allows you to write optimized SQL queries. Using parameters is important in create SQL to prevent SQL injection attacks where users enter malicious data into forms to gain access to your database. SQL builders help you to use parameters and avoid these attacks.

Having used Hibernate for Java and Active Record with Rails (a couple of popular Object Relational Mappers, I’ve seen what a ORM’s can do and they are nice when they do what you want, but all to often once you get into more advanced uses, they tend to get in your way and you are fighting with the tool to try to get it to generate the SQL you want.

So a SQL builder which allows you to create just the right queries but helps you deal with parameterization and tokens, provides a great balance. It stays out of your way and just helps with the bookkeeping. The sql it generates and the parameter array can be used directly with your database module like the pg Postgres client.

SQLBits - Node.js SQL builder

I reviewed a bunch of Node.js SQL builders listed in NPM and I was not happy with any of them until I found SQLBits.

Too many of them seemed complicated or too integrated bordering on ORM capabilities. I wanted something that was simple and just server a single purpose to help me build good parameterized queries. I didn’t want it to run my queries or perform schema manipulation. I don’t want a complete new DSL (domain specific language) or API that I need to learn. SQL is already a DSL that does that just fine, but it can get tricky to manage as queries get complex.

SQLBits is a simple tool which helps you generate SQL but stays out of your way. You can create custom tuned SQL which pulls back only what is needed over the wire and have it manage the parameters and tokens.

SQLBits Install

npm install sqlbits --save  # install and add to package.json

Simple example

var bits = require('sqlbits');
var SQL = bits.SQL;
var $ = bits.$;
var AND = bits.AND;
var params = { max_price: 10.00, clearance: true };
var query =
  SQL('select name, desc from widgets')
   .WHERE('price < ', $(params.max_price), AND,
          'clearance = ', $(params.clearance))
   .ORDERBY('name asc');

// sql: select name, desc from widgets WHERE(price < $1 AND clearance = $2) ORDER BY name asc
console.log('sql: ', query.sql);
console.log('params: ', query.params); // params: [ 10, true ]

Additional criteria

You can add in additional filter criteria with:

• AND - bits.AND


• OR - bits.OR


• BETWEEN - bits.BETWEEN (discussed in depth later)


• .IN(array)


• $ - bits.$ - used to specify that something is a parameter $(params.foo)

Joins

With relational data, you will often need to join tables together, and to maintain good performance these joins may need to be optimized. SQLBits allows you to perform any joins you need (left, right, inner, outer) just like you would normally with raw SQL.

var bits = require('sqlbits');
var SQL = bits.SQL;
var query =
  SQL('select w.name, c.type from widgets w ' +
      'join category c on w.categoryID = c.categoryID')
    .ORDERBY('w.name asc');

// OR

var query =
  SQL('select w.name, c.type from widgets w')
    ._('join category c on w.categoryID = c.categoryID')
    .ORDERBY('w.name asc');

// sql: select w.name, c.type from widgets w join category c on w.categoryID = c.categoryID ORDER BY w.name asc
console.log('sql: ', query.sql);

The ._() is another way to concatenate sql together.

Filters driven by defined parameters

If you have ever had to create SQL with filter clauses that was driven by user input, you should really appreciate this next feature.

Let’s say for instance that you have a report or catalog that you want to allow the user to provide filter criteria to limit by.

• If the user doesn’t enter any criteria then return the whole list unfiltered.


• If they add a minimum price use that in the filter


• If they add a maximum price limit the rows with that criteria


• If they include a clearance boolean, use that in the criteria

Even in this simple example, there are many combinations of possible filters depending on whether the user enters any of the filter data points.

If you were building this SQL by hand, typically you would create a series of if statements to conditionally include the filter, and you would build up an array of params which you have to manually keep track of.

However with SQLBits this is child’s play. You only have to create one complex query that includes all the possible filter criteria, and SQLBits will eliminate the parts where the parameters involved are undefined.

Also by using the BETWEEN command, SQLBits handles all four scenarios automatically:

• min and max are defined: price BETWEEN $1 AND $2


• only min is defined: price >= $1


• only max is defined: price <= $1


• neither min or max is defined: the criteria is eliminated

var bits = require('sqlbits');
var SQL = bits.SQL;
var $ = bits.$;
var AND = bits.AND;
var BETWEEN = bits.BETWEEN;

function queryByParam(param