tediore v5.0.1

Simple wrapper around tedious, with a few extras features. Note that the full tedious API is not currently supported, namely handling transactions and output parameters.

Install

npm install tediore

Config

Setup a dbConfig.json file or config object as follows:

{
	connection:
	{
		userName: "myUser",
		password: "myPassword",
		server: "127.0.0.1",
		options:
		{
			database: "myDatabase",
			connectTimeout: 15000,
            requestTimeout: 120000,
            useUTC: false,
            useColumnNames: false,
            rowCollectionOnDone: false,
            rowCollectionOnRequestCompletion: false
		}
	},
	pool:
	{
		min: 0,
        max: 25,
        idleTimeoutMillis: 300000,
        retryDelay: 5000,
        acquireTimeout: 60000
	},
	misc:
	{
		dateTimeFormat: "YYYY-MM-DD hh:mm:ss",
		dateFormat: "YYYY-MM-DD",
		timeFormat: "hh:mm:ss"
	}
}

The connection property uses the same configuration options as tedious' Connection class. The pool property uses the same configuration options as tedious-connection-pool.

The misc property accepts the following options:

• dateTimeFormat {String} The formatting that should be applied to DateTime columns when using stringify option.
• dateFormat {String} The formatting that should be applied to Date columns when using using stringify option.
• timeFormat {String} The formatting that should be applied to Time columns when using using stringify option.

API

Tediore exposes the following properties:

• connectionPool {Object} This a reference to the connection pool instance.
• types {Object} This is a reference to tedious TYPES.
• bulkLoad {Function} used to abstract tedious' BulkLoad.
• execSQL {Function} used to abstract tedious' Request.
  • options {Object} configuration objects as follows:
    • stringify {Boolean} If true then rows will have their values converted into string representations. Supported types here are defined here.
    • toArray {Boolean} If true formats the result sets as a 2D array, i.e. CSV matrix instead of an array of objects.
    • callProcedure {Boolean} If true execSQL uses tedious' callProcedure instead of execSql.

Usage (Common JS)

const tediore = require("tediore")
const dbConfig = require("./dbConfig.json")
const db = new tediore.Tediore(dbConfig)

Usage (ES2015)

import {Tediore} from "tediore"
import * as dbConfig from "./dbConfig.json"
const db = new Tediore(dbConfig)

execSQL

// Simple SELECT (one result set returned)
db.execSQL({statement: "SELECT TOP 1 * FROM [User]"})
.then(results => console.log(results))
.catch(error => console.log(error.message))

// Simple SELECT with parameterised query
db.execSQL(
{
    statement: "SELECT TOP 1 * FROM [User] WHERE ID = @ID",
    parameters:
    [
        ["ID", db.types.Int, 1]
    ]
})
.then(results => console.log(results))
.catch(error => console.log(error.message))

N.B. Check the examples folder for more usage examples.

bulkLoad

// bulkLoad -> returns a promise containing the number of rows inserted or an error.
const users =
[
    {
        FirstName: "Adam",
        LastName: "Jenson",
        EmailAddress: "adam.jensen@sarifindustries.com"
    },
    {
        FirstName: "Billy",
        LastName: "Bob",
        EmailAddress: "billy@bob.name"
    },
    {
        FirstName: "Charlie",
        LastName: "Cook",
        EmailAddress: "charlie.cook@gmail.com"
    }
]

const columns = []
columns.push(["FirstName", db.types.NVarChar, { nullable: true }])
columns.push(["LastName", db.types.NVarChar, { nullable: true }])
columns.push(["EmailAddress", db.types.NVarChar, { nullable: true }])
columns.push(["CreatedOn", db.types.DateTime, { nullable: false }])

const rows = []
users.forEach(user => rows.push({FirstName: user.FirstName, LastName: user.LastName, EmailAddress: user.EmailAddress, CreatedOn: new Date()}))
db.bulkLoad({table: `${dbConfig.options.database}.dbo.[User]`, columns, rows})
.then(rowCount => console.log(`Inserted ${rowCount} rows.`))
.catch(error => console.log(error.message))