Jest中Mock MySQL测试失败：认证错误与环境销毁问题

需求 #

• 在Jest测试中模拟数据库操作，验证函数是否按预期将数据写入目标表
• 无需复制完整数据库结构，仅创建包含所需字段的临时表，测试完成后自动删除

尝试的代码 #

var mysql = require('mysql2');
require('iconv-lite').encodingExists('foo');


it('tests jest-mysql', () => {

    var connection = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        database: 'test'
    });

    connection.connect();
       
    connection.query(
        "CREATE TABLE users( Name varchar(45),Age int)",
        (error, results, fields) => {
            if(error){
                throw error;
            }
            console.log('results', results);
        }
    )
    connection.end();
});

遇到的错误 #

ReferenceError: You are trying to `import` a file after the Jest environment has been torn down. From index.test.js.

      at ClientHandshake.handshakeResult (node_modules/mysql2/lib/commands/client_handshake.js:150:26)
      at ClientHandshake.execute (node_modules/mysql2/lib/commands/command.js:45:22)
      at Connection.handlePacket (node_modules/mysql2/lib/connection.js:456:32)
      at PacketParser.onPacket (node_modules/mysql2/lib/connection.js:85:12)
      at PacketParser.executeStart (node_modules/mysql2/lib/packet_parser.js:75:16)
      at Socket.<anonymous> (node_modules/mysql2/lib/connection.js:92:25)
C:\Office\bulk-backend\beats-bulk-ave-claim-sqs-trigger\claims\index.test.js:12
      throw error;
      ^

TypeError: authSwitch.authSwitchRequest is not a function
    at ClientHandshake.handshakeResult (C:\Office\bulk-backend\beats-bulk-ave-claim-sqs-trigger\claims\node_modules\mysql2\
lib\commands\client_handshake.js:155:22)
    at ClientHandshake.execute (C:\Office\bulk-backend\beats-bulk-ave-claim-sqs-trigger\claims\node_modules\mysql2\lib\comm
ands\command.js:45:22)
    at Connection.handlePacket (C:\Office\bulk-backend\beats-bulk-ave-claim-sqs-trigger\claims\node_modules\mysql2\lib\conn
ection.js:456:32)
    at PacketParser.onPacket (C:\Office\bulk-backend\beats-bulk-ave-claim-sqs-trigger\claims\node_modules\mysql2\lib\connec
tion.js:85:12)
    at PacketParser.executeStart (C:\Office\bulk-backend\beats-bulk-ave-claim-sqs-trigger\claims\node_modules\mysql2\lib\pa
cket_parser.js:75:16)
    at Socket.<anonymous> (C:\Office\bulk-backend\beats-bulk-ave-claim-sqs-trigger\claims\node_modules\mysql2\lib\connectio
n.js:92:25)
    at Socket.emit (node:events:527:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  code: 'AUTH_SWITCH_PLUGIN_ERROR',
  fatal: true
}

错误原因 #

1. Jest环境提前销毁：原代码用回调处理异步的connection.query，但Jest测试同步执行结束后会立刻销毁环境，此时异步回调才开始执行，导致报错。
2. 数据库认证失败：连接配置缺少密码，或是mysql2版本与MySQL服务器的认证插件不兼容，触发AUTH_SWITCH_PLUGIN_ERROR。

解决方案代码 #

const mysql = require('mysql2/promise'); // 使用Promise版本，适配async/await

let connection;

// 每个测试前创建连接和临时表
beforeEach(async () => {
  connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '你的数据库密码', // 补充密码解决认证问题
    database: 'test',
    // 若仍有认证插件问题，可添加此配置
    // authPlugins: { mysql_native_password: require('mysql2/lib/auth_plugins/mysql_native_password') }
  });

  await connection.execute("CREATE TABLE users( Name varchar(45), Age int)");
});

// 每个测试后删除临时表并关闭连接
afterEach(async () => {
  await connection.execute("DROP TABLE users");
  await connection.end();
});

it('验证数据写入功能', async () => {
  // 模拟待测试的写入函数
  const insertUser = async (name, age) => {
    await connection.execute("INSERT INTO users (Name, Age) VALUES (?, ?)", [name, age]);
  };

  // 执行写入操作
  await insertUser('张三', 25);

  // 查询验证数据是否正确写入
  const [rows] = await connection.execute("SELECT * FROM users WHERE Name = ?", ['张三']);
  
  expect(rows.length).toBe(1);
  expect(rows[0].Age).toBe(25);
});

方案说明 #

• 使用mysql2/promise配合async/await，确保Jest等待所有异步操作完成，避免环境提前销毁的问题。
• 通过beforeEach和afterEach实现测试前后的资源管理：创建临时表、测试后删除表并关闭连接，保证每个测试的独立性。
• 补充数据库密码解决认证错误，若仍存在插件兼容问题，可添加authPlugins配置指定认证插件。
• 测试中通过查询数据库验证数据是否正确写入，完全匹配需求。

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