sql-template-strings - npm Package Compare versions

Comparing version 1.0.2 to 1.1.0

index.js

@@ -1,7 +0,29 @@
-module.exports = function SQL(strings) {
-  return {
-    sql: strings.join('?'), // for mysql / mysql2
-    text: strings.reduce((previous, current, i) => previous + '$' + i + current), // for postgres
-    values: Array.from(arguments).slice(1) // since node doesnt support argument unpacking yet
+'use strict'
+
+function SQL (strings) {
+  let args = Array.from(arguments).slice(1)
+  let sql = '' // for mysql/mysql2
+  let text = '' // for postgres
+  let values = []
+  for (let i = 0, stringsLength = strings.length, argsLength = args.length; i < stringsLength; i++) {
+    sql += strings[i]
+    text += strings[i]
+    if (typeof args[i] === 'object' && args[i] !== null && args[i].raw) {
+      sql += args[i].value
+      text += args[i].value
+    } else if (i < argsLength) {
+      values.push(args[i])
+      if (i < stringsLength - 1) {
+        sql += '?'
+        text += '$' + values.length
+      }
+    }
   }
-}
+  return {sql, text, values}
+}
+
+SQL.raw = function (value) {
+  return {value, raw: true}
+}
+
+module.exports = SQL

jsconfig.json

@@ -0,0 +0,0 @@ {

package.json

 {
   "name": "sql-template-strings",
-  "version": "1.0.2",
+  "version": "1.1.0",
   "description": "Allows you to use ES6 tagged template strings for prepared statements with mysql and postgres",
@@ -10,2 +10,5 @@ "main": "index.js",
   },
+  "scripts": {
+    "test": "mocha && standard"
+  },
   "keywords": [
@@ -25,3 +28,8 @@ "mysql",
   "author": "Felix Becker",
-  "license": "ISC"
+  "license": "ISC",
+  "devDependencies": {
+    "chai": "^3.3.0",
+    "mocha": "^2.3.3",
+    "standard": "^5.3.1"
+  }
 }

README.md

@@ -1,18 +0,18 @@
-A simple yet powerful module to allow you to use ES6 tagged template strings for prepared/escaped statements in [mysql](https://www.npmjs.com/package/mysql) / [mysql2](https://www.npmjs.com/package/mysql2) and [postgres](https://www.npmjs.com/package/pq) (and with simple, I mean only 7 lines of code!).
+A simple yet powerful module to allow you to use ES6 tagged template strings for prepared/escaped statements in [mysql](https://www.npmjs.com/package/mysql) / [mysql2](https://www.npmjs.com/package/mysql2) and [postgres](https://www.npmjs.com/package/pq).
 
 Example for escaping queries (callbacks omitted):
 ```js
-let SQL = require('sql-template-strings');
+let SQL = require('sql-template-strings')
 
-let book = 'harry potter';
+let book = 'harry potter'
 
 // mysql (for mysql2 prepared statements, just replace query with execute):
-mysql.query('SELECT author FROM books WHERE name = ?', [book]);
+mysql.query('SELECT author FROM books WHERE name = ?', [book])
 // is equivalent to
-mysql.query(SQL`SELECT author FROM books WHERE name = ${book}`);
+mysql.query(SQL`SELECT author FROM books WHERE name = ${book}`)
 
 // postgres:
-pg.query('SELECT author FROM books WHERE name = $1', [book]);
+pg.query('SELECT author FROM books WHERE name = $1', [book])
 // is equivalent to
-pg.query(SQL`SELECT author FROM books WHERE name = ${book}`);
+pg.query(SQL`SELECT author FROM books WHERE name = ${book}`)
 ```
@@ -25,3 +25,3 @@ This might not seem like a big deal, but when you do an INSERT with a lot columns writing all the placeholders becomes a nightmare:
   [name, author, isbn, category, recommendedAge, pages, price]
-);
+)
 // is better written as
@@ -33,19 +33,51 @@ db.query(SQL`
   VALUES  (${name}, ${author}, ${isbn}, ${category}, ${recommendedAge}, ${pages}, ${price})
-`);
+`)
 ```
 Also template strings support line breaks, while normal strings do not.
 
-Please note that postgre requires prepared statements to be named, otherwise the parameters will be escaped and replaced on the client side.
+## Adding raw values
+Some values cannot be replaced by placeholders in prepared statements, like table names. In these cases, you need to use `SQL.raw()` so the values get inserted directly. Please note that you are then responsible for escaping these values with proper escaping functions first if they come from user input. Also, executing many prepared statements with changing raw values in a loop will quickly overflow the prepared statement buffer (and destroy their performance benefit), so be careful. Examples:
+```js
+let table = 'books'
+let order = 'DESC'
+let column = 'author'
+
+db.query(SQL`SELECT * FROM ${SQL.raw(table)} WHERE author = ${author} ORDER BY ${column} ${SQL.raw(order)}`)
+
+// you MUST escape user input manually
+mysql.query(SQL`SELECT * FROM ${SQL.raw(mysql.escapeId(someUserInput))} WHERE name = ${book} ORDER BY ${column} ${SQL.raw(order)}`)
+
+// you might need to add quotes
+pg.query(SQL`SELECT * FROM "${SQL.raw(table)}"`)
+mysql.query(SQL`SELECT * FROM \`${SQL.raw(table)}\``)
+
+// DONT DO THIS - THIS WILL OVERFLOW YOUR PREPARED STATEMENT BUFFER
+for (let table of largeArray) {
+  // for every iteration, a new query will be prepared, even though it is only executed once.
+  // use mysql.query() instead.
+  mysql2.execute(SQL`SELECT * FROM ${SQL.raw(table)}`)
+}
+```
+
+## Prepared Statements in Postgre
+Postgre requires prepared statements to be named, otherwise the parameters will be escaped and replaced on the client side.
 You can still use SQL template strings though, you just need to assign a name to the query before using it:
 ```js
 // old way
-pg.query({name: 'my_query', text: 'SELECT author FROM books WHERE name = $1', values: [book]});
+pg.query({name: 'my_query', text: 'SELECT author FROM books WHERE name = $1', values: [book]})
 
-//with template strings
-let query = SQL`SELECT author FROM books WHERE name = ${book}`;
-query.name = 'my_query';
-pg.query(query);
+// with template strings
+let query = SQL`SELECT author FROM books WHERE name = ${book}`
+query.name = 'my_query'
+pg.query(query)
 
 // or using lodash
 pg.query(_.assign(SQL`SELECT author FROM books WHERE name = ${book}`, {name: 'my_query'}))
-```
+```
+
+## Contributing
+ - Tests are written using [mocha](https://www.npmjs.com/package/mocha) (BDD style) and [chai](https://www.npmjs.com/package/chai) (expect style)
+ - This module follows [standard](https://www.npmjs.com/package/standard) coding style
+ - You can use `npm test` to run the tests and check coding style
+ - Since this module is only compatible with ES6 versions of node anyway, use all the ES6 goodies
+ - Pull requests are welcome :)

Fixed alerts

No tests

Quality

Package does not have any tests. This is a strong signal of a poorly maintained or low quality package.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

7933

increased by153.94%

Number of package files

9

increased by80%

Lines of code

120

increased by700%

Number of low quality alerts

0

decreased by-100%

Number of lines in readme file

81

increased by65.31%

Worsened metrics

Dev dependency count

3

increased byInfinity%

No dependency changes