Security News
38% of CISOs Fear They’re Not Moving Fast Enough on AI
CISOs are racing to adopt AI for cybersecurity, but hurdles in budgets and governance may leave some falling behind in the fight against cyber threats.
@fxjs/sql-query
Advanced tools
NOTICE: This is node-sql-query's fibjs version, thx a lot to node-sql-query's author : )
npm install sql-query --save
This module is used by @fxjs/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('@fxjs/sql-query')
var sqlQuery = sql.Query(); // for dialect: sql.Query('mysql')
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
[![NPM version](https://img.shields.io/npm/v/@fxjs/sql-query.svg)](https://www.npmjs.org/package/@fxjs/sql-query) [![Build Status](https://travis-ci.org/fxjs-modules/orm.svg)](https://travis-ci.org/fxjs-modules/orm)
The npm package @fxjs/sql-query receives a total of 42 weekly downloads. As such, @fxjs/sql-query popularity was classified as not popular.
We found that @fxjs/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.
Security News
CISOs are racing to adopt AI for cybersecurity, but hurdles in budgets and governance may leave some falling behind in the fight against cyber threats.
Research
Security News
Socket researchers uncovered a backdoored typosquat of BoltDB in the Go ecosystem, exploiting Go Module Proxy caching to persist undetected for years.
Security News
Company News
Socket is joining TC54 to help develop standards for software supply chain security, contributing to the evolution of SBOMs, CycloneDX, and Package URL specifications.