与Jest和PostgreSQL的集成测试
#database #测试 #jest

开发人员的关键任务之一是为数据库编写集成测试，以确保实现按预期执行。但是，与共享数据库同时独立和同时运行集成测试可能会导致测试相互干扰并间歇性地失败。所有代码示例都在打字稿中，但是相同的逻辑适用于任何其他语言。

问题

当集成测试套件同时运行的共享数据库实例时，问题出现。例如，如果使用TS-Postgres库和代码块中提供的JEST测试套件对SQL存储库进行了简单实现，则您可以开玩笑几次并遇到并发问题。该问题源于await repository.insert(id);和expect(await repository.findAll()).toEqual([[id]]);语句之间的数据库，这是因为选择“无需测试完成”并在期望语句之前删除表记录。

。

src/dixpository.ts 

import { Client, Value } from "ts-postgres";

export class ExampleRepository {
  private readonly _client: Client;

  constructor(client: Client) {
    this._client = client;
  }

  async findAll(): Promise<Array<Value>> {
    return (await this._client.query("SELECT * FROM example")).rows;
  }

  async insert(id: string): Promise<void> {
    await this._client.query("INSERT INTO example (id) VALUES ($1)", [id]);
  }
}

src/ test /problematic/insert.test.ts 

import { Client } from "ts-postgres";
import { ExampleRepository } from "../../exampleRepository";
import { config } from "dotenv";
import { v4 } from "uuid";
import { rootConnect } from "../postgresUtils";

describe("Insert Test Suite", () => {
  let client: Client;
  let repository: ExampleRepository;

  beforeAll(async () => {
    config();
    client = await rootConnect();
  });

  afterAll(async () => {
    await client.end();
  });

  beforeEach(() => {
    repository = new ExampleRepository(client);
  });

  afterEach(async () => {
    await client.query("DELETE FROM example");
  });

  it("inserts and select", async () => {
    const id = v4();
    await repository.insert(id);
    expect(await repository.findAll()).toEqual([[id]]);
  });
});

src/ test /problematic/select.test.ts 

import { Client } from "ts-postgres";
import { ExampleRepository } from "../../exampleRepository";
import { config } from "dotenv";
import { rootConnect } from "../postgresUtils";

describe("Select Test Suite", () => {
  let client: Client;
  let repository: ExampleRepository;

  beforeAll(async () => {
    config();
    client = await rootConnect();
  });

  afterAll(async () => {
    await client.end();
  });

  beforeEach(() => {
    repository = new ExampleRepository(client);
  });

  afterEach(async () => {
    await client.query("DELETE FROM example");
  });

  it("selects nothing", async () => {
    expect(await repository.findAll()).toEqual([]);
  });
});

src/ test /postgreutils.ts 

export const rootConnect = async () => connectToDatabase(process.env.DB_NAME);

const connectToDatabase = async (database: string) => {
  const client = new Client({
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT),
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database,
    ssl: SSLMode.Disable,
  });

  await client.connect();
  return client;
};

解决方案

虽然我们可以将--maxWorkers=1指定为Jest，但这将大大增加我们的测试持续时间。一种更好的方法是通过确保每个测试套件在一个单独的隔离数据库实例上运行，启用可靠的，孤立的集成测试。为了实现这一目标，我们可以在beforeall函数期间创建一个新的数据库。

假设您的测试套件位于一个文件夹src/__test__和设置数据库表所需的SQL脚本中连接以运行SQL迁移脚本。为了实现这一目标，我们可以创建一个简单的助手，这将使我们只能在测试套件中调用一个功能。修改的代码如下所示。

src/ test /postgreutils.ts 

import { Client, SSLMode } from "ts-postgres";
import { v4 as uuid } from "uuid";
import { readdirSync, readFileSync } from "fs";

export const connectToTestDatabase = async () => {
  let client = await rootConnect();
  const database = uuid();
  await client.query(`CREATE DATABASE "${database}"`);
  await client.end();
  client = await connectToDatabase(database);
  await runMigrations(client);
  return client;
};

export const rootConnect = async () => connectToDatabase(process.env.DB_NAME);

const connectToDatabase = async (database: string) => {
  const client = new Client({
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT),
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database,
    ssl: SSLMode.Disable,
  });

  await client.connect();
  return client;
};

const runMigrations = async (client: Client) => {
  const migrationsPath = `${__dirname}/../../sql`;
  for (const filePath of readdirSync(migrationsPath))
    await runMigration(client, `${migrationsPath}/${filePath}`);
};

const runMigration = async (client: Client, filePath) => {
  for (const query of queriesInMigrationFile(filePath))
    await client.query(query);
};

/**
 * :warning: - Fails if a query inserts data containing ";" character
 * @param filePath
 */
const queriesInMigrationFile = (filePath: string) =>
  readFileSync(filePath).toString().split(";");

通过利用上述辅助功能，您可以确保每个测试套件都按照自己的数据库实例运行，避免并发问题并实现可靠的，孤立的集成测试。

使用PostgreSQL客户端优化迁移

为了提高运行迁移的性能，我们可以利用PostgreSQL客户端的使用。虽然先前涉及SQL文件自定义解析的解决方案可能对您有效，但PostgreSQL之类的客户可以提供更好的效率，并将支持涉及触发器的迁移。

要开始，只需按照您的操作系统按照以下说明安装PostgreSQL客户端：

  • 对于OS X，运行brew install libpq && brew link --force libpq5
  • 对于Ubuntu/Debian，Run sudo apt install postgresql-client

通过运行psql --version

验证安装

接下来，我们可以使用PostgreSQL客户端配置稍微修改我们的Rungigrations Helper函数以执行每个迁移文件。为此的代码段如下所示：

export const runMigrations: async (client: Client) => {
  for (const filePath of readdirSync(migrationsPath))
    child_process.execSync(
      `PGPASSWORD="${client.config.password}" psql -h ${client.config.host} -p ${client.config.port} -U ${client.config.user} -d ${client.config.database} -a -f ${migrationsPath}/${filePath}`
    );
};

奖金：连续集成（CI）

为了结束本文，我想为您提供使用PostgreSQL数据库服务的CI作业的两个示例，从而在CI/CD Pipeline上自动化测试。

与gitlab-ci（.gitlab-ci.yml） 

stages:
  - test

integration-tests:
  stage: test
  image: node:18.14.0
  services:
    - name: postgres:15.2
      alias: postgres
  variables:
    POSTGRES_DB: jest-psql-example
    POSTGRES_USER: postgres
    POSTGRES_PASSWORD: example
    POSTGRES_HOST_AUTH_METHOD: trust
    DB_HOST: "postgres"
    DB_PORT: "5432"
    DB_USER: "postgres"
    DB_PASSWORD: "example"
    DB_NAME: "my-database"
  before_script:
    - apt-get update && apt-get install -y postgresql-client
    - yarn
    - for f in ./sql/*.sql; do psql -h ${DB_HOST} -U ${DB_USER} -d ${DB_NAME} -a -f $f > /dev/null; done
  script:
    - jest

使用github工作流（.github/worflows/test.yaml） 

name: test
on: [ push ]
jobs:
  integration-tests:
    runs-on: ubuntu-latest
    container: node:18.14
    services:
      postgres:
        image: postgres:15.2
        env:
          POSTGRES_DB: jest-psql-example
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: example
          POSTGRES_HOST_AUTH_METHOD: trust
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432
    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-node@v3
        with:
          node-version: '18'
      - run: npm install -g yarn
      - run: yarn
      - name: Run migrations
        env:
          DB_HOST: "postgres"
          DB_USER: "postgres"
          PGPASSWORD: "example"
          DB_NAME: "my-database"
        run: |
          apt-get update
          apt-get install --yes postgresql-client
          for f in ./sql/*.sql; do psql -h ${DB_HOST} -U ${DB_USER} -d ${DB_NAME} -a -f $f > /dev/null; done
      - name: Run integration tests
        env:
          DB_HOST: "postgres"
          DB_PORT: "5432"
          DB_USER: "postgres"
          DB_PASSWORD: "example"
          DB_NAME: "jest-psql-example"
        run: jest

所有代码示例均可在Jest-PostgresSQL-Integration-Testing repository

上找到