sql-template-strings - npm Package Compare versions

Comparing version 2.1.1 to 2.2.0

.vscode/launch.json

@@ -10,3 +10,3 @@ {
 			"stopOnEntry": false,
-			"args": ["--no-timeouts"],
+			"args": ["--no-timeouts", "--colors"],
 			"cwd": "${workspaceRoot}",
@@ -38,2 +38,2 @@ "preLaunchTask": null,
 	]
-}
+}

index.d.ts

@@ -57,2 +57,9 @@
   setName(name: string): this;
+
+  /**
+   * Use a prepared statement with Sequelize.
+   * Makes `query` return a query with `$n` syntax instead of `?`  and switches the `values` key name to `bind`
+   * If omitted, `value` defaults to `true`.
+   */
+  useBind(value?: boolean): this;
 }
@@ -59,0 +66,0 @@

index.js

@@ -16,3 +16,3 @@ 'use strict'
   get query() {
-    return this.sql
+    return this.bind ? this.text : this.sql
   }
@@ -32,4 +32,4 @@
       this.strings[this.strings.length - 1] += statement.strings[0]
-      this.strings.push.apply(this.strings, statement.strings.slice(1))
-      this.values.push.apply(this.values, statement.values)
+      this.strings.push.apply(this.strings, statement.strings.slice(1));
+      (this.values || this.bind).push.apply(this.values, statement.values)
     } else {
@@ -42,2 +42,22 @@ this.strings[this.strings.length - 1] += statement
   /**
+   * Use a prepared statement with Sequelize.
+   * Makes `query` return a query with `$n` syntax instead of `?`  and switches the `values` key name to `bind`
+   * @param {boolean} [value=true] value If omitted, defaults to `true`
+   * @returns this
+   */
+  useBind(value) {
+    if (value === undefined) {
+      value = true
+    }
+    if (value && !this.bind) {
+      this.bind = this.values
+      delete this.values
+    } else if (!value && this.bind) {
+      this.values = this.bind
+      delete this.bind
+    }
+    return this
+  }
+
+  /**
    * @param {string} name
@@ -44,0 +64,0 @@ * @returns {this}

package.json

 {
   "name": "sql-template-strings",
-  "version": "2.1.1",
+  "version": "2.2.0",
   "description": "ES6 tagged template strings for prepared statements with mysql and postgres",
@@ -5,0 +5,0 @@ "main": "index.js",

README.md

@@ -13,2 +13,3 @@ [![Version](https://img.shields.io/npm/v/sql-template-strings.svg?maxAge=2592000)](https://www.npmjs.com/package/sql-template-strings)
 Example for escaping queries (callbacks omitted):
+
 ```js
@@ -29,2 +30,7 @@ const SQL = require('sql-template-strings')
 pg.query(SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`)
+
+// sequelize:
+sequelize.query('SELECT author FROM books WHERE name = ? AND author = ?', {replacements: [book, author]})
+// is equivalent to
+sequelize.query(SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`)
 ```
@@ -111,6 +117,29 @@ 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:
 
+## Bound Statements in sequelize
+By default, Sequelize will escape replacements on the client.
+To switch to using a bound statement in Sequelize, call `useBind()`.
+The boolean parameter defaults to `true`.
+Like all methods, returns `this` for chaining.
+Please note that as long as the bound mode is active, the statement object only supports Sequelize, not the other drivers.
+
+```js
+// old way
+sequelize.query('SELECT author FROM books WHERE name = ? AND author = ?', {bind: [book, author]})
+
+// with template strings
+sequelize.query(SQL`SELECT author FROM books WHERE name = ${book}`.useBind(true))
+sequelize.query(SQL`SELECT author FROM books WHERE name = ${book}`.useBind()) // the same
+
+// works with append (you can call useBind at any time)
+const query = SQL`SELECT * FROM books`.useBind(true)
+if (params.name) {
+  query.append(SQL` WHERE name = ${params.name}`)
+}
+query.append(SQL` LIMIT 10 OFFSET ${params.offset || 0}`)
+```
+
 ## Contributing
- - Tests are written using [mocha](https://www.npmjs.com/package/mocha) (BDD style) and [chai](https://www.npmjs.com/package/chai) (expect style)
+ - Tests are written using [mocha](https://www.npmjs.com/package/mocha)
  - 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 :)

test/index.js

@@ -11,2 +11,3 @@ 'use strict'
     assert.equal(query.text, 'SELECT * FROM table')
+    assert.equal(query.query, 'SELECT * FROM table')
     assert.deepEqual(query.values, [])
@@ -19,2 +20,3 @@ })
     assert.equal(query.sql, 'SELECT * FROM table WHERE column = ?')
+    assert.equal(query.query, 'SELECT * FROM table WHERE column = ?')
     assert.equal(query.text, 'SELECT * FROM table WHERE column = $1')
@@ -29,2 +31,3 @@ assert.deepEqual(query.values, [value])
     assert.equal(query.sql, 'SELECT * FROM table WHERE column1 = ? AND column2 = ?')
+    assert.equal(query.query, 'SELECT * FROM table WHERE column1 = ? AND column2 = ?')
     assert.equal(query.text, 'SELECT * FROM table WHERE column1 = $1 AND column2 = $2')
@@ -44,8 +47,2 @@ assert.deepEqual(query.values, [value1, value2])
 
-  it('should return the same as sql for query for sequelize', () => {
-    const value = 123
-    const statement = SQL`SELECT * FROM table WHERE column = ${value}`
-    assert.equal(statement.query, statement.sql)
-  })
-
   describe('append()', () => {
@@ -74,2 +71,13 @@
     })
+
+    it('should work with a bound statement', () => {
+      const value = 1234
+      const statement = SQL`SELECT * FROM table WHERE column = ${value}`.useBind(true).append(' ORDER BY other_column')
+      assert.equal(statement.sql, 'SELECT * FROM table WHERE column = ? ORDER BY other_column')
+      assert.equal(statement.text, 'SELECT * FROM table WHERE column = $1 ORDER BY other_column')
+      assert.strictEqual(statement.query, 'SELECT * FROM table WHERE column = $1 ORDER BY other_column')
+      assert.strictEqual(statement.values, undefined)
+      assert.strictEqual('values' in statement, false)
+      assert.deepStrictEqual(statement.bind, [1234])
+    })
   })
@@ -83,2 +91,41 @@
   })
+
+  describe('useBind()', () => {
+
+    it('should change query to $n syntax and swap values with bind', () => {
+      const value = 123
+      const statement = SQL`SELECT * FROM table WHERE column = ${value}`.useBind(true)
+      assert.strictEqual(statement.query, 'SELECT * FROM table WHERE column = $1')
+      assert.strictEqual(statement.values, undefined)
+      assert.strictEqual('values' in statement, false)
+      assert.deepStrictEqual(statement.bind, [123])
+    })
+
+    it('should allow to omit the parameter', () => {
+      const value = 123
+      const statement = SQL`SELECT * FROM table WHERE column = ${value}`.useBind()
+      assert.strictEqual(statement.query, 'SELECT * FROM table WHERE column = $1')
+      assert.strictEqual(statement.values, undefined)
+      assert.strictEqual('values' in statement, false)
+      assert.deepStrictEqual(statement.bind, [123])
+    })
+
+    it('should be idempotent', () => {
+      const value = 123
+      const statement = SQL`SELECT * FROM table WHERE column = ${value}`.useBind(true).useBind(true)
+      assert.strictEqual(statement.query, 'SELECT * FROM table WHERE column = $1')
+      assert.strictEqual(statement.values, undefined)
+      assert.strictEqual('values' in statement, false)
+      assert.deepStrictEqual(statement.bind, [123])
+    })
+
+    it('should be reversable', () => {
+      const value = 123
+      const statement = SQL`SELECT * FROM table WHERE column = ${value}`.useBind(true).useBind(false)
+      assert.strictEqual(statement.query, 'SELECT * FROM table WHERE column = ?')
+      assert.strictEqual(statement.bind, undefined)
+      assert.strictEqual('bind' in statement, false)
+      assert.deepStrictEqual(statement.values, [123])
+    })
+  })
 })

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

20515

increased by26.64%

Lines of code

361

increased by23.21%

Number of lines in readme file

143

increased by25.44%

No dependency changes