🚀 Socket Launch Week Day 4:Socket MCP Adds Org Alerts, Threat Feed Review, and Package Inspection.Learn more →
Sign In

sql-concat

Package Overview
Dependencies
Maintainers
1
Versions
25
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

sql-concat - npm Package Compare versions

Comparing version
2.0.1
 to
2.1.0
+14
tagged-template.js
module.exports = (queryParts, ...values) =>
queryParts.reduce(
(queryObject, queryPart, i) => {
queryObject.str += queryPart
if (i < values.length) {
queryObject.str += `?`
queryObject.params.push(values[i])
}
return queryObject
},
{ str: ``, params: [] }
)
+81
-29
clause-handlers.js
const { clauseOrder } = require(`./constants`)
module.exports = {
staticText: function staticText(text) {
staticText(text) {
return {

@@ -9,9 +9,7 @@ str: text,

},
whateverTheyPutIn: function whateverTheyPutIn() {
const args = Array.prototype.slice.apply(arguments)
const clausePartsJoinedBy = args.shift()
const partsJoinedBy = args.shift()
whateverTheyPutIn(clausePartsJoinedBy, partsJoinedBy, ...expressions) {
const { str, params } = joinExpressions(expressions, partsJoinedBy)
return {
str: args.join(partsJoinedBy),
str,
params,
joinedBy: clausePartsJoinedBy,

@@ -25,3 +23,3 @@ }

return {
str: combineWithAlias(`(\n\t${result.str}\n)`, alias),
str: combineWithAlias(`(\n\t${ result.str }\n)`, alias),
params: result.params,

@@ -35,17 +33,31 @@ }

},
columnParam: function columnParam(joinedBy, opts, column, comparison, param) {
columnParam(joinedBy, opts, expression, comparator, value) {
opts = opts || {}
if (param === undefined) {
param = comparison
comparison = undefined
if (value === undefined) {
value = comparator
comparator = undefined
}
const expressionObject = expressionToObject(expression)
const valueIsObject = (value && typeof value === `object` && value.params)
const valueParams = valueIsObject
? value.params
: [ value ]
const params = [ ...expressionObject.params, ...valueParams ]
const comparatorAndValue = valueIsObject
? getComparison(opts.like, comparator) + ` ` + value.str
: getComparisonAndParameterString(value, opts.like, comparator)
return {
params: [ param ],
str: column + getComparisonAndParameterString(param, opts.like, comparison),
str: `${expressionObject.str} ${comparatorAndValue}`,
params,
joinedBy,
}
},
joinClauseHandler: function joinClauseHandler(type, table, alias, on) {
joinClauseHandler(type, table, alias, on) {
if (!on) {

@@ -57,9 +69,18 @@ on = alias

function joinString() {
return `${type}JOIN `
return `${ type }JOIN `
}
function onString() {
return on ? ` ON ${on}` : ``
let onParams = []
let onString = ``
if (on) {
if (on.params) {
onParams = on.params
onString = makeOn(on.str)
} else {
onString = makeOn(on)
}
}
if (isAQuery(table)) {

@@ -69,4 +90,4 @@ const result = table.build(`\n\t`)

return {
str: joinString() + combineWithAlias(`(\n\t${result.str}\n)`, alias) + onString(),
params: result.params,
str: joinString() + combineWithAlias(`(\n\t${ result.str }\n)`, alias) + onString,
params: [ ...result.params, ...onParams ],
joinedBy: `\n`,

@@ -76,3 +97,4 @@ }

return {
str: joinString() + combineWithAlias(table, alias) + onString(),
str: joinString() + combineWithAlias(table, alias) + onString,
params: onParams,
joinedBy: `\n`,

@@ -84,13 +106,43 @@ }

function getComparisonAndParameterString(param, like, comparison) {
if (param === null) {
return ` ${(comparison || `IS`)} ?`
} else if (Array.isArray(param)) {
return ` ${(comparison || `IN`)}(?)`
const makeOn = on => on ? ` ON ${ on }` : ``
const expressionToObject = expression => {
if (expression && typeof expression === `object` && expression.params) {
return expression
} else {
const equalityCheck = like ? `LIKE` : (comparison || `=`)
return ` ${equalityCheck} ?`
return {
str: expression,
params: []
}
}
}
const joinExpressions = (expressions, joinedBy) => {
const params = []
const strs = []
expressions.forEach(expression => {
const object = expressionToObject(expression)
params.push(...object.params)
strs.push(object.str)
})
return {
str: strs.join(joinedBy),
params,
}
}
const getComparison = (like, comparison) => like ? `LIKE` : (comparison || `=`)
function getComparisonAndParameterString(value, like, comparison) {
if (value === null) {
return `${ (comparison || `IS`) } ?`
} else if (Array.isArray(value)) {
return `${ (comparison || `IN`) }(?)`
} else {
return `${ getComparison(like, comparison) } ?`
}
}
function isAQuery(q) {

@@ -103,3 +155,3 @@ const clauses = q && typeof q.getClauses === `function` && q.getClauses()

function combineWithAlias(str, alias) {
return alias ? (`${str} AS ${alias}`) : str
return alias ? (`${ str } AS ${ alias }`) : str
}
+2
-1
index.js
const q = require(`./query-object`)
const { startingClauses } = require(`./constants`)
const taggedTemplate = require(`./tagged-template.js`)
module.exports = q(startingClauses)
module.exports = Object.assign(taggedTemplate, q(startingClauses))
+1
-1
package.json
{
"name": "sql-concat",
"version": "2.0.1",
"version": "2.1.0",
"description": "It's just fancy string concatenation",

@@ -5,0 +5,0 @@ "main": "index.js",

+8
-8
query-object.js

@@ -12,3 +12,3 @@ const {

select: addToClause(clauses, `select`, (...args) => whateverTheyPutIn(`, `, `, `, ...args)),
from: addToClause(clauses, `from`, tableNameOrSubquery.bind(null)),
from: addToClause(clauses, `from`, tableNameOrSubquery),
join: addToClause(clauses, `join`, (...args) => joinClauseHandler(``, ...args)),

@@ -25,6 +25,6 @@ leftJoin: addToClause(clauses, `join`, (...args) => joinClauseHandler(`LEFT `, ...args)),

limit: addToClause(clauses, `limit`, (...args) => whateverTheyPutIn(`, `, `, `, ...args)),
forUpdate: addToClause(clauses, `lock`, (...args) => staticText(`FOR UPDATE`, ...args)),
lockInShareMode: addToClause(clauses, `lock`, (...args) => staticText(`LOCK IN SHARE MODE`, ...args)),
build: build.bind(null, clauses),
getClauses: copy.bind(null, clauses),
forUpdate: addToClause(clauses, `lock`, () => staticText(`FOR UPDATE`)),
lockInShareMode: addToClause(clauses, `lock`, () => staticText(`LOCK IN SHARE MODE`)),
build: joinedBy => build(clauses, joinedBy),
getClauses: () => copy(clauses),
})

@@ -63,3 +63,3 @@

params: reducedClause.params,
str: (`${clauseQueryString} ${reducedClause.str}`).trim(),
str: (`${ clauseQueryString } ${ reducedClause.str }`).trim(),
}

@@ -76,5 +76,5 @@ }

function addToClause(clauses, key, stringBuilder) {
return function() {
return (...args) => {
const newClauses = copy(clauses)
newClauses[key].push(stringBuilder(...arguments))
newClauses[key].push(stringBuilder(...args))
return q(newClauses)

@@ -81,0 +81,0 @@ }

+76
-25
readme.md

@@ -14,9 +14,8 @@ # sql-concat

- Build queries programmatically
- Eliminate some query boilerplate and provide a better syntax than a big 'ol concatenated string (at least until I start making use of ES6 template strings)
- Allow simpler combining of query parts and their associated parameters than one long query string followed by a long array of parameter values
- Allow simple combining of query parts and their associated parameters (as opposed to writing a long query string followed by a long array of parameter values)
- Build queries for the [mysqljs/mysql](https://github.com/mysqljs/mysql) library (specifically, by expecting its [rules for query values](https://github.com/mysqljs/mysql#escaping-query-values) instead of MySQL's stored procedure parameters)
## Other features
## Features
- Easily compose query parts - the query-builder object is immutable, so you can build up a base query and re-use it over and over again with small modifications (add where clauses or joins conditionally, for example)
- Builds queries for [mysqljs/mysql](https://github.com/mysqljs/mysql) (specifically, by expecting its [rules for query values](https://github.com/mysqljs/mysql#escaping-query-values) instead of MySQL's stored procedure parameters)
- Easily compose query parts - the query-builder object is immutable, so you can build up a base query and re-use it over and over again with small modifications (for example, with conditional where clauses or joins)
- Not as overblown as [knex](http://knexjs.org/), and allows more freedom in using string literals within query chunks

@@ -27,2 +26,6 @@ - Queries should look good when printed out (newlines between clauses, subqueries indented with tabs)

```
const q = require('sql-concat')
```
<!--js

@@ -33,3 +36,4 @@ var q = require('./')

```js
const result = q.select('table1.some_boring_id, table2.something_interesting, mystery_table.surprise')
const minNumber = 0
const result = q.select('table1.some_boring_id, table2.something_interesting, mystery_table.surprise', q`LEAST(table1.whatever, ${minNumber}) AS whatever`)
.from('table1')

@@ -42,3 +46,3 @@ .join('table2', 'table1.some_boring_id = table2.id')

const expectedQuery = 'SELECT table1.some_boring_id, table2.something_interesting, mystery_table.surprise\n'
const expectedQuery = 'SELECT table1.some_boring_id, table2.something_interesting, mystery_table.surprise, LEAST(table1.whatever, ?) AS whatever\n'
+ 'FROM table1\n'

@@ -51,3 +55,3 @@ + 'JOIN table2 ON table1.some_boring_id = table2.id\n'

result.params // => [ 'fancy', 99 ]
result.params // => [ 0, 'fancy', 99 ]

@@ -115,20 +119,20 @@ ```

## API so far
## API
Because [node-mysql](https://github.com/felixge/node-mysql) already makes inserting so easy, this module is focused on `SELECT` queries. I've implemented new clauses as I've needed them, and it's pretty well fleshed out at the moment.
Because the [mysql](https://github.com/mysqljs/mysql) package already makes inserting so easy, this module is focused on `SELECT` queries. I've implemented new clauses as I've needed them, and it's pretty well fleshed out at the moment.
If you need a clause added that is not implemented yet, feel free to open a pull request. If you're not sure what the API should look like, open an issue and we can talk it through.
- `q.select(column1, column2, etc)`
- `q.select(expression1, expression2, etc)`
- `q.from(tablename | subquery, alias)`
- `q.join(tablename | subquery, [alias], on)`
- `q.leftJoin(tablename | subquery, [alias], on)`
- `q.where(column, [comparitor], value)`
- `q.orWhere(column, [comparitor], value)`
- `q.whereLike(column, value)`
- `q.orWhereLike(column, value)`
- `q.having(column, [comparitor], value)`
- `q.orHaving(column, [comparitor], value)`
- `q.groupBy(column1, column2, etc)`
- `q.orderBy(column1, column2, etc)`
- `q.where(expression, [comparitor], value)`
- `q.orWhere(expression, [comparitor], value)`
- `q.whereLike(expression, value)`
- `q.orWhereLike(expression, value)`
- `q.having(expression, [comparitor], value)`
- `q.orHaving(expression, [comparitor], value)`
- `q.groupBy(expression1, expression2, etc)`
- `q.orderBy(expression1, expression2, etc)`
- `q.limit(offset)`

@@ -138,5 +142,5 @@ - `q.forUpdate()`

All of the column/table fields are just strings that aren't escaped or fiddled with in any way, so you can add aliases or whatnot without worrying that you're going to break some query parser.
`expression` strings are inserted without being parameterized, but you can also pass in [tagged template strings](https://github.com/TehShrike/sql-concat/tree/tagged-template-support#tagged-template-strings) to do anything special.
If `value` is `NULL` it will be automatically compared with `IS`, and if it's an array it will be automatically compared with `IN()`:
All `value`s are automatically parameterized. If a `value` is `NULL` it will be automatically compared with `IS`, and if it's an array it will be automatically compared with `IN()`:

@@ -160,9 +164,56 @@ ```js

## To do:
### Tagged template strings
- [Issue 2](https://github.com/TehShrike/sql-concat/issues/2): calling MySQL functions with dynamic parameters as arguments `WHERE some_column = LPAD(other_column, ?, ?)`
- [Issue 3](https://github.com/TehShrike/sql-concat/issues/3): nested parenthetical groupings `WHERE some_column = ? AND (other_column = ? OR other_column = ?)`
sql-concat is also a template tag:
Chime in if you're interested.
```js
const rainfall = 3
const templateTagResult = q`SELECT galoshes FROM puddle WHERE rain > ${ rainfall }`
templateTagResult.str // => `SELECT galoshes FROM puddle WHERE rain > ?`
templateTagResult.params // => [ 3 ]
```
You can pass these results into any method as a value. This allows you to properly parameterize function calls:
```js
const shoeSize = 9
const functionCallResult = q.select('rubbers')
.from('puddle')
.where('rain', '>', 4)
.where('size', q`LPAD(${ shoeSize }, 2, '0')`)
.build()
const functionCallQuery = `SELECT rubbers\n`
+ `FROM puddle\n`
+ `WHERE rain > ? AND size = LPAD(?, 2, '0')`
functionCallResult.str // => functionCallQuery
functionCallResult.params // => [ 4, 9 ]
```
## Long-shot feature
Some syntax for generating nested clauses conditionally would be nice, so you could easily generate something like this dynamically:
```sql
WHERE important = ? AND (your_column = ? OR your_column = ?)
```
Maybe something like:
```
const whereCondition = possibleValues.reduce(
(condition, possibleValue) => condition.add('your_column', possibleValue),
q.someMagicalOrConditional()
)
const query = q.select('everything')
.from('table')
.where('important', true)
.where(whereCondition)
```
You can discuss this feature in [Issue 3](https://github.com/TehShrike/sql-concat/issues/3) if you're interested.
## Running the tests

@@ -169,0 +220,0 @@ 

+78
-0
test.js

@@ -264,1 +264,79 @@ const test = require(`tape`)

})
test(`Tagged template string`, t => {
const result = q`SELECT wat FROM a WHERE foo = ${ 4 } AND bar IN(${ [ 1, 2 ] })`
t.equal(result.str, `SELECT wat FROM a WHERE foo = ? AND bar IN(?)`)
t.deepEqual(result.params, [ 4, [ 1, 2 ] ])
t.end()
})
test(`Passing a str/params object as a value`, t => {
const { str, params } = q.select(`howdy`)
.from(`meh`)
.where(`a`, 1)
.where(`tag`, {
str: `FANCY(?, ?)`,
params: [ `pants`, `butts` ],
})
.where(`b`, 2)
.build()
t.equal(str, `SELECT howdy\nFROM meh\nWHERE a = ? AND tag = FANCY(?, ?) AND b = ?`)
t.deepEqual(params, [ 1, `pants`, `butts`, 2 ])
t.end()
})
test(`Integration: passing a tagged template string result as an argument`, t => {
const { str, params } = q.where(`tag`, q`FANCY(${ `pants` }, ${ `butts` })`).build()
t.equal(str, `WHERE tag = FANCY(?, ?)`)
t.deepEqual(params, [ `pants`, `butts` ])
t.end()
})
test(`Passing str/params into every clause`, t => {
const assertLegit = (query, expectedStr) => {
const { str, params } = query.build()
t.equal(str, expectedStr, expectedStr)
t.deepEqual(params, [ 1, 2 ])
}
assertLegit(
q.select(q`FOO(${ 1 })`, q`BAR(${ 2 })`),
`SELECT FOO(?), BAR(?)`
)
assertLegit(
q.join(`table`, q`FOO(${ 1 }) = BAR(${ 2 })`),
`JOIN table ON FOO(?) = BAR(?)`
)
assertLegit(
q.leftJoin(`table`, q`FOO(${ 1 }) = BAR(${ 2 })`),
`LEFT JOIN table ON FOO(?) = BAR(?)`
)
assertLegit(
q.where(q`FOO(${ 1 })`, q`BAR(${ 2 })`),
`WHERE FOO(?) = BAR(?)`
)
assertLegit(
q.whereLike(q`FOO(${ 1 })`, q`BAR(${2})`),
`WHERE FOO(?) LIKE BAR(?)`
)
assertLegit(
q.having(q`FOO(${ 1 })`, q`BAR(${2})`),
`HAVING FOO(?) = BAR(?)`
)
assertLegit(
q.groupBy(q`FOO(${ 1 })`, q`BAR(${ 2 })`),
`GROUP BY FOO(?), BAR(?)`
)
assertLegit(
q.orderBy(q`FOO(${ 1 })`, q`BAR(${ 2 })`),
`ORDER BY FOO(?), BAR(?)`
)
t.end()
})