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

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)
• $ - bit...