JavaScript Chaining for SQL

Chaining methods, or cascading, is a design technique that results in cleaner, more readable code. Popular libraries such sas jQuery, Mongo, and D3 utilize chaining when performing operations on the same objects. You can chain actions on DOM elements, documents, and charts. It can also be used for sequencing. You may have used libraries that chain function().then(). Testing libraries like Mocha and Chai use it for syntactic sugar: expect(result).to.be.a.function. The 'to.be.a' serves no real purpose.

Chaining is also useful for modularity. This is why chaining is a great technique to use when creating an ORM for SQL. SQL as a language is modular.

SELECT books FROM shelf;  
// Works by itself 

SELECT books FROM shelf  
WHERE author = 'Stephen King';

SELECT books FROM shelf  
LIMIT 1; 

SELECT books FROM shelf  
LEFT INNER JOIN dvdrack ON shelf.title = dvdrack.title;

SELECT books FROM shelf  
WHERE author = 'Stephen King'  
LIMIT 1;

SELECT books FROM shelf  
LEFT INNER JOIN dvdrack ON shelf.title = dvdrack.title  
WHERE author = 'Stephen King'  
LIMIT 1;  
// These also work

In this example, the chain should start with a select module and end with a fetch module that returns the requested data. The functions in between are all option, and could be swapped around as long as there's only one of each and they are compiled in the right order for the fetch function.

Chaining is accomplished by returning the object at the end of each function. It might look something like this.

var dbSearch = function(table, fields) {  
    this.command = 'SELECT ' + fields + ' FROM ' + table + ' ';
    return this;
};

dbSearch.prototype.where = function(field, value) {  
    this.command += field + ' = ' + value;
    return this;
};

dbSearch.prototype.fetch = function() {  
    // connect to database with this.command
    // return data
};

dbSearch(shelf, books).where(author, Stephen King).fetch();  
// expected to return all books from shelf where author is 
// Stephen King

This has advantages over nested objects and arrays, which Mongo still has even though they are chaining. It also has advantages over writing direct SQL statements because it protects you from injected code. It's also easy for someone not familiar with SQL to use.

Ruby on Raise has Active Record, a SQL ORM that does exactly this. I'm currently working on a team to create a JavaScript chaining ORM. Stay tuned for status updates.