CodeWinds - Leading edge web developer news and training | javascript / React.js / Node.js / HTML5 / web development - Jeff Barczewski - 003 SQLBits - lightweight SQL builder for Node.js for use with Postgres or other ANSI SQL databases

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

02/17/14 • 17 min

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.

Episode Info
Episode Notes
Quick Reference
Resources
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...

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 Notes
Quick Reference
Resources
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)?

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.

SQLBits Install

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

Simple example

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...

Previous Episode

002 First look at Hapijs 2.0, the Node.js web framework from Walmart Labs

This is a review of the Hapi 2.0 functionality unveiled last Monday, Jan 27, 2014 at &yet. The event was live streamed so I was able to watch and take notes. Eran Hammer @eranhammer, a Walmart labs engineer and lead developer for Hapi shared a top to bottom feature discussion including the new changes for version 2. Eran’s Slides

My goal with this podcast is to mention some of the key changes with Hapi 2 and how you would leverage them in your code.

This review and Eran’s presentation are done such that you do not need have a familiarity with Hapi to get value, new users should come away with some understanding of Hapi and how it can be useful for building robust web applications.

Episode Info
Episode Notes
Major breaking changes
Resources
Summary

Episode Info

Episode: CW 002
Published: February 1st, 2014
Tags: nodejs, hapijs
Duration: 21:46

Episode Notes

Background

show notes http://codewinds.com/podcast/002.html
I have a link to the original live stream, the content starts at about 24 minutes in, so the URL in the show notes jumps directly to that spot.
Hapi is the awesome open source web framework created by Walmart Labs.
The Hapi framework grew out of years of development first at Yahoo as Sled, later renamed Postmile, then at Walmart Labs.
Initially Eran tried to build on Express a popular web framework, but found issues with plugin order, undocumented dependencies, fragility with large team use.
Hapi was developed by Walmart Labs for their mobile division
Hapi is built as a scalable web framework that they could use to evolve their system over time.
Initiallly used to reverse proxy or pass through requests to their existing backend written in java or other languages.
Then decorate, batch, and begin to replace legacy code with new code in Node.js
It ran 100% of their mobile traffic starting with Black Friday and proved its stability running at about 1-2% cpu on 50 servers throughout the highest load. Lasagna graphs flatlines for cpu and memory use
Walmart starting to replace more of its legacy code with Hapi and even now part of walmart.com
Hapi has great REST API support as well as infrastructure for building web apps or even serving static content
Just configure the routes and you are ready, no extra plugins are necessary for the most common functionality
Liked that Eran provides some quick commentary about features and how they evolved
Hapi 2.0 isn’t a massive increase in new functionality, in fact it is more about taking out the cruft, eliminating the redundant ways to do things. Names that evolved over time, picking the best and cleaning up the others.
This strategy shows the wisdom and experience of its architect, to resi the urge to add features by first solidifying the base.

Routes, Caching, State

Route handler(request, reply) signature changed
server.route({}), can keep routes in one place while config and handler in separate module. validation is configurable, state (cookies)
server.table() gives you the active routing table
Hapi is a configuration centric framework, set js properties, then it tries to stay out of your way.
Hapi is written so the meat of your web application can stay pure, you don’t have to deal with the HTTP mechanisms unless you need to for a special case. This leads to clean code which is more focused on your business logic and less on how to deliver via HTTP.
Helpers can abstract out functionality used in many places like looking up a user profile or users shopping cart
Caching is changed in 2.0, dropping support for full page caching. You can still configure the client cache headers and expiry but the team removed the ...

Next Episode

004 Pete Hunt, Software Engineer for Facebook, discussing Facebook's open source js UI framework, React

In this episode, Jeff interviews Pete Hunt, a software engineer with Facebook about Facebook’s hot new open source js UI framework, React. React is a fresh look at how to build UI’s, attempting to improve on the limitations of the past. React gets some of its inspiration from how game engines acehieve awesome performance in their rendering pipeline.

React can be used in the browser and on the server with node.js so you can have single page apps that are fully indexable by the search engines and have instant load characteristics. React has pluggable backends so it can be used to target the DOM, HTML, canvas, SVG, and other formats.

The ideas and concepts behind React are very compelling, I encourage you to give it a look.

Episode Info
Episode Notes
1. Interview timeline
Notable users of React
Resources

Episode Info

Episode: CW 004
Published: March 17th, 2014
Tags: js, nodejs,UI,framework
Duration: 42:17
Updated: 2014-05-01 Added links to great articles by Reto Schläpfer

Episode Notes

Interview timeline

2:08 - What is React and why might people want to use it?
3:08 - The symbiotic relationship of ClojureScript and React
4:54 - The history of React and why it was created
9:43 - Updating web page with React without using data binding, a better approach inspired by game engines
13:11 - Using the virtual DOM to change the browser DOM
13:57 - Programming with React, render target HTML, canvas, other. Javascript is reference implementation of React. Node.js, ClojureScript, other environments.
16:45 - Working with designers. Contrasted with Ember and AngularJS. React encourages building a component architecture.
21:45 - JSX Compiler bridging HTML and React javascript
23:50 - Autobuilding JSX and in browser tools for React
24:50 - Tips and tricks to working with React, suggestions for getting started with React
27:17 - Rendering HTML on the server with Node.js. Pluggable React rendering backends (DOM, HTML, canvas, mixed mode static HTML + background JS binding). Better single page app performance
29:20 - React evolved through survival of the fittest at Facebook
30:15 - Ideas for having state on server and client, possibilities for using React over web sockets.
32:05 - React-multiuser - distributed shared mutable state using Firebase
33:03 - Better debugging with React using the state transitions, rewinding the state to replay the events
34:08 - Differences from Web Components
34:25 - Notable companies using React - Khan Academy, Oscars (Neartime.com), Largest bank in Russia, Facebook, Instagram
35:16 - Could a React backend plugin be created to target PDF?
36:30 - Future of React, what’s next?
39:38 - Contributing and getting help

Notable users of React

Instagram.com is 100% built on React, both public site and internal tools.
Facebook.com’s commenting interface, business management tools, Lookback video editor, page insights, and most, if not all, new JS development.
Khan Academy uses React for most new JS development.
Shirtstarter is 100% built on React.
Sberbank, Russia’s number one bank, is built with React.
The New York Times’s 2014 Red Carpet Project is built with React.

Resources

Links from Show

Online Training Courses

CodeWinds React.js Fundamentals - The premier online course and community where you learn React.js by pragmatically building a real web app while applying TDD/BDD principles

“Highly recommended!”
- Kevin Old, Senior Software Engineer “Look no further, the React.js Fundamenta...