SQL builder sketches for node.js and node-postgres

update.js

/*jshint node:true globalstrict:true*/
"use strict";

var assert = require('assert');

//
// Convenience function for building a simple UPDATE/SET/WHERE/RETURNING statement.
//
// e.g.
//
//   var sql = UPDATE('users', { email: 'foo@example.com' }, { id: 1 }, '*');
//   db.query(sql, ...);
//
// or:
//
//   var sql = UPDATE({ 
//       table: 'users', 
//       set: { email: 'foo@example.com' }, 
//       where: { id: 1 }, 
//       returning: '*' 
//   });
//   db.query(sql, ...);
//
function UPDATE(table, set, where, returning) {

    // also allow named option style:
    if (arguments.length === 1) {
        var options = table;
        table = options.table;
        set = options.set;
        where = options.where;
        returning = options.returning;
    }

    // paranoid
    assert(table); 
    assert(set); 

    var statement = [ 'UPDATE', table ],
        values = [];

    // build set clause:
    var set_columns = Object.keys(set),
        set_clause = set_columns.map(column_placeholders(1)),
        set_values = set_columns.map(function(c){ return set[c]; });

    values.push.apply(values, set_values);
    statement.push('SET', set_clause);

    // build where clause
    if (where) {
        var where_columns = Object.keys(where),
            where_clause = where_columns.map(column_placeholders(set_columns.length)),
            where_values = where_columns.map(function(c){ return where[c]; });

        // yummy side-effects
        values.push.apply(values, where_values);

        statement.push('WHERE', where_clause);
    }

    if (returning) {
        // returning can be null or an array of columns or '*'
        if (Array.isArray(returning)) {
            returning = returning.join(',');
        }
        statement.push('RETURNING', returning);
    }

    return {
        text: statement.join(' '),
        values: values
    };
}

// helper for Array.map in UPDATE:
function column_placeholders(starting_at) {
    return function(c,i) {
        return c + ' = $' + (i+starting_at);
    };
}

module.exports = {
    UPDATE: UPDATE
};

update_chained.js

/*jshint node:true globalstrict:true*/
"use strict";

var assert = require('assert');

//
// Convenience function for building a simple UPDATE/SET/WHERE/RETURNING statement.
//
// e.g.
//
//   var sql = UPDATE('users').SET({ email: 'foo@example.com' }).WHERE({ id: 1 }).RETURNING('*');
//   db.query(sql, ...);
//
function UPDATE(table) {
    if (!(this instanceof UPDATE)) {
        return new UPDATE(table);
    }
    this.text = 'UPDATE ' + table;
    this.values = [];
}

UPDATE.prototype = {
    SET: function(set) {
        // build set clause:
        var set_columns = Object.keys(set),
            set_clause = set_columns.map(column_placeholders(1)),
            set_values = set_columns.map(function(c){ return set[c]; });
        this.text += ' SET ' + set_clause.join(',');
        this.values.push.apply(this.values, set_values);
        this.has_set = true;
        return this;
    },
    WHERE: function(where,conjunction) {
        assert(this.has_set);
        if (conjunction === undefined) {
            conjunction = 'AND';
        }
        var where_columns = Object.keys(where),
            where_clause = where_columns.map(column_placeholders(1+this.values.length)),
            where_values = where_columns.map(function(c){ return where[c]; });
        this.text += ' WHERE ' + where_clause.join(' ' + conjunction + ' ');
        this.values.push.apply(this.values, where_values);
        return this;
    },
    RETURNING: function(returning) {
        assert(this.has_set);
        // returning can be an array of columns or '*'
        if (Array.isArray(returning)) {
            returning = returning.join(',');
        }
        this.text += ' RETURNING ' + returning;
        return this;
    }
};

// helper for Array.map in UPDATE:
function column_placeholders(starting_at) {
    return function(c,i) {
        return c + ' = $' + (i+starting_at);
    };
}

module.exports = {
    UPDATE: UPDATE
};

update_chained_no_proto.js

//
// just a sketch, left here for posterity
// not tested :-p
//
function UPDATE(table) {
    var query = {
        text: 'UPDATE ' + table,
        values: []
    };
    return {
        SET: function(set) {
            // build set clause:
            var set_columns = Object.keys(set),
                set_clause = set_columns.map(column_placeholders(1)),
                set_values = set_columns.map(function(c){ return set[c]; });
            query.text += ' SET ' + set_clause;
            query.values.push.apply(query.values, set_values);
            return {
                WHERE: function(where) {
                    var where_columns = Object.keys(where),
                        where_clause = where_columns.map(column_placeholders(query.values.length)),
                        where_values = where_columns.map(function(c){ return where[c]; });
                    query.text += ' WHERE ' + where_clause;
                    query.values.push.apply(query.values, where_values);
                    return {
                        RETURNING: function(returning) {
                            // returning can be an array of columns or '*'
                            if (Array.isArray(returning)) {
                                returning = returning.join(',');
                            }
                            query.text += ' RETURNING ' + returning;
                            return {
                                query: query
                            };
                        }, // RETURNING
                        query: query
                    };
                }, // WHERE
                query: query
            };
        }, // SET
        query: query
    };
} // UPDATE

This became https://github.com/bloomtime/bloom-sql-js