调用API时出现TypeError: connection.execute is not a function的解决方法

错误场景 #

调用接口 http://localhost:4000/api/employees/employees1 时触发以下错误：

TypeError: connection.execute is not a function
    at getQuestion1 (C:\Users\ej\OneDrive\Desktop\OJT\sdd\api\controllers\employee\part_1_question.js:28:48)
    at Layer.handle [as handle_request] (C:\Users\ej\OneDrive\Desktop\OJT\sdd\api\node_modules\express\lib\router\layer.js:95:5)
    at next (C:\Users\ej\OneDrive\Desktop\OJT\sdd\api\node_modules\express\lib\router\route.js:144:13)
    at Route.dispatch (C:\Users\ej\OneDrive\Desktop\OJT\sdd\api\node_modules\express\lib\router\route.js:114:3)
    at Layer.handle [as handle_request] (C:\Users\ej\OneDrive\Desktop\OJT\sdd\api\node_modules\express\lib\router\layer.js:95:5)
    at C:\Users\ej\OneDrive\Desktop\OJT\sdd\api\node_modules\express\lib\router\index.js:284:15
    at Function.process_params (C:\Users\ej\OneDrive\Desktop\OJT\sdd\api\node_modules\express\lib\router\index.js:346:12)
    at next (C:\Users\ej\OneDrive\Desktop\OJT\sdd\api\node_modules\express\lib\router\index.js:280:10)
    at Function.handle (C:\Users\ej\OneDrive\Desktop\OJT\sdd\api\node_modules\express\lib\router\index.js:175:3)
    at router (C:\Users\ej\OneDrive\Desktop\OJT\sdd\api\node_modules\express\lib\router\index.js:47:12)

问题代码 #

Db.js

const mysql2 = require('mysql2/promise');
const connection = mysql2.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'admin',
  database: 'sdd_crud',
});
module.exports = connection;

Part_1.js

const connection = require('../../DB/db.js');
 const getQuestion1 = async (req,res) =>{
    const query = "SELECT * FROM studentsquestionpart1"
    try {
        const [rows, field] = await connection.execute(query)
        res.status(200).json(rows)
    } catch (error) {
        res.status(400).json(error)
    }
}
module.exports = {getQuestion1 }

router.js

router.get('/employees1/', getQuestion1)

index.js

const express = require('express');
const cors = require('cors');
const employeeQuestion = require('./routes/employee/part_1_question.js');
const app = express()
app.use(cors());
app.use(express.json());
app.get('/', (req,res) =>{
    res.send("Hello World")
})
app.use('/api/employees', employeeQuestion)
app.listen(4000, () =>{
    console.log(`Website is running at port http://localhost:${4000}`)
})

错误原因 #

mysql2/promise 的 createConnection 方法返回的是Promise对象，而非直接可用的数据库连接实例。原代码中直接导出这个Promise，在Part_1.js里调用connection.execute时，实际是在Promise实例上调用方法，而Promise并没有execute属性，因此触发类型错误。

修复方案 #

推荐使用连接池（更适合生产环境，性能更稳定），或者修正连接获取逻辑确保拿到实际连接实例。

方案1：使用mysql2连接池（推荐）

修改Db.js为连接池模式：

const mysql2 = require('mysql2/promise');

// 创建连接池
const pool = mysql2.createPool({
  host: 'localhost',
  user: 'root',
  password: 'admin',
  database: 'sdd_crud',
  waitForConnections: true,
  connectionLimit: 10, // 可根据需求调整连接数
  queueLimit: 0
});

module.exports = pool;

Part_1.js无需修改，连接池支持直接调用execute方法，内部会自动管理连接。

方案2：修正单连接获取逻辑

如果坚持使用单连接，修改Db.js确保导出实际连接实例：

const mysql2 = require('mysql2/promise');

// 立即执行异步函数获取连接
let connection;
(async () => {
  try {
    connection = await mysql2.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'admin',
      database: 'sdd_crud',
    });
    console.log('数据库连接成功');
  } catch (err) {
    console.error('数据库连接失败:', err);
  }
})();

module.exports = connection;

注意：这种方式需要确保服务启动后连接已建立，否则首次调用可能仍会报错，因此更推荐连接池方案。

验证 #

重启Express服务，重新用Postman调用接口，即可正常返回查询结果。

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