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

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.

我想在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.

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

5 个解决方案

#1

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. It has not been tested though.

我提问并回答了这个问题已经3年了。从那时起，一些事情发生了变化。这是我今天建议的基于ES6，mssql 4和Express 4的新解决方案。它尚未经过测试。

Two key elements are at play here.

这里有两个关键要素。

1. Modules are cached after the first time they are loaded. This means that every call to require('./db') will get exactly the same object returned. The first require of db.js will run that file and create the promise and export it. The second require of db.js will export THAT same promise. That promise will resolve with the pool.
2. 模块在第一次加载后进行缓存。这意味着每次调用require（'。/ db'）将获得完全相同的返回对象。 db.js的第一个要求将运行该文件并创建promise并将其导出。 db.js的第二个要求将导出相同的承诺。该承诺将通过池解决。
3. 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.
4. 可以再次承诺一个承诺。如果它之前解决了，它会立即再次解决它第一次解决的问题，即池。

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 = 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.js and 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;
};

#2

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文件顶部的要求）

Just like the docs:

就像文档一样：

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

var sql = require（'mssql'）; var connection = new sql.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); //...

var sql = require（'mssql'）; var sqlConn = sql.globalConnection; var request = new sql.Request（sqlConn）; // ...

That should do it!

应该这样做！

All that said, go use knex to 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实例。以及慷慨的帮助。

#3

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 also MSSQL SDK for Node.js, T-SQL Reference, React Starter Kit

另请参阅MSSQL SDK for Node.js，T-SQL Reference，React Starter Kit

#4

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 connPoolPromise below will only be initialized once since modules are cached after the first time they are loaded.

我使用了类似的概念（单连接池），但将连接逻辑包装在一个文件中（无需将连接池传递到其他位置）。由于模块在第一次加载后被缓存，因此下面的connPoolPromise只会被初始化一次。

e.g. DBUtil.js

例如DBUtil.js

var sql = require('mssql'),
  connPoolPromise = null;

function getConnPoolPromise() {
  if (connPoolPromise) return connPoolPromise;

  connPoolPromise = new Promise(function (resolve, reject) {
    var conn = new sql.Connection(require('./dbConfig'));

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

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

  return connPoolPromise;
}

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

  getConnPoolPromise().then(function (connPool) {

    var sqlRequest = new sql.Request(connPool);
    return sqlRequest.query(sqlQuery);

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

};

Usage user.js:

用法user.js：

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

  // Handle recordsets logic

}

#5

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.

编辑：更新以匹配克里斯蒂安韦斯特贝克的要点，这是更清洁。