Test_database

Chapter 45: Test Database Management

Managing Test Databases Effectively

45.1 Test Database Overview

Proper test database management ensures reliable, isolated, and fast tests.

                    Test Database Lifecycle
    ================================================================================

    Test Suite Start
         |
         v
    +------------------+
    | Create Database  |  <-- Fresh test database
    +------------------+
         |
         v
    +------------------+
    | Run Migrations   |  <-- Set up schema
    +------------------+
         |
         v
    +------------------+
    | Run Tests        |  <-- Each test in transaction
    +------------------+
         |
         v
    +------------------+
    | Cleanup          |  <-- Rollback or truncate
    +------------------+
         |
         v
    +------------------+
    | Drop Database    |  <-- Clean up
    +------------------+

    ================================================================================

45.2 Test Database Configuration

Environment Configuration

NODE_ENV=test
DB_HOST=localhost
DB_PORT=5432
DB_USERNAME=postgres
DB_PASSWORD=postgres
DB_DATABASE=myapp_test
DB_SYNCHRONIZE=true
DB_DROP_SCHEMA=true

// test/config/test-database.config.ts
import { DataSource, DataSourceOptions } from 'typeorm';
import { config } from 'dotenv';

config({ path: '.env.test' });

export const testDatabaseConfig: DataSourceOptions = {
  type: 'postgres',
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT, 10) || 5432,
  username: process.env.DB_USERNAME || 'postgres',
  password: process.env.DB_PASSWORD || 'postgres',
  database: process.env.DB_DATABASE || 'myapp_test',
  entities: ['src/**/*.entity{.ts,.js}'],
  synchronize: process.env.DB_SYNCHRONIZE === 'true',
  dropSchema: process.env.DB_DROP_SCHEMA === 'true',
  logging: false,
};

export const createTestDataSource = (): DataSource => {
  return new DataSource(testDatabaseConfig);
};

Test Database Service

import { DataSource, EntityMetadata } from 'typeorm';

export class TestDatabaseService {
  private static dataSource: DataSource | null = null;
  private static isInitialized = false;

  static async getDataSource(): Promise<DataSource> {
    if (this.dataSource && this.dataSource.isInitialized) {
      return this.dataSource;
    }

    this.dataSource = new DataSource({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'postgres',
      password: 'postgres',
      database: 'myapp_test',
      entities: ['src/**/*.entity{.ts,.js}'],
      synchronize: true,
      dropSchema: true,
      logging: false,
    });

    await this.dataSource.initialize();
    this.isInitialized = true;

    return this.dataSource;
  }

  static async cleanup(): Promise<void> {
    if (this.dataSource?.isInitialized) {
      await this.dataSource.destroy();
      this.dataSource = null;
      this.isInitialized = false;
    }
  }

  static async clearTables(): Promise<void> {
    const dataSource = await this.getDataSource();
    const entities = dataSource.entityMetadatas;

    for (const entity of entities) {
      const repository = dataSource.getRepository(entity.name);
      await repository.query(`TRUNCATE "${entity.tableName}" CASCADE`);
    }
  }

  static async resetDatabase(): Promise<void> {
    await this.cleanup();
    await this.getDataSource();
  }
}

45.3 Transaction-Based Tests

Transaction Rollback Strategy

import { DataSource, QueryRunner } from 'typeorm';

export class TransactionTestService {
  private queryRunner: QueryRunner | null = null;

  constructor(private dataSource: DataSource) {}

  async startTransaction(): Promise<QueryRunner> {
    this.queryRunner = this.dataSource.createQueryRunner();
    await this.queryRunner.connect();
    await this.queryRunner.startTransaction();
    return this.queryRunner;
  }

  async rollbackTransaction(): Promise<void> {
    if (this.queryRunner) {
      await this.queryRunner.rollbackTransaction();
      await this.queryRunner.release();
      this.queryRunner = null;
    }
  }

  getManager() {
    return this.queryRunner?.manager;
  }
}

// Usage in tests
describe('With Transaction Rollback', () => {
  let transactionService: TransactionTestService;
  let dataSource: DataSource;

  beforeAll(async () => {
    dataSource = await TestDatabaseService.getDataSource();
    transactionService = new TransactionTestService(dataSource);
  });

  afterAll(async () => {
    await TestDatabaseService.cleanup();
  });

  afterEach(async () => {
    await transactionService.rollbackTransaction();
  });

  it('should create user and rollback', async () => {
    await transactionService.startTransaction();
    const manager = transactionService.getManager();

    await manager?.save(User, {
      name: 'Test User',
      email: 'test@example.com',
    });

    // User exists in transaction
    const found = await manager?.findOne(User, {
      where: { email: 'test@example.com' },
    });
    expect(found).toBeDefined();

    // After test, transaction is rolled back
  });

  it('should start with clean state', async () => {
    await transactionService.startTransaction();
    const manager = transactionService.getManager();

    // Previous user doesn't exist
    const found = await manager?.findOne(User, {
      where: { email: 'test@example.com' },
    });
    expect(found).toBeNull();
  });
});

Jest Setup for Transaction Tests

import { TestDatabaseService } from './services/test-database.service';
import { TransactionTestService } from './services/transaction-test.service';

let transactionService: TransactionTestService;

global.beforeAll(async () => {
  const dataSource = await TestDatabaseService.getDataSource();
  transactionService = new TransactionTestService(dataSource);
});

global.afterAll(async () => {
  await TestDatabaseService.cleanup();
});

global.beforeEach(async () => {
  await transactionService.startTransaction();
});

global.afterEach(async () => {
  await transactionService.rollbackTransaction();
});

export const getTestManager = () => transactionService.getManager();

45.4 Database Seeding

Seed Service

import { DataSource } from 'typeorm';
import { UserFixture } from '../fixtures/user.fixture';
import { PostFixture } from '../fixtures/post.fixture';

export class SeedService {
  constructor(private dataSource: DataSource) {}

  async seedUsers(count: number = 10): Promise<User[]> {
    const users = UserFixture.createMany(count);
    return this.dataSource.getRepository(User).save(users);
  }

  async seedAdmin(): Promise<User> {
    const admin = UserFixture.create({
      name: 'Admin User',
      email: 'admin@example.com',
      role: 'admin',
    });
    return this.dataSource.getRepository(User).save(admin);
  }

  async seedPostsWithUsers(options: {
    userCount: number;
    postsPerUser: number;
  }): Promise<{ users: User[]; posts: Post[] }> {
    const users = await this.seedUsers(options.userCount);
    const posts: Post[] = [];

    for (const user of users) {
      const userPosts = PostFixture.createMany(options.postsPerUser);
      for (const post of userPosts) {
        post.authorId = user.id;
      }
      const saved = await this.dataSource.getRepository(Post).save(userPosts);
      posts.push(...saved);
    }

    return { users, posts };
  }

  async seedFullScenario(): Promise<{
    users: User[];
    posts: Post[];
    comments: Comment[];
  }> {
    const users = await this.seedUsers(5);
    const posts: Post[] = [];
    const comments: Comment[] = [];

    for (const user of users) {
      const userPosts = PostFixture.createMany(3);
      for (const post of userPosts) {
        post.authorId = user.id;
      }
      const savedPosts = await this.dataSource.getRepository(Post).save(userPosts);
      posts.push(...savedPosts);

      for (const post of savedPosts) {
        const postComments = CommentFixture.createMany(2);
        for (const comment of postComments) {
          comment.postId = post.id;
          comment.authorId = users[Math.floor(Math.random() * users.length)].id;
        }
        const savedComments = await this.dataSource.getRepository(Comment).save(postComments);
        comments.push(...savedComments);
      }
    }

    return { users, posts, comments };
  }
}

45.5 Parallel Test Execution

Database Isolation for Parallel Tests

import { DataSource } from 'typeorm';

export class ParallelTestService {
  private static databases = new Map<string, DataSource>();

  static async createDatabase(testId: string): Promise<DataSource> {
    const dbName = `test_${testId}_${Date.now()}`;

    const dataSource = new DataSource({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'postgres',
      password: 'postgres',
      database: 'postgres',  // Connect to default first
      synchronize: false,
    });

    await dataSource.initialize();

    // Create test database
    await dataSource.query(`CREATE DATABASE "${dbName}"`);

    // Connect to test database
    const testDataSource = new DataSource({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'postgres',
      password: 'postgres',
      database: dbName,
      entities: ['src/**/*.entity{.ts,.js}'],
      synchronize: true,
    });

    await testDataSource.initialize();
    this.databases.set(testId, testDataSource);

    await dataSource.destroy();

    return testDataSource;
  }

  static async dropDatabase(testId: string): Promise<void> {
    const dataSource = this.databases.get(testId);

    if (dataSource) {
      const dbName = dataSource.options.database as string;
      await dataSource.destroy();
      this.databases.delete(testId);

      // Drop database
      const mainDataSource = new DataSource({
        type: 'postgres',
        host: 'localhost',
        port: 5432,
        username: 'postgres',
        password: 'postgres',
        database: 'postgres',
      });

      await mainDataSource.initialize();
      await mainDataSource.query(`DROP DATABASE IF EXISTS "${dbName}"`);
      await mainDataSource.destroy();
    }
  }

  static async cleanupAll(): Promise<void> {
    for (const [testId] of this.databases) {
      await this.dropDatabase(testId);
    }
  }
}

// Jest configuration for parallel tests
// jest-parallel.config.js
module.exports = {
  maxWorkers: 4,  // Run 4 tests in parallel
  testTimeout: 60000,
};

45.6 Test Database Migrations

Migration Testing

import { DataSource } from 'typeorm';

export class MigrationTestService {
  static async testMigration(
    migrationClass: any,
    direction: 'up' | 'down' = 'up',
  ): Promise<void> {
    const dataSource = new DataSource({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'postgres',
      password: 'postgres',
      database: 'myapp_migration_test',
      entities: ['src/**/*.entity{.ts,.js}'],
      synchronize: false,
      dropSchema: true,
    });

    await dataSource.initialize();

    try {
      const migration = new migrationClass();

      if (direction === 'up') {
        await migration.up(dataSource.createQueryRunner());
      } else {
        await migration.down(dataSource.createQueryRunner());
      }

      // Verify migration result
      // Add assertions here
    } finally {
      await dataSource.destroy();
    }
  }

  static async testAllMigrations(): Promise<void> {
    const dataSource = new DataSource({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'postgres',
      password: 'postgres',
      database: 'myapp_migration_test',
      entities: ['src/**/*.entity{.ts,.js}'],
      migrations: ['src/migrations/*{.ts,.js}'],
      synchronize: false,
      dropSchema: true,
    });

    await dataSource.initialize();

    try {
      // Run all migrations
      const migrations = await dataSource.runMigrations();
      console.log(`Ran ${migrations.length} migrations`);

      // Verify schema
      const tables = await dataSource.query(`
        SELECT table_name FROM information_schema.tables
        WHERE table_schema = 'public'
      `);
      console.log('Tables:', tables);

      // Rollback all migrations
      while (true) {
        try {
          await dataSource.undoLastMigration();
        } catch {
          break;
        }
      }
    } finally {
      await dataSource.destroy();
    }
  }
}

// Migration test
describe('User Migration', () => {
  it('should create users table', async () => {
    await MigrationTestService.testMigration(CreateUsersTable1700000001);

    // Verify table exists
    const dataSource = await getTestDataSource();
    const result = await dataSource.query(`
      SELECT EXISTS (
        SELECT FROM information_schema.tables
        WHERE table_name = 'users'
      );
    `);

    expect(result[0].exists).toBe(true);
  });
});

45.7 Test Database Utilities

Database Helper Functions

import { DataSource, EntityMetadata } from 'typeorm';

export async function truncateAllTables(dataSource: DataSource): Promise<void> {
  const entities = dataSource.entityMetadatas;

  await dataSource.query('SET session_replication_role = replica');  // Disable FK

  for (const entity of entities) {
    await dataSource.query(`TRUNCATE "${entity.tableName}" CASCADE`);
  }

  await dataSource.query('SET session_replication_role = DEFAULT');  // Re-enable FK
}

export async function resetSequences(dataSource: DataSource): Promise<void> {
  const entities = dataSource.entityMetadatas;

  for (const entity of entities) {
    const tableName = entity.tableName;
    const idColumn = entity.primaryColumns[0];

    if (idColumn) {
      await dataSource.query(`
        SELECT setval(
          pg_get_serial_sequence('"${tableName}"', '${idColumn.databaseName}'),
          1,
          false
        )
      `);
    }
  }
}

export async function getTableCount(
  dataSource: DataSource,
  tableName: string,
): Promise<number> {
  const result = await dataSource.query(`
    SELECT COUNT(*) FROM "${tableName}"
  `);
  return parseInt(result[0].count, 10);
}

export async function tableExists(
  dataSource: DataSource,
  tableName: string,
): Promise<boolean> {
  const result = await dataSource.query(`
    SELECT EXISTS (
      SELECT FROM information_schema.tables
      WHERE table_name = $1
    );
  `, [tableName]);

  return result[0].exists;
}

export async function columnExists(
  dataSource: DataSource,
  tableName: string,
  columnName: string,
): Promise<boolean> {
  const result = await dataSource.query(`
    SELECT EXISTS (
      SELECT FROM information_schema.columns
      WHERE table_name = $1 AND column_name = $2
    );
  `, [tableName, columnName]);

  return result[0].exists;
}

45.8 CI/CD Database Configuration

GitHub Actions Configuration

name: Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: myapp_test
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

      redis:
        image: redis:7
        ports:
          - 6379:6379

    steps:
      - uses: actions/checkout@v4

      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: '20'
          cache: 'npm'

      - name: Install dependencies
        run: npm ci

      - name: Run unit tests
        run: npm run test

      - name: Run integration tests
        run: npm run test:integration
        env:
          DB_HOST: localhost
          DB_PORT: 5432
          DB_USERNAME: postgres
          DB_PASSWORD: postgres
          DB_DATABASE: myapp_test

      - name: Run e2e tests
        run: npm run test:e2e
        env:
          DB_HOST: localhost
          DB_PORT: 5432
          DB_USERNAME: postgres
          DB_PASSWORD: postgres
          DB_DATABASE: myapp_e2e_test

      - name: Upload coverage
        uses: codecov/codecov-action@v3
        with:
          files: ./coverage/lcov.info

45.9 Summary

                    Test Database Management Quick Reference
    +------------------------------------------------------------------+
    |                                                                   |
    |    Strategy          | Use Case                                   |
    |    -------------------|------------------------------------------|
    |    Truncate         | Fast cleanup between tests                |
    |    Transaction      | Automatic rollback                        |
    |    Separate DB      | Parallel test execution                   |
    |    Synchronize      | Auto-create schema                        |
    |                                                                   |
    |    Configuration     | Description                              |
    |    -------------------|------------------------------------------|
    |    .env.test        | Test environment variables                |
    |    synchronize:true | Create schema automatically               |
    |    dropSchema:true  | Clean database on start                   |
    |    logging:false    | Disable query logging                     |
    |                                                                   |
    |    Best Practices    | Description                              |
    |    -------------------|------------------------------------------|
    |    Isolated tests   | Each test has clean state                 |
    |    Fast cleanup     | Use truncate or transactions              |
    |    CI services      | Use containerized databases               |
    |    Parallel support | Separate databases for parallel           |
    |                                                                   |
    +------------------------------------------------------------------+

Next Chapter

Chapter 46: Blog API Project

Last Updated: February 2026