Huge News!Announcing our $40M Series B led by Abstract Ventures.Learn More →

sql-query

Package Overview

Dependencies

Advanced tools

Install Socket

Detect and block malicious and high-risk dependencies

Install

sql-query

NodeJS SQL query builder

0.1.27
latest
Source
npm

Version published: 6 years ago

Weekly downloads: 980; increased by13.43%

Maintainers: 2

Weekly downloads

Created: 12 years ago

Source

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"

Keywords

FAQs

What is sql-query?

Is sql-query popular?

Is sql-query well maintained?

Package last updated on 27 Jun 2018

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.

Install