Test Database: ізольоване тестове середовище

project/
├── migrations/
│   └── 001_create_users.sql
├── src/
│   └── users.js
├── test/
│   ├── db.js
│   ├── fixtures.js
│   └── users.test.js
├── migrate-test.js
└── package.json

npm install pg

CREATE DATABASE app_test;

export TEST_DATABASE_URL=postgres://app:password@localhost:5432/app_test

$env:TEST_DATABASE_URL="postgres://app:password@localhost:5432/app_test"

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

import { readdir, readFile } from 'node:fs/promises';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import pg from 'pg';

const { Client } = pg;

if (process.env.NODE_ENV !== 'test') {
  throw new Error('Міграції цього скрипту можна запускати лише в test-середовищі');
}

if (!process.env.TEST_DATABASE_URL) {
  throw new Error('Не задано TEST_DATABASE_URL');
}

const migrationsDirectory = path.join(
  path.dirname(fileURLToPath(import.meta.url)),
  'migrations',
);

const client = new Client({
  connectionString: process.env.TEST_DATABASE_URL,
});

await client.connect();

try {
  await client.query(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      name TEXT PRIMARY KEY,
      applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `);

  const migrationFiles = (await readdir(migrationsDirectory))
    .filter((file) => file.endsWith('.sql'))
    .sort();

  const appliedResult = await client.query(
    'SELECT name FROM schema_migrations ORDER BY name',
  );

  const appliedMigrations = new Set(
    appliedResult.rows.map((row) => row.name),
  );

  for (const migrationFile of migrationFiles) {
    if (appliedMigrations.has(migrationFile)) {
      continue;
    }

    const migrationPath = path.join(migrationsDirectory, migrationFile);
    const migrationSql = await readFile(migrationPath, 'utf8');

    await client.query('BEGIN');

    try {
      await client.query(migrationSql);
      await client.query(
        'INSERT INTO schema_migrations (name) VALUES ($1)',
        [migrationFile],
      );
      await client.query('COMMIT');

      console.log(`Застосовано міграцію: ${migrationFile}`);
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    }
  }
} finally {
  await client.end();
}

001_create_users.sql
002_add_status_to_users.sql
003_create_orders.sql

import pg from 'pg';

const { Pool } = pg;

if (process.env.NODE_ENV !== 'test') {
  throw new Error('Тести повинні запускатися з NODE_ENV=test');
}

if (!process.env.TEST_DATABASE_URL) {
  throw new Error('Не задано TEST_DATABASE_URL');
}

const databaseName = new URL(process.env.TEST_DATABASE_URL).pathname
  .replace(/^\//, '');

if (!databaseName.includes('test')) {
  throw new Error(
    `Небезпечне ім'я тестової бази: ${databaseName}. Очікується база з "test" у назві`,
  );
}

export const pool = new Pool({
  connectionString: process.env.TEST_DATABASE_URL,
});

export async function closeDatabase() {
  await pool.end();
}

// test/db.js

export async function resetDatabase() {
  await pool.query(`
    TRUNCATE TABLE users
    RESTART IDENTITY
    CASCADE
  `);
}

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

export async function createUser({
  email = 'olena@example.com',
  name = 'Олена',
} = {}) {
  const result = await pool.query(
    `
      INSERT INTO users (email, name)
      VALUES ($1, $2)
      RETURNING id, email, name, created_at
    `,
    [email, name],
  );

  return result.rows[0];
}

const firstUser = await createUser();

const secondUser = await createUser({
  email: 'ivan@example.com',
  name: 'Іван',
});

export async function findUserByEmail(db, email) {
  const result = await db.query(
    `
      SELECT id, email, name, created_at
      FROM users
      WHERE email = $1
    `,
    [email],
  );

  return result.rows[0] ?? null;
}

export async function renameUser(db, userId, name) {
  const result = await db.query(
    `
      UPDATE users
      SET name = $1
      WHERE id = $2
      RETURNING id, email, name, created_at
    `,
    [name, userId],
  );

  return result.rows[0] ?? null;
}

import { after, beforeEach, describe, it } from 'node:test';
import assert from 'node:assert/strict';

import { findUserByEmail, renameUser } from '../src/users.js';
import { closeDatabase, pool, resetDatabase } from './db.js';
import { createUser } from './fixtures.js';

describe('робота з користувачами', () => {
  beforeEach(async () => {
    await resetDatabase();
  });

  after(async () => {
    await closeDatabase();
  });

  it('знаходить користувача за email', async () => {
    const user = await createUser({
      email: 'olena@example.com',
      name: 'Олена',
    });

    const foundUser = await findUserByEmail(
      pool,
      'olena@example.com',
    );

    assert.deepEqual(foundUser, user);
  });

  it('повертає null, якщо користувача не знайдено', async () => {
    const foundUser = await findUserByEmail(
      pool,
      'missing@example.com',
    );

    assert.equal(foundUser, null);
  });

  it('змінює ім’я користувача', async () => {
    const user = await createUser({
      email: 'ivan@example.com',
      name: 'Іван',
    });

    const updatedUser = await renameUser(pool, user.id, 'Іван Петренко');

    assert.equal(updatedUser.id, user.id);
    assert.equal(updatedUser.email, 'ivan@example.com');
    assert.equal(updatedUser.name, 'Іван Петренко');
  });
});

{
  "type": "module",
  "scripts": {
    "migrate:test": "NODE_ENV=test node migrate-test.js",
    "test": "NODE_ENV=test node --test --test-concurrency=1"
  },
  "dependencies": {
    "pg": "^8.13.0"
  }
}

TEST_DATABASE_URL=postgres://app:password@localhost:5432/app_test npm run migrate:test

TEST_DATABASE_URL=postgres://app:password@localhost:5432/app_test npm test

$env:NODE_ENV="test"
$env:TEST_DATABASE_URL="postgres://app:password@localhost:5432/app_test"

npm run migrate:test
npm test

it('створює користувача з очікуваним ідентифікатором', async () => {
  const user = await createUser({
    email: 'test@example.com',
    name: 'Тестовий користувач',
  });

  assert.equal(user.id, 1);
});

const firstUser = await createUser({
  email: 'first@example.com',
});

const secondUser = await createUser({
  email: 'second@example.com',
});