node-mssql

The library uses connection pooling to reuse database connections across multiple requests in a single Node.js process. The sql.connect() function manages a single global connection pool.

Key behaviors:

• Singleton Pattern: There can only be one global connection pool connected at a time. Subsequent calls to sql.connect() with different configurations will not create a new connection if a pool is already connected.
• Idempotency: If the global pool is already connected, sql.connect() resolves immediately to the existing pool.
• Lifecycle: Do not call sql.close() after individual queries, as this destroys the pool for the entire application. Only call sql.close() when the application is shutting down (e.g., in a CLI tool or CRON job).

It is recommended to await or .then() the pool creation to ensure it is ready before executing queries.

const sql = require('mssql')
const config = { ... }

// run a query against the global connection pool
function runQuery(query) {
  // sql.connect() will return the existing global pool if it exists or create a new one if it doesn't
  return sql.connect(config).then((pool) => {
    return pool.query(query)
  })
}

node-mssql includes built-in deserializers for SQL Server's Geography and Geometry CLR data types.

Geography

Geography results are returned as objects containing srid, version, points, figures, shapes, and segments.

Important: When working with Geography points, use the lat and lng properties. Avoid using the x and y properties, as they are flipped for compatibility and may lead to incorrect coordinate mapping.

Geometry

Geometry results are returned as objects containing srid, version, points, figures, shapes, and segments. Unlike Geography, Geometry types consistently place x before y in their coordinate ordering.

// Example Geography output structure
{
  srid: 4326,
  version: 2,
  points: [
    Point { lat: 1, lng: 1, z: null, m: null },
    // ...
  ],
  // ...
}

// Example Geometry output structure
{
  srid: 4326,
  version: 1,
  points: [
    Point { x: 1, y: 1, z: null, m: null },
    // ...
  ],
  // ...
}

The node-mssql package publishes telemetry via Node.js diagnostics_channel. This allows APM tools to observe queries, connections, and internal events with near-zero overhead when no subscribers are active.

Constants for all channels are exported via the CHANNELS object.

Tracing Channels (Async Lifecycle)

These wrap async operations and emit start, end, asyncStart, asyncEnd, and error events. Subscribe using the pattern tracing:<channel_name>:<event>.

Important Security Note: The command, procedure, or statement fields in trace contexts contain the SQL text sent to the server. Because node-mssql is parameterized-first, user values appear in parameters and not in the SQL text. However, avoid hard-coding credentials or PII as inline SQL literals, as they will appear verbatim in traces.

const dc = require('node:diagnostics_channel')
const { CHANNELS } = require('mssql')

dc.subscribe(`tracing:${CHANNELS.TRACE_QUERY}:start`, ({ command, requestId }) => {
  console.log(`[${requestId}] Query: ${command}`)
})

dc.subscribe(`tracing:${CHANNELS.TRACE_QUERY}:error`, ({ requestId, error }) => {
  console.error(`[${requestId}] Failed:`, error.message)
})

By default, if a query returns multiple columns with the same name, node-mssql only returns metadata for the last column with that name, and values may be lost or incorrectly ordered.

To resolve this, use the arrayRowMode configuration parameter. This can be set globally in the connection config or per-request on a sql.Request instance. When arrayRowMode is true, rows are returned as arrays of values, and a separate columns array provides the metadata for each index, preserving all columns even if they share names.

const request = new sql.Request()
request.arrayRowMode = true
request
    .query("select 'asdf' as name, 'qwerty' as other_name, 'jkl' as name")
    .then(result => {
        console.log(result);
    });

// When streaming with arrayRowMode enabled, the 'recordset' event 
// returns an array of column metadata instead of a keyed object.
request.stream = true
request.arrayRowMode = true
request.query("select 'asdf' as name, 'qwerty' as other_name, 'jkl' as name")
request.on('recordset', recordset => console.log(recordset))

If you need to connect to multiple databases or separate pools for different operations (e.g., Read vs. Write), you should implement a custom pool manager using new sql.ConnectionPool(config).

An effective manager typically uses a Map to cache pool instances by name, ensuring that each unique configuration is only connected once.

// pool-manager.js
const mssql = require('mssql')
const pools = new Map();

module.exports = {
 /**
  * Get or create a pool. If a pool doesn't exist the config must be provided.
  * If the pool does exist the config is ignored (even if it was different to the one provided
  * when creating the pool)
  *
  * @param {string} name
  * @param {{}} [config]
  * @return {Promise.<mssql.ConnectionPool>}
  */
 get: (name, config) => {
  if (!pools.has(name)) {
   if (!config) {
    throw new Error('Pool does not exist');
   }
   const pool = new mssql.ConnectionPool(config);
   // automatically remove the pool from the cache if `pool.close()` is called
   const close = pool.close.bind(pool);
   pool.close = (...args) => {
    pools.delete(name);
    return close(...args);
   }
   pools.set(name, pool.connect());
  }
  return pools.get(name);
 },
 /**
  * Closes all the pools and removes them from the store
  *
  * @return {Promise<mssql.ConnectionPool[]>}
  */
 closeAll: () => Promise.all(Array.from(pools.values()).map((connect) => {
  return connect.then((pool) => pool.close());
 })),
};