tedious-promises v0.4.1
Wraps Tedious SQL commands with
Q
ores6
promises. Uses fluent syntax
Whats new?
- Transaction support (beta)
- Alternate return data formats (see Row Transformers)
- Should support any promise library
Install
$ npm install --save tedious-promises
Configure
Sample: config.json See the Tedious documentation for configuration details.
{
"userName": "user",
"password": "pass",
"server": "server",
"options": {
"database": "dbo",
"encrypt": true,
}
}
Initialization without connection pooling
var tp = require('tedious-promises');
var dbConfig = require('config.json');
var TYPES = require('tedious').TYPES;
tp.setConnectionConfig(dbConfig); // global scope
Initialization with connection pooling
var tp = require('tedious-promises');
var dbConfig = require('config.json');
var TYPES = require('tedious').TYPES;
var ConnectionPool = require('tedious-connection-pool');
var poolConfig = {}; // see tedious-connection-pool documentation
var pool = new ConnectionPool(poolConfig, dbConfig);
tp.setConnectionPool(pool); // global scope
Configure automatic column renaming
The callback can be any function that accepts a single string parameter, and returns a string.
var _ = require('lodash');
tp.setDefaultColumnRenamer(_.camelCase); // global scope
Use
Basic usage
tp.sql("SELECT col1, col2 FROM dbo.table")
.execute()
.then(function(results) {
// do something with the results
}).fail(function(err) {
// do something with the failure
});
results === [{
col1: 'row 1 col 1',
col2: 'row 1 col 2'
}, {
col1: 'row 2 col 1',
col2: 'row 2 col 2'
}]
Overriding column behavior
tp.sql("SELECT col1, col2, col3, col4 FROM dbo.table")
.column('col1', 'firstName') // rename column
.column('col2', 'lastName')
.column('col3', 'nameParts.first') // create 'nameParts' object with 'first' property
.column('col4', 'nameParts.last')
.execute()
.then(function(results) {
// do something with the results
}).fail(function(err) {
// do something with the failure
});
Column types conversion
asBoolean can convert from:
- null === null
- Any integer: 0 === false
- strings: 'TRUE','T', 'Y', 'YES', '1' 'FALSE', 'F', 'N', 'NO', '0'
asDate can convert from:
- null === null
- integer: new Date(value)
- string: Date.parse(value)
tp.sql("SELECT col1, col2 FROM dbo.table")
.column('col1').asBoolean()
.column('col2').asDate()
.execute()
.then(function(results) {
// do something with the results
}).fail(function(err) {
// do something with the failure
});
In a function returning the promise, with a parameter
function getData(id) {
return tp.sql("SELECT col1, col2, FROM table WHERE id_col = @id")
.parameter('id', TYPES.Int, id)
.execute();
}
Returning the generated key from an identity column
(Technically, you don't need to use 'as id', but it makes the code easier to read.)
tp.sql("INSERT INTO table (col1, col2) VALUES ('x','y'); SELECT @@identity as id")
.execute()
.then(function(results) {
console.log(results[0].id);
});
Handeling each row yourself
tp.sql("SELECT * FROM table")
.forEachRow(function(row) {
// do something with the row
})
.execute()
.then(function(results) {
// result is row count
}).fail(function(err) {
// do something with the failure
});
Return row count instead of data
Only usefull for INSERT, UPDATE, and DELETE statements
tp.sql("insert into table (col1, col2) values('qwerty', '123')" )
.returnRowCount()
.execute()
.then(function(rowCount) {
// done, you have the modified row count
}).fail(function(err) {
// do something with the failure
});
Row transformers
- 'rowToObject' (default) converts each row into an object where the column names become the keys
[{
col1: 'row 1 col 1',
col2: 'row 1 col 2'
}, {
col1: 'row 2 col 1',
col2: 'row 2 col 2'
}]
- 'rowToArray' converts each row into an array of values
[
['row 1 col 1', 'row 1 col 2'],
['row 2 col 1', 'row 2 col 2']
]
- You can also pass in a function to do your own row transformation.
function customTransformer(row, getColumnMap) {
result = []; // Or {}, or anything you'd like
for (var i = 0; i < row.length; i++) {
var col = row[i];
var name = col.metadata.colName;
// The getColumnMap function returns the built in column mappings.
// The GetColumnValue function returns the columns value after being
// processed by transformers like asBoolean() and asDate().
// See TediousPromiseColumn.js for other column functions.
var map = this.GetColumnMap(name);
var value = map.GetColumnValue(col)
// do something with the column name and value
}
return result;
}
Promises
The execute
function returns a promise (as do the transaction functions).
By default this is a Q promise.
es6 promises are also supported out of the box, but you should be able to
use any promise library by writing a small polyfill.
es6 Promises
// Set when you initialize TP to make it global
tp.setConnectionPool(poolConfig);
tp.setPromiseLibrary('es6');
// You can also set the promise library on specific sql commands
return tp.sql('SELECT something FROM something')
.setPromiseLibrary('es6')
.execute();
Transactions
Transaction support in Tedious has been around for a long time, but it's new to Tedious Promises. So consider it beta for now. It is possible that there will be breaking changes in the future. (Initial implemetation by @akanieski)
var trans;
// create the transaction from the a tp instance
tp.beginTransaction()
.then(function(newTransaction) {
// remember the transaction, you'll need it later
trans = newTransaction;
// use the transaction like a normal tp instance
// ('return' chains the promises)
return trans.sql(testSql)
.returnRowCount()
.execute();
})
.then(function(testResult) {
// this is the result of executing testSql on the transaction
// do something with it
// you can execute another sql statement using the same syntax as above
// i.e. return trans.sql(...
// when you're done using the transaction, commit it
return trans.commitTransaction();
})
.fail(function(err) {
// rollback on failures
return trans.rollbackTransaction();
})
Mocking for unit tests
Set the global mock function instead of setConnectionConfig or setConnectionPool to intercept all calls to tp.execute()
tp.setMockDataCallback(function(sql, parameters) {
if(sql === 'Select...' && parameters.id.value === 123) {
return data; // an array of the objects you'd normally get back
}
return [];
});
parameters === {
id: {
name: 'id',
type: TYPES.Int,
value: 123,
options: null
}
}
Setup end to end testing
- Create a SQL database either in Azure or locally
- Sample user setup and permissions are in
test/database/init.sql
- Create
test/database/config.json
based upon the sample below
- Replace all
REQUIRED
fields with your database credentials - This file is git ignored so that it isn't accidently checked in
- Run
grunt resetTestDatabase
to create the tables and populate test data - Run
grunt
to run the tests, orgrunt watch
to run the test on every file change
{
"userName": "REQUIRED",
"password": "REQUIRED",
"server": "REQUIRED",
"options": {
"database": "REQUIRED",
"encrypt": true,
"debug": {
"packet": false
}
}
}
To do
- documentation
- sql generation and/or integration of a LINQ package
- local unit tests (current tests require a real database)
- more tests for the mock connection
- [![NPM version][npm-image]][npm-url]
- [![Build Status][travis-image]][travis-url]
License
MIT © Charles Hill