Microsoft SQL Server Client for Node.js

12.3.1 · active · verified Sun Apr 19

mssql is a robust and actively maintained Node.js client for Microsoft SQL Server, currently stable at version 12.3.1. It features a rapid release cadence, with multiple minor and patch versions frequently released, often within weeks, and major versions roughly annually. The library supports multiple underlying TDS drivers: the pure JavaScript `Tedious` driver (default) and the native `MSNodeSQLv8` driver, offering flexibility based on deployment environment and performance needs. It provides a modern API with support for async/await, Promises, ES6 tagged template literals, and streaming, alongside traditional callbacks. Key differentiators include its comprehensive API for managing connections, connection pools, and requests, as well as explicit support for SQL Server features like input/output parameters, stored procedure execution, and bulk inserts.

Common errors

Warnings

Install

Imports

Quickstart

This quickstart demonstrates how to establish a connection to SQL Server using a configuration object, perform parameterized inserts, retrieve data using a tagged template literal query, and properly close the connection pool. It uses environment variables for sensitive credentials and shows basic error handling.

import sql from 'mssql'; // Use require('mssql') for CommonJS

const sqlConfig = {
  user: process.env.DB_USER ?? 'SA', // Use 'SA' or another user if not using Windows Auth
  password: process.env.DB_PASSWORD ?? 'YourStrong@Password123!',
  database: process.env.DB_NAME ?? 'master',
  server: process.env.DB_SERVER ?? 'localhost', // or your server IP/hostname
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
  },
  options: {
    encrypt: process.env.DB_ENCRYPT === 'true', // For Azure SQL, this should be true
    trustServerCertificate: process.env.NODE_ENV !== 'production' // Change to true for local dev / self-signed certs
  }
};

async function runDatabaseOperations() {
  let pool; // Declare pool outside try block for finally access
  try {
    pool = await sql.connect(sqlConfig);
    console.log('Successfully connected to SQL Server.');

    // Example 1: Insert data using a parameterized query
    const newProductName = 'Test Product ' + Date.now();
    const insertResult = await pool.request()
      .input('productName', sql.NVarChar, newProductName)
      .input('price', sql.Decimal(10, 2), 19.99)
      .query`INSERT INTO Products (Name, Price) VALUES (@productName, @price); SELECT SCOPE_IDENTITY() AS NewId;`;
    console.log(`Inserted new product with ID: ${insertResult.recordset[0].NewId}`);

    // Example 2: Select data
    const selectResult = await pool.query`SELECT Id, Name, Price FROM Products WHERE Name LIKE '%Test Product%';`;
    console.log('Selected products:', selectResult.recordset);

    // Example 3: Execute a stored procedure (assuming 'GetProductById' exists)
    // const productId = insertResult.recordset[0].NewId;
    // const spResult = await pool.request()
    //   .input('id', sql.Int, productId)
    //   .execute('GetProductById');
    // console.log('Stored procedure result:', spResult.recordset);

  } catch (err) {
    console.error('Database operation failed:', err.message);
    // Implement robust error handling here, e.g., logging, retries
  } finally {
    if (pool) {
      await pool.close();
      console.log('Database connection closed.');
    }
  }
}

// Ensure a Products table exists for the examples
// CREATE TABLE Products (Id INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(255), Price DECIMAL(10, 2));
// Make sure to set DB_USER, DB_PASSWORD, DB_NAME, DB_SERVER environment variables
runDatabaseOperations();

view raw JSON →