zsql

SQL generator for node.js modeled after Zend_Db_Select. Intended for use with easy-mysql

Overview

The query generation is separated into two parts: query and params. You can access the string query and params array for all objects using Query.toString() and Query.params()

The Query.all() function returns an array of the query and parameters, and optionally a callback. Typically you would use easy-mysql like so:

zsql.select(easyMySQL)
    .from('tableName')
    .where('columnName', 'value')
    .limit(100)
    .getAll(function(err, results) {
      if( err ) throw err;
      doSomething(results);
    });

The getAll/getOne/execute functions are just proxies for easy-mysql:

this._mysql.execute.apply(this._mysql, this.all(callback));

Below is provided various examples and the resultant SQL and parameters that would be passed into easy-mysql.

Examples

Select

zsql.select()
    .from('tableName')
    .where('columnName', 1)
    .where('columnName2 > ?', 2)
    .whereIn('columnName3', [3, 4])
    .order('columnName4', 'ASC')
    .limit(5)
    .offset(20)
    .all();

[ 'SELECT * FROM `tableName` WHERE `columnName` = ? && columnName2 > ? && `columnName3` IN (?, ?) ORDER BY `columnName4` ASC LIMIT ?, ?',
  [ 1,
    2,
    3,
    4,
    20,
    5 ] ]

Insert

zsql.insert()
    .into('tableName')
    .value('columnName', 1)
    .value('columnName2', zsql.expr('NOW()'))
    .all();

[ 'INSERT INTO `tableName` (`columnName`, `columnName2`) VALUES (?, NOW())',
  [ 1 ] ]

Delete

zsql.del()
    .from('tableName')
    .where('columnName', 112358)
    .limit(1)
    .all();

[ 'DELETE FROM `tableName` WHERE `columnName` = ? LIMIT ?',
  [ 112358, 1 ] ]

Update

zsql.update()
    .table('tableName')
    .set('columnName1', 3.14)
    .set('columnName2', zsql.expr('columnName2 + 1'))
    .set('columnName3', 'value')
    .where('columnName4', 159)
    .limit(2)
    .all();

[ 'UPDATE `tableName` SET `columnName1` = ? , `columnName2` = columnName2 + 1 , `columnName3` = ? WHERE `columnName4` = ? LIMIT ?',
  [ 3.14, 'value', 159, 2 ] ]

Todo

• escape/interpolate support
• node-mysql support