Microsoft SQL Server client for Node.js
Microsoft SQL Server client for Node.js
[![NPM Version][npm-image]][npm-url] [![NPM Downloads][downloads-image]][downloads-url]
Supported TDS drivers:
npm install mssqlnpm install mssql msnodesqlv8This package requires TCP/IP to connect to SQL Server, and you may need to enable this in your installation.
const sql = require('mssql')
(async () => {
try {
// make sure that any items are correctly URL encoded in the connection string
await sql.connect('Server=localhost,1433;Database=database;User Id=username;Password=password;Encrypt=true')
const result = await sql.query`select * from mytable where id = ${value}`
console.dir(result)
} catch (err) {
// ... error checks
}
})()If you're on Windows Azure, add ?encrypt=true to your connection string. See docs to learn more.
Parts of the connection URI should be correctly URL encoded so that the URI can be parsed correctly.
Assuming you have set the appropriate environment variables, you can construct a config object as follows:
…const sql = require('mssql/msnodesqlv8');
const config = {
server: "MyServer",
database: "MyDatabase",
options: {
trustedConnection: true, // Set to true if using Windows Authentication
trustServerCertificate: true, // Set to true if using self-signed certificates
},
// driver: "ODBC Driver 18 for SQL Server", // Uncomment to use specific driver
};
(async () => {
try {
await sql.connect(config);
const result = await sql.query`select TOP 10 * from MyTable`;
console.dir(result);
} catch (err) {
console.error(err);
}
})();const config = {
user: '...',
password: '...',
server: 'localhost', // You can use 'localhost\\instance' to connect to named instance
database: '...',
}…const sql = require('mssql')
sql.on('error', err => {
// ... error handler
})
sql.connect(config).then(pool => {
// Query
return pool.request()
.input('input_parameter', sql.Int, value)
.query('select * from mytable where id = @input_parameter')
}).then(result => {
console.dir(result)
}).catch(err => {
// ... error checks
});const sql = require('mssql')
sql.on('error', err => {
// ... error handler
})
sql.connect(config).then(pool => {
// Stored procedure
return pool.request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name')
}).then(result => {
console.dir(result)
}).catch(err => {
// ... error checks
})Native Promise is used by default. You can easily change this with sql.Promise = require('myownpromisepackage').
const sql = require('mssql')
sql.connect(config).then(() => {
return sql.query`select * from mytable where id = ${value}`
}).then(result => {
console.dir(result)
}).catch(err => {
// ... error checks
})
sql.on('error', err => {
// ... error handler
})All values are automatically sanitized against sql injection. This is because it is rendered as prepared statement, and thus all limitations imposed in MS SQL on parameters apply. e.g. Column names cannot be passed/set in statements using variables.
…If you plan to work with large amount of rows, you should always use streaming. Once you enable this, you must listen for events to receive data. Events must be attached before the query completes, but can be attached while in-flight.
…When streaming large sets of data you want to back-off or chunk the amount of data you're processing
to prevent memory exhaustion issues; you can use the Request.pause() function to do this. Here is
an example of managing rows in batches of 15:
let rowsToProcess = [];
request.on('row', row => {
rowsToProcess.push(row);
if (rowsToProcess.length >= 15) {
request.pause();
processRows();
}
});
request.on('done', () => {
processRows();
});
function processRows() {
// process rows
rowsToProcess = [];
request.resume();
}An important concept to understand when using this library is Connection Pooling as this library uses connection pooling extensively. As one Node JS process is able to handle multiple requests at once, we can take advantage of this long running process to create a pool of database connections for reuse; this saves overhead of connecting to the database for each request (as would be the case in something like PHP, where one process handles one request).
With the advantages of pooling comes some added complexities, but these are mostly just conceptual and once you understand how the pooling is working, it is simple to make use of it efficiently and effectively.
To assist with pool management in your application there is the sql.connect() function that is used to connect to the global connection pool. You can make repeated calls to this function, and if the global pool is already connected, it will resolve to the connected pool. The following example obtains the global connection pool by running sql.connect(), and then runs the query against the pool.
NB: It's important to note that there can only be one global connection pool connected at a time. Providing a different connection config to the connect() function will not create a new connection if it is already connected.
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)
})
}Awaiting or .then-ing the pool creation is a safe way to ensure that the pool is always ready, without knowing where it is needed first. In practice, once the pool is created then there will be no delay for the next connect() call.
Also notice that we do not close the global pool by calling sql.close() after the query is executed, because other queries may need to be run against this pool and closing it will add additional overhead to running subsequent queries. You should only ever close the global pool if you're certain the application is finished. Or for example, if you are running some kind of CLI tool or a CRON job you can close the pool at the end of the script.
The ability to call connect() and close() repeatedly on the global pool is intended to make pool management easier, however it is better to maintain your own reference to the pool, where connect() is called once, and the resulting global pool's connection promise is re-used throughout the entire application.
For example, in Express applications, the following approach uses a single global pool instance added to the app.locals so the application has access to it when needed. The server start is then chained inside the connect() promise.
…Then the route uses the connection pool in the app.locals object:
// ./routes/route1.js
const sql = require('mssql');
module.exports = function(req, res) {
req.app.locals.db.query('SELECT TOP 10 * FROM table_name', function(err, recordset) {
if (err) {
console.error(err)
res.status(500).send('SERVER ERROR')
return
}
res.status(200).json({ message: 'success' })
})
}For some use-cases you may want to implement your own connection pool management, rather than using the global connection pool. Reasons for doing this include:
The following code is an example of a custom connection pool implementation.
…This file can then be used in your application to create, fetch, and close pools.
const { get } = require('./pool-manager')
async function example() {
const pool = await get('default')
return pool.request().query('SELECT 1')
}Similar to the global connection pool, you should aim to only close a pool when you know it will never be needed by the application again. Typically this will only be when your application is shutting down.
In some instances it is desirable to manipulate the r
No open issues yet, or sync has not completed.