JS library for converting MongoDB-like selectors to SQL

examples.js

// Basic usage
mongo2sql({ 'a': 'b', 'c': 'd' });
"[a] = 'b' AND [c] = 'd'"

mongo2sql({ 'a': { $gt: 123 }, 'c': 'd' });
"[a] > 123 AND [c] = 'd'"

mongo2sql({ $or: [{ 'a': 'b'}, { 'c': 'd' }] });
"( ( [a] = 'b' ) OR ( [c] = 'd' ) )"

mongo2sql({ 'a': { 'b': 'c' } });
"[a__b] = 'c'"

mongo2sql({ 'a': { 'b': { $lte: 123} } });
"[a__b] <= 123"

mongo2sql({ 'a': 'b', $or: [{ 'c': 'd' }, { 'e': 'f' }] });
"[a] = 'b' AND ( ( [c] = 'd' ) OR ( [e] = 'f' ) )"

mongo2sql({ 'a': { $or: [{ 'b': 'c' }, { 'd': 'e', 'f': 'g' }] } });
"( ( [a__b] = 'c' ) OR ( [a__d] = 'e' AND [a__f] = 'g' ) )"

// Parametric usage
mongo2sql_parametric({ 'a': 'b', 'c': 'd' });
[
	"[a] = ? AND [c] = ?",
	[ "b", "d" ]
]

mongo2sql_parametric({ 'a': 'b', $or: [{ 'c': 'd' }, { 'e': 'f' }] });
[
	"[a] = ? AND ( ( [c] = ? ) OR ( [e] = ? ) )",
	[ "b", "d", "f" ]
]


// In practice, the whole query could be formed like this:

// Basic usage:
"SELECT * FROM [table] WHERE " + mongo2sql({ 'a': 'b' }) + " ORDER BY [a] DESC"

// Parametric usage:
var cond = mongo2sql_parametric({ 'a': 'b' });
// tx is a Web SQL Transaction object
tx.executeSql(
	"SELECT * FROM [table] WHERE " + cond[0] + " ORDER BY [a] DESC",
	cond[1]
);

mongo2sql.js

goog.provide('mongo2sql');

goog.require('goog.object');


/**
 * Separator of a namespace and a keyname within each level (ns-SEP-keyname)
 * For instance if the separator were set to "__" the column name for a field
 * named "address:city" in the code would be "address__city".
 * @type {string}
 */
mongo2sql.NAMESPACE_SEPARATOR = '__';

/**
 * Separator of levels (level-SEP-level)
 * For instance if the separator were set to "___" the column name
 * for a structure named "parent.address:city" in the code would be
 * "parent___address__city".
 * @type {string}
 */
mongo2sql.LEVEL_SEPARATOR = '___';

/**
 * Regular expression for matching valid column names
 * If an invalid column name results from the input keys, an Error is thrown.
 * @type {RegExp}
 */
mongo2sql.VALID_COLUMN_NAME = /^(\w+|(lower|upper)\(\[\w+\]\))$/;

/**
 * Returns a string that can be used as a WHERE clause in SQL queries and its
 *   parameters. The string features "?" instead of each parameter to prevent
 *   SQL injection by letting the native codebase handle the substitution.
 * @param {!Object} selector
 * @param {string=} prefix Key prefix
 *   Used mostly internally for nested selectors
 * @return {{sql: string, params: Array}}
 */
mongo2sql.stringify = function (selector, prefix) {
	prefix = prefix ? prefix + mongo2sql.LEVEL_SEPARATOR : '';

	var chunks = [];
	var params = [];

	goog.object.getKeys(selector).forEach(function (key) {
		var value = selector[key];

		if (key.charAt(0) !== '$') {
			key = key.replace(/:/g, mongo2sql.NAMESPACE_SEPARATOR);
			key = key.replace(/\./g, mongo2sql.LEVEL_SEPARATOR);
			key = prefix + key;
			if (!mongo2sql.VALID_COLUMN_NAME.test(key)) {
				throw new Error('Invalid column name ' + key);
			}

			if (typeof value !== 'object') {
				chunks.push('AND ' + mongo2sql.normalizeKey_(key) + ' = ?');
				params.push(value);
			} else {
				goog.object.getKeys(value).forEach(function (sub_key) {
					var sub_value = value[sub_key];
					var sub;
					if (sub_key.charAt(0) !== '$') {
						sub_key = key + mongo2sql.LEVEL_SEPARATOR + sub_key;
						// { key: value = { sub_key: sub_value }}
						if (typeof sub_value === 'object') {
							sub = mongo2sql.stringify(value, key);
							chunks.push('AND', sub.sql);
							params = params.concat(sub.params);
						} else {
							chunks.push('AND ' + mongo2sql.normalizeKey_(sub_key) + ' = ?');
							params.push(sub_value);
						}
					} else {
						// { key: value = { $modifier: value }}
						sub = mongo2sql.stringifyModifier_(key, sub_key.substr(1), sub_value);
						chunks.push('AND', sub.sql);
						params = params.concat(sub.params);
					}
				});
			}
		} else {
			var modifier = key.substr(1);
			switch (modifier) {
			case 'or':
				// { $or: value = [ selector, selector ]}
				chunks.push('AND (');
				value.forEach(function (option, i) {
					var sub = mongo2sql.stringify(option, prefix);
					if (i) {
						chunks.push('OR');
					}
					chunks.push('(', sub.sql, ')');
					params = params.concat(sub.params);
				});
				chunks.push(')');
				break;
			default:
				throw new Error('Invalid modifier $' + modifier);
			}
		}
	});

	return {
		sql: chunks.join(' ').substr(4),
		params: params
	};
};

/**
 * Returns the key in a form that can be safely used as a column name
 * @param {string} key The key to normalize
 * @return {string}
 */
mongo2sql.normalizeKey_ = function (key) {
	return (key.search(/\[/) !== -1) ? key : '[' + key + ']';
};

/**
 * Returns a part of a WHERE clause
 * @param {string} key The name of the column
 * @param {string} modifier The modifier to apply
 * @return {{sql: string, params: Array}}
 */
mongo2sql.stringifyModifier_ = function (key, modifier, value) {
	key = mongo2sql.normalizeKey_(key);

	var chunks = [ key ];
	var params = [];

	switch (modifier) {
	case 'gt':
		chunks.push('>', '?');
		params.push(value);
		break;
	case 'gte':
		chunks.push('>=', '?');
		params.push(value);
		break;
	case 'lt':
		chunks.push('<', '?');
		params.push(value);
		break;
	case 'lte':
		chunks.push('<=', '?');
		params.push(value);
		break;
	case 'ne':
		chunks.push('!=', '?');
		params.push(value);
		break;
	case 'in':
		if (!value.length) {
			throw 'Invalid state: No items for the IN operator';
		}
		chunks.push('IN (', new Array(value.length + 1).join(', '), ')');
		params = params.concat(value);
		break;
	case 'nin':
		if (!value[modifier].length) {
			throw 'Invalid state: No items for the NOT IN operator';
		}
		chunks.push('NOT IN (', new Array(value.length + 1).join(', '), ')');
		params = params.concat(value);
		break;
	case 'exists':
		chunks.push('IS', value ? 'NOT NULL' : 'NULL');
		break;
	case 'search':
		chunks = [];
		value.forEach(function (item, i) {
			chunks.push(i ? 'AND (' : '(');
			chunks.push(key, "LIKE '" + item.replace("'", "\\'") + "%'");
			chunks.push('OR')
			chunks.push(key, "LIKE '% " + item.replace("'", "\\'") + "%'");
			chunks.push(')');
		});
		break;
	case 'or':
		// { key: { $or: value = [ selector, selector ]}}
		chunks.push('AND (');
		value.forEach(function (option, i) {
			var sub = mongo2sql.stringify(option, key);
			if (i) {
				chunks.push('OR');
			}
			chunks.push('(', sub.sql, ')');
			params = params.concat(sub.params);
		});
		chunks.push(')');
		break;
	default:
		throw new Error('Invalid modifier $' + modifier);
	}

	return {
		sql: chunks.join(' '),
		params: params
	};
};