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)
- Event: 'connect'
- Event: 'end'
- Event: 'error'
- Event: 'debug'
- Event: 'infoMessage'
- Event: 'errorMessage'
- Event: 'databaseChange'
- Event: 'languageChange'
- Event: 'charsetChange'
- Event: 'secure'
- connection.connect(callback)
- connection.beginTransaction(callback, [name], [isolationLevel])
- connection.callProcedure(request)
- connection.cancel()
- connection.close()
- connection.commitTransaction(callback)
- connection.execSql(request)
- connection.execSqlBatch(request)
- connection.execBulkLoad(bulkLoad)
- connection.execute(request, parameters)
- connection.prepare(request)
- connection.reset(callback)
- connection.rollbackTransaction(callback, [name])
- connection.saveTransaction(callback)
- connection.transaction(callback)
- connection.unprepare(request)
- connection.newBulkLoad(tableName, callback)
new Connection(config)
Note: be aware of the different options field:
-
server
- Hostname to connect to.
-
authentication.type
-
Type of the authentication method, valid types are
default
,ntlm
,azure-active-directory-default
,azure-active-directory-password
,azure-active-directory-access-token
,azure-active-directory-msi-vm
,azure-active-directory-msi-app-service
, orazure-active-directory-service-principal-secret
azure-active-directory-default
- clientId (string): The (`client`) ID of the user you want to log in with.
azure-active-directory-password
- username (string): A user needs to provide the `userName` associated to their account.
- password (string): A user needs to provide the `password` associated to their account.
- clientId (string): Application (`client`) ID from your registered Azure application. The application has to have public client flows enabled and has to have `user_impersonation` permissions for Azure SQL Database. The user account that is being used also has to be assigned to the application, and has to grant consent (either explicitly or via admin consent). Note: the previously provided default client ID will expire in the near future and stop working.
- tenantId (string): Optional parameter for specific Azure tenant ID. (default:
common
). If your server does not support the/common
endpoint, please provide a tenantId. Note: this used to be calleddomain
.domain
will proceed to work but will be removed in the near future. azure-active-directory-access-token
- token (string): A user needs to provide the `token` which they retrieved elsewhere to forming the connection.
This uses
DefaultAzureCredential
to try multiple methods of authentication. You can find more information on what values to provide and how to provide them from this Microsoft document.Best Practices
Tedious supports an authentication type:azure-active-directory-password
. This is the recommended method because Tedious will handle the process of retrieving the Azure token internally instead of requiring the user to manually retrieve the token as shown below.How to get the token
There are different approaches if a user actually needs to retrieve a token manually. The details can be found in Microsoft documentation. Here are some examples:azure-active-directory-msi-vm
- clientId (string): If a user wants to connect to an Azure app service using a specific client account, the user must provide a `clientId` that is associated with their created identity. This is optional for retrieving the token from the Azure web app service.
- msiEndpoint (string): A user needs to provide the `msiEndpoint` for retriving the access token.
azure-active-directory-msi-app-service
- clientId (string): If a user wants to connect to an Azure app service using a specific client account, the user must provide a `clientId` that is associated with their created identity. This is optional for retrieving the token from the Azure web app service.
- msiEndpoint (string): An msi app service environment needs to provide the `msiEndpoint` to retrieve the access token.
- msiSecret (string): An msi app service environment needs to provide the `msiSecret` to retrieve the access token.
azure-active-directory-service-principal-secret
- clientId (string): Application (`client`) ID from your registered Azure application.
- clientSecret (string): The created `client secret` for this registered Azure application.
- tenantId (string): Directory (`tenant`) ID from your registered Azure application.
NTLM
- If using NTLM with node 17 or newer, the
--openssl-legacy-provider
command line flag must be enabled, else an error will be thrown. Check the FAQ for more information.
-
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.
-
authentication.options.token
- When using azure-active-directory-access-token authentication type, this is the access token to use for authentication.
-
authentication.options.clientId
- When using azure-active-directory-msi-app-service (optional), azure-active-directory-msi-vm (optional), or azure-active-directory-service-principal-secret authentication type, this is the Application (client) ID of the identity to use for authentication.
-
authentication.options.clientSecret
- When using azure-active-directory-service-principal-secret authentication type, this is a secret associated with the clientId to use for authentication.
-
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 acolumnNameReplacer
. (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
READ_COMMITED
). -
options.cryptoCredentialsDetails
-
When options.encrypt is set to true, an object may be supplied that will be used
as the
secureContext
field when calling new tls.TLSSocket. The available options are listed under tls.createSecureContext. (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:
true
) -
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 string value set to
strict
enables the TDS 8.0 protocol. Otherwise, encrypt can be set to a boolean value which determines whether or not the connection will be encrypted under the TDS 7.x protocol. (default:true
) -
options.fallbackToDefaultDb
-
By default, if the database requested by
options.database
cannot be accessed, the connection will fail with an error. However, ifoptions.fallbackToDefaultDb
is set totrue
, 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 withoptions.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
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 withoptions.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 (use for SQL Server 2000)
7_2
7_3_A
7_3_B
7_4
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
) (Textsize is set by a numeric value.) -
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:
true
). -
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.
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.
The debug message.
Event: 'infoMessage'
function (info) { }The server has issued an information message.
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.
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.
The name of the new active database
Event: 'languageChange'
function (languageName) { }The server has reported that the language has changed.
The newly active language.
Event: 'charsetChange'
function (charset) { }The server has reported that the charset has changed.
The new charset.
Event: 'secure'
function (cleartext) { }A secure connection has been established.
The
cleartext
stream of a tls SecurePair.
The cipher
and peer
certificate
(server certificate) may be inspected if desired.
connection.connect(callback)
Initialize a connection.
The callback is called when the connection is either successfully created (on a connect
event) or has an error (on a error
event).
If an error occurs, then err
will describe the error.
This parameter is optional, if a user choose to not include this parameter, then they have to call the
connection.on('connect', (err) => {});
to check the connection status.
If an error occurs, either a connection error or an aggregate error object will be returned.
- If a connection error is returned, then it can be directly logged.
- If an aggregate error object is returned, then it needs to be logged as an array of messages. Be careful to check the size of the error message array before logging it as an array
connection.beginTransaction(callback, [name], [isolationLevel])
Start a transaction.
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.
If an error occurred, a string with details of the error.
A string representing a name to associate with the transaction.
Optional, and defaults to an empty string.
Required when isolationLevel
is present.
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
.
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.
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.
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.
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.
A Request
object representing the request.
connection.execBulkLoad(bulkLoad, rows)
Executes a BulkLoad.
The row data. Accepts objects that implements the Iterable
or AsyncIterable
interface
(e.g. a Readable
stream or an AsyncGenerator
). Each row can either be an array or object.
- Using an Iterable object
- Using an AsyncIterable object
connection.execute(request, parameters)
Execute previously prepared SQL, using the supplied parameters.
A previously prepared Request
.
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
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.
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.
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.
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.
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.
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.
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.
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.
Pass null to commit the transaction or not null (an error) when the transaction is to be rolled back.
This is another callback you provide that tedious calls when it has committed the transaction or a rollback transaction. It will call done with one or more parameters, namely:
an indication of any problems with the final commit request
Yet another call back with error as first parameter, followed by variable number of arguments as array.
the additional parameters you provided to tedious, which tedious will reflect back to you
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.
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.
Here's the actual parameter input of transaction in TypeScript notation:
transaction <T extends (...args: any[]) => void>(cb: (err: Error | null | undefined, txDone?: (err: Error | null | undefined, done: T, ...args: Parameters<T>) => void) => void, isolationLevel?: typeof ISOLATION_LEVEL[keyof typeof ISOLATION_LEVEL])
connection.unprepare(request)
Release the SQL Server resources associated with a previously prepared request.
The request to be unprepared.
connection.newBulkLoad(tableName, options, callback)
Creates a new BulkLoad instance.
The name of the table to bulk-insert into.
-
checkConstraints
-
Honors constraints during bulk load, using T-SQL CHECK_CONSTRAINTS. (default:
false
) -
fireTriggers
-
Honors insert triggers during bulk load, using the T-SQL FIRE_TRIGGERS. (default:
false
) -
keepNulls
-
Honors null value passed, ignores the default values set on table, using T-SQL KEEP_NULLS. (default:
false
) -
tableLock
-
Places a bulk update(BU) lock on table while performing bulk load, using T-SQL TABLOCK. (default:
false
)
A function which will be called after the BulkLoad finishes executing. rowCount
will equal the
number of rows inserted.