TDD and Database Interactions: Testing Data Layer

Introduction

Testing database interactions is a crucial part of Test-Driven Development. This article explores strategies for effectively testing database operations while maintaining test isolation and performance.

Test Database Setup

// database.test.js
describe('Database Tests', () => {
  let db;

  beforeAll(async () => {
    db = await setupTestDatabase();
  });

  afterAll(async () => {
    await db.close();
  });

  beforeEach(async () => {
    await db.clear();
  });

  test('should create and retrieve user', async () => {
    const userData = {
      name: 'John',
      email: 'john@example.com'
    };

    const user = await db.users.create(userData);
    expect(user).toHaveProperty('id');

    const retrieved = await db.users.findById(user.id);
    expect(retrieved).toMatchObject(userData);
  });
});
            

Repository Pattern Testing

// userRepository.test.js
describe('UserRepository', () => {
  let repository;
  let mockDb;

  beforeEach(() => {
    mockDb = {
      query: jest.fn(),
      transaction: jest.fn()
    };
    repository = new UserRepository(mockDb);
  });

  test('should create user', async () => {
    const userData = {
      name: 'John',
      email: 'john@example.com'
    };

    mockDb.query.mockResolvedValue({
      rows: [{ id: 1, ...userData }]
    });

    const user = await repository.create(userData);
    expect(user).toHaveProperty('id', 1);
    expect(mockDb.query).toHaveBeenCalledWith(
      expect.stringContaining('INSERT INTO users'),
      expect.arrayContaining([userData.name, userData.email])
    );
  });

  test('should handle database errors', async () => {
    mockDb.query.mockRejectedValue(
      new Error('Database error')
    );

    await expect(repository.create({
      name: 'John',
      email: 'john@example.com'
    })).rejects.toThrow('Database error');
  });
});
            

Transaction Testing

// orderService.test.js
describe('OrderService', () => {
  test('should create order with items in transaction', async () => {
    const mockDb = {
      transaction: jest.fn().mockImplementation(async (callback) => {
        return callback({
          query: jest.fn().mockResolvedValue({ rows: [] })
        });
      })
    };

    const service = new OrderService(mockDb);
    await service.createOrder({
      items: ['item1', 'item2']
    });

    expect(mockDb.transaction).toHaveBeenCalled();
  });

  test('should rollback on error', async () => {
    const mockDb = {
      transaction: jest.fn().mockImplementation(async (callback) => {
        throw new Error('Transaction failed');
      })
    };

    const service = new OrderService(mockDb);
    await expect(service.createOrder({
      items: ['item1', 'item2']
    })).rejects.toThrow('Transaction failed');
  });
});
            

Migration Testing

// migration.test.js
describe('Database Migrations', () => {
  test('should apply migration', async () => {
    const migration = new AddUserTableMigration();
    await migration.up();

    const result = await db.query(
      'SELECT * FROM users LIMIT 1'
    );
    expect(result.rows).toBeDefined();
  });

  test('should rollback migration', async () => {
    const migration = new AddUserTableMigration();
    await migration.up();
    await migration.down();

    await expect(db.query(
      'SELECT * FROM users LIMIT 1'
    )).rejects.toThrow('relation "users" does not exist');
  });
});
            

Best Practices

• Use test databases for testing
• Clean up test data between tests
• Mock database connections in unit tests
• Test transactions and rollbacks
• Verify database constraints

Conclusion

Testing database interactions requires careful consideration of test isolation, performance, and data integrity. By following these practices, you can create reliable and maintainable database tests.

"Database testing is not just about verifying queries, but about ensuring data integrity and consistency." - Martin Fowler