@nearform/sql - npm Package Compare versions

Comparing version 1.7.0 to 1.8.0

package.json

 {
   "name": "@nearform/sql",
-  "version": "1.7.0",
+  "version": "1.8.0",
   "description": "SQL injection protection module",
@@ -5,0 +5,0 @@ "main": "./SQL.js",

README.md

@@ -11,9 +11,12 @@ # SQL
 3. [Methods](#methods)
-   1. [append](#appendstatement)
-   2. [glue](#gluepieces-separator)
-4. [How it works?](#how-it-works)
-5. [Undefined values and nullable fields](#undefined-values-and-nullable-fields)
-6. [Testing, linting, & coverage](#testing-linting--coverage)
-7. [Benchmark](#benchmark)
-8. [License](#license)
+   1. [glue](#gluepieces-separator)
+   2. (deprecated) [append](#deprecated-appendstatement-options)
+4. [Utilities](#utilities)
+   1. [unsafe](#unsafevalue)
+   2. [quoteIdent](#quoteidentvalue)
+5. [How it works?](#how-it-works)
+6. [Undefined values and nullable fields](#undefined-values-and-nullable-fields)
+7. [Testing, linting, & coverage](#testing-linting--coverage)
+8. [Benchmark](#benchmark)
+9. [License](#license)
 
@@ -62,14 +65,2 @@ ## Install
 
-### append(statement[, options])
-
-```js
-const username = 'user1'
-const email = '[e-mail]'
-const userId = 1
-
-const sql = SQL`UPDATE users SET name = ${username}, email = ${email}`
-sql.append(SQL`, ${dynamicName} = 'dynamicValue'`, { unsafe: true })
-sql.append(SQL`WHERE id = ${userId}`)
-```
-
 > ⚠️ **Warning**
@@ -86,4 +77,2 @@ >
 
-const sql = SQL` UPDATE users SET `
-
 const updates = []
@@ -93,4 +82,3 @@ updates.push(SQL`name = ${username}`)
 
-sql.append(sql.glue(updates, ' , '))
-sql.append(SQL`WHERE id = ${userId}`)
+const sql = SQL`UPDATE users SET ${SQL.glue(updates, ' , ')} WHERE id = ${userId}`
 ```
@@ -103,9 +91,8 @@
 const value = 'test'
-const sql = SQL` UPDATE users SET property = ${value}`
-
-const idsSqls = ids.map(id => SQL`${id}`)
-
-sql.append(SQL`WHERE id IN (`)
-sql.append(sql.glue(idsSqls, ' , '))
-sql.append(SQL`)`)
+const sql = SQL`
+UPDATE users
+SET property = ${value}
+WHERE id
+IN (${SQL.glue(ids.map(id => SQL`${id}`), ' , ')})
+`
 ```
@@ -130,12 +117,31 @@
 
-const sql = SQL`INSERT INTO users (id, name) VALUES `
-
-sql.append(
-  SQL.glue(
+const sql = SQL`INSERT INTO users (id, name) VALUES 
+  ${SQL.glue(
     users.map(user => SQL`(${user.id},${user.name}})`),
     ' , '
-  )
-)
+  )}
+`
 ```
 
+### (deprecated) append(statement[, options])
+
+Append has been deprecated in favour of using template literals:
+
+```js
+const from = SQL`FROM table`
+const sql = SQL`SELECT * ${from}`
+```
+
+For now, you can still use append as follows:
+
+```js
+const username = 'user1'
+const email = '[e-mail]'
+const userId = 1
+
+const sql = SQL`UPDATE users SET name = ${username}, email = ${email}`
+sql.append(SQL`, ${dynamicName} = 'dynamicValue'`, { unsafe: true })
+sql.append(SQL`WHERE id = ${userId}`)
+```
+
 ## Utilities
@@ -142,0 +148,0 @@

SQL.d.ts

@@ -47,8 +47,2 @@ /** A tagged template containing strings and values */
 
-  /**
-   * Generates a PostgreSQL or MySQL statement string from this statement's strings and values
-   * @param type the type of statement string to be generated
-   */
-  generateString(type?: 'pg' | 'mysql'): string
-
   /** Returns a formatted but unsafe statement of strings and values, useful for debugging */
@@ -68,2 +62,3 @@ get debug(): string
    * Appends another statement onto this statement
+   * @deprecated Please append within template literals, e.g. SQL`SELECT * ${sql}`
    * @param statement a statement to be appended onto this existing statement
@@ -70,0 +65,0 @@ * @param options allows disabling the safe template escaping while appending

SQL.js

@@ -48,3 +48,3 @@ 'use strict'
 
-  generateString (type) {
+  _generateString (type, namedValueOffset = 0) {
     let text = this.strings[0]
@@ -62,6 +62,10 @@ let valueOffset = 0
         valueOffset--
+      } else if (valueContainer instanceof SqlStatement) {
+        text += `${valueContainer._generateString(type, valueIndex + namedValueOffset)}${this.strings[i]}`
+        valueOffset += valueContainer.values.length - 1
+        values.splice(valueIndex, 1, ...valueContainer.values)
       } else {
         let delimiter = '?'
         if (type === 'pg') {
-          delimiter = '$' + (i + valueOffset)
+          delimiter = '$' + (i + valueOffset + namedValueOffset)
         }
@@ -85,2 +89,5 @@
         quote = ''
+      } else if (data instanceof SqlStatement) {
+        data = data.debug
+        quote = ''
       }
@@ -99,13 +106,23 @@ typeof data === 'string' ? (text += quote + data + quote) : (text += data)
   get text () {
-    return this.generateString('pg')
+    return this._generateString('pg')
   }
 
   get sql () {
-    return this.generateString('mysql')
+    return this._generateString('mysql')
   }
 
   get values () {
-    return this._values.filter(v => !v || !v[wrapped])
+    return this._values.filter(v => !v || !v[wrapped]).reduce((acc, v) => {
+      if (v instanceof SqlStatement) {
+        acc.push(...v.values)
+      } else {
+        acc.push(v)
+      }
+      return acc
+    }, [])
   }
 
+  /**
+   * @deprecated Please append within template literals, e.g. SQL`SELECT * ${sql}`
+   */
   append (statement, options) {
@@ -112,0 +129,0 @@ if (!statement) {

SQL.test.js

@@ -432,1 +432,127 @@ 'use strict'
 })
+
+test('should be able to append a SqlStatement within a template literal', t => {
+  const a = SQL`FROM table`
+  const selectWithLiteralExpression = SQL`SELECT * ${a}`
+
+  t.equal(
+    selectWithLiteralExpression.text,
+    'SELECT * FROM table'
+  )
+  t.equal(
+    selectWithLiteralExpression.sql,
+    'SELECT * FROM table'
+  )
+  t.equal(
+    selectWithLiteralExpression.debug,
+    'SELECT * FROM table'
+  )
+  t.end()
+})
+
+test('should be able to use SQL.glue within template literal', t => {
+  const pre = 'A'
+  const ids = [1, '2', 'three']
+  const idValues = ids.map(id => SQL`${id}`)
+  const names = ['Bee', 'Cee', 'Dee']
+  const nameValues = names.map(name => SQL`${name}`)
+  const post = 'B'
+  const sql = SQL`UPDATE my_table SET active = FALSE WHERE pre=${pre} AND id IN (${SQL.glue(idValues, ',')}) AND name IN (${SQL.glue(nameValues, ',')}) AND post=${post}`
+  t.equal(
+    sql.text,
+    'UPDATE my_table SET active = FALSE WHERE pre=$1 AND id IN ($2,$3,$4) AND name IN ($5,$6,$7) AND post=$8'
+  )
+  t.equal(
+    sql.sql,
+    'UPDATE my_table SET active = FALSE WHERE pre=? AND id IN (?,?,?) AND name IN (?,?,?) AND post=?'
+  )
+  t.equal(
+    sql.debug,
+    'UPDATE my_table SET active = FALSE WHERE pre=\'A\' AND id IN (1,\'2\',\'three\') AND name IN (\'Bee\',\'Cee\',\'Dee\') AND post=\'B\''
+  )
+  t.same(
+    sql.values,
+    ['A', 1, '2', 'three', 'Bee', 'Cee', 'Dee', 'B']
+  )
+  t.end()
+})
+
+test('should be able to use nested SQLStatements in template literal', t => {
+  const a = 'A'
+  const b = 'B'
+  const c = 'C'
+  const d = 'D'
+  const sql = SQL`UPDATE my_table SET active = FALSE WHERE a=${a} AND ${SQL`b=${b} AND ${SQL`c=${c}`}`} AND d=${d}`
+  t.equal(
+    sql.text,
+    'UPDATE my_table SET active = FALSE WHERE a=$1 AND b=$2 AND c=$3 AND d=$4'
+  )
+  t.equal(
+    sql.sql,
+    'UPDATE my_table SET active = FALSE WHERE a=? AND b=? AND c=? AND d=?'
+  )
+  t.equal(
+    sql.debug,
+    'UPDATE my_table SET active = FALSE WHERE a=\'A\' AND b=\'B\' AND c=\'C\' AND d=\'D\''
+  )
+  t.same(
+    sql.values,
+    ['A', 'B', 'C', 'D']
+  )
+  t.end()
+})
+
+test('examples in the readme work as expected', t => {
+  {
+    const username = 'user1'
+    const email = '[e-mail]'
+    const userId = 1
+
+    const updates = []
+    updates.push(SQL`name = ${username}`)
+    updates.push(SQL`email = ${email}`)
+
+    const sql = SQL`UPDATE users SET ${SQL.glue(updates, ' , ')} WHERE id = ${userId}`
+    t.equal(
+      sql.text,
+      'UPDATE users SET name = $1 , email = $2 WHERE id = $3'
+    )
+  }
+  {
+    const ids = [1, 2, 3]
+    const value = 'test'
+    const sql = SQL`
+      UPDATE users
+      SET property = ${value}
+      WHERE id
+      IN (${SQL.glue(ids.map(id => SQL`${id}`), ' , ')})
+    `
+    t.equal(
+      sql.text,
+      `UPDATE users
+SET property = $1
+WHERE id
+IN ($2 , $3 , $4)`)
+  }
+
+  {
+    const users = [
+      { id: 1, name: 'something' },
+      { id: 2, name: 'something-else' },
+      { id: 3, name: 'something-other' }
+    ]
+
+    const sql = SQL`INSERT INTO users (id, name) VALUES 
+      ${SQL.glue(
+        users.map(user => SQL`(${user.id},${user.name}})`),
+        ' , '
+      )}
+    `
+    t.equal(
+      sql.text,
+      `INSERT INTO users (id, name) VALUES
+($1,$2}) , ($3,$4}) , ($5,$6})`
+    )
+  }
+  t.end()
+})

No alert changes

Improved metrics

Total package byte prevSize

52889

increased by8.2%

Lines of code

1091

increased by13.53%

Number of lines in readme file

250

increased by2.46%

No dependency changes