node.js 连接 sql server 数据库

1、在项目中安装 tedious 模块。 tedious 是 TDS 协议的实现，用于与 SQL Server 通信。

npm install tedious --save

2、连接数据库

var Connection = require('tedious').Connection;
var config = {
    server: '110.249.130.202',    //服务器地址
    authentication: {
        type: 'default',
        options: {
            userName: 'name',    //用户名
            password: '123456'    //密码
        }
    },
    options: {
        encrypt: false,//  如果不是 Microsoft Azure，则必须设置为 false，否则报错
        database: 'haha',    //数据库
        port: 1433  // 端口号
    }
};
var connection = new Connection(config);
connection.on('connect', function (err) {

    if (err) {
        console.log("连接数据库失败")
        console.log(err)
    } else {
        console.log("连接数据库成功");
        executeStatement();  //执行查询查询
    }

});

connection.connect();

执行查询：

    var Request = require('tedious').Request;  
    var TYPES = require('tedious').TYPES;  
  
    function executeStatement() {  
        request = new Request("SELECT c.CustomerID, c.CompanyName,COUNT(soh.SalesOrderID) AS OrderCount FROM SalesLT.Customer AS c LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, c.CompanyName ORDER BY OrderCount DESC;", function(err) {  
        if (err) {  
            console.log(err);}  
        });  
        var result = "";  
        request.on('row', function(columns) {  
            columns.forEach(function(column) {  
              if (column.value === null) {  
                console.log('NULL');  
              } else {  
                result+= column.value + " ";  
              }  
            });  
            console.log(result);  
            result ="";  
        });  
  
        request.on('done', function(rowCount, more) {  
        console.log(rowCount + ' rows returned');  
        });  
        
        // Close the connection after the final event emitted by the request, after the callback passes
        request.on("requestCompleted", function (rowCount, more) {
            connection.close();
        });
        connection.execSql(request);  
    }

参考

1、https://docs.microsoft.com/zh-cn/sql/connect/node-js/step-3-proof-of-concept-connecting-to-sql-using-node-js?view=sql-server-ver15

2、https://tediousjs.github.io/tedious/index.html