TypeScript下使用Jest模拟Postgres pg库Pool/PoolClient连接测试问题

测试实现方案 #

1. 编写测试用例文件（fetch.test.ts） #

import pool from './db';
import { fetch } from './fetch';

// 全局mock pg模块
jest.mock('pg', () => {
  const mockClient = {
    query: jest.fn(),
    release: jest.fn()
  };
  const mockPool = {
    connect: jest.fn(() => Promise.resolve(mockClient))
  };
  return { Pool: jest.fn(() => mockPool) };
});

describe('fetch函数测试', () => {
  beforeEach(() => {
    // 每次用例执行前清空mock调用记录
    jest.clearAllMocks();
  });

  it('查询成功时返回正确的SO数组', async () => {
    // 模拟数据库返回的行数据
    const mockRows = [
      { v1: 'test_code1', v2: 'R1', v3: 'd1', v4: 'a1' },
      { v1: 'test_code2', v2: 'R2', v3: 'd2', v4: 'a2' }
    ];
    // 获取mock的client实例
    const mockClient = await pool.connect();
    // 给query方法绑定模拟返回值
    (mockClient.query as jest.Mock).mockResolvedValue({ rows: mockRows });

    // 调用待测试函数
    const result = await fetch('test_input_code');

    // 验证返回结果符合预期
    expect(result).toEqual([
      { service: { code: 'test_code1' }, rCode: 'R1', dVo: 'd1', aVo: 'a1' },
      { service: { code: 'test_code2' }, rCode: 'R2', dVo: 'd2', aVo: 'a2' }
    ]);
    // 验证SQL和参数正确传入
    expect(mockClient.query).toHaveBeenCalledWith(
      'Select sg.v1,sg.v2,sg.v3,sg.v4 from table1 sg where code = $1 and sg.r_code not in(\'ABC\', \'XYZ\', \'PQR\') order by sg.datetime asc',
      ['test_input_code']
    );
    // 验证数据库连接已释放
    expect(mockClient.release).toHaveBeenCalled();
  });

  it('查询报错时正常处理异常', async () => {
    const mockError = new Error('数据库查询失败');
    const mockClient = await pool.connect();
    (mockClient.query as jest.Mock).mockRejectedValue(mockError);
    const consoleSpy = jest.spyOn(console, 'error').mockImplementation();

    const result = await fetch('test_code');

    expect(result).toEqual([]);
    expect(consoleSpy).toHaveBeenCalledWith(mockError);
    expect(mockClient.release).toHaveBeenCalled();
    consoleSpy.mockRestore();
  });
});

2. 动态列表参数适配方案 #

如果需要把查询条件中的固定列表改为动态传入，可按如下方式调整代码和测试逻辑：

1. 修改fetch函数的参数和SQL拼接逻辑：

// 新增excludeRCodes参数接收动态排除列表
export async function fetch(code: string, excludeRCodes: string[]): Promise<SO[]>{
  let socalls: SO[] =[]
  // 动态生成IN子句的占位符，第一个参数code对应$1，列表参数从$2开始计数
  const excludePlaceholders = excludeRCodes.map((_, idx) => `$${idx + 2}`).join(',');
  const sql = `Select sg.v1,sg.v2,sg.v3,sg.v4 from table1 sg where code = $1 and sg.r_code not in(${excludePlaceholders}) order by sg.datetime asc`
  // 合并参数
  const values = [code, ...excludeRCodes]
  // 后续原有逻辑保持不变...
}

2. 对应测试用例中验证动态参数即可：

it('支持动态传入列表参数', async () => {
  const mockRows = [{ v1: 'test', v2: 'R3', v3: 'd3', v4: 'a3' }];
  const mockExcludeList = ['AAA', 'BBB', 'CCC'];
  const mockClient = await pool.connect();
  (mockClient.query as jest.Mock).mockResolvedValue({ rows: mockRows });

  await fetch('test_code', mockExcludeList);

  // 验证动态生成的SQL和参数符合预期
  expect(mockClient.query).toHaveBeenCalledWith(
    expect.stringContaining('not in($2,$3,$4)'),
    ['test_code', 'AAA', 'BBB', 'CCC']
  );
});

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