Connection

A Connection instance represents a single connection to a database server.

Only one request at a time may be executed on a connection. Once a Request has been initiated (with callProcedure, execSql, or execSqlBatch), another should not be initiated until the Request's completion callback is called.

new Connection(config)

var Connection = require('tedious').Connection;
var config = {...};
var connection = new Connection(config);

config

userName

User name to use for authentication.

Only SQL Server accounts are supported. Windows domain accounts are not supported.

password

Password to use for authentication.

server

Hostname to connect to.

options.port

Port to connect to (default: 1433).
Mutually exclusive with options.instanceName.

options.instanceName

The instance name to connect to. The SQL Server Browser service must be running on the database server, and UDP port 1444 on the database server must be reachable.
(no default)
Mutually exclusive with options.port.

options.database

Database to connect to (default: dependent on server configuration).

options.connectTimeout

The number of milliseconds before the attempt to connect is considered failed (default: 15000).

options.requestTimeout

The number of milliseconds before a request is considered failed (default: 15000).

options.cancelTimeout

The number of milliseconds before the cancel (abort) of a request is considered failed (default: 5000).

options.packetSize

The size of TDS packets (subject to negotiation with the server). Should be a power of 2. (default: 4096).

options.debug.packet

A boolean, controlling whether debug events will be emitted with text describing packet details (default: false).

options.debug.data

A boolean, controlling whether debug events will be emitted with text describing packet data details (default: false).

options.debug.payload

A boolean, controlling whether debug events will be emitted with text describing packet payload details (default: false).

options.debug.token

A boolean, controlling whether debug events will be emitted with text describing token stream tokens (default: false).

options.isolationLevel

The default isolation level that transactions will be run with. The isolation levels are available from require('tedious').ISOLATON_LEVEL .

READ_UNCOMMITTED
READ_COMMITTED
REPEATABLE_READ
SERIALIZABLE
SNAPSHOT

(default: READ_UNCOMMITED).

options.encrypt

A boolean determining whether or not the connection will be encrypted. (default: false).
Encryption support is experimental.

options.cryptoCredentialsDetails

When encryption is used, an object may be supplied that will be used for the first argument when calling tls.createSecurePair (default: {}).

options.rowCollectionOnDone

A boolean, that when true will expose received rows in Requests' done* events. See done, doneInProc and doneProc. (default: false)

Caution: If many row are received, enabling this option could result in excessive memory usage.

options.rowCollectionOnRequestCompletion

A boolean, that when true will expose received rows in Requests' completion callback. See new Request. (default: false)

Caution: If many row are received, enabling this option could result in excessive memory usage.

options.tdsVersion

The version of TDS to use. The version are available from require('tedious').TDS_VERSION .

7_1 (limited support)
7_2
7_3_A (future use)
7_3_B (future use)

(default: 7_2).

Event: 'connect'

function (err) { }

The attempt to connect and validate has completed.

err

If successfully connected, will be falsey.

If there was a problem (with either connecting or validation), will be a string with details of the error.

Event: 'end'

function () { }

The connection has ended. This may be as a result of the client calling close(), the server closing the connection, or a network error.

Event: 'debug'

function (messageText) { }

A debug message is available. It may be logged or ignored.

messageText

The debug message.

Event: 'infoMessage'

function (info) { }

The server has issued an information message.

info

An object with these properties:

number: Error number

state: The error state, used as a modifier to the error number.

class: The class (severity) of the error. A class of less than 10 indicates an informational message.

message: The message text.

procName: The stored procedure name (if a stored procedure generated the message).

lineNumber: The line number in the SQL batch or stored procedure that caused the error. Line numbers begin at 1; therefore, if the line number is not applicable to the message, the value of LineNumber will be 0.

Event: 'errorMessage'

function (error) { }

The server has issued an error message.

error

An object with the same properties as.listed for the infoMessage event.

Event: 'databaseChange'

function (databaseName) { }

The server has reported that the active database has changed. This may be as a result of a sucessful login, or a use statement.

databaseName

The name of the new active database

Event: 'languageChange'

function (languageName) { }

The server has reported that the language has changed.

languageName

The newly active language.

Event: 'charsetChange'

function (charset) { }

The server has reported that the charset has changed.

charset

The new charset.

Event: 'secure'

function (cleartext) { }

A secure connection has been established.

cleartext

The cleartext stream of a tls SecurePair. The cipher and peer certificate (server certificate) may be inspected if desired.

connection.beginTransaction(callback, name, isolationLevel)

Start a transaction.

callback

function (err) { }

The callback is called when the request to start the transaction has completed, either successfully or with an error. If an error occured then err will describe the error.

As only one request at a time may be executed on a connection, another request should not be initiated until this callback is called.

err

If an error occured, a string with details of the error.

name

A string representing a name to associate with the transaction.

Optional, and defaults to an empty string. Required when isolationLevel is present.

isolationLevel

The isolation level that the transaction is to be run with. The isolation levels are available from require('tedious').ISOLATON_LEVEL .

READ_UNCOMMITTED
READ_COMMITTED
REPEATABLE_READ
SERIALIZABLE
SNAPSHOT

Optional, and defaults to the Connection's isolation level.

connection.callProcedure(request)

Call a stored procedure represented by request.

request

A Request object representing the request.

connection.close()

Closes the connection to the database.

The end will be emmited once the connection has been closed.

connection.execSql(request)

Execute the SQL represented by request.

As sp_executesql is used to execute the SQL, if the same SQL is executed multiples times using this function, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

Beware of the way that scoping rules apply, and how they may affect local temp tables. If you're running in to scoping issues, then execSqlBatch may be a better choice. See also issue #24.

request

A Request object representing the request.

connection.commitTransaction(callback)

Commit a transaction.

There should be an active transaction. That is, beginTransaction should have been previously called.

callback

function (err) { }

The callback is called when the request to commit the transaction has completed, either successfully or with an error. If an error occured then err will describe the error.

As only one request at a time may be executed on a connection, another request should not be initiated until this callback is called.

err

If an error occured, a string with details of the error.

connection.execSqlBatch(request)

Execute the SQL batch represented by request. There is no param support, and unlike execSql, it is not likely that SQL Server will reuse the execution plan it generates for the SQL.

In almost all cases, execSql will be a better choice.

request

A Request object representing the request.

connection.execute(request, parameters)

Execute previously prepared SQL, using the supplied parameters.

request

A previously prepared Request.

parameters

An object whose names correspond to the names of parameters that were added to the request before it was prepared. The object's values are passed as the parameters' values when the request is executed.

connection.prepare(request)

Prepare the SQL represented by the request. The request can then be used in subsequent calls to execute and unprepare

request

A Request object representing the request.

Parameters only require a name and type. Parameter values are ignored.

connection.reset(callback)

Reset the connection to its initial state. Can be useful for connection pool implementations.

callback

function (err) { }

The callback is called when the connection reset has completed, either successfully or with an error. If an error occured then err will describe the error.

As only one request at a time may be executed on a connection, another request should not be initiated until this callback is called.

err

If an error occured, a string with details of the error.

connection.rollbackTransaction(callback)

Rollback a transaction.

There should be an active transaction. That is, beginTransaction should have been previously called.

callback

function (err) { }

The callback is called when the request to rollback the transaction has completed, either successfully or with an error. If an error occured then err will describe the error.

As only one request at a time may be executed on a connection, another request should not be initiated until this callback is called.

err

If an error occured, a string with details of the error.

connection.unprepare(request)

Release the SQL Server resources associated with a previously prepared request.

request

The request to be unprepared.