sql-query
Advanced tools
Readme
npm install sql-query --save
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.
var sql = require('sql-query'),
sqlQuery = sql.Query(); //for dialect: sql.Query('postgresql')
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))"
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`"
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"
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))"
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"
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`"
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')"
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"
FAQs
NodeJS SQL query builder
The npm package sql-query receives a total of 926 weekly downloads. As such, sql-query popularity was classified as not popular.
We found that sql-query demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 2 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Research
Security News
The Socket Research team found this npm package includes code for collecting sensitive developer information, including your operating system username, Git username, and Git email.
Security News
OpenJS is warning of social engineering takeovers targeting open source projects after receiving a credible attempt on the foundation.
Company News
Come meet the Socket team at BSidesSF and RSA! We're sponsoring several fun networking events and we would love to see you there.