node.js 如何在 Express 4 Web 应用程序中跨多个路由使用单个 mssql 连接池？

Question

提问by Christiaan Westerbeek

I want to use node-mssqlas a MSSQL database connector in a Node JS Express 4 web application. Route handler logic is handled in separate files.

我想在 Node JS Express 4 Web 应用程序中使用node-mssql作为 MSSQL 数据库连接器。路由处理程序逻辑在单独的文件中处理。

How do I create a single/global connection pool and use it across several files where route logic is handled? I don't want to make a new connection pool in each route handler function/file.

如何创建单个/全局连接池并在处理路由逻辑的多个文件中使用它？我不想在每个路由处理程序函数/文件中创建一个新的连接池。

Answer 1

回答by Christiaan Westerbeek

It's been 3 years since I asked and answered the question. Since then a few things have changed. Here's the new solution based on ES6, mssql 4 and Express 4 that I would suggest today.

自从我问和回答这个问题已经三年了。从那时起，一些事情发生了变化。这是我今天建议的基于 ES6、mssql 4 和 Express 4 的新解决方案。

Two key elements are at play here.

这里有两个关键因素在起作用。

Modules are cachedafter the first time they are loaded. This means that every call to require('./db') will return exactly the same object. The first require of db.js will run that file and create the promise and export it. The second require of db.js will return THAT same promise without running the file. And it's that promise that will resolve with the pool.
A promise can be thenified again. And if it resolved before, it will immediately resolve again with whatever it resolved with the first time, which is the pool.

模块在第一次加载后被缓存。这意味着每次调用 require('./db') 都将返回完全相同的对象。db.js 的第一个 require 将运行该文件并创建承诺并将其导出。db.js 的第二个要求将在不运行文件的情况下返回相同的承诺。正是这个承诺将与游泳池一起解决。
一个承诺可以再次确认。如果之前解决了，它会立即再次解决第一次解决的问题，也就是池。

In server.js

在 server.js

const express = require('express')
// require route handlers.
// they will all include the same connection pool
const set1Router = require('./routes/set1')
const set2Router = require('./routes/set2')

// generic express stuff
const app = express()

// ...
app.use('/set1', set1Router)
app.use('/set2', set2Router)

// No need to connect the pool
// Just start the web server

const server = app.listen(process.env.PORT || 3000, () => {
  const host = server.address().address
  const port = server.address().port

  console.log(`Example app listening at http://${host}:${port}`)
})

In db.js

在 db.js

const sql = require('mssql')
const config = {/*...*/}

const poolPromise = new sql.ConnectionPool(config)
  .connect()
  .then(pool => {
    console.log('Connected to MSSQL')
    return pool
  })
  .catch(err => console.log('Database Connection Failed! Bad Config: ', err))

module.exports = {
  sql, poolPromise
}

In routes/set1.jsand routes/set2.js

在routes/set1.js和routes/set2.js

const express = require('express')
const router = express.Router()
const { poolPromise } = require('./db')

router.get('/', async (req, res) => {
  try {
    const pool = await poolPromise
    const result = await pool.request()
        .input('input_parameter', sql.Int, req.query.input_parameter)
        .query('select * from mytable where id = @input_parameter')      

    res.json(result.recordset)
  } catch (err) {
    res.status(500)
    res.send(err.message)
  }
})

module.exports = router

To summarize

总结一下

You'll always get the same promise due to module caching and that promise will, again and again, resolve with the pool it resolved with the first time. Thus each router file uses the same pool.

由于模块缓存，您将始终获得相同的承诺，并且该承诺将一次又一次地与第一次解决的池一起解决。因此，每个路由器文件使用相同的池。

BTW: there are easier ways to go about the try catch in the express route that I won't cover in this answer. Read about it here: https://medium.com/@Abazhenov/using-async-await-in-express-with-node-8-b8af872c0016

顺便说一句：有更简单的方法可以在我不会在本答案中介绍的快速路线中进行尝试捕获。在这里阅读：https: //medium.com/@Abazhenov/using-async-await-in-express-with-node-8-b8af872c0016

The old solution

旧的解决方案

This is the solution I posted 3 years ago, because I believed I had an answer that was worth to share and I couldn't find a documented solution elsewhere. Also in a few issues (#118, #164, #165) at node-mssql this topic is discussed.

这是我 3 年前发布的解决方案，因为我相信我有一个值得分享的答案，而且我在其他地方找不到记录在案的解决方案。同样在node-mssql的几个问题（#118、#164、#165）中讨论了这个主题。

In server.js

在 server.js

var express = require('express');
var sql     = require('mssql');
var config  = {/*...*/};
//instantiate a connection pool
var cp      = new sql.Connection(config); //cp = connection pool
//require route handlers and use the same connection pool everywhere
var set1    = require('./routes/set1')(cp);
var set2    = require('./routes/set2')(cp);

//generic express stuff
var app = express();

//...
app.get('/path1', set1.get);
app.get('/path2', set2.get);

//connect the pool and start the web server when done
cp.connect().then(function() {
  console.log('Connection pool open for duty');

  var server = app.listen(3000, function () {

    var host = server.address().address;
    var port = server.address().port;

    console.log('Example app listening at http://%s:%s', host, port);

  });
}).catch(function(err) {
  console.error('Error creating connection pool', err);
});

In routes/set1.js

在 routes/set1.js

var sql     = require('mssql');

module.exports = function(cp) {
  var me = {
    get: function(req, res, next) {
      var request = new sql.Request(cp);
      request.query('select * from test', function(err, recordset) {
        if (err) {
          console.error(err);
          res.status(500).send(err.message);
          return;
        }
        res.status(200).json(recordset);
      });
    }
  };

  return me;
};

Answer 2

回答by clay

When you configure your app (like when you create the express server), make the DB connection. Make sure this is done BEFORE you require all your routes! (finagle the requires at the top of the file)

当您配置您的应用程序时（就像您创建快速服务器时），建立数据库连接。在您需要所有路线之前，请确保已完成此操作！(finagle 文件顶部的 requires )

Just like the docs:

就像文档一样：

var sql = require('mssql'); var connection = new sql.Connection(..... //store the connection sql.globalConnection = connection;

Then in all your route files, you can do this:

然后在所有路由文件中，您可以执行以下操作：

var sql = require('mssql'); var sqlConn = sql.globalConnection; var request = new sql.Request(sqlConn); //...

That should do it!

应该这样做！

All that said, go use knexto manage your MySQL query building. It has a built in connection pool, and you store the connected knex instance the same way. As well as a generous helping of awesome.

说了这么多，去使用knex来管理你的 MySQL 查询构建。它有一个内置的连接池，您可以以相同的方式存储连接的 knex 实例。以及真棒的慷慨帮助。

Answer 3

回答by Konstantin Tarkus

`src/config.js`

export default {
  database: {
    server: process.env.DATABASE_SERVER || '<server>.database.windows.net',
    port: 1433,
    user: process.env.DATABASE_USER || '<user>@<server>',
    password: process.env.DATABASE_PASSWORD || '<password>',
    database: process.env.DATABASE_NAME || '<database>',
    connectionTimeout: 30000,
    driver: 'tedious',
    stream: false,
    options: {
      appName: '<app-name>',
      encrypt: true
    }
  }
};

`src/server.js`

import sql from 'mssql';
import express from 'express';
import config from './config';

// Create and configure an HTTP server
const server = express();
server.set('port', (process.env.PORT || 5000));

// Register Express routes / middleware
server.use('/api/user', require('./api/user');

// Open a SQL Database connection and put it into the global
// connection pool, then launch the HTTP server
sql.connect(config.database, err => {
  if (err) {
    console.log('Failed to open a SQL Database connection.', err.stack);
  }
  server.listen(server.get('port'), () => {
    console.log('Node app is running at http://127.0.0.1:' + server.get('port'));
  });
});

sql.on('error', err => console.log(err.stack));

`src/api/user.js`

import sql from 'mssql';
import { Router } from 'express';

const router = new Router();

router.get('/:id', async (req, res, next) => {
  try {
    const request = new sql.Request();
    request.input('UserID', req.params.id);
    request.multiple = true;

    const dataset = await request.query(`
      SELECT UserID, Name, Email
      FROM [User] WHERE UserID = @UserID;
      SELECT r.RoleName FROM UserRole AS r
        INNER JOIN [User] AS u ON u.UserID = r.UserID
      WHERE u.UserID = @UserID
    `);

    const user = dataset[0].map(row => ({
      id: row.UserID,
      name: row.Name,
      email: row.Email,
      roles: dataset[1].map(role => role.RoleName)
    })).shift();

    if (user) {
      res.send(user);
    } else {
      res.statusCode(404);
    }
  } catch (err) {
    next(err);
  }
});

export default router;

See alsoMSSQL SDK for Node.js, T-SQL Reference, React Starter Kit

另请参阅适用于 Node.js 的 MSSQL SDK、T-SQL 参考、React Starter Kit

Answer 4

回答by Jonathan

I used similar concept (single connection pool), but wrapped the connection logic in one file (No need to pass connection pool to other places). The connPoolPromisebelow will only be initialized once since modules are cached after the first time they are loaded.

我使用了类似的概念 ( single connection pool)，但将连接逻辑包装在一个文件中（无需将连接池传递到其他地方）。在connPoolPromise下面将只有一次，因为模块是他们第一次加载后缓存被初始化。

e.g. DBUtil.js

例如 DBUtil.js

const sql = require('mssql');
const dbConfig = require('./dbconfig');
let connPoolPromise = null;

const getConnPoolPromise = () => {
  if (connPoolPromise) return connPoolPromise;

  connPoolPromise = new Promise((resolve, reject) => {
    const conn = new sql.ConnectionPool(dbConfig);

    conn.on('close', () => {
      connPoolPromise = null;
    });

    conn.connect().then(connPool => {
      return resolve(connPool);
    }).catch(err => {
      connPoolPromise = null;
      return reject(err);
    });
  });

  return connPoolPromise;
}

// Fetch data example using callback
exports.query = (sqlQuery, callback) => {

  getConnPoolPromise().then(connPool => {

    return connPool.request().query(sqlQuery);

  }).then(result => {
    callback(null, result);
  }).catch(err => {
    callback(err);
  });

};

Usage user.js:

用法user.js：

const DBUtil = require('./DBUtil');
DBUtil.query('select * from user where userId = 12', (err, recordsets) => {
  if (err) return callback(err);

  // Handle recordsets logic

}

Answer 5

回答by ozzieisaacs

This is how I did it which I think is a little simpler than some of the other solutions.

这就是我这样做的方式，我认为这比其他一些解决方案要简单一些。

Database File (db.js):

数据库文件（db.js）：

const sql = require('mssql')

const config = {}

const pool = new sql.ConnectionPool(config)
  .connect()
  .then(pool => {
    console.log('Connected to MSSQL')
    return pool
  })
  .catch(err => console.log('Database Connection Failed! Bad Config: ', err))

module.exports = {
  sql, pool
}

Query:

询问：

const { pool, sql } = require('../db')

return pool.then(conn => {
    const ps = new sql.PreparedStatement(conn)
    ps.input('xxxx', sql.VarChar)

    return ps.prepare(`SELECT * from table where xxxx = @xxxx`)
      .then(data => ps.execute({ xxxx: 'xxxx' }))
  })

EDIT: Updated to match Christiaan Westerbeek's gist which was much cleaner.

编辑：更新以匹配更清晰的 Christiaan Westerbeek 的要点。

Answer 6

回答by Shawn Kelly

Not crazy about the examples I've seen so far for setting up pooled connection. I do:

到目前为止我看到的用于设置池连接的示例并不疯狂。我愿意：

const pool = new mssql.ConnectionPool(msConfig).connect()
  .then(_ => { return _ } )
  .catch(e => console.error("Database Trouble!  ", e))
  
 /* ... */
 
 pool
  .then(_ => _.query( /* ... */ )
  .then(result => { /* ... */ })
  .catch(e => { /* ... */ })

node.js 如何在 Express 4 Web 应用程序中跨多个路由使用单个 mssql 连接池？

提问by Christiaan Westerbeek

回答by Christiaan Westerbeek

回答by clay

回答by Konstantin Tarkus

`src/config.js`

`src/config.js`

`src/server.js`

`src/server.js`

`src/api/user.js`

`src/api/user.js`

回答by Jonathan

回答by ozzieisaacs

回答by Shawn Kelly

相关推荐

最近更新

标签

node.js 如何在 Express 4 Web 应用程序中跨多个路由使用单个 mssql 连接池？

提问by Christiaan Westerbeek

回答by Christiaan Westerbeek

回答by clay

回答by Konstantin Tarkus

src/config.js

src/config.js

src/server.js

src/server.js

src/api/user.js

src/api/user.js

回答by Jonathan

回答by ozzieisaacs

回答by Shawn Kelly

相关推荐

node.js 传入的参数必须是 24 个十六进制字符的字符串 - 我认为是

node.js Webpack 4“大小超过推荐限制（244 KiB）”

node.js npm install packagename --save-dev 不更新 package.json

node.js npm install 出现错误：`gyp` 失败，退出代码：1

相关推荐

最近更新

标签

`src/config.js`

`src/config.js`

`src/server.js`

`src/server.js`

`src/api/user.js`

`src/api/user.js`