msnodesqlv8

The sql.Pool class manages a set of connections to handle concurrent work efficiently. It includes features like periodic heartbeats to check connection integrity, inactivity timeouts, and the ability to pause, resume, or cancel individual queries within the pool.

const sql = require('msnodesqlv8')

const pool = new sql.Pool({
  connectionString: 'Driver={ODBC Driver 13 for SQL Server};Server=(localdb)\node;Database=scratch;Trusted_Connection=yes;'
})

pool.on('open', (options) => console.log('Pool opened', options))
pool.on('error', e => console.error(e))

pool.open()

// Submitting a query
const q = pool.query('SELECT * FROM MyTable')
q.on('done', () => console.log('Query finished'))

// Controlling a query
q.cancelQuery() // Stops the query
q.pauseQuery()  // Pauses the query
q.resumeQuery() // Resumes the query

The library provides a modern asynchronous API where all promise-based methods are collected under a .promises property on the respective objects. This allows you to use async/await syntax instead of callbacks.

Key promise interfaces include:

• PoolPromises: Provides open() and close() for connection pools.
• SqlClientPromises: Provides query(), callProc(), and open() for direct SQL client operations.
• ConnectionPromises: Provides query(), callProc(), prepare(), getTable(), close(), and cancel() for active connections.
• BulkTableMgrPromises: Provides select(), insert(), delete(), and update() for bulk table operations.
• PreparedPromises: Provides query() and free() for prepared statements.

The query() method returns results as an array of JavaScript objects (one object per row). The queryRaw() method returns the raw database rows in a structured object containing meta and rows, where rows is an array of arrays.

// query() returns: [{ col1: val, col2: val }, ...]
conn.query("SELECT * FROM Employee", (err, res) => { ... });

// queryRaw() returns: { meta: [...], rows: [[val1, val2], [val1, val2]] }
conn.queryRaw("SELECT * FROM Employee", (err, res) => { ... });

Windows

msnodesqlv8 runs on both 32-bit and 64-bit versions of Node.js hosted on Windows. It supports all major Node.js versions (tested for >= 10).

Linux

If running on Linux, you must install the Microsoft ODBC driver. Version >= 17.5 is recommended and has been tested. Supported distributions include:

• Ubuntu 18.04 / 20.04
• Alpine 3.12
• Debian 10.5
• Fedora 32
• Windows Subsystem for Linux 2 (WSL2)

macOS (darwin)

Native binaries are supplied via NPM. The driver has been tested on macOS High Sierra.

BCP (Bulk Copy Program) allows for high-speed data insertion by binding columns in memory and performing a memory copy to the server. This requires ODBC Driver 17 or 18.

To use BCP, you must use the tableMgr to get a table object and then call setUseBcp(true) on that table.

// Example pattern for BCP
// 1. Get the table via the connection's table manager
const tm = theConnection.tableMgr();
const table = await tm.getTable(this.tableName);

// 2. Enable BCP on the table
table.setUseBcp(true);

// 3. Insert rows using the table instance
await table.insertRows(rows);

The project uses an interface-based architecture for SQL statements, categorized into three types to handle different execution patterns:

1. TransientStatement: Used for one-off query executions.
2. PreparedStatement: Used for parameterized, reusable statements.
3. TvpStatement: Used for Table-Valued Parameters (TVP).

All statements follow a standard lifecycle:

1. Creation: Created via OdbcStatementFactory.
2. Preparation: (Specific to PreparedStatement) The statement is prepared for execution.
3. Execution: The statement is executed with provided parameters.
4. Result Processing: Metadata for the result is collected.
5. Fetching: Rows are retrieved in batches.
6. Next Result Set: Moving to subsequent result sets if they exist.
7. Cleanup: The statement is freed.

When the driver detects a sql_variant type, it automatically queries the underlying type information and interprets the column as that specific type. For example, a sql_variant containing an integer will be returned as a JavaScript number type.

c.query("select cast(10000 as sql_variant) as data;",  (err, res) => {});