mssql-ng
Next Generation MS-SQL Client Interface for Node.js/io.js
This module provides an interface to use JavaScript (ES6/ES2015) Template strings in order to construct SQL requests.
This library inherits mssql module under the covers. For use of template processors, you should use a version of node/io.js that supports template strings or use a transpiler like BabelJS.
Thank you Taras Mitran for the idea.
Usage:
\\reference to original module for use with legacy codevar mssql = ; \\mssql connection optionsvar opts = ... //mssql connection options \\ \\ extended with mssqlTYPES\\ query and queryStream template processors\\ input and output parameter handlersvar sql = ; ...\\ simple query template processor returns promise for a resultsqlquery` --you can specify output parameters (name, type, initialValue) SET
Responses with multiple recordsets are not supported. Use mssql
directly.
The module itself is a method that returns a Promise that will resolve with the active connection, similar to mssql.connect()
... there will be additional methods for template parsers attached to the Promise itself (these will not carry forward). This allows one to resolve the connection object directly (since mssql's resolver doesn't include a reference to the connection object).
var mssql = ;var sql = ; sql;
ConnectionPromise.query - returns Promise - resolves {parameters,recordset}
The .query
method is an ES6 template parser, which will resolve to an object containing sql request parameters
for access to output parameters, in addition to recordset
which will be an array of rows for the result.
ConnectionPromise.queryStream - returns Promise - resolves streaming mssql request object
Note: mssql/tedious does not support backpressure
The .queryStream
method is an ES6 template parser, which will resolve to an mssql.Request
which has made a query in stream mode, and is ready to pipe.
queryStream` SELECT * FROM SomeTable WHERE Foo= AND Baz= AND Something <= `
mssql
datatype
ConnectionPromise.input(name,type,value) - specify parameter name and If you want to specify the mssql
datatype, you can use sql.input
in your template.
sqlquery` EXEC someSproc sql.input('myParam', sql.Decimal(22,5), 45.333) `
ConnectionPromise.output(name,type,defaultValue)
If you need an output parameter, you can use sql.output
to specify the parameter name and type.
sqlquery` SET = SYSDATETIMEOFFSET() `
Automatic Type Detection
This module will only do very simplistic type checking...
- null or undefined -
Bit
set to null - Boolean - will use
Bit
- Number
~~num === num
-Int
Math.floor(num) === Math.ceil(num)
-BigInt
- otherwise
Float
- Objects
- Buffer -
VarBinary
- Date -
DateTimeOffset
- otherwise fallthrough to string using
JSON.stringify(safeclonedeep(value))
- Buffer -
- String
- UUID - via regular expression test -
UniqueIdentifier
- otherwise
NVarChar
- UUID - via regular expression test -
NOTE: If you need another interpretation for numbers, you can pass it as an appropriately formatted string (SQL coersion) or you can use sql.input
to specify a different type.
Requirements
- Requires a very recent 2.x release of mssql as a peerDependency.
- Requirews i-promise as a peerDependency, which requires native Promises, or a detected Promise library.
- If you wish to force a promise library, you can do so before requiring this module via
require('i-promise/config').use(require('bluebird'));
- If you wish to force a promise library, you can do so before requiring this module via