Fork me on GitHub

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 = {
  "authentication": {
    ...,
    "options": {...}
  },

  "options": {
    ...
  }
};
var connection = new Connection(config);
config

Note: be aware of the different options field:

1) config.authentication.options
2) config.options

server
Hostname to connect to.
authentication.type
Type of the authentication method, valid types are default, ntlm, azure-active-directory-password
authentication.options.userName
User name to use for authentication.
authentication.options.password
Password to use for authentication.
authentication.options.domain
Once you set domain for ntlm authentication type, driver will connect to SQL Server using domain login.
options.abortTransactionOnError
A boolean determining whether to rollback a transaction automatically if any error is encountered during the given transaction's execution. This sets the value for SET XACT_ABORT during the initial SQL phase of a connection (documentation).
options.appName
Application name used for identifying a specific application in profiling, logging or tracing tools of SQL Server. (default: Tedious)
options.camelCaseColumns
A boolean, controlling whether the column names returned will have the first letter converted to lower case (true) or not. This value is ignored if you provide a columnNameReplacer. (default: false).
options.cancelTimeout
The number of milliseconds before the cancel (abort) of a request is considered failed (default: 5000).
options.columnNameReplacer
A function with parameters (columnName, index, columnMetaData) and returning a string. If provided, this will be called once per column per result-set. The returned value will be used instead of the SQL-provided column name on row and meta data objects. This allows you to dynamically convert between naming conventions. (default: null).
options.connectionRetryInterval
Number of milliseconds before retrying to establish connection, in case of transient failure. (default: 500)
options.connectTimeout
The number of milliseconds before the attempt to connect is considered failed (default: 15000).
options.connectionIsolationLevel
The default isolation level for new connections. All out-of-transaction queries are executed with this setting. The isolation levels are available from require('tedious').ISOLATION_LEVEL .
  • READ_UNCOMMITTED
  • READ_COMMITTED
  • REPEATABLE_READ
  • SERIALIZABLE
  • SNAPSHOT
(default: READ_COMMITED).
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.database
Database to connect to (default: dependent on server configuration).
options.datefirst
Sets the first day of the week to a number from 1 through 7.
options.dateFormat
A string representing position of month, day and year in temporal datatypes. (default: mdy)
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.enableAnsiNull
A boolean, controls the way null values should be used during comparison operation. (default: true)
options.enableAnsiNullDefault
If true, SET ANSI_NULL_DFLT_ON ON will be set in the initial sql. This means new columns will be nullable by default. See the T-SQL documentation for more details. (Default: true).
options.enableAnsiPadding
A boolean, controls if padding should be applied for values shorter than the size of defined column. (default: true)
options.enableAnsiWarnings
If true, SQL Server will follow ISO standard behavior during various error conditions. For details, see documentation. (default: true)
options.enableArithAbort
Ends a query when an overflow or divide-by-zero error occurs during query execution. See documentation for more details. (default: false)
options.enableConcatNullYieldsNull
A boolean, determines if concatenation with NULL should result in NULL or empty string value, more details in documentation. (default: true)
options.enableCursorCloseOnCommit
A boolean, controls whether cursor should be closed, if the transaction opening it gets committed or rolled back. (default: null)
options.enableImplicitTransactions
A boolean, sets the connection to either implicit or autocommit transaction mode. (default: false)
options.enableNumericRoundabort
If false, error is not generated during loss of precession. (default: false)
options.enableQuotedIdentifier
If true, characters enclosed in single quotes are treated as literals and those enclosed double quotes are treated as identifiers. (default: true)
options.encrypt
A boolean determining whether or not the connection will be encrypted. Set to true if you're on Windows Azure. (default: false).
options.fallbackToDefaultDb
By default, if the database requested by options.database cannot be accessed, the connection will fail with an error. However, if options.fallbackToDefaultDb is set to true, then the user's default database will be used instead (Default: false).
options.instanceName
The instance name to connect to. The SQL Server Browser service must be running on the database server, and UDP port 1434 on the database server must be reachable.
(no default)
Mutually exclusive with options.port.
options.isolationLevel
The default isolation level that transactions will be run with. The isolation levels are available from require('tedious').ISOLATION_LEVEL .
  • READ_UNCOMMITTED
  • READ_COMMITTED
  • REPEATABLE_READ
  • SERIALIZABLE
  • SNAPSHOT
(default: READ_COMMITED).
options.language
Specifies the language environment for the session. The session language determines the datetime formats and system messages. (default: us_english).
options.localAddress
A string indicating which network interface (ip address) to use when connecting to SQL Server.
options.maxRetriesOnTransientErrors
The maximum number of connection retries for transient errors. (default: 3).
options.multiSubnetFailover
Sets the MultiSubnetFailover = True parameter, which can help minimize the client recovery latency when failovers occur. (default: false).
options.packetSize
The size of TDS packets (subject to negotiation with the server). Should be a power of 2. (default: 4096).
options.port
Port to connect to (default: 1433).
Mutually exclusive with options.instanceName.
options.readOnlyIntent
A boolean, determining whether the connection will request read only access from a SQL Server Availability Group. For more information, see here. (default: false).
options.requestTimeout
The number of milliseconds before a request is considered failed, or 0 for no timeout (default: 15000).
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. If server doesn't support specified version, negotiated version is used instead. The versions are available from require('tedious').TDS_VERSION.
  • 7_1
  • 7_2
  • 7_3_A
  • 7_3_B
  • 7_4
(default: 7_4).
options.textsize
Specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement. (default: 2147483647).
options.trustServerCertificate
If "true", the SQL Server SSL certificate is automatically trusted when the communication layer is encrypted using SSL. If "false", the SQL Server validates the server SSL certificate. If the server certificate validation fails, the driver raises an error and terminates the connection. Make sure the value passed to serverName exactly matches the Common Name (CN) or DNS name in the Subject Alternate Name in the server certificate for an SSL connection to succeed. (default: false).
options.useColumnNames
A boolean determining whether to return rows as arrays or key-value collections. (default: false).
options.useUTC
A boolean determining whether to pass time values in UTC or local time. (default: true).

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 an error object.

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: 'error'

function (err) { }

Internal error occurs.

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 successful 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 occurred 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 occurred, 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').ISOLATION_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.cancel()

Cancel currently executed request.

connection.close()

Closes the connection to the database.

The end will be emitted 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. This may also result in SQL server treating the request like a stored procedure which can result in the doneInProc or doneProc events being emitted instead of the done event you might expect. Using execSqlBatch will prevent this from occurring but may have a negative performance impact.

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 occurred 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 occurred, 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.execBulkLoad(bulkLoad)

Executes a BulkLoad.

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 occurred 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 occurred, 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 occurred 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 occurred, a string with details of the error.

connection.saveTransaction(callback)

Set a savepoint within 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 set a savepoint within the transaction has completed, either successfully or with an error. If an error occurred 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 occurred, a string with details of the error.

connection.transaction(callback, name, isolationLevel)

Run the given callback after starting a transaction, and commit or rollback the transaction afterwards.

This is a helper that employs beginTransaction, commitTransaction, rollbackTransaction and saveTransaction to greatly simplify the use of database transactions and automatically handle transaction nesting.

callback
function (err, done) { }

The callback is called when the request to start a transaction (or create a savepoint, in the case of a nested transaction) has completed, either successfully or with an error. If an error occurred, then err will describe the error. If no error occurred, the callback should perform its work and eventually call done with an error or null (to trigger a transaction rollback or a transaction commit) and an additional completion callback that will be called when the request to rollback or commit the current transaction has completed, either successfully or with an error. Additional arguments given to done will be passed through to this callback.

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

err

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

done

If no error occurred, a function to be called to commit or rollback the transaction.

name

A string representing a name to associate with the transaction.

Optional, and defaults to an empty string. In case of a nested transaction, naming the transaction name has no effect.

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.unprepare(request)

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

request

The request to be unprepared.

connection.newBulkLoad(tableName, callback)

Creates a new BulkLoad instance.

tableName

The name of the table to bulk-insert into.

callback
function (err, rowCount) { }

A function which will be called after the BulkLoad finishes executing. rowCount will equal the number of rows inserted.