{"id":11369,"library":"mssql","title":"Microsoft SQL Server Client for Node.js","description":"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.","status":"active","version":"12.3.1","language":"javascript","source_language":"en","source_url":"https://github.com/tediousjs/node-mssql","tags":["javascript","database","mssql","sql","server","msnodesql","sqlserver","tds","node-tds"],"install":[{"cmd":"npm install mssql","lang":"bash","label":"npm"},{"cmd":"yarn add mssql","lang":"bash","label":"yarn"},{"cmd":"pnpm add mssql","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Required for using the MSNodeSQLv8 driver on Windows or Linux/macOS 64 bits for native driver performance and Windows Authentication. It must be installed separately.","package":"msnodesqlv8","optional":true}],"imports":[{"note":"While CommonJS `require` is still supported, ESM `import` is preferred in modern Node.js environments (v18+). The default export `sql` provides the main API and access to connection pools and data types.","wrong":"const sql = require('mssql')","symbol":"sql","correct":"import sql from 'mssql'"},{"note":"SQL data types are exposed under `TYPES` (e.g., `TYPES.Int`, `TYPES.NVarChar`) or directly on the `sql` object (e.g., `sql.Int`). Direct named imports for `TYPES` are clearer.","wrong":"import { SqlTypes } from 'mssql'","symbol":"TYPES","correct":"import { TYPES } from 'mssql'"},{"note":"`Request` is used for building and executing queries, especially with parameters. `ConnectionPool` and `Transaction` are also commonly imported named exports.","symbol":"Request","correct":"import { Request } from 'mssql'"},{"note":"To use the MSNodeSQLv8 driver, you must import it from its specific path `mssql/msnodesqlv8` *after* installing the `msnodesqlv8` package. This changes the default driver for the imported `sql` object.","wrong":"import { msnodesqlv8 } from 'mssql'","symbol":"msnodesqlv8_driver","correct":"import sql from 'mssql/msnodesqlv8'"}],"quickstart":{"code":"import sql from 'mssql'; // Use require('mssql') for CommonJS\n\nconst sqlConfig = {\n  user: process.env.DB_USER ?? 'SA', // Use 'SA' or another user if not using Windows Auth\n  password: process.env.DB_PASSWORD ?? 'YourStrong@Password123!',\n  database: process.env.DB_NAME ?? 'master',\n  server: process.env.DB_SERVER ?? 'localhost', // or your server IP/hostname\n  pool: {\n    max: 10,\n    min: 0,\n    idleTimeoutMillis: 30000\n  },\n  options: {\n    encrypt: process.env.DB_ENCRYPT === 'true', // For Azure SQL, this should be true\n    trustServerCertificate: process.env.NODE_ENV !== 'production' // Change to true for local dev / self-signed certs\n  }\n};\n\nasync function runDatabaseOperations() {\n  let pool; // Declare pool outside try block for finally access\n  try {\n    pool = await sql.connect(sqlConfig);\n    console.log('Successfully connected to SQL Server.');\n\n    // Example 1: Insert data using a parameterized query\n    const newProductName = 'Test Product ' + Date.now();\n    const insertResult = await pool.request()\n      .input('productName', sql.NVarChar, newProductName)\n      .input('price', sql.Decimal(10, 2), 19.99)\n      .query`INSERT INTO Products (Name, Price) VALUES (@productName, @price); SELECT SCOPE_IDENTITY() AS NewId;`;\n    console.log(`Inserted new product with ID: ${insertResult.recordset[0].NewId}`);\n\n    // Example 2: Select data\n    const selectResult = await pool.query`SELECT Id, Name, Price FROM Products WHERE Name LIKE '%Test Product%';`;\n    console.log('Selected products:', selectResult.recordset);\n\n    // Example 3: Execute a stored procedure (assuming 'GetProductById' exists)\n    // const productId = insertResult.recordset[0].NewId;\n    // const spResult = await pool.request()\n    //   .input('id', sql.Int, productId)\n    //   .execute('GetProductById');\n    // console.log('Stored procedure result:', spResult.recordset);\n\n  } catch (err) {\n    console.error('Database operation failed:', err.message);\n    // Implement robust error handling here, e.g., logging, retries\n  } finally {\n    if (pool) {\n      await pool.close();\n      console.log('Database connection closed.');\n    }\n  }\n}\n\n// Ensure a Products table exists for the examples\n// CREATE TABLE Products (Id INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(255), Price DECIMAL(10, 2));\n// Make sure to set DB_USER, DB_PASSWORD, DB_NAME, DB_SERVER environment variables\nrunDatabaseOperations();","lang":"typescript","description":"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."},"warnings":[{"fix":"Ensure all connection configuration objects are treated as immutable once passed to `sql.connect()`. Make copies if modifications are necessary elsewhere in the application lifecycle.","message":"As of v12.0.0, configuration objects passed to `sql.connect()` are no longer cloned by the library. They are intended to be immutable and treated as readonly. Modifying these objects after passing them to `connect()` will result in undefined behavior.","severity":"breaking","affected_versions":">=12.0.0"},{"fix":"Enable TCP/IP in SQL Server Configuration Manager for your SQL Server instance and ensure the SQL Server Browser service is running or that you specify the port (e.g., 1433) directly in your connection string/config.","message":"Microsoft SQL Server typically requires TCP/IP protocol to be enabled on the server instance for external connections. This is often disabled by default in fresh SQL Server installations or development environments.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"For Azure, always set `options: { encrypt: true }`. For local development with self-signed certificates, use `options: { trustServerCertificate: true }`. For production with proper SSL certificates, `trustServerCertificate` should be `false` (default) and a valid CA provided.","message":"Connecting to Azure SQL Database or SQL Server with self-signed certificates requires specific `options` in the connection configuration, such as `encrypt: true` and `trustServerCertificate: true` (for self-signed certs). Incorrect settings will lead to connection failures.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Install `msnodesqlv8` with `npm install mssql msnodesqlv8` and use `import sql from 'mssql/msnodesqlv8'` (or `require('mssql/msnodesqlv8')`) to leverage this driver. Ensure your environment meets the native driver's OS requirements.","message":"The `msnodesqlv8` driver offers native performance and Windows Authentication, but it requires a separate `npm install msnodesqlv8` and then importing `sql` from `mssql/msnodesqlv8`. It also has specific OS requirements (Windows or Linux/macOS 64 bits).","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"When constructing connection strings manually, ensure that parts like `User Id`, `Password`, `Server`, and `Database` are correctly URL encoded (e.g., using `encodeURIComponent`). Using a config object for `sql.connect()` generally handles this for individual properties.","message":"URL encoding is critical for connection strings, especially for passwords or server names containing special characters. Incorrect encoding can lead to parsing errors or failed authentication.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Update to the latest `mssql` version to benefit from BigInt handling improvements. When working with `msnodesqlv8`, be aware that BigInts will be transmitted as strings and ensure your SQL schema can accommodate this if implicit conversion isn't desired.","message":"BigInt type handling has seen various fixes across versions. In older versions (e.g., before v11.0.1) passing BigInt parameters could lead to `TypeError`. The `msnodesqlv8` driver specifically treats BigInt as a string.","severity":"gotcha","affected_versions":">=11.0.0"}],"env_vars":null,"last_verified":"2026-04-19T00:00:00.000Z","next_check":"2026-07-18T00:00:00.000Z","problems":[{"fix":"Update `mssql` to version 11.0.1 or higher. Alternatively, convert BigInt values to a compatible number or string type before passing them as parameters if an update is not possible.","cause":"Attempting to pass a JavaScript BigInt type as a parameter to a query in older versions of `mssql` that lacked proper BigInt serialization support.","error":"TypeError: Do not know how to serialize a BigInt"},{"fix":"Verify credentials, ensure the user exists and has correct permissions in the SQL Server database. Check if SQL Server Authentication is enabled if not using Windows Authentication. If using Windows Authentication, ensure `trustedConnection: true` is set and `msnodesqlv8` is correctly configured.","cause":"Incorrect username, password, or an issue with authentication mechanisms (e.g., Windows Authentication not configured, or SQL Server Authentication not enabled/user not mapped).","error":"ConnectionError: Login failed for user 'yourusername'."},{"fix":"Ensure SQL Server is running, TCP/IP is enabled in SQL Server Configuration Manager, and no firewall rules are blocking the specified port (default 1433). Verify the `server` and `port` (if specified) in your connection configuration.","cause":"SQL Server is not running, TCP/IP is not enabled, firewall is blocking the connection, incorrect server name/IP, or incorrect port.","error":"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible."},{"fix":"Run `npm install msnodesqlv8` in your project directory. Ensure you are importing correctly with `import sql from 'mssql/msnodesqlv8'` (or `require('mssql/msnodesqlv8')`) and not from the base `mssql` package if you intend to use this specific driver.","cause":"The `msnodesqlv8` package was not installed or installed incorrectly, or the `msnodesqlv8` specific import path was used without the package being present.","error":"Error: Cannot find module 'msnodesqlv8' or module not found error when importing from 'mssql/msnodesqlv8'"},{"fix":"Ensure `sql.close()` is called before attempting to establish a new global connection. For most applications, use connection pools (the default behavior when using `sql.connect(config)`) to manage multiple connections without needing to manually close and reopen the global connection for each operation.","cause":"Attempting to call `sql.connect()` multiple times without first closing the previous global connection. This typically happens in development or when managing connections improperly outside a pool.","error":"Error: Global connection already exists. Call sql.close() first."}],"ecosystem":"npm"}