如何在 Express 4 Web 应用程序中跨多个路由使用单个 mssql 连接池? [英] How can I use a single mssql connection pool across several routes in an Express 4 web application?

问题描述

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

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

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

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.

推荐答案

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

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.

这里有两个关键元素在起作用.

Two key elements are at play here.

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

1. Modules are cached after 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.
2. 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.

在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}`)
})

在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
}

在 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

总结

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

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.

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

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

旧的解决方案

这是我 3 年前发布的解决方案，因为我相信我有一个值得分享的答案，而且我在其他地方找不到文档化的解决方案.还有一些问题(#118、#164, #165) 在 node-mssql 中讨论了这个话题.

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.

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

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

这篇关于如何在 Express 4 Web 应用程序中跨多个路由使用单个 mssql 连接池?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！