@nearform/sql - npm Package Compare versions

Comparing version 1.9.2 to 1.10.0

package.json

 {
   "name": "@nearform/sql",
-  "version": "1.9.2",
+  "version": "1.10.0",
   "description": "SQL injection protection module",
@@ -12,3 +12,3 @@ "main": "./SQL.js",
     "test:typescript": "tsd",
-    "pretest:security": "napa sqlmapproject/sqlmap && node ./sqlmap/db-init.js",
+    "pretest:security": "napa https://github.com/sqlmapproject/sqlmap && node ./sqlmap/db-init.js",
     "lint": "standard",
@@ -30,3 +30,3 @@ "benchmark": "node benchmark/index.js"
     "benchmark": "^2.1.4",
-    "fastify": "^3.7.0",
+    "fastify": "^4.0.1",
     "fastify-postgres": "^3.1.0",
@@ -37,5 +37,5 @@ "jsonfile": "^6.1.0",
     "sql-template-strings": "^2.2.2",
-    "standard": "^16.0.3",
+    "standard": "^17.0.0",
     "tap": "^16.0.0",
-    "tsd": "^0.19.0"
+    "tsd": "^0.24.0"
   },
@@ -42,0 +42,0 @@ "standard": {

README.md

@@ -12,2 +12,3 @@ # SQL
    1. [glue](#gluepieces-separator)
+   2. [map](#maparray-mapperfunction)
    2. (deprecated) [append](#deprecated-appendstatement-options)
@@ -121,2 +122,34 @@ 4. [Utilities](#utilities)
 
+### map(array, mapperFunction)
+
+Using the default mapperFunction which is just an iteration over the array elements
+```js
+const ids = [1, 2, 3]
+
+const values = SQL.map(ids)
+const sql = SQL`INSERT INTO users (id) VALUES (${values})`
+```
+
+Using an array of objects which requires a mapper function
+
+```js
+const objArray = [{
+  id: 1,
+  name: 'name1'
+},
+{
+  id: 2,
+  name: 'name2'
+},
+{
+  id: 3,
+  name: 'name3'
+}]
+
+const mapperFunction = (objItem) => objItem.id
+const values = SQL.map(objArray, mapperFunction)
+
+const sql = SQL`INSERT INTO users (id) VALUES (${values})`
+```
+
 ### (deprecated) append(statement[, options])
@@ -224,2 +257,34 @@
 
+## Example custom utilities
+
+### Insert into from a JS object
+
+The below example functions can be used to generate an INSERT INTO statement from an object, which will convert the object keys to snake case.
+
+```js
+function insert(table, insertData, { toSnakeCase } = { toSnakeCase: false }) {
+  const builder = Object.entries(insertData).reduce(
+    (acc, [column, value]) => {
+      if (value !== undefined) {
+        toSnakeCase
+          ? acc.columns.push(pascalOrCamelToSnake(column))
+          : acc.columns.push(column)
+        acc.values.push(SQL`${value}`)
+      }
+      return acc
+    },
+    { columns: [], values: [] }
+  )
+  return SQL`INSERT INTO ${SQL.quoteIdent(table)} (${SQL.unsafe(
+    builder.columns.join(', ')
+  )}) VALUES (${SQL.glue(builder.values, ', ')})`
+}
+
+const pascalOrCamelToSnake = str =>
+  str[0].toLowerCase() +
+  str
+    .slice(1, str.length)
+    .replace(/[A-Z]/g, letter => `_${letter.toLowerCase()}`)
+```
+
 ## Testing, linting, & coverage
@@ -226,0 +291,0 @@

SQL.js

@@ -38,2 +38,16 @@ 'use strict'
 
+  /**
+   * A function that accepts an array of objects and a mapper function
+   * It returns a clean SQL format using the object properties defined in the mapper function
+   */
+  map (array, mapFunc = i => i) {
+    if ((mapFunc instanceof Function) && array?.length > 0) {
+      return this.glue(
+        array.map(mapFunc).map((item) => SQL`${item}`),
+        ','
+      )
+    }
+    return null
+  }
+
   _generateString (type, namedValueOffset = 0) {
@@ -161,2 +175,3 @@ let text = this.strings[0]
 SQL.glue = SqlStatement.prototype.glue
+SQL.map = SqlStatement.prototype.map
 
@@ -163,0 +178,0 @@ module.exports = SQL

SQL.test.js

@@ -88,2 +88,65 @@ 'use strict'
 
+test('SQL helper - build complex query with map', async t => {
+  const objArray = [{
+    id: 1,
+    name: 'name1'
+  },
+  {
+    id: 2,
+    name: 'name2'
+  },
+  {
+    id: 3,
+    name: 'name3'
+  }]
+
+  const mapFunction = (objItem) => {
+    return objItem.id
+  }
+
+  const values = SQL.map(objArray, mapFunction)
+  t.equal(values !== null, true)
+  const sql = SQL`INSERT INTO users (id) VALUES (${values})`
+
+  t.equal(sql.text, 'INSERT INTO users (id) VALUES ($1,$2,$3)')
+  t.equal(sql.sql, 'INSERT INTO users (id) VALUES (?,?,?)')
+  t.equal(sql.debug, 'INSERT INTO users (id) VALUES (1,2,3)')
+  t.same(sql.values, [1, 2, 3])
+})
+
+test('SQL helper - build complex query with map - using default mapper function', async t => {
+  const ids = [1, 2, 3]
+
+  const values = SQL.map(ids)
+  t.equal(values !== null, true)
+  const sql = SQL`INSERT INTO users (id) VALUES (${values})`
+
+  t.equal(sql.text, 'INSERT INTO users (id) VALUES ($1,$2,$3)')
+  t.equal(sql.sql, 'INSERT INTO users (id) VALUES (?,?,?)')
+  t.equal(sql.debug, 'INSERT INTO users (id) VALUES (1,2,3)')
+  t.same(sql.values, [1, 2, 3])
+})
+
+test('SQL helper - build complex query with map - empty array', async t => {
+  const objArray = []
+
+  const mapFunction = (objItem) => {
+    return objItem.id
+  }
+
+  const values = SQL.map(objArray, mapFunction)
+
+  t.equal(values, null)
+})
+
+test('SQL helper - build complex query with map - bad mapper function', async t => {
+  const objArray = []
+
+  const mapFunction = null
+
+  const values = SQL.map(objArray, mapFunction)
+
+  t.equal(values, null)
+})
+
 test('SQL helper - build complex query with glue', async t => {
@@ -90,0 +153,0 @@ const name = 'Team 5'

No alert changes

Improved metrics

Total package byte prevSize

58578

increased by6.23%

Number of package files

29

increased by3.57%

Lines of code

1175

increased by5.57%

Number of lines in readme file

319

increased by25.59%

No dependency changes