Node.js/Express连接池报错：Cannot read properties of undefined (reading 'getConnection')

我新建了一个Node.js项目，用Express.js搭建基础框架，想通过连接池发起简单GET请求从数据库获取数据。在app.js里执行调用没问题，但在routes目录下的users.js的GET路由中调用时，抛出错误：connection pool Cannot read properties of undefined (reading 'getConnection')。

app.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var mysql = require('mysql');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');

var app = express();

// establish connection to database
var conpool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: '****',
  password: '****',
  database: 'db_name'
});

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/users', usersRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = {app, conpool};

users.js（位于与app.js同级的routes目录下）

var express = require('express');
var router = express.Router();
var db = require('../app').conpool;

/* GET users listing. */
router.get('/get', function (req, res, next) {
  db.getConnection((err, connection) => {
    if (err) throw err;
    var sql = null;
    if (req.query.name) {
      sql = 'SELECT * FROM users WHERE name = ' + sql.escape(req.query.name);
    } else {
      sql = 'SELECT * FROM users';
    }
    connection.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
    });
  });
});

module.exports = router;

核心原因：循环依赖导致的变量未定义 #

你遇到的问题是模块加载顺序引发的循环依赖：

• 在app.js中，你先加载了usersRouter = require('./routes/users')，此时users.js会立即执行，尝试从../app导入conpool，但此时app.js里的conpool还没被定义（它在加载路由之后才创建），所以users.js里拿到的db是undefined。
• 虽然最后app.js导出了conpool，但users.js在首次加载时已经缓存了undefined的引用，不会自动更新。

解决方案 #

方案1：调整加载顺序（快速修复） #

把conpool的创建代码移到路由加载之前，确保加载usersRouter时连接池已经存在：
修改app.js的代码顺序：

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var mysql = require('mysql');

// 先创建连接池
var conpool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: '****',
  password: '****',
  database: 'db_name'
});

// 再加载路由
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');

var app = express();

// 后续代码保持不变...

module.exports = {app, conpool};

方案2：抽离独立的数据库模块（规范推荐） #

创建单独的数据库配置文件，彻底避免循环依赖：

1. 项目根目录新建db.js：

var mysql = require('mysql');

var conpool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: '****',
  password: '****',
  database: 'db_name'
});

module.exports = conpool;

2. 修改app.js，从db.js导入连接池：

// 替换原有的mysql和conpool代码
var conpool = require('./db');

3. 修改users.js，从db.js导入：

var express = require('express');
var router = express.Router();
// 改为从独立模块导入
var db = require('../db');

// 后续路由代码不变...

额外注意：修复SQL注入风险 #

你代码里的sql.escape(req.query.name)存在错误——这里的sql是你定义的变量，不是mysql模块的escape方法，会导致报错。推荐用参数化查询彻底避免注入问题：

router.get('/get', function (req, res, next) {
  db.getConnection((err, connection) => {
    if (err) throw err;
    if (req.query.name) {
      // 参数化查询，用?占位符
      connection.query('SELECT * FROM users WHERE name = ?', [req.query.name], function (err, result) {
        if (err) throw err;
        console.log(result);
        connection.release(); // 记得释放连接
        res.send(result);
      });
    } else {
      connection.query('SELECT * FROM users', function (err, result) {
        if (err) throw err;
        console.log(result);
        connection.release();
        res.send(result);
      });
    }
  });
});

内容的提问来源于stack exchange，提问作者Sander De Pauw