Mock postgres database connection(Pool, PoolClient, pg) using jest in typescript

Question

I have a simple function to fetch values from the Postgres database. I need to test the method by mocking the database. I have tried various approaches provided but none of them worked. Some errors always occur. I have tried the below solutions:

• How to Mock postgresql (pg) in node.js using jest
• How to mock pg Pool with Sinon
• Mock database Jest using ts-jest/utils
• How do you test Postgres with Node.js / Jest without mocking the pg import How to test async await pg connection with jest?
• How to mock SQL Server connection pool using Jest?
• How to Mock postgresql (pg) in node.js using jest

Below are my files.

db.ts

import { Pool } from 'pg'

const pool = new Pool({
    user: `${process.env.POSTGRES_USER}`,
    database: `${process.env.POSTGRES_DB}`,
    password: `${process.env.POSTGRES_PASSWORD}`,
    port: `${process.env.POSTGRES_PORT}`,
    host: `${process.env.POSTGRES_HOST}`,

})
export default pool;

fetch.ts

import pool from "./db";
import {SO} from "./s2l";

    export async function fetch(code: string): Promise<SO[]>{
        let socalls: SO[] =[]
        const sql = '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'
        const values = [code]
        const client =  await pool.connect()
        await client.query(sql, values).then(res => {
            const data = res.rows;
            let so
            data.forEach(row => {
                so ={
                service: {
                    code: row["v1"]
                },
                rCode: row["v2"],
                dVo: row["v3"],
                aVo: row["v4"],
            };
            socalls.push(so)
        });
        }).catch(e => console.error(e))
        .then(() => client.release())
        return Promise.all(socalls);
        }

Also, I need to pass the list in the query dynamically.

Answer 1

The below approach worked for me

fetch.test.ts

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

describe("Test fetch of so calls from database", () => {
  afterAll(() => {
    jest.resetAllMocks();
  });
  it("Happy case", async () => {
    (pool as any).connect = jest.fn().mockReturnThis();
    (pool as any).query = jest.fn().mockReturnThis();
    (pool as any).release = jest.fn().mockReturnThis();
    (pool as any).query.mockResolvedValueOnce({
      rows: [
        {
          v2: "EHRBFK76TGSMD",
          v3: "355GG",
          v4: "355GG",
          v1: "Q923892GT",
        },
      ],
    });
    const result = await fetchSiteCalls("Y2K");
    expect(result).toEqual([
      {
        rCode: "EHRBFK76TGSMD",
        dVo: "355GG",
        aVo: "355GG",
        service: { code: "Q923892GT" },
      },
    ]);
  });
});