DOCSEARCHbyAlgolia
Submit Docs

tedious

repository·master·Indexed 23 days ago

https://github.com/tediousjs/tedious

A pure-JavaScript implementation of the Tabular Data Stream (TDS) protocol, serving as a driver for Node.js applications to connect to Microsoft SQL Server. It supports TDS versions 7.1 through 7.4 (SQL Server 2000 to 2022) and provides features for executing SQL queries via the Request class, high-performance bulk inserts using BulkLoad, and support for Transparent Column Encryption (TCE).

Tokens
5.1K
Snippets
12
Records
25
Agent score
82%

What's inside tedious

  2. Run Tedious benchmarks

    master

    To run an existing benchmark, execute the specific benchmark file using node.

    Prerequisite: Because the benchmarks attempt to load tedious code from the lib directory, you must run npm run prepublish before executing any benchmark files to ensure the necessary files are prepared.

    npm run prepublish
node benchmarks/query/select-many-rows.js

  3. Configure nullable columns and login behavior

    master

    As of version 1.11.0, new columns are nullable by default. If your application requires the previous behavior, you can disable this by setting enableAnsiNullDefault to false in your configuration object.

    Note that default login behavior also changed in version 1.2.

  4. Understand TDS Token classes

    master

    Tedious uses a hierarchy of Token classes to represent different types of data received from the SQL Server via the TDS protocol. Each token class corresponds to a specific protocol event and includes properties relevant to that event.

    Key token categories include:

    • Metadata Tokens: e.g., ColMetadataToken (describes columns).
    • Row Tokens: e.g., RowToken and NBCRowToken (contain actual data rows).
    • Status/Completion Tokens: e.g., DoneToken, DoneInProcToken, DoneProcToken (indicate command completion and row counts).
    • Environment Change Tokens: e.g., DatabaseEnvChangeToken, LanguageEnvChangeToken, CharsetEnvChangeToken (signal changes in the connection environment).
    • Message Tokens: e.g., InfoMessageToken, ErrorMessageToken (provide server-side messages or errors).
    • Return Tokens: e.g., ReturnValueToken, ReturnStatusToken (handle return values from stored procedures).

    Each token has a name and a handlerName which maps to a method on the TokenHandler interface used by the driver to process the incoming stream.

  5. Understand the Metadata and BaseMetadata types

    master

    When working with TDS data types, Tedious uses BaseMetadata and Metadata types to describe column information.

    BaseMetadata contains the core properties for any column:

    • userType: The user-defined type number.
    • flags: Bitmask flags for the column.
    • type: The DataType (e.g., VarChar, Int, Binary).
    • collation: An optional Collation object (used for character types).
    • precision: The precision (applicable to numeric and decimal).
    • scale: The scale (applicable to numeric, decimal, time, datetime2, and datetimeoffset).
    • dataLength: The length (used for char, varchar, nvarchar, and varbinary).
    • schema: An optional XmlSchema object.
    • udtInfo: Optional information for User-Defined Types (UDT).

    Metadata extends BaseMetadata by adding an optional cryptoMetadata field for Always Encrypted support.

  6. Perform a bulk insert with BulkLoad

    master

    Use the BulkLoad class to perform high-performance bulk inserts into a SQL Server table. The workflow involves three steps:

    1. Create a BulkLoad instance using connection.newBulkLoad(tableName, options, callback).
    2. Define the table schema by calling bulkLoad.addColumn(...) for each column.
    3. Execute the load by passing the instance and an array of row objects to connection.execBulkLoad(bulkLoad, rows).

    Rows can be provided as arrays of values or as objects where keys match the column's objName.

    // optional BulkLoad options
const options = { keepNulls: true };

// instantiate - provide the table where you'll be inserting to, options and a callback
const bulkLoad = connection.newBulkLoad('MyTable', options, (error, rowCount) => {
  console.log('inserted %d rows', rowCount);
});

// setup your columns - always indicate whether the column is nullable
bulkLoad.addColumn('myInt', TYPES.Int, { nullable: false });
bulkload.addColumn('myString', TYPES.NVarChar, { length: 50, nullable: true });

// execute
connection.execBulkLoad(bulkLoad, [
  { myInt: 7, myString: 'hello' },
  { myInt: 23, myString: 'world' }
]);

  7. Configure BulkLoad options

    master

    When instantiating a BulkLoad object, you can provide an Options object to control T-SQL bulk load behavior:

    • checkConstraints: (boolean, default false) Honors constraints during bulk load.
    • fireTriggers: (boolean, default false) Honors insert triggers during bulk load.
    • keepNulls: (boolean, default false) Honors null values passed and ignores table default values.
    • lockTable: (boolean, default false) Places a bulk update (BU) lock on the table (TABLOCK).
    • order: (object, default {}) Specifies the ordering of the data to increase performance. Keys are column names and values must be 'ASC' or 'DESC'.

  8. Supported TDS versions

    master

    Tedious supports the following Tabular Data Stream (TDS) protocol versions and their corresponding Microsoft SQL Server versions:

    • TDS 7.4: SQL Server 2012, 2014, 2016, 2017, 2019, 2022
    • TDS 7.3.B: SQL Server 2008 R2
    • TDS 7.3.A: SQL Server 2008
    • TDS 7.2: SQL Server 2005
    • TDS 7.1: SQL Server 2000

  9. Execute SQL queries with the Request class

    master

    The Request class is used to execute SQL statements or stored procedures on a connection. You instantiate a Request with the SQL text (or procedure name) and a CompletionCallback. The callback is executed once the request is fully completed, either successfully or with an error.

    Important: Only one request can be executed on a connection at a time. Do not initiate a new request until the previous request's callback has been called.

    To execute the request, pass the Request instance to connection.execSql(request).

    const { Request } = require('tedious');
const request = new Request("select 42, 'hello world'", (err, rowCount) => {
  // Request completion callback...
});
connection.execSql(request);

  10. Generate SQL for temporary table creation

    master

    If you are performing a bulk insert into a temporary table (e.g., a table starting with #), you can use bulkLoad.getTableCreationSql() to generate a valid CREATE TABLE statement based on the columns you have already added to the BulkLoad instance.

    Note: To access a local temporary table after the bulk load, you must use the same connection and execute subsequent requests using Connection.execSqlBatch instead of Connection.execSql.

    var sql = bulkLoad.getTableCreationSql();

  11. Use the Transaction class

    master
    The Transaction class is used to manage TDS transaction operations. You instantiate it with a unique name and an optional isolationLevel. The class provides methods to generate payloads for different transaction lifecycle stages: beginPayload, commitPayload, rollbackPayload, and savePayload. These methods require a txnDescriptor (Buffer) and return an iterable object containing the generated buffer data.