003 SQLBits - lightweight SQL builder for Node.js for use with Postgres or other ANSI SQL databases

CodeWinds - Leading edge web developer news and training | javascript / React.js / Node.js / HTML5 / web development - Jeff Barczewski

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.

1. Episode Info
2. Episode Notes
   1. Background - SQL Builder vs ORM
   2. SQLBits - Node.js SQL builder
   3. SQLBits Install
   4. Simple example
   5. Additional criteria
   6. Joins
   7. Filters driven by defined parameters
   8. Inserts
   9. Updates
   10. Deletes
3. Quick Reference
4. Resources
5. Summary

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(params) { var query = SQL('select name, desc from widgets') .WHERE('price', BETWEEN(params.min, params.max), AND, 'clearance = ', $(params.clearance)) .ORDERBY('name asc'); return query; } var query = queryByParam({ min: 2.00, max: 10.00, clearance: true }); // sql: select name, desc from widgets WHERE(price BETWEEN $1 AND $2 AND clearance = $3) ORDER BY name asc console.log('sql: ', query.sql); console.log('params: ', query.params); // params: [ 2, 10, true ] var query = queryByParam({ min: 2.00 }); // sql: select name, desc from widgets WHERE price >=$1 ORDER BY name asc console.log('sql: ', query.sql); console.log('params: ', query.params); // params: [ 2 ]; var query = queryByParam({ max: 10.00 }); // sql: select name, desc from widgets WHERE price <=$1 ORDER BY name asc console.log('sql: ', query.sql); console.log('params: ', query.params); // params: [ 10 ] var query = queryByParam({ min: 2.00, max: 10.00 }); // sql: select name, desc from widgets WHERE price BETWEEN $1 AND $2 ORDER BY name asc console.log('sql: ', query.sql); console.log('params: ', query.params); // params: [ 2, 10 ] var query = queryByParam({ min: 2.00, clearance: true }); // 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: [ 2, true ]

In my opinion, this is one of the killer features of SQLBits. You create a single complex query with all the possible filters and then just provide a params object to SQLBits with a parameters object having the keys that the user wants to filter by, and it provides the customized query.

This eliminates all the conditional logic which is hard to test and debug when it fails and you are left with a single complex query to maintain.

Inserts

Inserting data into your tables by key is as simple as providing an object to SQLBits and it will add all the keys and values.

var bits = require('sqlbits'); var INSERT = bits.INSERT; function insertWidget(obj) { return INSERT.INTO('widgets', obj); } var query = insertWidget({ name: 'foo', desc: 'Foo man', price: 23.45 }); //INSERT INTO widgets (name,desc,price) SELECT $1,$2,$3 console.log('sql: ', query.sql); console.log('params: ', query.params); // params: [ 'foo', 'Foo man', 23.45 ] var query = insertWidget({ name: 'bar', price: 11.23 }); //INSERT INTO widgets (name,price) SELECT $1,$2 console.log('sql: ', query.sql); console.log('params: ', query.params); // params: [ 'bar', 11.23 ] var query = insertWidget({ name: 'bar' }); // INSERT INTO widgets (name) SELECT $1 console.log('sql: ', query.sql); console.log('params: ', query.params); // params: ['bar']

Updates

Updating data works just like the inserts but uses the UPDATE and .SET()

var bits = require('sqlbits'); var UPDATE = bits.UPDATE; function updateWidget(id, updateValues) { return UPDATE('widget') .SET(updateValues) ._('where id=', $(id)); } var query = updateWidget(1, { name: 'bar' }); // sql: UPDATE widget SET name=$1 where id=$2 console.log('sql: ', query.sql); console.log('params: ', query.params); // [ 'bar', 1 ] var query = updateWidget(2, { name: 'cat', price: 1.12 }); // UPDATE widget SET name=$1,price=$2 where id=$3 console.log('sql: ', query.sql); console.log('params: ', query.params); // [ 'cat', 1.12, 2 ]

Note: with update’s we don’t want to have the WHERE clause collapse so it is safer to use a non-conditional string with ._('where id = ', $(obj.id)) to there is no chance of the filter clause being collapsed.

Deletes

Finally deletes use DELETE.FROM to specify a deletion.

var bits = require('sqlbits'); var DELETE = bits.DELETE; var params = { id: 10 }; var query = DELETE.FROM('widgets') ._('where id=', $(params.id)); // sql: DELETE FROM widgets where id=$1 console.log('sql: ', query.sql); console.log('params: ', query.params); // [ 10 ]

Note: with delete’s we don’t want to have the WHERE clause collapse so it is safer to use a non-conditional string with ._('where id = ', $(obj.id)) to there is no chance of the filter clause being collapsed.

Quick Reference

var bits = require('sqlbits'); var SQL = bits.SQL; // SQL('select name, desc from widgets') var $ = bits.$; // SQL('...').WHERE('id=', $(obj.id)); var AND = bits.AND; // .WHERE('id=', $(obj.id), AND, 'type=', $(obj.type)); var OR = bits.OR; // .WHERE('type=', $(obj.type), OR, 'color=', $(obj.color)); var IN = bits.IN; // .WHERE('id', IN(array)); var BETWEEN = bits.BETWEEN; // .WHERE(price, BETWEEN($(obj.min), $(obj.max)); var INSERT = bits.INSERT; // INSERT.INTO('widgets', obj) var UPDATE = bits.UPDATE; // UPDATE('widgets').SET(updateValues)._('where id = ', $(obj.id)) var DELETE = bits.DELETE; // DELETE.FROM('widgets')._('where id = ', $(obj.id))

Methods to help chain

• ._() - appends to the SQL after adding a space
• .WHERE(...) - add a where clause which checks the params that are defined to conditionally add the filter criteria
• .SET(updateValues) - used with UPDATE to provide the field names and values to update
• .FROM('mytable') - used with DELETE to specify table
• .ORDERBY('name asc, qty desc') - add sorting criteria
• .LIMIT(number) - limit the max rows returned, defaults to 0
• .OFFSET(number) - start rows at offset (for paging), defaults to 0

Accessor properties

• .sql - get the parameterized SQL string
• .params - get the array of parameters to use with the SQL

Resources

• SQLBits - GitHub repo and project readme. This brief page provides examples of how to use SQLBits and is a great place to get started

Summary

SQLBits is a nice lightweight way to create safe parameterized SQL for Postgres or other ANSI SQL databases. It focuses on simply helping you build the SQL by managing the parameterization process, but you can still use the full power of SQL to create optimized queries and even special features that a particular SQL engine provides.

One of its killer features is the ability to create custom filter queries based on whether parameters are defined, so you can create one complex query which is relaxed based on the parameters defined at runtime.

Check out SQLBits, and let me know what you think. You can leave a comment in the discussion secion of this episode. I find it to be a great tool to help me generate SQL for Postgres and hope you will too.