Skip to content package
sql-query v0.1.16 NodeJS SQL query builder
node * 0 deps cjs open source
overview readme
license MIT
deps 0
unpacked Unknown
files Unknown
node * $ npm install sql-query@0.1.16n npmREADME GitHub-flavored package documentation.
downloads Across all versions.
Download trend unavailable.
compatibility Runtime metadata.
install size Estimated unpacked size across 1 packages.
74.8 kB
Includes dependencies.
versions Latest published Jul 26, 2026
maintainers Registry package owners.
contents NodeJS SQL query builder
Install
npm install sql-query --save copy
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') copy
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)"
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' )
.
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"
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' )
.
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" copy
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'
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
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"
dresende
dresende@thinkdigital.pt
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))"
copy
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 outer' })
. select ( 'id2' )
. build ();
"SELECT `t1`.`id1`, `t2`.`id2` FROM `table1` `t1` LEFT OUTER 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`"
copy
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"
copy
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))"
copy
, [
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`"
copy
(
'table1'
)
. set ({ col1: 1 , col2: 'a' })
. build ();
"INSERT INTO `table1` (`col1`, `col2`) VALUES (1, 'a')"
copy
sqlUpdate
. into ( 'table1' )
. set ({ col1: 1 , col2: 2 }). where ({ id: 3 })
. build ();
"UPDATE `table1` SET `col1` = 1, `col2` = 2 WHERE `id` = 3"
copy