Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: master
Fetching contributors…

Octocat-spinner-32-eaf2f5

Cannot retrieve contributors at this time

file 124 lines (93 sloc) 2.4 kb

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
Something went wrong with that request. Please try again.