Repository: tediousjs/node-mssql (js)

Issue:
Custom data type handlers
I'm loading `DATE`, `DATETIME` and `DATETIMEOFFSET(3)` from my SQL server database. I've only tested `DATETIME` with `node-mssql` so far, but it looks like it's parsed into a native JavaScript `Date`, which makes sense. However, I would want to change this so that...:

* `DATE` (`sql.Date`) into `js-joda`s `LocalDate`
* `DATETIME` (`sql.DateTime`) into `js-joda`'s `LocalDateTime`
* `DATETIMEOFFSET(x)` (`sql.DateTimeOffset [scale]`) into `js-joda`'s `ZonedDateTime`

Is there a way to register custom handlers for these types?

Pull request description:
Allow processing of return column values by callback
Related issues:

closes #1170

supersedes #1171 

Pre/Post merge checklist:

- [x] Update change log
- [x] Add docs to readme
- [x] fix mssqlv8 driver

---

Thanks to @anton-johansson for his first pass at this

Patch:
diff --git a/CHANGELOG.txt b/CHANGELOG.txt
index 39e783a..55128ec 100644
--- a/CHANGELOG.txt
+++ b/CHANGELOG.txt
@@ -1,6 +1,7 @@
-v8.0.0 (2021-??-??)
+v8.0.0 (2022-??-??)
 -------------------
 [new] Add table.rows.clear() method to allow for chunking updates ([#1094](https://github.com/tediousjs/node-mssql/pull/1094))
+[new] Add valueHandler map to store callbacks that are used to process row values ([#1356](https://github.com/tediousjs/node-mssql/pull/1356))
 [change] msnodesqlv8 driver detects os platform and attempts to pick correct connections string for it ((#1318)[https://github.com/tediousjs/node-mssql/pull/1318])
 [change] Updated to latest Tedious 14 ((#1312)[https://github.com/tediousjs/node-mssql/pull/1312])
 [change] Errors for bad bulk load parameters have slightly different error messages ((#1318)[https://github.com/tediousjs/node-mssql/pull/1318])
diff --git a/README.md b/README.md
index a0dddaf..961c4b9 100644
--- a/README.md
+++ b/README.md
@@ -512,6 +512,23 @@ async function example() {
 
 Similar to the global connection pool, you should aim to only close a pool when you know it will never be needed by the application again. Typically this will only be when your application is shutting down.
 
+### Result value manipulation
+
+In some instances it is desirable to manipulate the record data as it is returned from the database, this may be to cast it as a particular object (eg: `moment` object instead of `Date`) or similar.
+
+In v8.0.0+ it is possible to register per-datatype handlers:
+
+```js
+const sql = require('mssql')
+
+// in this example all integer values will return 1 more than their actual value in the database
+sql.valueHandler.set(sql.TYPES.Int, (value) => value + 1)
+
+sql.query('SELECT * FROM [example]').then((result) => {
+  // all `int` columns will return a manipulated value as per the callback above
+})
+```
+
 ## Configuration
 
 The following is an example configuration object:
diff --git a/lib/base/index.js b/lib/base/index.js
index 80ca5fc..bd29209 100644
--- a/lib/base/index.js
+++ b/lib/base/index.js
@@ -54,6 +54,13 @@ Object.defineProperty(module.exports, 'Promise', {
   }
 })
 
+Object.defineProperty(module.exports, 'valueHandler', {
+  enumerable: true,
+  value: shared.valueHandler,
+  writable: false,
+  configurable: false
+})
+
 for (const key in TYPES) {
   const value = TYPES[key]
   module.exports.exports[key] = value
diff --git a/lib/msnodesqlv8/index.js b/lib/msnodesqlv8/index.js
index da4e68b..1e68b7d 100644
--- a/lib/msnodesqlv8/index.js
+++ b/lib/msnodesqlv8/index.js
@@ -22,6 +22,13 @@ Object.defineProperty(module.exports, 'Promise', {
   }
 })
 
+Object.defineProperty(module.exports, 'valueHandler', {
+  enumerable: true,
+  value: base.valueHandler,
+  writable: false,
+  configurable: false
+})
+
 base.driver.name = 'msnodesqlv8'
 base.driver.ConnectionPool = ConnectionPool
 base.driver.Transaction = Transaction
diff --git a/lib/msnodesqlv8/request.js b/lib/msnodesqlv8/request.js
index abd9f74..c643105 100644
--- a/lib/msnodesqlv8/request.js
+++ b/lib/msnodesqlv8/request.js
@@ -8,6 +8,7 @@ const { IDS, objectHasProperty } = require('../utils')
 const { TYPES, DECLARATIONS, declare } = require('../datatypes')
 const { PARSERS: UDT } = require('../udt')
 const Table = require('../table')
+const { valueHandler } = require('../shared')
 
 const JSON_COLUMN_ID = 'JSON_F52E2B61-18A1-11d1-B105-00805F49916B'
 const XML_COLUMN_ID = 'XML_F52E2B61-18A1-11d1-B105-00805F49916B'
@@ -122,7 +123,12 @@ const createColumns = function (metadata, arrayRowMode) {
 }
 
 const valueCorrection = function (value, metadata) {
-  if ((metadata.sqlType === 'time') && (value != null)) {
+  const type = metadata && objectHasProperty(metadata, 'sqlType') && objectHasProperty(DECLARATIONS, metadata.sqlType)
+    ? DECLARATIONS[metadata.sqlType]
+    : null
+  if (type && valueHandler.has(type)) {
+    return valueHandler.get(type)(value)
+  } else if ((metadata.sqlType === 'time') && (value != null)) {
     value.setFullYear(1970)
     return value
   } else if ((metadata.sqlType === 'udt') && (value != null)) {
diff --git a/lib/shared.js b/lib/shared.js
index 5dc982c..16ef7d7 100644
--- a/lib/shared.js
+++ b/lib/shared.js
@@ -105,3 +105,10 @@ Object.defineProperty(module.exports, 'Promise', {
     PromiseLibrary = value
   }
 })
+
+Object.defineProperty(module.exports, 'valueHandler', {
+  enumerable: true,
+  value: new Map(),
+  writable: false,
+  configurable: false
+})
diff --git a/lib/tedious/index.js b/lib/tedious/index.js
index 0a0d2af..88eea03 100644
--- a/lib/tedious/index.js
+++ b/lib/tedious/index.js
@@ -22,6 +22,13 @@ Object.defineProperty(module.exports, 'Promise', {
   }
 })
 
+Object.defineProperty(module.exports, 'valueHandler', {
+  enumerable: true,
+  value: base.valueHandler,
+  writable: false,
+  configurable: false
+})
+
 base.driver.name = 'tedious'
 base.driver.ConnectionPool = ConnectionPool
 base.driver.Transaction = Transaction
diff --git a/lib/tedious/request.js b/lib/tedious/request.js
index 639c5eb..6c6b806 100644
--- a/lib/tedious/request.js
+++ b/lib/tedious/request.js
@@ -8,6 +8,7 @@ const { IDS, objectHasProperty } = require('../utils')
 const { TYPES, DECLARATIONS, declare, cast } = require('../datatypes')
 const Table = require('../table')
 const { PARSERS: UDT } = require('../udt')
+const { valueHandler } = require('../shared')
 
 const JSON_COLUMN_ID = 'JSON_F52E2B61-18A1-11d1-B105-00805F49916B'
 const XML_COLUMN_ID = 'XML_F52E2B61-18A1-11d1-B105-00805F49916B'
@@ -159,7 +160,10 @@ const createColumns = function (metadata, arrayRowMode) {
 }
 
 const valueCorrection = function (value, metadata) {
-  if ((metadata.type === tds.TYPES.UDT) && (value != null)) {
+  const type = getMssqlType(metadata.type)
+  if (valueHandler.has(type)) {
+    return valueHandler.get(type)(value)
+  } else if ((metadata.type === tds.TYPES.UDT) && (value != null)) {
     if (UDT[metadata.udtInfo.typeName]) {
       return UDT[metadata.udtInfo.typeName](value)
     } else {


Test patch:
diff --git a/test/common/tests.js b/test/common/tests.js
index 813fe78..9e21504 100644
--- a/test/common/tests.js
+++ b/test/common/tests.js
@@ -123,6 +123,19 @@ module.exports = (sql, driver) => {
   }
 
   return {
+    'value handler' (done) {
+      let callCount = 0
+      const callArgs = []
+      // assign a "spy" to the valuehandler for varchar
+      sql.valueHandler.set(sql.TYPES.VarChar, function () { callCount++; callArgs.push(arguments); return arguments[0].toUpperCase() })
+      sql.query('SELECT TOP 1 * FROM [streaming]  ').then((result) => {
+        assert.strictEqual(callCount, 1)
+        assert.strictEqual(result.recordset.length, 1)
+        assert.notStrictEqual(result.recordset[0], callArgs[0])
+        assert.notStrictEqual(result.recordset[0], callArgs[0][0].toUpperCase())
+        done()
+      }).catch(done)
+    },
     'stored procedure' (mode, done) {
       const req = new TestRequest()
       req.input('in', sql.Int, null)
diff --git a/test/common/unit.js b/test/common/unit.js
index 9c18eec..1efc449 100644
--- a/test/common/unit.js
+++ b/test/common/unit.js
@@ -1,6 +1,6 @@
 'use strict'
 
-/* globals describe, it */
+/* globals describe, it, afterEach */
 
 const sql = require('../../')
 const assert = require('assert')
@@ -352,3 +352,37 @@ describe('config cloning', () => {
     assert.notDeepStrictEqual(options, pool.config.options)
   })
 })
+
+describe('value handlers', () => {
+  afterEach('reset valueHandler', () => {
+    sql.valueHandler.clear()
+  })
+  it('can set a value handler', () => {
+    assert.strictEqual(sql.valueHandler instanceof Map, true)
+    assert.strictEqual(sql.valueHandler.size, 0)
+    sql.valueHandler.set(sql.TYPES.Int, (value) => value.toUpperCase())
+    assert.strictEqual(sql.valueHandler.size, 1)
+    assert.strictEqual(sql.valueHandler.has(sql.TYPES.Int), true)
+  })
+  it('can delete a value handler', () => {
+    assert.strictEqual(sql.valueHandler instanceof Map, true)
+    assert.strictEqual(sql.valueHandler.size, 0)
+    sql.valueHandler.set(sql.TYPES.Int, (value) => value.toUpperCase())
+    assert.strictEqual(sql.valueHandler.size, 1)
+    assert.strictEqual(sql.valueHandler.has(sql.TYPES.Int), true)
+    sql.valueHandler.delete(sql.TYPES.Int)
+    assert.strictEqual(sql.valueHandler.has(sql.TYPES.Int), false)
+    assert.strictEqual(sql.valueHandler.size, 0)
+  })
+  it('can reset all value handlers', () => {
+    assert.strictEqual(sql.valueHandler instanceof Map, true)
+    assert.strictEqual(sql.valueHandler.size, 0)
+    sql.valueHandler.set(sql.TYPES.Int, (value) => value.toUpperCase())
+    sql.valueHandler.set(sql.TYPES.BigInt, (value) => value.toUpperCase())
+    assert.strictEqual(sql.valueHandler.size, 2)
+    assert.strictEqual(sql.valueHandler.has(sql.TYPES.Int), true)
+    assert.strictEqual(sql.valueHandler.has(sql.TYPES.BigInt), true)
+    sql.valueHandler.clear()
+    assert.strictEqual(sql.valueHandler.size, 0)
+  })
+})
diff --git a/test/msnodesqlv8/msnodesqlv8.js b/test/msnodesqlv8/msnodesqlv8.js
index 51c5c00..96d766c 100644
--- a/test/msnodesqlv8/msnodesqlv8.js
+++ b/test/msnodesqlv8/msnodesqlv8.js
@@ -36,6 +36,7 @@ describe('msnodesqlv8', function () {
       })
     })
   )
+  afterEach(() => sql.valueHandler.clear())
 
   describe('basic test suite', function () {
     before(function (done) {
@@ -43,6 +44,7 @@ describe('msnodesqlv8', function () {
       sql.connect(cfg, done)
     })
 
+    it('value handler', done => TESTS['value handler'](done))
     it('stored procedure (exec)', done => TESTS['stored procedure']('execute', done))
     it('stored procedure (batch)', done => TESTS['stored procedure']('batch', done))
     it('user defined types', done => TESTS['user defined types'](done))
diff --git a/test/tedious/tedious.js b/test/tedious/tedious.js
index b9c0d41..99caffb 100644
--- a/test/tedious/tedious.js
+++ b/test/tedious/tedious.js
@@ -42,6 +42,7 @@ describe('tedious', () => {
       })
     })
   )
+  afterEach(() => sql.valueHandler.clear())
 
   describe('basic test suite', () => {
     before((done) => {
@@ -50,6 +51,7 @@ describe('tedious', () => {
       sql.connect(cfg, done)
     })
 
+    it('value handler', done => TESTS['value handler'](done))
     it('stored procedure (exec)', done => TESTS['stored procedure']('execute', done))
     it('stored procedure (batch)', done => TESTS['stored procedure']('batch', done))
     it('user defined types', done => TESTS['user defined types'](done))