从 Node.js 连接到 SQL Server 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10309540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connect to SQL Server database from Node.js
提问by penartur
The question duplicates some older questions, but the things may have changed since then.
这个问题重复了一些较旧的问题，但从那时起事情可能已经发生了变化。
Is there some official support for connecting to SQL Server from Node.js (e.g. official library from MS)? Or at least some well-maintained third-party library appropriate for a production-grade application?
是否有一些官方支持从 Node.js 连接到 SQL Server（例如来自 MS 的官方库）？或者至少是一些维护良好的第三方库，适用于生产级应用程序？
We usually use ASP.NET MVC/SQL Server combination, but currently I have a task for which express/Node.js seems to be more appropriate (and I'd like to play with something new), so the question is whether we can rely on a Node.js and SQL Server interaction.
我们通常使用 ASP.NET MVC/SQL Server 组合，但目前我有一个任务，express/Node.js 似乎更合适（我想玩一些新的东西），所以问题是我们是否可以依赖 Node.js 和 SQL Server 交互。
UPD: It seems that Microsoft has, at last, released the official driver: https://github.com/WindowsAzure/node-sqlserver
UPD：看来微软终于发布了官方驱动：https: //github.com/WindowsAzure/node-sqlserver
采纳答案by Futur
I am not sure did you see this list of MS SQL Modules for Node JS
我不确定您是否看到了 Node JS的MS SQL 模块列表
Share your experience after using one if possible .
如果可能，分享您使用后的体验。
Good Luck
祝你好运
回答by user203687
This is mainly for future readers. As the question (at least the title) focuses on "connecting to sql server database from node js", I would like to chip in about "mssql" node module.
这主要是为了未来的读者。由于问题（至少是标题）侧重于“从节点 js 连接到 sql server 数据库”，我想介绍一下“mssql”节点模块。
At this moment, we have a stable version of Microsoft SQL Server driver for NodeJs ("msnodesql") available here: https://www.npmjs.com/package/msnodesql. While it does a great job of native integration to Microsoft SQL Server database (than any other node module), there are couple of things to note about.
目前，我们有一个稳定版本的用于 NodeJs 的 Microsoft SQL Server 驱动程序（“msnodesql”）：https://www.npmjs.com/package/msnodesql 。虽然它在与 Microsoft SQL Server 数据库的本地集成方面做得很好（比任何其他节点模块都好），但有几件事需要注意。
"msnodesql" require a few pre-requisites (like python, VC++, SQL native client etc.) to be installed on the host machine. That makes your "node" app "Windows" dependent. If you are fine with "Windows" based deployment, working with "msnodesql" is the best.
“msnodesql”需要在主机上安装一些先决条件（如python、VC++、SQL本机客户端等）。这使您的“节点”应用程序“Windows”依赖。如果您对基于“Windows”的部署感到满意，则最好使用“msnodesql”。
On the other hand, there is another module called "mssql" (available here https://www.npmjs.com/package/mssql) which can work with "tedious" or "msnodesql" based on configuration. While this module may not be as comprehensive as "msnodesql", it pretty much solves most of the needs.
另一方面，还有另一个名为“mssql”的模块（可在此处获得https://www.npmjs.com/package/mssql），它可以根据配置与“乏味”或“msnodesql”一起使用。虽然这个模块可能不如“msnodesql”那么全面，但它几乎解决了大部分需求。
If you would like to start with "mssql", I came across a simple and straight forward video, which explains about connecting to Microsoft SQL Server database using NodeJs here: https://www.youtube.com/watch?v=MLcXfRH1YzE
如果你想从“mssql”开始，我看到了一个简单直接的视频，它解释了如何使用 NodeJs 连接到 Microsoft SQL Server 数据库：https: //www.youtube.com/watch?v=MLcXfRH1YzE
Source code for the above video is available here: http://techcbt.com/Post/341/Node-js-basic-programming-tutorials-videos/how-to-connect-to-microsoft-sql-server-using-node-js
上述视频的源代码可在此处获得：http: //techcbt.com/Post/341/Node-js-basic-programming-tutorials-videos/how-to-connect-to-microsoft-sql-server-using-节点js
Just in case, if the above links are not working, I am including the source code here:
以防万一，如果上述链接不起作用，我在此处包含源代码：
var sql = require("mssql");
var dbConfig = {
server: "localhost\SQL2K14",
database: "SampleDb",
user: "sa",
password: "sql2014",
port: 1433
};
function getEmp() {
var conn = new sql.Connection(dbConfig);
conn.connect().then(function () {
var req = new sql.Request(conn);
req.query("SELECT * FROM emp").then(function (recordset) {
console.log(recordset);
conn.close();
})
.catch(function (err) {
console.log(err);
conn.close();
});
})
.catch(function (err) {
console.log(err);
});
//--> another way
//var req = new sql.Request(conn);
//conn.connect(function (err) {
// if (err) {
// console.log(err);
// return;
// }
// req.query("SELECT * FROM emp", function (err, recordset) {
// if (err) {
// console.log(err);
// }
// else {
// console.log(recordset);
// }
// conn.close();
// });
//});
}
getEmp();The above code is pretty self explanatory. We define the db connection parameters (in "dbConfig" JS object) and then use "Connection" object to connect to SQL Server. In order to execute a "SELECT" statement, in this case, it uses "Request" object which internally works with "Connection" object. The code explains both flavors of using "promise" and "callback" based executions.
上面的代码是不言自明的。我们定义db连接参数（在“dbConfig”JS对象中），然后使用“Connection”对象连接到SQL Server。为了执行“SELECT”语句，在这种情况下，它使用“Request”对象，该对象在内部与“Connection”对象一起工作。该代码解释了使用基于“promise”和“callback”的执行的两种风格。
The above source code explains only about connecting to sql server database and executing a SELECT query. You can easily take it to the next level by following documentation of "mssql" node available at: https://www.npmjs.com/package/mssql
上面的源代码只解释了连接到 sql server 数据库和执行 SELECT 查询。您可以通过以下“mssql”节点的文档轻松将其提升到一个新的水平：https: //www.npmjs.com/package/mssql
UPDATE:There is a new video which does CRUD operations using pure Node.js REST standard (with Microsoft SQL Server) here: https://www.youtube.com/watch?v=xT2AvjQ7q9E. It is a fantastic video which explains everything from scratch (it has got heck a lot of code and it will not be that pleasing to explain/copy the entire code here)
更新：这里有一个使用纯 Node.js REST 标准（使用 Microsoft SQL Server）执行 CRUD 操作的新视频：https: //www.youtube.com/watch?v=xT2AvjQ7q9E。这是一个很棒的视频，它从头开始解释了所有内容（它有很多代码，在这里解释/复制整个代码不会那么令人愉快）
回答by kop48
We just released preview driver for Node.JS for SQL Server connectivity. You can find it here: Introducing the Microsoft Driver for Node.JS for SQL Server.
我们刚刚发布了用于 SQL Server 连接的 Node.JS 预览版驱动程序。您可以在此处找到它： Introducing the Microsoft Driver for Node.JS for SQL Server。
The driver supports callbacks (here, we're connecting to a local SQL Server instance):
驱动程序支持回调（这里，我们连接到本地 SQL Server 实例）：
// Query with explicit connection
var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};Server=(local);Database=AdventureWorks2012;Trusted_Connection={Yes}";
sql.open(conn_str, function (err, conn) {
if (err) {
console.log("Error opening the connection!");
return;
}
conn.queryRaw("SELECT TOP 10 FirstName, LastName FROM Person.Person", function (err, results) {
if (err) {
console.log("Error running query!");
return;
}
for (var i = 0; i < results.rows.length; i++) {
console.log("FirstName: " + results.rows[i][0] + " LastName: " + results.rows[i][1]);
}
});
});
Alternatively, you can use events (here, we're connecting to SQL Azure a.k.a Windows Azure SQL Database):
或者，您可以使用事件（这里，我们连接到 SQL Azure 又名 Windows Azure SQL 数据库）：
// Query with streaming
var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};Server={tcp:servername.database.windows.net,1433};UID={username};PWD={Password1};Encrypt={Yes};Database={databasename}";
var stmt = sql.query(conn_str, "SELECT FirstName, LastName FROM Person.Person ORDER BY LastName OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY");
stmt.on('meta', function (meta) { console.log("We've received the metadata"); });
stmt.on('row', function (idx) { console.log("We've started receiving a row"); });
stmt.on('column', function (idx, data, more) { console.log(idx + ":" + data);});
stmt.on('done', function () { console.log("All done!"); });
stmt.on('error', function (err) { console.log("We had an error :-( " + err); });
If you run into any problems, please file an issue on Github: https://github.com/windowsazure/node-sqlserver/issues
如果遇到任何问题，请在 Github 上提交问题：https: //github.com/windowsazure/node-sqlserver/issues
回答by Menztrual
There is a module on npm called mssqlhelper
npm 上有一个模块叫做 mssqlhelper
You can install it to your project by npm i mssqlhelper
您可以通过以下方式将其安装到您的项目中 npm i mssqlhelper
Example of connecting and performing a query:
连接和执行查询的示例：
var db = require('./index');
db.config({
host: '192.168.1.100'
,port: 1433
,userName: 'sa'
,password: '123'
,database:'testdb'
});
db.query(
'select @Param1 Param1,@Param2 Param2'
,{
Param1: { type : 'NVarChar', size: 7,value : 'myvalue' }
,Param2: { type : 'Int',value : 321 }
}
,function(res){
if(res.err)throw new Error('database error:'+res.err.msg);
var rows = res.tables[0].rows;
for (var i = 0; i < rows.length; i++) {
console.log(rows[i].getValue(0),rows[i].getValue('Param2'));
}
}
);
You can read more about it here: https://github.com/play175/mssqlhelper
您可以在此处阅读更多相关信息：https: //github.com/play175/mssqlhelper
:o)
:o)
回答by Buju
msnodesqlis working out great for me. Here is a sample:
msnodesql对我来说效果很好。这是一个示例：
var mssql = require('msnodesql'),
express = require('express'),
app = express(),
nconf = require('nconf')
nconf.env()
.file({ file: 'config.json' });
var conn = nconf.get("SQL_CONN");
var conn_str = "Driver={SQL Server Native Client 11.0};Server=server.name.here;Database=Product;Trusted_Connection={Yes}";
app.get('/api/brands', function(req, res){
var data = [];
var jsonObject = {};
mssql.open(conn_str, function (err, conn) {
if (err) {
console.log("Error opening the connection!");
return;
}
conn.queryRaw("dbo.storedproc", function (err, results) {
if(err) {
console.log(err);
res.send(500, "Cannot retrieve records.");
}
else {
//res.json(results);
for (var i = 0; i < results.rows.length; i++) {
var jsonObject = new Object()
for (var j = 0; j < results.meta.length; j++) {
paramName = results.meta[j].name;
paramValue = results.rows[i][j];
jsonObject[paramName] = paramValue;
}
data.push(jsonObject); //This is a js object we are jsonizing not real json until res.send
}
res.send(data);
}
});
});
});
回答by Vijay Kumar Sharma
//start the program
var express = require('express');
var app = express();
app.get('/', function (req, res) {
var sql = require("mssql");
// config for your database
var config = {
user: 'datapullman',
password: 'system',
server: 'localhost',
database: 'chat6'
};
// connect to your database
sql.connect(config, function (err) {
if (err) console.log(err);
// create Request object
var request = new sql.Request();
// query to the database and get the records
request.query("select * From emp", function (err, recordset) {
if (err) console.log(err)
// send records as a response
res.send(recordset);
});
});
});
var server = app.listen(5000, function () {
console.log('Server is running..');
});
//create a table as emp in a database (i have created as chat6)
//在数据库中创建一个表作为emp（我已经创建为chat6）
// programs ends here
//save it as app.js and run as node app.js //open in you browser as localhost:5000
//将其保存为app.js并作为节点app.js运行 //在浏览器中以localhost:5000打开
