node-mssql

Microsoft SQL Server client for Node.js

NPM Version NPM Downloads Travis CI Appveyor CI Join the chat at https://gitter.im/patriksimek/node-mssql

Supported TDS drivers:支持的TDS驱动程序：

Installation安装

npm install mssql

Short Example: Use Connect String简短示例：使用连接字符串

const sql = require('mssql')

async () => {
    try {
        // make sure that any items are correctly URL encoded in the connection string确保连接字符串中的所有项目都正确地进行了URL编码
        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.如果您使用的是Windows Azure，请向连接字符串添加?encrypt=true。See docs to learn more.查看文档了解更多信息。

Parts of the connection URI should be correctly URL encoded so that the URI can be parsed correctly.连接URI的一部分应该正确地进行URL编码，以便可以正确解析URI。

Longer Example: Connect via Config Object更详细的示例：通过配置对象连接

Assuming you have set the appropriate environment variables, you can construct a config object as follows:假设已经设置了适当的环境变量，则可以按如下方式构造配置对象：

const sql = require('mssql')
const sqlConfig = {
  user: process.env.DB_USER,
  password: process.env.DB_PWD,
  database: process.env.DB_NAME,
  server: 'localhost',
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
  },
  options: {
    encrypt: true, // for azure
    trustServerCertificate: false // change to true for local dev / self-signed certs本地开发人员/自签名证书更改为true
  }
}

async () => {
 try {
  // make sure that any items are correctly URL encoded in the connection string确保连接字符串中的所有项目都正确地进行了URL编码
  await sql.connect(sqlConfig)
  const result = await sql.query`select * from mytable where id = ${value}`
  console.dir(result)
 } catch (err) {
  // ... error checks
 }
}

Documentation文档

Examples例子

Configuration配置

Drivers驱动程序

Connections连接

Requests请求

Transactions事务

Prepared Statements准备好的语句

Other其它

Examples例子

Config

const config = {
    user: '...',
    password: '...',
    server: 'localhost', // You can use 'localhost\\instance' to connect to named instance
    database: '...',
}

Async/Await

const sql = require('mssql')

(async function () {
    try {
        let pool = await sql.connect(config)
        let result1 = await pool.request()
            .input('input_parameter', sql.Int, value)
            .query('select * from mytable where id = @input_parameter')
            
        console.dir(result1)
    
        // Stored procedure
        
        let result2 = await pool.request()
            .input('input_parameter', sql.Int, value)
            .output('output_parameter', sql.VarChar(50))
            .execute('procedure_name')
        
        console.dir(result2)
    } catch (err) {
        // ... error checks
    }
})()

sql.on('error', err => {
    // ... error handler
})

PromisesPromise

Queries查询

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
});

Stored procedures存储过程

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. 默认情况下使用本机Promise。You can easily change this with sql.Promise = require('myownpromisepackage').您可以使用sql.Promise = require('myownpromisepackage')轻松更改这一点。

ES6 Tagged template literals标记的模板文本

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.所有值都会针对sql注入进行自动清理。This is because it is rendered as prepared statement, and thus all limitations imposed in MS SQL on parameters apply.这是因为它被呈现为prepared语句，因此MS SQL中对参数施加的所有限制都适用。e.g. Column names cannot be passed/set in statements using variables.列名不能在使用变量的语句中传递/设置。

Callbacks回调

const sql = require('mssql')

sql.connect(config, err => {
    // ... error checks

    // Query

    new sql.Request().query('select 1 as number', (err, result) => {
        // ... error checks

        console.dir(result)
    })

    // Stored Procedure存储过程

    new sql.Request()
    .input('input_parameter', sql.Int, value)
    .output('output_parameter', sql.VarChar(50))
    .execute('procedure_name', (err, result) => {
        // ... error checks

        console.dir(result)
    })

    // Using template literal使用模板文字

    const request = new sql.Request()
    request.query(request.template`select * from mytable where id = ${value}`, (err, result) => {
        // ... error checks
        console.dir(result)
    })
})

sql.on('error', err => {
    // ... error handler
})

Streaming流

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.如果计划处理大量行，则应始终使用流式处理。启用此功能后，必须侦听事件以接收数据。

const sql = require('mssql')

sql.connect(config, err => {
    // ... error checks

    const request = new sql.Request()
    request.stream = true // You can set streaming differently for each request您可以为每个请求设置不同的流
    request.query('select * from verylargetable') // or request.execute(procedure)或请求执行（过程）

    request.on('recordset', columns => {
        // Emitted once for each recordset in a query为查询中的每个记录集发出一次
    })

    request.on('row', row => {
        // Emitted for each row in a recordset为记录集中的每一行发出
    })

    request.on('rowsaffected', rowCount => {
        // Emitted for each `INSERT`, `UPDATE` or `DELETE` statement
        // Requires NOCOUNT to be OFF (default)
    })

    request.on('error', err => {
        // May be emitted multiple times可能会发射多次
    })

    request.on('done', result => {
        // Always emitted as the last one总是作为最后一个发射
    })
})

sql.on('error', err => {
    // ... error handler
})

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. 在流式传输大数据集时，为了防止内存耗尽问题，您希望回退或分块正在处理的数据量；您可以使用Request.pause()函数来实现这一点。Here is an example of managing rows in batches of 15:下面是一个以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();
}

Pool Management池管理

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).由于一个节点JS进程能够同时处理多个请求，我们可以利用这个长时间运行的进程来创建一个数据库连接池以供重用；这节省了为每个请求连接到数据库的开销（就像PHP中的情况一样，一个进程处理一个请求）。

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.池的优点带来了一些额外的复杂性，但这些大多只是概念性的，一旦您了解池是如何工作的，就可以简单高效地使用它。

The Global Connection (Pool)全局连接（池）

To assist with pool management in your application there is the global connect() function that is available for use. 为了帮助应用程序中的池管理，可以使用global connect()函数。As of v6 of this library a developer can make repeated calls to this function to obtain the global connection pool. 从该库的v6开始，开发人员可以重复调用该函数以获得全局连接池。This means you do not need to keep track of the pool in your application (as used to be the case). 这意味着您不需要在应用程序中跟踪池（以前是这样）。If the global pool is already connected, it will resolve to the connected pool. 如果全局池已连接，它将解析为已连接的池。For example:例如：

const sql = require('mssql')

// 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().then((pool) => {
    return pool.query(query)
  })
}

Here we obtain the global connection pool by running sql.connect() and we then run the query against the pool. 在这里，我们通过运行sql.connect()获得全局连接池，然后对该池运行查询。We also do not close the pool after the query is executed and that is because other queries may need to be run against this pool and closing it will add an overhead to running the query. 我们也不会在执行查询后关闭该池，这是因为可能需要对该池运行其他查询，而关闭该池将增加运行查询的开销。We should only ever close the pool when our application is finished. 我们应该在申请完成后才关闭该池。For example, if we are running some kind of CLI tool or a CRON job:例如，如果我们正在运行某种CLI工具或CRON作业：

const sql = require('mssql')

(() => {
  sql.connect().then(pool => {
    return pool.query('SELECT 1')
  }).then(result => {
    // do something with result
  }).then(() => {
    return sql.close()
  })
})()

Here the connection will be closed and the node process will exit once the queries and other application logic has completed. 在这里，一旦查询和其他应用程序逻辑完成，连接将被关闭，节点进程将退出。You should aim to only close the pool once in your application, when it is exiting or you know your application will never make another SQL query.当应用程序退出时，或者当您知道应用程序将永远不会进行另一个SQL查询时，您应该只在应用程序中关闭池一次。

Advanced Pool Management高级池管理

In some instances you will not want to use the connection pool, you may have multiple databases to connect to or you may have one pool for read-only operations and another pool for read-write. 在某些情况下，您可能不想使用连接池，您可能需要连接多个数据库，或者一个池用于只读操作，另一个池用于读写。In this instance you will need to implement your own pool management.在这种情况下，您需要实现自己的池管理。

That could look something like this:可能看起来像这样：

const { ConnectionPool } = require('mssql')
const POOLS = {}

function createPool(config, name) {
  if (getPool(name)) {
    return Promise.reject(new Error('Pool with this name already exists'))
  }
  return (new ConnectionPool(config)).connect().then((pool) => {
    return POOLS[name] = pool
  })
}

function closePool(name) {
  const pool = getPool(name)
  if (pool) {
    delete POOLS[name]
    return pool.close()
  }
  return Promise.resolve()
}

function getPool(name) {
  if (Object.prototype.hasOwnProperty.apply(POOLS, name)) {
    return POOLS[name]
  }
}

module.exports = {
  closePool,
  createPool,
  getPool
}

This helper file can then be used in your application to create, fetch and close your pools. 然后，可以在应用程序中使用此帮助文件来创建、获取和关闭池。As with the global pools, you should aim to only close a pool when you know it will never be needed by the application again; typically this will be when your application is shutting down.与全局池一样，只有当您知道应用程序不再需要某个池时，才应该关闭该池；通常情况下，这会在应用程序关闭时发生。

Connection Pools连接池

Using a single connection pool for your application/service is recommended. 建议对应用程序/服务使用单个连接池。Instantiating a pool with a callback, or immediately calling .connect, is asynchronous to ensure a connection can be established before returning. 用回调实例化池或立即调用.connect是异步的，以确保在返回前可以建立连接。From that point, you're able to acquire connections as normal:从那时起，你就可以像平常一样获得联系：

const sql = require('mssql')

// async/await style:
const pool1 = new sql.ConnectionPool(config);
const pool1Connect = pool1.connect();

pool1.on('error', err => {
    // ... error handler
})

async function messageHandler() {
    await pool1Connect; // ensures that the pool has been created确保已创建池
    try {
        const request = pool1.request(); // or: new sql.Request(pool1)
        const result = await request.query('select 1 as number')
        console.dir(result)
        return result;
    } catch (err) {
        console.error('SQL error', err);
    }
}

// promise style:
const pool2 = new sql.ConnectionPool(config)
const pool2Connect = pool2.connect()

pool2.on('error', err => {
    // ... error handler
})

function runStoredProcedure() {
    return pool2Connect.then((pool) => {
        pool.request() // or: new sql.Request(pool2)
        .input('input_parameter', sql.Int, 10)
        .output('output_parameter', sql.VarChar(50))
        .execute('procedure_name', (err, result) => {
            // ... error checks
            console.dir(result)
        })
    }).catch(err => {
        // ... error handler
    })
}

Awaiting or .thening the pool creation is a safe way to ensure that the pool is always ready, without knowing where it is needed first. 等待或.then池创建是一种安全的方法，可以确保池始终处于就绪状态，而不知道首先需要它的位置。In practice, once the pool is created then there will be no delay for the next operation.实际上，一旦创建了池，下一个操作就不会有延迟。

As of v6.1.0 you can make repeat calls to ConnectionPool.connect() and ConnectonPool.close() without an error being thrown, allowing for the safe use of mssql.connect().then(...) throughout your code as well as making multiple calls to close when your application is shutting down.从v6.1.0开始您可以重复调用ConnectionPool.connect()和ConnectonPool.close()，而不会引发错误，这样就可以在整个代码中安全地使用mssql.connect().then(...)，以及在应用程序关闭时多次调用关闭。

The ability to call connect() repeatedly is intended to make pool management easier, however it is still recommended to follow the example above where connect() is called once and using the original resolved connection promise. 重复调用connect()的功能旨在简化池管理，但是仍然建议遵循上面的示例，其中connect()只调用一次，并使用原始解析的连接Promise。Repeatedly calling connect() when running queries risks running into problems when close() is called on the pool.在运行查询时重复调用connect()可能会在池上调用close()时遇到问题。

ES6 Tagged template literals

new sql.ConnectionPool(config).connect().then(pool => {
    return pool.query`select * from mytable where id = ${value}`
}).then(result => {
    console.dir(result)
}).catch(err => {
    // ... error checks
})

All values are automatically sanitized against sql injection.所有值都会针对sql注入进行自动清理。

Managing connection pools管理连接池

Most applications will only need a single ConnectionPool that can be shared throughout the code. 大多数应用程序只需要一个可以在整个代码中共享的ConnectionPool。To aid the sharing of a single pool this library exposes a set of functions to access a single global connection. eg:为了帮助共享单个池，此库公开了一组用于访问单个全局连接的函数。如：

// as part of your application's boot process作为应用程序启动过程的一部分

const sql = require('mssql')
const poolPromise = sql.connect()

// during your applications runtime在运行时运行应用程序

poolPromise.then(() => {
  return sql.query('SELECT 1')
}).then(result => {
  console.dir(result)
})

// when your application exits当应用程序退出时
poolPromise.then(() => {
  return sql.close()
})

If you require multiple pools per application (perhaps you have many DBs you need to connect to or you want a read-only pool), then you will need to manage your pools yourself. 如果每个应用程序需要多个池（可能需要连接多个DBs，或者需要一个只读池），则需要自己管理池。The best way to do this is to create a shared library file that can hold references to the pools for you. 最好的方法是创建一个共享库文件，可以为您保存对池的引用。For example:例如：

const sql = require('mssql')

const pools = {}

// manage a set of pools by name (config will be required to create the pool)按名称管理一组池（创建池需要配置）
// a pool will be removed when it is closed池关闭后将被移除
async function getPool(name, config) {
  if (!Object.prototype.hasOwnProperty.call(pools, name)) {
    const pool = new sql.ConnectionPool(config)
    const close = pool.close.bind(pool)
    pool.close = (...args) => {
      delete pools[name]
      return close(...args)
    }
    await pool.connect()
    pools[name] = pool
  }
  return pools[name]
}

// close all pools关闭所有池
function closeAll() {
  return Promise.all(Object.values(pools).map((pool) => {
    return pool.close()
  }))
}

module.exports = {
  closeAll,
  getPool
}

You can then use this library file in your code to get a connected pool when you need it:然后，您可以在需要时在代码中使用此库文件来获取连接池：

const { getPool } = require('./path/to/file')

// run a query
async function runQuery(query, config) {
  // pool will always be connected when the promise has resolved - may reject if the connection config is invalid
  const pool = await getPool('default', config)
  const result = await pool.request().query(query)
  return result
}

Configuration配置

const config = {
    user: '...',
    password: '...',
    server: 'localhost',
    database: '...',
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    }
}

General (same for all drivers)一般（所有驾驶员都一样）

Complete list of pool options can be found here.可在此处找到池选项的完整列表。

Formats格式

In addition to configuration object there is an option to pass config as a connection string. 除了配置对象之外，还有一个选项可以将配置作为连接字符串传递。Connection strings are supported.支持连接字符串。

Classic Connection String经典连接字符串
Server=localhost,1433;Database=database;User Id=username;Password=password;Encrypt=true
Driver=msnodesqlv8;Server=(local)\INSTANCE;Database=database;UID=DOMAIN\username;PWD=password;Encrypt=true

Drivers驱动程序

Tedious乏味的

Default driver, actively maintained and production ready. 默认驱动程序，积极维护并准备好生产。Platform independent, runs everywhere Node.js runs. Officially supported by Microsoft.与平台无关，Node.js运行在任何地方。微软正式支持。

Extra options:

require('mssql').connect({...config, beforeConnect: conn => {
  conn.once('connect', err => { err ? console.error(err) : console.log('mssql connected')})
  conn.once('end', err => { err ? console.error(err) : console.log('mssql disconnected')})
}})

Authentication:身份验证：

On top of the extra options, an authentication property can be added to the pool config option除了这些额外选项之外，还可以将authentication属性添加到池配置选项中

More information about Tedious specific options: 有关繁琐的特定选项的更多信息：http://tediousjs.github.io/tedious/api-connection.html

Microsoft / Contributors Node V8 Driver for Node.js for SQL Server用于SQL Server的Node.js的Microsoft/Contributors节点V8驱动程序

Requires Node.js v10+ or newer. 需要Node.js v10+或更新版本。Windows 32-64 bits or Linux/macOS 64 bits only.仅限Windows 32-64位或Linux/macOS 64位。 This driver is not part of the default package and must be installed separately by npm install msnodesqlv8@^2. 此驱动程序不是默认软件包的一部分，必须由npm install msnodesqlv8@^2单独安装。To use this driver, use this require syntax: 要使用此驱动程序，请使用以下语法：const sql = require('mssql/msnodesqlv8').。

Note: If you use import into your lib to prepare your request (const { VarChar } = require('mssql')) you also need to upgrade all your types import into your code (const { VarChar } = require('mssql/msnodesqlv8')) or a connection.on is not a function error will be thrown.注意：如果您使用导入到你的库来准备请求（const { VarChar } = require('mssql')），您还需要将所有类型的import升级到代码中（const { VarChar } = require('mssql/msnodesqlv8')），否则将抛出一个connection.on is not a function错误。

Extra options:额外选项：

Default connection string when connecting to port:连接到端口时的默认连接字符串：

Driver={SQL Server Native Client 11.0};Server={#{server},#{port}};Database={#{database}};Uid={#{user}};Pwd={#{password}};Trusted_Connection={#{trusted}};

Default connection string when connecting to named instance:连接到命名实例时的默认连接字符串：

Driver={SQL Server Native Client 11.0};Server={#{server}\\#{instance}};Database={#{database}};Uid={#{user}};Pwd={#{password}};Trusted_Connection={#{trusted}};

Please note that the connection string with this driver is not the same than tedious and use yes/no instead of true/false. 请注意，此驱动程序的连接字符串与冗长的连接字符串不同，请使用yes/no而不是true/false。You can see more on the ODBC documentation.您可以在ODBC文档中看到更多信息。

Connections连接

Internally, each ConnectionPool instance is a separate pool of TDS connections. 在内部，每个ConnectionPool实例都是一个单独的TDS连接池。Once you create a new Request/Transaction/Prepared Statement, a new TDS connection is acquired from the pool and reserved for desired action. 创建新的Request/Transaction/Prepared语句后，将从池中获取一个新的TDS连接，并为所需的操作保留该连接。Once the action is complete, connection is released back to the pool. 操作完成后，将释放回池的连接。Connection health check is built-in so once the dead connection is discovered, it is immediately replaced with a new one.连接健康检查是内置的，因此一旦发现死连接，就会立即用新连接替换。

IMPORTANT: Always attach an error listener to created connection. ：始终将error侦听器附加到创建的连接。Whenever something goes wrong with the connection it will emit an error and if there is no listener it will crash your application with an uncaught error.每当连接出现问题时，它就会发出一个错误，如果没有侦听器，它就会以一个未捕获的错误使应用程序崩溃。

const pool = new sql.ConnectionPool({ /* config */ })

Events事件

connect ([callback])

Create a new connection pool. The initial probe connection is created to find out whether the configuration is valid.创建一个新的连接池。创建初始探测连接以确定配置是否有效。

Arguments参数

Example实例

const pool = new sql.ConnectionPool({
    user: '...',
    password: '...',
    server: 'localhost',
    database: '...'
})

pool.connect(err => {
    // ...
})

Errors

close()

Close all active connections in the pool.关闭池中所有活动的连接。

Example实例

pool.close()

Request要求

const request = new sql.Request(/* [pool or transaction] */)

If you omit pool/transaction argument, global pool is used instead.如果省略pool/transaction参数，则使用全局池。

Events事件

execute (procedure, [callback])

Call a stored procedure.调用存储过程。

Arguments参数

Example例子

const request = new sql.Request()
request.input('input_parameter', sql.Int, value)
request.output('output_parameter', sql.Int)
request.execute('procedure_name', (err, result) => {
    // ... error checks

    console.log(result.recordsets.length) // count of recordsets returned by the procedure
    console.log(result.recordsets[0].length) // count of rows contained in first recordset
    console.log(result.recordset) // first recordset from result.recordsets
    console.log(result.returnValue) // procedure return value
    console.log(result.output) // key/value collection of output values
    console.log(result.rowsAffected) // array of numbers, each number represents the number of rows affected by executed statemens

    // ...
})

Errors

input (name, [type], value)

Add an input parameter to the request.向请求中添加一个输入参数。

Arguments参数

Example例子

request.input('input_parameter', value)
request.input('input_parameter', sql.Int, value)

JS Data Type To SQL Data Type MapJS数据类型到SQL数据类型映射

Default data type for unknown object is sql.NVarChar.未知对象的默认数据类型为sql.NVarChar。

You can define your own type map.您可以定义自己的类型映射。

sql.map.register(MyClass, sql.Text)

You can also overwrite the default type map.也可以覆盖默认类型映射。

sql.map.register(Number, sql.BigInt)

Errors (synchronous)

NB: Do not use parameters @p{n} as these are used by the internal drivers and cause a conflict.注意：不要使用@p{n}参数，因为这些参数由内部驱动程序使用，会导致冲突。

output (name, type, [value])

Add an output parameter to the request.向请求添加输出参数。

Arguments参数

Example实例

request.output('output_parameter', sql.Int)
request.output('output_parameter', sql.VarChar(50), 'abc')

Errors (synchronous)（同步）

toReadableStream

Convert request to a Node.js ReadableStream将请求转换为Node.js ReadableStream

Example例子

const { pipeline } = require('stream')
const request = new sql.Request()
const readableStream = request.toReadableStream()
pipeline(readableStream, transformStream, writableStream)
request.query('select * from mytable')

OR if you wanted to increase the highWaterMark of the read stream to buffer more rows in memory或者，如果您想增加读取流的highWaterMark，以便在内存中缓冲更多行

const { pipeline } = require('stream')
const request = new sql.Request()
const readableStream = request.toReadableStream({ highWaterMark: 100 })
pipeline(readableStream, transformStream, writableStream)
request.query('select * from mytable')

pipe (stream)

Sets request to stream mode and pulls all rows from all recordsets to a given stream.将请求设置为stream模式，并将所有记录集中的所有行拉至给定流。

Arguments参数

Example实例

const request = new sql.Request()
request.pipe(stream)
request.query('select * from mytable')
stream.on('error', err => {
    // ...
})
stream.on('finish', () => {
    // ...
})

query (command, [callback])

Execute the SQL command. 执行SQL命令。To execute commands like create procedure or if you plan to work with local temporary tables, use batch instead.要执行诸如create procedure之类的命令，或者如果您计划使用本地临时表，请改用batch。

Arguments参数

Example例子

const request = new sql.Request()
request.query('select 1 as number', (err, result) => {
    // ... error checks

    console.log(result.recordset[0].number) // return 1

    // ...
})

Errors

const request = new sql.Request()
request.query('select 1 as number; select 2 as number', (err, result) => {
    // ... error checks

    console.log(result.recordset[0].number) // return 1
    console.log(result.recordsets[0][0].number) // return 1
    console.log(result.recordsets[1][0].number) // return 2
})

NOTE: To get number of rows affected by the statement(s), see section Affected Rows.：要获取受语句影响的行数，请参阅受影响的行部分。

batch (batch, [callback])

Execute the SQL command. 执行SQL命令。Unlike query, it doesn't use sp_executesql, so is not likely that SQL Server will reuse the execution plan it generates for the SQL. 与query不同，它不使用sp_executesql，因此SQL Server不太可能重用它为SQL生成的执行计划。Use this only in special cases, for example when you need to execute commands like create procedure which can't be executed with query or if you're executing statements longer than 4000 chars on SQL Server 2000. 仅在特殊情况下使用此选项，例如，当您需要执行create procedure等无法通过query执行的命令时，或者在SQL Server 2000上执行的语句长度超过4000个字符时。Also you should use this if you're plan to work with local temporary tables (more information here).如果打算使用本地临时表（更多信息，请参阅此处），也应该使用此选项。

NOTE: Table-Valued Parameter (TVP) is not supported in batch.注意：批处理中不支持表值参数（TVP）。

Arguments参数

Example例子

const request = new sql.Request()
request.batch('create procedure #temporary as select * from table', (err, result) => {
    // ... error checks
})

Errors

You can enable multiple recordsets in queries with the request.multiple = true command.可以使用request.multiple = true命令在查询中启用多个记录集。

bulk (table, [options,] [callback])

Perform a bulk insert.执行批量插入。

Arguments参数

Example例子

const table = new sql.Table('table_name') // or temporary table, e.g. #temptable
table.create = true
table.columns.add('a', sql.Int, {nullable: true, primary: true})
table.columns.add('b', sql.VarChar(50), {nullable: false})
table.rows.add(777, 'test')

const request = new sql.Request()
request.bulk(table, (err, result) => {
    // ... error checks
})

IMPORTANT: Always indicate whether the column is nullable or not!：始终指示列是否可为空！

TIP: If you set table.create to true, module will check if the table exists before it start sending data. ：如果将table.create设置为true，模块将在开始发送数据之前检查表是否存在。If it doesn't, it will automatically create it. 如果没有，它将自动创建它。You can specify primary key columns by setting primary: true to column's options. 通过将primary:true设置为列的选项，可以指定主键列。Primary key constraint on multiple columns is supported.支持多列的主键约束。

TIP: You can also create Table variable from any recordset with recordset.toTable(). ：您还可以使用recordset.toTable()从任何记录集创建表变量。You can optionally specify table type name in the first argument.可以选择在第一个参数中指定表类型名称。

Errors

cancel()

Cancel currently executing request. 取消当前正在执行的请求。Return true if cancellation packet was send successfully.如果取消数据包成功发送，则返回true。

Example例子

const request = new sql.Request()
request.query('waitfor delay \'00:00:05\'; select 1 as number', (err, result) => {
    console.log(err instanceof sql.RequestError)  // true
    console.log(err.message)                      // Cancelled.
    console.log(err.code)                         // ECANCEL

    // ...
})

request.cancel()

Transaction事务

IMPORTANT:重要事项： always use Transaction class to create transactions - it ensures that all your requests are executed on one connection. 始终使用Transaction类来创建事务——它确保所有请求都在一个连接上执行。Once you call begin, a single connection is acquired from the connection pool and all subsequent requests (initialized with the Transaction object) are executed exclusively on this connection. 一旦调用begin，就会从连接池中获取一个连接，并且所有后续请求（使用事务对象初始化）都会在此连接上独占执行。After you call commit or rollback, connection is then released back to the connection pool.调用commit或rollback后，连接将被释放回连接池。

const transaction = new sql.Transaction(/* [pool] */)

If you omit connection argument, global connection is used instead.如果省略connection参数，则使用全局连接。

Example例子

const transaction = new sql.Transaction(/* [pool] */)
transaction.begin(err => {
    // ... error checks

    const request = new sql.Request(transaction)
    request.query('insert into mytable (mycolumn) values (12345)', (err, result) => {
        // ... error checks

        transaction.commit(err => {
            // ... error checks

            console.log("Transaction committed.")
        })
    })
})

Transaction can also be created by const transaction = pool.transaction(). 事务也可以由const transaction = pool.transaction()创建。Requests can also be created by const request = transaction.request().请求也可以由const request = transaction.request()创建。

Aborted transactions中止的事务

This example shows how you should correctly handle transaction errors when abortTransactionOnError (XACT_ABORT) is enabled. 此示例显示了在启用abortTransactionOnError（XACT_ABORT）时应如何正确处理事务错误。Added in 2.0.

const transaction = new sql.Transaction(/* [pool] */)
transaction.begin(err => {
    // ... error checks

    let rolledBack = false

    transaction.on('rollback', aborted => {
        // emited with aborted === true

        rolledBack = true
    })

    new sql.Request(transaction)
    .query('insert into mytable (bitcolumn) values (2)', (err, result) => {
        // insert should fail because of invalid value

        if (err) {
            if (!rolledBack) {
                transaction.rollback(err => {
                    // ... error checks
                })
            }
        } else {
            transaction.commit(err => {
                // ... error checks
            })
        }
    })
})

Events

begin ([isolationLevel], [callback])

Begin a transaction.开始事务。

Arguments参数

Example例子

const transaction = new sql.Transaction()
transaction.begin(err => {
    // ... error checks
})

Errors

commit ([callback])

Commit a transaction.提交事务。

Arguments参数

Example例子

const transaction = new sql.Transaction()
transaction.begin(err => {
    // ... error checks

    transaction.commit(err => {
        // ... error checks
    })
})

Errors

rollback ([callback])

Rollback a transaction. 回滚事务。If the queue isn't empty, all queued requests will be Cancelled and the transaction will be marked as aborted.如果队列不是空的，所有排队的请求都将被取消，事务将被标记为中止。

Arguments参数

Example例子

const transaction = new sql.Transaction()
transaction.begin(err => {
    // ... error checks

    transaction.rollback(err => {
        // ... error checks
    })
})

Errors

Prepared Statement准备好的语句

IMPORTANT: always use PreparedStatement class to create prepared statements - it ensures that all your executions of prepared statement are executed on one connection. 始终使用PreparedStatement类来创建准备好的语句——它确保所有准备好的语句都在一个连接上执行。Once you call prepare, a single connection is acquired from the connection pool and all subsequent executions are executed exclusively on this connection. 一旦调用prepare，就会从连接池中获取一个连接，并且所有后续执行都会专门在此连接上执行。After you call unprepare, the connection is then released back to the connection pool.调用unprepare后，连接将被释放回连接池。

const ps = new sql.PreparedStatement(/* [pool] */)

If you omit the connection argument, the global connection is used instead.如果省略connection参数，则使用全局连接。

Example例子

const ps = new sql.PreparedStatement(/* [pool] */)
ps.input('param', sql.Int)
ps.prepare('select @param as value', err => {
    // ... error checks

    ps.execute({param: 12345}, (err, result) => {
        // ... error checks

        // release the connection after queries are executed
        ps.unprepare(err => {
            // ... error checks

        })
    })
})

IMPORTANT: Remember that each prepared statement means one reserved connection from the pool. ：请记住，每个准备好的语句都意味着池中的一个保留连接。Don't forget to unprepare a prepared statement when you've finished your queries!当你完成你的查询时，别忘了准备一份准备好的语句！

You can execute multiple queries against the same prepared statement but you must unprepare the statement when you have finished using it otherwise you will cause the connection pool to run out of available connections.您可以对同一个已准备好的语句执行多个查询，但必须在使用完语句后取消对该语句的准备，否则将导致连接池耗尽可用的连接。

TIP: You can also create prepared statements in transactions (new sql.PreparedStatement(transaction)), but keep in mind you can't execute other requests in the transaction until you call unprepare.：您还可以在事务中创建准备好的语句（new sql.PreparedStatement(transaction)），但请记住，在调用unprepare之前，您不能在事务中执行其他请求。

input (name, type)

Add an input parameter to the prepared statement.将输入参数添加到准备好的语句中。

Arguments参数

Example例子

ps.input('input_parameter', sql.Int)
ps.input('input_parameter', sql.VarChar(50))

Errors (synchronous)

output (name, type)

Add an output parameter to the prepared statement.将输出参数添加到准备好的语句中。

Arguments参数

Example例子

ps.output('output_parameter', sql.Int)
ps.output('output_parameter', sql.VarChar(50))

Errors (synchronous)

prepare (statement, [callback])

Prepare a statement.准备一个语句。

Arguments参数

Example例子

const ps = new sql.PreparedStatement()
ps.prepare('select @param as value', err => {
    // ... error checks
})

Errors

execute (values, [callback])

Execute a prepared statement.执行准备好的语句。

Arguments参数

Example例子

const ps = new sql.PreparedStatement()
ps.input('param', sql.Int)
ps.prepare('select @param as value', err => {
    // ... error checks

    ps.execute({param: 12345}, (err, result) => {
        // ... error checks

        console.log(result.recordset[0].value) // return 12345
        console.log(result.rowsAffected) // Returns number of affected rows in case of INSERT, UPDATE or DELETE statement.
        
        ps.unprepare(err => {
            // ... error checks
        })
    })
})

You can also stream executed request.您还可以流式处理已执行的请求。

const ps = new sql.PreparedStatement()
ps.input('param', sql.Int)
ps.prepare('select @param as value', err => {
    // ... error checks

    ps.stream = true
    const request = ps.execute({param: 12345})

    request.on('recordset', columns => {
        // Emitted once for each recordset in a query
    })

    request.on('row', row => {
        // Emitted for each row in a recordset
    })

    request.on('error', err => {
        // May be emitted multiple times
    })

    request.on('done', result => {
        // Always emitted as the last one
        
        console.log(result.rowsAffected) // Returns number of affected rows in case of INSERT, UPDATE or DELETE statement.
        
        ps.unprepare(err => {
            // ... error checks
        })
    })
})

TIP: To learn more about how number of affected rows works, see section Affected Rows.：要了解更多有关受影响行数的工作原理，请参阅受影响行一节。

Errors

unprepare ([callback])

Unprepare a prepared statement.取消准备一个事先准备好的语句。

Arguments参数

Example例子

const ps = new sql.PreparedStatement()
ps.input('param', sql.Int)
ps.prepare('select @param as value', err => {
    // ... error checks

    ps.unprepare(err => {
        // ... error checks

    })
})

Errors

CLI

Before you can start using CLI, you must install mssql globally with npm install mssql -g. 在开始使用CLI之前，必须使用npm install mssql -g全局安装mssql。Once you do that you will be able to execute mssql command.一旦这样做，您将能够执行mssql命令。

Setup

Create a .mssql.json configuration file (anywhere). 创建.mssql.json配置文件（任何地方）。Structure of the file is the same as the standard configuration object.文件的结构与标准配置对象相同。

{
    "user": "...",
    "password": "...",
    "server": "localhost",
    "database": "..."
}

Example例子

echo "select * from mytable" | mssql /path/to/config

Results in:结果如下：

[[{"username":"patriksimek","password":"tooeasy"}]]

You can also query for multiple recordsets.您还可以查询多个记录集。

echo "select * from mytable; select * from myothertable" | mssql

Results in:结果如下：

[[{"username":"patriksimek","password":"tooeasy"}],[{"id":15,"name":"Product name"}]]

If you omit config path argument, mssql will try to load it from current working directory.如果省略配置路径参数，mssql将尝试从当前工作目录加载它。

Geography and Geometry地理与几何

node-mssql has built-in deserializer for Geography and Geometry CLR data types.节点mssql具有用于地理和几何CLR数据类型的内置反序列化器。

Geography

Geography types can be constructed several different ways. 地理类型可以通过几种不同的方式构建。Refer carefully to documentation to verify the coordinate ordering; the ST methods tend to order parameters as longitude (x) then latitude (y), while custom CLR methods tend to prefer to order them as latitude (y) then longitude (x).仔细参考文件，以验证坐标顺序；ST方法倾向于将参数按经度（x）再按纬度（y）排序，而自定义CLR方法倾向于按纬度（y）再按经度（x）排序。

The query:

select geography::STGeomFromText(N'POLYGON((1 1, 3 1, 3 1, 1 1))',4326)

results in:结果如下：

{
  srid: 4326,
  version: 2,
  points: [
    Point { lat: 1, lng: 1, z: null, m: null },
    Point { lat: 1, lng: 3, z: null, m: null },
    Point { lat: 1, lng: 3, z: null, m: null },
    Point { lat: 1, lng: 1, z: null, m: null }
  ],
  figures: [ { attribute: 1, pointOffset: 0 } ],
  shapes: [ { parentOffset: -1, figureOffset: 0, type: 3 } ],
  segments: []
}

NOTE: You will also see x and y coordinates in parsed Geography points, they are not recommended for use. 您还将在解析的地理点中看到x和y坐标，不建议使用它们。They have thus been omitted from this example. 因此，本例中省略了它们。For compatibility, they remain flipped (x, the horizontal offset, is instead used for latitude, the vertical), and thus risk misleading you. 为了兼容性，它们保持翻转（x是水平偏移，而用于纬度和垂直），因此可能会误导您。Prefer instead to use the lat and lng properties.更倾向于使用lat和lng特性。

Geometry几何图形

Geometry types can also be constructed in several ways. 几何图形类型也可以通过多种方式构造。Unlike Geographies, they are consistent in always placing x before y. 与地理位置不同，它们总是将x置于y之前。node-mssql decodes the result of this query:节点mssql解码此查询的结果：

select geometry::STGeomFromText(N'POLYGON((1 1, 3 1, 3 7, 1 1))',4326)

into the JavaScript object:进入JavaScript对象：

{
  srid: 4326,
  version: 1,
  points: [
    Point { x: 1, y: 1, z: null, m: null },
    Point { x: 1, y: 3, z: null, m: null },
    Point { x: 7, y: 3, z: null, m: null },
    Point { x: 1, y: 1, z: null, m: null }
  ],
  figures: [ { attribute: 2, pointOffset: 0 } ],
  shapes: [ { parentOffset: -1, figureOffset: 0, type: 3 } ],
  segments: []
}

Table-Valued Parameter (TVP)表值参数（TVP）

Supported on SQL Server 2008 and later. 在SQL Server 2008及更高版本上受支持。You can pass a data table as a parameter to stored procedure. 可以将数据表作为参数传递给存储过程。First, we have to create custom type in our database.首先，我们必须在数据库中创建自定义类型。

CREATE TYPE TestType AS TABLE ( a VARCHAR(50), b INT );

Next we will need a stored procedure.接下来我们需要一个存储过程。

CREATE PROCEDURE MyCustomStoredProcedure (@tvp TestType readonly) AS SELECT * FROM @tvp

Now let's go back to our Node.js app.现在让我们回到Node.js应用程序。

const tvp = new sql.Table() // You can optionally specify table type name in the first argument.

// Columns must correspond with type we have created in database.列必须与我们在数据库中创建的类型对应。
tvp.columns.add('a', sql.VarChar(50))
tvp.columns.add('b', sql.Int)

// Add rows
tvp.rows.add('hello tvp', 777) // Values are in same order as columns.

You can send table as a parameter to stored procedure.可以将表作为参数发送到存储过程。

const request = new sql.Request()
request.input('tvp', tvp)
request.execute('MyCustomStoredProcedure', (err, result) => {
    // ... error checks

    console.dir(result.recordsets[0][0]) // {a: 'hello tvp', b: 777}
})

TIP: You can also create Table variable from any recordset with recordset.toTable(). ：您还可以使用recordset.toTable()从任何记录集创建表变量。You can optionally specify table type name in the first argument.可以选择在第一个参数中指定表类型名称。

Response Schema

An object returned from a sucessful basic query would look like the following.sucessful基本查询返回的对象如下所示。

{
	recordsets: [
		[
			{
				COL1: "some content",
				COL2: "some more content"
			}
		]
	],
	recordset: [
		{
			COL1: "some content",
			COL2: "some more content"
		}
	],
	output: {},
	rowsAffected: [1]
}

Affected Rows受影响的行

If you're performing INSERT, UPDATE or DELETE in a query, you can read number of affected rows. 如果在查询中执行INSERT、UPDATE或DELETE，则可以读取受影响的行数。The rowsAffected variable is an array of numbers. rowsAffected变量是一个数字数组。Each number represents number of affected rows by a single statement.每个数字表示一条语句所影响的行数。

Example using Promises使用Promise的示例

const request = new sql.Request()
request.query('update myAwesomeTable set awesomness = 100').then(result => {
    console.log(result.rowsAffected)
})

Example using callbacks使用回调的示例

const request = new sql.Request()
request.query('update myAwesomeTable set awesomness = 100', (err, result) => {
    console.log(result.rowsAffected)
})

Example using streaming使用流媒体的示例

In addition to the rowsAffected attribute on the done event, each statement will emit the number of affected rows as it is completed.除了done事件上的rowsAffected属性外，每个语句在完成时都会发出受影响的行数。

const request = new sql.Request()
request.stream = true
request.query('update myAwesomeTable set awesomness = 100')
request.on('rowsaffected', rowCount => {
    console.log(rowCount)
})
request.on('done', result => {
    console.log(result.rowsAffected)
})

JSON support

SQL Server 2016 introduced built-in JSON serialization. SQL Server 2016引入了内置JSON序列化。By default, JSON is returned as a plain text in a special column named JSON_F52E2B61-18A1-11d1-B105-00805F49916B.默认情况下，JSON在名为JSON_F52E2B61-18A1-11d1-B105-00805F49916B的特殊列中以纯文本形式返回。

Example

SELECT
    1 AS 'a.b.c',
    2 AS 'a.b.d',
    3 AS 'a.x',
    4 AS 'a.y'
FOR JSON PATH

Results in:结果如下：

recordset = [ { 'JSON_F52E2B61-18A1-11d1-B105-00805F49916B': '{"a":{"b":{"c":1,"d":2},"x":3,"y":4}}' } ]

You can enable built-in JSON parser with config.parseJSON = true. 可以使用config.parseJSON = true启用内置JSON解析器。Once you enable this, recordset will contain rows of parsed JS objects. 一旦启用此功能，记录集将包含解析的JS对象行。Given the same example, result will look like this:给出相同的示例，结果如下所示：

recordset = [ { a: { b: { c: 1, d: 2 }, x: 3, y: 4 } } ]

IMPORTANT: In order for this to work, there must be exactly one column named JSON_F52E2B61-18A1-11d1-B105-00805F49916B in the recordset.

More information about JSON support can be found in official documentation.

Handling Duplicate Column Names处理重复的列名

If your queries contain output columns with identical names, the default behaviour of mssql will only return column metadata for the last column with that name. 如果查询包含名称相同的输出列，mssql的默认行为将只返回具有该名称的最后一列的列元数据。You will also not always be able to re-assemble the order of output columns requested.您也不能总是重新组合所请求的输出列的顺序。

Default behaviour:默认行为：

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

Results in:结果如下：

{
  recordsets: [
    [ { name: [ 'asdf', 'jkl' ], other_name: 'qwerty' } ]
  ],
  recordset: [ { name: [ 'asdf', 'jkl' ], other_name: 'qwerty' } ],
  output: {},
  rowsAffected: [ 1 ]
}

You can use the arrayRowMode configuration parameter to return the row values as arrays and add a separate array of column values. 可以使用arrayRowMode配置参数将行值作为数组返回，并添加一个单独的列值数组。arrayRowMode can be set globally during the initial connection, or per-request.可以在初始连接期间全局设置，也可以按请求设置。

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)
    });

Results in:结果如下：

{
  recordsets: [ [ [ 'asdf', 'qwerty', 'jkl' ] ] ],
  recordset: [ [ 'asdf', 'qwerty', 'jkl' ] ],
  output: {},
  rowsAffected: [ 1 ],
  columns: [
    [
      {
        index: 0,
        name: 'name',
        length: 4,
        type: [sql.VarChar],
        scale: undefined,
        precision: undefined,
        nullable: false,
        caseSensitive: false,
        identity: false,
        readOnly: true
      },
      {
        index: 1,
        name: 'other_name',
        length: 6,
        type: [sql.VarChar],
        scale: undefined,
        precision: undefined,
        nullable: false,
        caseSensitive: false,
        identity: false,
        readOnly: true
      },
      {
        index: 2,
        name: 'name',
        length: 3,
        type: [sql.VarChar],
        scale: undefined,
        precision: undefined,
        nullable: false,
        caseSensitive: false,
        identity: false,
        readOnly: true
      }
    ]
  ]
}

Streaming Duplicate Column Names流式处理重复的列名

When using arrayRowMode with stream enabled, the output from the recordset event (as described in Streaming) is returned as an array of column metadata, instead of as a keyed object. The order of the column metadata provided by the recordset event will match the order of row values when arrayRowMode is enabled.

Default behaviour (without arrayRowMode):

const request = new sql.Request()
request.stream = true
request.query("select 'asdf' as name, 'qwerty' as other_name, 'jkl' as name")
request.on('recordset', recordset => console.log(recordset))

Results in:结果如下：

{
  name: {
    index: 2,
    name: 'name',
    length: 3,
    type: [sql.VarChar],
    scale: undefined,
    precision: undefined,
    nullable: false,
    caseSensitive: false,
    identity: false,
    readOnly: true
  },
  other_name: {
    index: 1,
    name: 'other_name',
    length: 6,
    type: [sql.VarChar],
    scale: undefined,
    precision: undefined,
    nullable: false,
    caseSensitive: false,
    identity: false,
    readOnly: true
  }
}

With arrayRowMode:

const request = new sql.Request()
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))

Results in:结果如下：

[
  {
    index: 0,
    name: 'name',
    length: 4,
    type: [sql.VarChar],
    scale: undefined,
    precision: undefined,
    nullable: false,
    caseSensitive: false,
    identity: false,
    readOnly: true
  },
  {
    index: 1,
    name: 'other_name',
    length: 6,
    type: [sql.VarChar],
    scale: undefined,
    precision: undefined,
    nullable: false,
    caseSensitive: false,
    identity: false,
    readOnly: true
  },
  {
    index: 2,
    name: 'name',
    length: 3,
    type: [sql.VarChar],
    scale: undefined,
    precision: undefined,
    nullable: false,
    caseSensitive: false,
    identity: false,
    readOnly: true
  }
]

Errors

There are 4 types of errors you can handle:

Those errors are initialized in node-mssql module and its original stack may be cropped. You can always access original error with err.originalError.

SQL Server may generate more than one error for one request so you can access preceding errors with err.precedingErrors.

Error Codes

Each known error has name, code and message properties.

Name Code Message
ConnectionError ELOGIN Login failed.
ConnectionError ETIMEOUT Connection timeout.
ConnectionError EDRIVER Unknown driver.
ConnectionError EALREADYCONNECTED Database is already connected!
ConnectionError EALREADYCONNECTING Already connecting to database!
ConnectionError ENOTOPEN Connection not yet open.
ConnectionError EINSTLOOKUP Instance lookup failed.
ConnectionError ESOCKET Socket error.
ConnectionError ECONNCLOSED Connection is closed.
TransactionError ENOTBEGUN Transaction has not begun.
TransactionError EALREADYBEGUN Transaction has already begun.
TransactionError EREQINPROG Can't commit/rollback transaction. There is a request in progress.
TransactionError EABORT Transaction has been aborted.
RequestError EREQUEST Message from SQL Server. Error object contains additional details.
RequestError ECANCEL Cancelled.
RequestError ETIMEOUT Request timeout.
RequestError EARGS Invalid number of arguments.
RequestError EINJECT SQL injection warning.
RequestError ENOCONN No connection is specified for that request.
PreparedStatementError EARGS Invalid number of arguments.
PreparedStatementError EINJECT SQL injection warning.
PreparedStatementError EALREADYPREPARED Statement is already prepared.
PreparedStatementError ENOTPREPARED Statement is not prepared.

Detailed SQL Errors

SQL errors (RequestError with err.code equal to EREQUEST) contains additional details.

Informational messages信息消息

To receive informational messages generated by PRINT or RAISERROR commands use:要接收PRINT或RAISERROR命令生成的信息性消息，请使用：

const request = new sql.Request()
request.on('info', info => {
    console.dir(info)
})
request.query('print \'Hello world.\';', (err, result) => {
    // ...
})

Structure of informational message:信息信息的结构：

Metadata

Recordset metadata are accessible through the recordset.columns property.可以通过recordset.columns属性访问记录集元数据。

const request = new sql.Request()
request.query('select convert(decimal(18, 4), 1) as first, \'asdf\' as second', (err, result) => {
    console.dir(result.recordset.columns)

    console.log(result.recordset.columns.first.type === sql.Decimal) // true
    console.log(result.recordset.columns.second.type === sql.VarChar) // true
})

Columns structure for example above:列结构，例如上述：

{
    first: {
        index: 0,
        name: 'first',
        length: 17,
        type: [sql.Decimal],
        scale: 4,
        precision: 18,
        nullable: true,
        caseSensitive: false
        identity: false
        readOnly: true
    },
    second: {
        index: 1,
        name: 'second',
        length: 4,
        type: [sql.VarChar],
        nullable: false,
        caseSensitive: false
        identity: false
        readOnly: true
    }
}

Data Types数据类型

You can define data types with length/precision/scale:可以使用长度/精度/比例定义数据类型：

request.input("name", sql.VarChar, "abc")               // varchar(3)
request.input("name", sql.VarChar(50), "abc")           // varchar(50)
request.input("name", sql.VarChar(sql.MAX), "abc")      // varchar(MAX)
request.output("name", sql.VarChar)                     // varchar(8000)
request.output("name", sql.VarChar, "abc")              // varchar(3)

request.input("name", sql.Decimal, 155.33)              // decimal(18, 0)
request.input("name", sql.Decimal(10), 155.33)          // decimal(10, 0)
request.input("name", sql.Decimal(10, 2), 155.33)       // decimal(10, 2)

request.input("name", sql.DateTime2, new Date())        // datetime2(7)
request.input("name", sql.DateTime2(5), new Date())     // datetime2(5)

List of supported data types:支持的数据类型列表：

sql.Bit
sql.BigInt
sql.Decimal ([precision], [scale])
sql.Float
sql.Int
sql.Money
sql.Numeric ([precision], [scale])
sql.SmallInt
sql.SmallMoney
sql.Real
sql.TinyInt

sql.Char ([length])
sql.NChar ([length])
sql.Text
sql.NText
sql.VarChar ([length])
sql.NVarChar ([length])
sql.Xml

sql.Time ([scale])
sql.Date
sql.DateTime
sql.DateTime2 ([scale])
sql.DateTimeOffset ([scale])
sql.SmallDateTime

sql.UniqueIdentifier

sql.Variant

sql.Binary
sql.VarBinary ([length])
sql.Image

sql.UDT
sql.Geography
sql.Geometry

To setup MAX length for VarChar, NVarChar and VarBinary use sql.MAX length. Types sql.XML and sql.Variant are not supported as input parameters.

SQL injection

This module has built-in SQL injection protection. Always use parameters or tagged template literals to pass sanitized values to your queries.

const request = new sql.Request()
request.input('myval', sql.VarChar, '-- commented')
request.query('select @myval as myval', (err, result) => {
    console.dir(result)
})

Known issues

Tedious

6.x to 7.x changes (pre-release)

5.x to 6.x changes

4.x to 5.x changes

3.x to 4.x changes