sqlish

A SQL generator for for NodeJS, Mongo Shell and web browsers.

Overview

JavaScript's ability to chaining function calls provides an nice semantic for generating SQL statements. sqlish (Californian pronunciation: sk wil' ish) takes this approach and attempts to be portable as well as simple to use.

Advantages?

There are several advantages two taking this approach

• lower cognitive noise (you're programming in one language not two)
• your JS tools (e.g. jslint) now cover your assembly of SQL statements
• assembling complex SQL is just a matter of chaining functions
• MongoDB like object literals for expressions
• sqlish is very small without dependancies
• it works as a NodeJS module
• it works as a loadable script in MongoDB's shell
• it works should work web browsers supporting SQLite

installation

To use with NodeJS install via npm.

    npm install sqlish

A NodeJS example

    var sqlish = require("sqlish"),
        dialect = sqlish.Dialect,
        Sql = new sqlish.Sqlish("MySQL 5.5"),
        message = {
            id: 0,
            name: "fred",
            email: "fred@gmail.com",
            messages: 'He Said, "Hello World"',
            sent: new Date("09/01/2012 14:15:00")
        };
 
    Sql.use_UTC = false;	
    
    // Output:
    // INSERT INTO messages (name, email, msg, sent) VALUES (
    //	"fred", "fred@example.com", "He siad, \"Hello World\"", 
    //	"2012-09-01 14:15:00");
    console.log(Sql.insert("test", message).toString());
    
    // Output:
    // SELECT id, name, email, msg, sent FROM messages 
    //	WHERE email LIKE "%@example.com";
    console.log(Sql.select(Object.keys(message))
            .from("messages")
            .where({email: {$like: "%@example.com"}}).toString());
    
    // Output:
    // REPLACE INTO messages (id, name, email, msg, sent) VALUES (
    //	10123, "George", "george@example.com", "He siad, \"Hello World\"", 
    //	"2012-07-01");
    message.id = 10123;
    message.name = "George";
    message.email = "george@example.com";
    message.sent = new Date("07/01/2012");
    console.log(Sql.replace("test", message).toString());

a word about sqlish's toString()

Normally you want SQL statements to end with a semi-colon and by default this is what the toString() at the end of the function chain will do. However their are cases where you may want to render complex SQL statements by parts. In that case you can overwrite the default semi-colon by passing the terminating string (including the empty string) as a paramater to toString().

    var sqlish = require("sqlish"),
        Sql = new sqlish.Sqlish();
    
    // No trailing semi-colon
    console.log(sql.select("count()").toString(""));
    // Trailing semi-colon
    console.log(sql.select("count()").toString());

Running the above will yeald something like-

    > console.log(sql.select("count()").toString(""));
    'SELECT count()'
    > console.log(sql.select("count()").toString());
    'SELECT count();'

If you would like to have some other delimiter used as the end of statement marker you can do so when overwriting Sql.eol at time of object creation or before calling toString().

    var sqlish = require("sqlish"),
        Sql = new sqlish.Sqlish();
        
    Sql.eol = ";\n\n";
    
    // No trailing semi-colon
    console.log(sql.select("count()").toString(""));
    // Trailing semi-colon with two new lines
    console.log(sql.select("count()").toString());

Would yeild something like-

    > console.log(sql.select("count()").toString(""));
    'SELECT count()'
    > console.log(sql.select("count()").toString());
    'SELECT count();'
    
    

MongoDB shell example

Copy sqlish.js and load-sqlish.js to your working directory. Then use MongoDB's shell's load() function to include it. See mongo-example.js:

    load("load-sqlish.js");
    sql = new Sql();
    // Output should look like:
    //  SELECT id, name, email, modified FROM myTable;
    print(sql.select(["id", "name", "email", "modified"]).from("myTable").toString());
    item = {id:1, name: "fred", email: "fred@example.com", modified: new Date("09/14/2012 10:21:14")};
    // Output should look like:
    //  REPLACE INTO myTable (id, name, email, modified) VALUES ("fred", "fred@example.com", "2012-09-14 10:21:14");
    print(sql.replace("myTable", item).toString());

Save the above example code as mongo-example.js. Run under MongoDB's shell-

    mongo mongo-example.js

Output should look something like-

    MongoDB shell version: 2.2.0
    connecting to: test
    SELECT id, name, email, modified FROM myTable;
    REPLACE INTO myTable (id, name, email, modified) VALUES (1, "fred", "fred@example.com", "2012-09-14 10:21:14");

Alternatives?

If you don't need web browser or MongoDB shell support two SQL generators at npmjs.org look very promising-

• bloom-sql
• sql-generator