sql-query v0.1.27
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 = require('sql-query'),
sqlQuery = sql.Query(); //for dialect: sql.Query('postgresql')
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"
6 years ago
9 years ago
9 years ago
9 years ago
10 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago
11 years ago