如何在请求外部使用连接池？定时任务中数据库查询报错问题

问题根源 #

• 定时任务的回调函数没有HTTP请求上下文（req/res对象），所以tools.js里的req.app.locals.db会因为req不存在而报错
• Express接口里能正常使用是因为接口回调自带req对象，而定时任务脱离了HTTP请求流程

解决方案 #

方案1：将数据库连接池实例传入工具函数 #

修改server.js和tools.js，直接把db实例传给tool1：

server.js

const express = require('express');
const sql = require('mssql');
const schedule = require('node-schedule');
const app = express();

// 导入工具函数并保存引用
const tools = require('./tools.js')();
const job = schedule.scheduleJob('*/1 * * * *', function(){
    console.log('This job was supposed to run every 1 minute');
    // 传入已初始化的db实例
    tools.tool1(app.locals.db);
});

appPool.connect().then((pool) => {
    app.locals.db = pool;
    console.log('Connected to database: '+config.database);

    const server = app.listen(process.env.APP_PORT, () => {
        const host = server.address().address;
        const port = server.address().port;
        console.log('👍👍👍 ' + process.env.APP_NAME + ' app listening at http://%s:%s 👍👍👍', host, port);
    });
}).catch((err) => {
    console.error('Error creating connection pool', err);
});

tools.js

module.exports = function () {
    this.tool1 = function (db) {
        // 直接使用传入的db实例执行查询
        db.query('SELECT 1')
          .then(result => console.log('Query result:', result))
          .catch(err => console.error('Query error:', err));
    }
}

方案2：让工具模块接收Express app实例 #

初始化工具模块时传入app，工具函数直接访问app.locals.db：

server.js

// 修改导入方式，传入app实例
const tools = require('./tools.js')(app);

tools.js

module.exports = function (app) {
    this.tool1 = function () {
        app.locals.db.query('SELECT 1')
          .then(result => console.log('Query result:', result))
          .catch(err => console.error('Query error:', err));
    }
}

方案3：单独抽离数据库连接模块 #

把数据库连接逻辑做成独立模块，工具函数和定时任务直接引用：

db.js

const sql = require('mssql');
const config = require('./config'); // 你的数据库配置文件

let pool;

async function getPool() {
    if (!pool) {
        pool = await sql.connect(config);
    }
    return pool;
}

module.exports = { getPool };

server.js

const express = require('express');
const schedule = require('node-schedule');
const { getPool } = require('./db.js');
const app = express();

const tools = require('./tools.js')();
// 确保连接池初始化完成后再启动定时任务
getPool().then((pool) => {
    app.locals.db = pool;
    console.log('Connected to database: '+config.database);

    const job = schedule.scheduleJob('*/1 * * * *', async function(){
        console.log('This job was supposed to run every 1 minute');
        const db = await getPool();
        tools.tool1(db);
    });

    const server = app.listen(process.env.APP_PORT, () => {
        const host = server.address().address;
        const port = server.address().port;
        console.log('👍👍👍 ' + process.env.APP_NAME + ' app listening at http://%s:%s 👍👍👍', host, port);
    });
}).catch((err) => {
    console.error('Error creating connection pool', err);
});

tools.js

module.exports = function () {
    this.tool1 = function (db) {
        db.query('SELECT 1')
          .then(result => console.log('Query result:', result))
          .catch(err => console.error('Query error:', err));
    }
}

注意事项 #

• 所有数据库操作必须添加错误捕获，防止定时任务因未处理异常崩溃
• 确保连接池初始化完成后再启动定时任务，避免出现未连接状态下的查询

内容的提问来源于stack exchange，提问作者Adam W