NodeJS SQL query builder
Install
npm install sql-query --save
Dialects
- MySQL
- PostgreSQL
- SQLite
- MSSQL
About
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 = sqlQuery = sql; //for dialect: sql.Query('postgresql')
Create
var sqlCreate = sqlQuery; sqlCreate "CREATE TABLE 'table1'()" sqlCreate "CREATE TABLE 'table1'('id' INTEGER PRIMARY KEY AUTO_INCREMENT)" sqlCreate "CREATE TABLE 'table1'('id' INTEGER PRIMARY KEY AUTO_INCREMENT,'a_text' TEXT)" sqlCreate "CREATE TABLE 'table1'('id' INTEGER PRIMARY KEY AUTO_INCREMENT,'a_num' INTEGER)" sqlCreate "CREATE TABLE 'table1'('id' INTEGER PRIMARY KEY AUTO_INCREMENT,'a_num' FLOAT(12,2))" sqlCreate "CREATE TABLE 'table1'('id' INTEGER PRIMARY KEY AUTO_INCREMENT,'a_bool' TINYINT(1))"
Select
var sqlSelect = sqlQuery; sqlSelect ; "SELECT * FROM `table1`" sqlSelect ; "SELECT `id`, `name` FROM `table1`" sqlSelect ; "SELECT `id`, `name` AS `label` FROM `table1`" sqlSelect ; "SELECT `id`, `name`, `title` AS `label` FROM `table1`" sqlSelect ; "SELECT `id`, `name` AS `label`, `title` FROM `table1`" sqlSelect ; "SELECT `id`, `name` FROM `table1`" sqlSelect ; "SELECT * FROM `table1`" sqlSelect ; "SELECT `abc`, `def`, (SOMEFUNC(ghi)) AS `ghi` FROM `table1`" sqlSelect ; "SELECT SQL_CALC_FOUND_ROWS * FROM `table1`" sqlSelect ; "SELECT SQL_CALC_FOUND_ROWS `id` FROM `table1`" sqlSelect ; "SELECT `t1`.`id1`, `t1`.`name`, `t2`.`id2` FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect ; "SELECT `t1`.`id1`, `t2`.`id2` FROM `table1` `t1` LEFT INNER JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect ; "SELECT `t1`.`id1`, `t1`.`name`, `t2`.`id2` FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect ; "SELECT COUNT(*) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect ; "SELECT COUNT(*) AS `c` FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect ; "SELECT COUNT(`t2`.`id`) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect ; "SELECT COUNT(`t1`.`id`), COUNT(`t2`.`id`) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect ; "SELECT COUNT(`t2`.`id`), COUNT(`t2`.`col`) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect ; "SELECT AVG(`t2`.`col`) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2` = `t1`.`id1`" sqlSelect ; "SELECT COUNT(`t2`.`id`) FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id2a` = `t1`.`id1a` AND `t2`.`id2b` = `t1`.`id1b`"
Where
var sqlSelect = sqlQuery; sqlSelect ; "SELECT * FROM `table1`" sqlSelect ; "SELECT * FROM `table1`" sqlSelect ; "SELECT * FROM `table1` WHERE `col` = 1" sqlSelect ; "SELECT * FROM `table1` WHERE `col` = 0" sqlSelect ; "SELECT * FROM `table1` WHERE `col` IS NULL" sqlSelect ; "SELECT * FROM `table1` WHERE `col` IS NULL" sqlSelect ; "SELECT * FROM `table1` WHERE `col` IS NOT NULL" sqlSelect ; "SELECT * FROM `table1` WHERE `col` IS NULL" sqlSelect ; "SELECT * FROM `table1` WHERE `col` = false" sqlSelect ; "SELECT * FROM `table1` WHERE `col` = ''" sqlSelect ; "SELECT * FROM `table1` WHERE `col` = true" sqlSelect ; "SELECT * FROM `table1` WHERE `col` = 'a'" sqlSelect ; "SELECT * FROM `table1` WHERE `col` = 'a\\''" sqlSelect ; "SELECT * FROM `table1` WHERE `col` IN (1, 2, 3)" sqlSelect ; "SELECT * FROM `table1` WHERE FALSE" sqlSelect ; "SELECT * FROM `table1` WHERE `col1` = 1 AND `col2` = 2" sqlSelect ; "SELECT * FROM `table1` WHERE (`col1` = 1) AND (`col2` = 2)" sqlSelect ; "SELECT * FROM `table1` WHERE (`col` = 1) AND (`col` = 2)" sqlSelect ; "SELECT * FROM `table1` WHERE (`col1` = 1 AND `col2` = 2) AND (`col3` = 3)" sqlSelect ; "SELECT * FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id` = `t1`.`id` WHERE (`t1`.`col` = 1) AND (`t2`.`col` = 2)" sqlSelect ; "SELECT * FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id` = `t1`.`id` WHERE (`t1`.`col` = 1) AND (`col` = 2)" sqlSelect ; "SELECT * FROM `table1` WHERE `col` > 1" sqlSelect ; "SELECT * FROM `table1` WHERE `col` >= 1" sqlSelect ; "SELECT * FROM `table1` WHERE `col` < 1" sqlSelect ; "SELECT * FROM `table1` WHERE `col` <= 1" sqlSelect ; "SELECT * FROM `table1` WHERE `col` = 1" sqlSelect ; "SELECT * FROM `table1` WHERE `col` <> 1" sqlSelect ; "SELECT * FROM `table1` WHERE `col` BETWEEN 'a' AND 'b'" sqlSelect ; "SELECT * FROM `table1` WHERE `col` NOT BETWEEN 'a' AND 'b'" sqlSelect ; "SELECT * FROM `table1` WHERE `col` LIKE 'abc'" sqlSelect ; "SELECT * FROM `table1` WHERE `col` NOT LIKE 'abc'" sqlSelect ; "SELECT * FROM `table1` WHERE `col` NOT IN (1, 2, 3)" sqlSelect ; "SELECT * FROM `table1` WHERE LOWER(`stuff`) LIKE 'peaches'" sqlSelect ; "SELECT * FROM `table1` WHERE LOWER(`stuff`) LIKE 'peaches'" sqlSelect ; "SELECT * FROM `table1` WHERE LOWER(`stuff`) LIKE 'peaches' AND `number` > 12" sqlSelect ; "SELECT * FROM `table1` WHERE LOWER(`stuff`) LIKE 'peaches' AND `number` == NULL"
Order
var sqlSelect = sqlQuery; sqlSelect ; "SELECT * FROM `table1` ORDER BY `col` ASC" sqlSelect ; "SELECT * FROM `table1` ORDER BY `col` ASC" sqlSelect ; "SELECT * FROM `table1` ORDER BY `col` DESC" sqlSelect ; "SELECT * FROM `table1` ORDER BY `col` ASC, `col2` DESC" sqlSelect ; "SELECT * FROM `table1` ORDER BY col" sqlSelect ; "SELECT * FROM `table1` ORDER BY `col` DESC" sqlSelect ; "SELECT * FROM `table1` ORDER BY ST_Distance(`geopoint`, ST_GeomFromText('POINT(-68.3394 27.5578)',4326))"
Limit
var sqlSelect = sqlQuery; sqlSelect ; "SELECT * FROM `table1` LIMIT 123" sqlSelect ; "SELECT * FROM `table1` LIMIT 123456789"
Select function
var sqlSelect = sqlQuery; sqlSelect ; "SELECT MYFUN(`col1`) FROM `table1`" sqlSelect ; "SELECT MYFUN(`col1`, `col2`) FROM `table1`" sqlSelect ; "SELECT DBO.FNBALANCE(80, NULL, NULL) AS `balance` FROM `table1`" sqlSelect ; "SELECT MYFUN(`col1`, `col2`) AS `alias` FROM `table1`" sqlSelect ; "SELECT MYFUN(`col1`, 'col2') AS `alias` FROM `table1`"
Insert
var sqlInsert = sqlQuery; sqlInsert ; "INSERT INTO `table1`" sqlInsert ; "INSERT INTO `table1` VALUES()" sqlInsert ; "INSERT INTO `table1` (`col`) VALUES (1)" sqlInsert ; "INSERT INTO `table1` (`col1`, `col2`) VALUES (1, 'a')"
Update
var sqlUpdate = sqlQuery sqlUpdate ; "UPDATE `table1`" sqlUpdate ; "UPDATE `table1` SET `col` = 1" sqlUpdate ; "UPDATE `table1` SET `col1` = 1, `col2` = 2" sqlUpdate ; "UPDATE `table1` SET `col1` = 1, `col2` = 2 WHERE `id` = 3"