npm install sql-query --save- MySQL
- PostgreSQL
- SQLite
- MSSQL
This module is used by ORM to build SQL queries in the different supported dialects. Sorry the API documentation is not complete. There are tests in ./test/integration that you can read.
#Usage var sql = require('sql-query'), sqlQuery = sql.Query();
##Create
var sqlCreate = sqlQuery.create(); sqlCreate .table('table1') .build() "CREATE TABLE 'table1'()" sqlCreate .table('table1') .field('id','id') .build() "CREATE TABLE 'table1'('id' INTEGER PRIMARY KEY AUTO_INCREMENT)" sqlCreate .table('table1') .fields({id: 'id', a_text: 'text'}) .build() "CREATE TABLE 'table1'('id' INTEGER PRIMARY KEY AUTO_INCREMENT,'a_text' TEXT)" sqlCreate .table('table1') .fields({id: 'id', a_num: 'int'}) .build() "CREATE TABLE 'table1'('id' INTEGER PRIMARY KEY AUTO_INCREMENT,'a_num' INTEGER)" sqlCreate .table('table1') .fields({id: 'id', a_num: 'float'}) .build() "CREATE TABLE 'table1'('id' INTEGER PRIMARY KEY AUTO_INCREMENT,'a_num' FLOAT(12,2))" sqlCreate .table('table1') .fields({id: 'id', a_bool: 'bool'}) .build() "CREATE TABLE 'table1'('id' INTEGER PRIMARY KEY AUTO_INCREMENT,'a_bool' TINYINT(1))" ##Select
var sqlSelect = sqlQuery.select(); sqlSelect .from('table1') .build(); "SELECT * FROM `table1`" sqlSelect .from('table1') .select('id', 'name') .build(); "SELECT `id`, `name` FROM `table1`" sqlSelect .from('table1') .select('id', 'name') .as('label') .build(); "SELECT `id`, `name` AS `label` FROM `table1`" sqlSelect .from('table1') .select('id', 'name') .select('title') .as('label') .build(); "SELECT `id`, `name`, `title` AS `label` FROM `table1`" sqlSelect .from('table1') .select('id', 'name') .as('label') .select('title') .build(); "SELECT `id`, `name` AS `label`, `title` FROM `table1`" sqlSelect .from('table1') .select([ 'id', 'name' ]) .build(); "SELECT `id`, `name` FROM `table1`" sqlSelect .from('table1') .select() .build(); "SELECT * FROM `table1`" sqlSelect .from('table1') .select(['abc','def',{a: 'ghi', sql: 'SOMEFUNC(ghi)' }]) .build(); "SELECT `abc`, `def`, (SOMEFUNC(ghi)) AS `ghi` FROM `table1`" sqlSelect .calculateFoundRows() .from('table1') .build(); "SELECT SQL_CALC_FOUND_ROWS * FROM `table1`" sqlSelect .calculateFoundRows() .from('table1') .select('id') .build(); "SELECT SQL_CALC_FOUND_ROWS `id` FROM `table1`" sqlSelect .from('table1') .select('id1', 'name') .from('table2', 'id2', 'id1') .select('id2') .build(); "SELECT `t1`.`id1`, `t1`.`name`, `t2`.`id2` FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect .from('table1') .select('id1') .from('table2', 'id2', 'id1',{joinType: 'left inner' }) .select('id2') .build(); "SELECT `t1`.`id1`, `t2`.`id2` FROM `table1` `t1` LEFT INNER JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect .from('table1') .select('id1', 'name') .from('table2', 'id2', 'table1', 'id1') .select('id2') .build(); "SELECT `t1`.`id1`, `t1`.`name`, `t2`.`id2` FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect .from('table1') .from('table2', 'id2', 'table1', 'id1') .count() .build(); "SELECT COUNT(*) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect .from('table1') .from('table2', 'id2', 'table1', 'id1') .count(null, 'c') .build(); "SELECT COUNT(*) AS `c` FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect .from('table1') .from('table2', 'id2', 'table1', 'id1') .count('id') .build(); "SELECT COUNT(`t2`.`id`) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect .from('table1') .count('id') .from('table2', 'id2', 'table1', 'id1') .count('id') .build(); "SELECT COUNT(`t1`.`id`), COUNT(`t2`.`id`) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect .from('table1') .from('table2', 'id2', 'table1', 'id1') .count('id') .count('col') .build(); "SELECT COUNT(`t2`.`id`), COUNT(`t2`.`col`) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect .from('table1') .from('table2', 'id2', 'table1', 'id1') .fun('AVG', 'col') .build(); "SELECT AVG(`t2`.`col`) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect .from('table1') .from('table2',['id2a', 'id2b'], 'table1', ['id1a', 'id1b']) .count('id') .build(); "SELECT COUNT(`t2`.`id`) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2a` = `t1`.`id1a` AND `t2`.`id2b` = `t1`.`id1b`" ##Where
var sqlSelect = sqlQuery.select(); sqlSelect .from('table1') .where() .build(); "SELECT * FROM `table1`" sqlSelect .from('table1') .where(null) .build(); "SELECT * FROM `table1`" sqlSelect .from('table1') .where({col: 1 }) .build(); "SELECT * FROM `table1` WHERE `col` = 1" sqlSelect .from('table1') .where({col: 0 }) .build(); "SELECT * FROM `table1` WHERE `col` = 0" sqlSelect .from('table1') .where({col: null }) .build(); "SELECT * FROM `table1` WHERE `col` IS NULL" sqlSelect .from('table1') .where({col: sql.eq(null) }) .build(); "SELECT * FROM `table1` WHERE `col` IS NULL" sqlSelect .from('table1') .where({col: sql.ne(null) }) .build(); "SELECT * FROM `table1` WHERE `col` IS NOT NULL" sqlSelect .from('table1') .where({col: undefined }) .build(); "SELECT * FROM `table1` WHERE `col` IS NULL" sqlSelect .from('table1') .where({col: false }) .build(); "SELECT * FROM `table1` WHERE `col` = false" sqlSelect .from('table1') .where({col: "" }) .build(); "SELECT * FROM `table1` WHERE `col` = ''" sqlSelect .from('table1') .where({col: true }) .build(); "SELECT * FROM `table1` WHERE `col` = true" sqlSelect .from('table1') .where({col: 'a' }) .build(); "SELECT * FROM `table1` WHERE `col` = 'a'" sqlSelect .from('table1') .where({col: 'a\'' }) .build(); "SELECT * FROM `table1` WHERE `col` = 'a\\''" sqlSelect .from('table1') .where({col: [ 1, 2, 3 ] }) .build(); "SELECT * FROM `table1` WHERE `col` IN (1, 2, 3)" sqlSelect .from('table1') .where({col: [] }) .build(); "SELECT * FROM `table1` WHERE FALSE" sqlSelect .from('table1') .where({col1: 1, col2: 2 }) .build(); "SELECT * FROM `table1` WHERE `col1` = 1 AND `col2` = 2" sqlSelect .from('table1') .where({col1: 1 },{col2: 2 }) .build(); "SELECT * FROM `table1` WHERE (`col1` = 1) AND (`col2` = 2)" sqlSelect .from('table1') .where({col: 1 }).where({col: 2 }) .build(); "SELECT * FROM `table1` WHERE (`col` = 1) AND (`col` = 2)" sqlSelect .from('table1') .where({col1: 1, col2: 2 }).where({col3: 3 }) .build(); "SELECT * FROM `table1` WHERE (`col1` = 1 AND `col2` = 2) AND (`col3` = 3)" sqlSelect .from('table1') .from('table2', 'id', 'id') .where('table1',{col: 1 }, 'table2',{col: 2 }) .build(); "SELECT * FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id` = `t1`.`id` WHERE (`t1`.`col` = 1) AND (`t2`.`col` = 2)" sqlSelect .from('table1') .from('table2', 'id', 'id') .where('table1',{col: 1 },{col: 2 }) .build(); "SELECT * FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id` = `t1`.`id` WHERE (`t1`.`col` = 1) AND (`col` = 2)" sqlSelect .from('table1') .where({col: sql.gt(1) }) .build(); "SELECT * FROM `table1` WHERE `col` > 1" sqlSelect .from('table1') .where({col: sql.gte(1) }) .build(); "SELECT * FROM `table1` WHERE `col` >= 1" sqlSelect .from('table1') .where({col: sql.lt(1) }) .build(); "SELECT * FROM `table1` WHERE `col` < 1" sqlSelect .from('table1') .where({col: sql.lte(1) }) .build(); "SELECT * FROM `table1` WHERE `col` <= 1" sqlSelect .from('table1') .where({col: sql.eq(1) }) .build(); "SELECT * FROM `table1` WHERE `col` = 1" sqlSelect .from('table1') .where({col: sql.ne(1) }) .build(); "SELECT * FROM `table1` WHERE `col` <> 1" sqlSelect .from('table1') .where({col: sql.between('a', 'b') }) .build(); "SELECT * FROM `table1` WHERE `col` BETWEEN 'a' AND 'b'" sqlSelect .from('table1') .where({col: sql.not_between('a', 'b') }) .build(); "SELECT * FROM `table1` WHERE `col` NOT BETWEEN 'a' AND 'b'" sqlSelect .from('table1') .where({col: sql.like('abc') }) .build(); "SELECT * FROM `table1` WHERE `col` LIKE 'abc'" sqlSelect .from('table1') .where({col: sql.not_like('abc') }) .build(); "SELECT * FROM `table1` WHERE `col` NOT LIKE 'abc'" sqlSelect .from('table1') .where({col: sql.not_in([ 1, 2, 3 ]) }) .build(); "SELECT * FROM `table1` WHERE `col` NOT IN (1, 2, 3)" sqlSelect .from('table1') .where({__sql: [["LOWER(`stuff`) LIKE 'peaches'"]] }) .build(); "SELECT * FROM `table1` WHERE LOWER(`stuff`) LIKE 'peaches'" sqlSelect .from('table1') .where({__sql: [["LOWER(`stuff`) LIKE ?", ['peaches']]] }) .build(); "SELECT * FROM `table1` WHERE LOWER(`stuff`) LIKE 'peaches'" sqlSelect .from('table1') .where({__sql: [["LOWER(`stuff`) LIKE ? AND `number` > ?", ['peaches', 12]]] }) .build(); "SELECT * FROM `table1` WHERE LOWER(`stuff`) LIKE 'peaches' AND `number` > 12" sqlSelect .from('table1') .where({__sql: [["LOWER(`stuff`) LIKE ? AND `number` == ?", ['peaches']]] }) .build(); "SELECT * FROM `table1` WHERE LOWER(`stuff`) LIKE 'peaches' AND `number` == NULL" ##Order
var sqlSelect = sqlQuery.select(); sqlSelect .from('table1') .order('col') .build(); "SELECT * FROM `table1` ORDER BY `col` ASC" sqlSelect .from('table1') .order('col', 'A') .build(); "SELECT * FROM `table1` ORDER BY `col` ASC" sqlSelect .from('table1') .order('col', 'Z') .build(); "SELECT * FROM `table1` ORDER BY `col` DESC" sqlSelect .from('table1') .order('col').order('col2', 'Z') .build(); "SELECT * FROM `table1` ORDER BY `col` ASC, `col2` DESC" sqlSelect .from('table1') .order('col', []) .build(); "SELECT * FROM `table1` ORDER BY col" sqlSelect .from('table1') .order('?? DESC', ['col']) .build(); "SELECT * FROM `table1` ORDER BY `col` DESC" sqlSelect .from('table1') .order('ST_Distance(??, ST_GeomFromText(?,4326))', ['geopoint', 'POINT(-68.3394 27.5578)']) .build(); "SELECT * FROM `table1` ORDER BY ST_Distance(`geopoint`, ST_GeomFromText('POINT(-68.3394 27.5578)',4326))" ##Limit
var sqlSelect = sqlQuery.select(); sqlSelect .from('table1') .limit(123) .build(); "SELECT * FROM `table1` LIMIT 123" sqlSelect .from('table1') .limit('123456789') .build(); "SELECT * FROM `table1` LIMIT 123456789" ##Select Function
var sqlSelect = sqlQuery.select(); sqlSelect .from('table1') .fun('myfun', 'col1') .build(); "SELECT MYFUN(`col1`) FROM `table1`" sqlSelect .from('table1') .fun('myfun', [ 'col1', 'col2']) .build(); "SELECT MYFUN(`col1`, `col2`) FROM `table1`" sqlSelect .from('table1') .fun('dbo.fnBalance', [ 80, null, null], 'balance') .build(); "SELECT DBO.FNBALANCE(80, NULL, NULL) AS `balance` FROM `table1`" sqlSelect .from('table1') .fun('myfun', [ 'col1', 'col2'], 'alias') .build(); "SELECT MYFUN(`col1`, `col2`) AS `alias` FROM `table1`" sqlSelect .from('table1') .fun('myfun', [ 'col1', sqlQuery.Text('col2') ], 'alias') .build(); "SELECT MYFUN(`col1`, 'col2') AS `alias` FROM `table1`" ##Insert
var sqlInsert = sqlQuery.insert(); sqlInsert .into('table1') .build(); "INSERT INTO `table1`" sqlInsert .into('table1') .set({}) .build(); "INSERT INTO `table1` VALUES()" sqlInsert .into('table1') .set({col: 1 }) .build(); "INSERT INTO `table1` (`col`) VALUES (1)" sqlInsert .into('table1') .set({col1: 1, col2: 'a' }) .build(); "INSERT INTO `table1` (`col1`, `col2`) VALUES (1, 'a')" ##Update
var sqlUpdate = sqlQuery.update() sqlUpdate .into('table1') .build(); "UPDATE `table1`" sqlUpdate .into('table1') .set({col: 1 }) .build(); "UPDATE `table1` SET `col` = 1" sqlUpdate .into('table1') .set({col1: 1, col2: 2 }) .build(); "UPDATE `table1` SET `col1` = 1, `col2` = 2" sqlUpdate .into('table1') .set({col1: 1, col2: 2 }).where({id: 3 }) .build(); "UPDATE `table1` SET `col1` = 1, `col2` = 2 WHERE `id` = 3"