sql-utilities/testContainers/README.md

testContainers - Database Integration Tests

This directory contains comprehensive integration tests using Testcontainers for testing the SQL utilities against real database instances running in Docker containers.

Overview

The testContainers projects provide true integration tests that:

• Spin up containerized database instances (PostgreSQL, SQL Server)
• Execute QueryBreakdown operations against real databases
• Verify SQL generation and execution across different database dialects
• Test data type handling and database-specific features
• Ensure compatibility with actual database behaviors

Projects

Strata.SqlTools.PostgreSql.TestContainers

Integration tests for PostgreSQL QueryBreakdown functionality using Testcontainers.PostgreSql.

Features Tested:

• Basic SELECT queries (filtering, ordering, pagination)
• JOIN operations (INNER, LEFT, RIGHT)
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
• GROUP BY with HAVING clauses
• PostgreSQL-specific LIMIT/OFFSET syntax
• Double-quoted case-sensitive identifiers
• Data types: SERIAL, VARCHAR, DECIMAL, BOOLEAN, TIMESTAMP
• CTE (Common Table Expressions) via WITH clause
• Parameterized queries with $1, $2, ... positional syntax

Test Files:

• PostgreSqlTestContainerFixture.cs - Base class handling container lifecycle
• PostgreSqlQueryBreakdownIntegrationTests.cs - Integration test cases

Running PostgreSQL Tests:

dotnet test testContainers/Strata.SqlTools.PostgreSql.TestContainers

Strata.SqlTools.SqlServer.TestContainers

Integration tests for SQL Server QueryBreakdown functionality using Testcontainers.MsSql.

Features Tested:

• Basic SELECT queries (filtering, ordering)
• JOIN operations (INNER, LEFT, RIGHT)
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
• GROUP BY with HAVING clauses
• SQL Server TOP/OFFSET FETCH syntax
• Square bracket identifiers for case sensitivity
• Data types: INT, NVARCHAR, DECIMAL, BIT, DATETIME
• CTE (Common Table Expressions) via WITH clause
• Parameterized queries with @parameter syntax

Test Files:

• SqlServerTestContainerFixture.cs - Base class handling container lifecycle
• SqlServerQueryBreakdownIntegrationTests.cs - Integration test cases

Running SQL Server Tests:

dotnet test testContainers/Strata.SqlTools.SqlServer.TestContainers

Requirements

• Docker daemon running locally (required for Testcontainers)
• .NET 8.0 SDK
• 2GB+ free disk space and RAM for running containers

Container Images Used

• PostgreSQL: postgres:16-alpine (lightweight Alpine Linux version)
• SQL Server: mcr.microsoft.com/mssql/server:2022-latest (official Microsoft SQL Server image)

Test Structure

Each test project follows NUnit's test organization pattern:

1. Fixture Base Class - Handles container lifecycle

   • OneTimeSetUp: Creates and starts container
   • OneTimeTearDown: Stops and cleans up container
   • Helper methods for executing queries

2. Test Classes - Organized by feature

   • BasicSELECT tests
   • JOIN tests
   • WHERE clause and parameter tests
   • Aggregate function tests
   • Pagination tests (LIMIT/OFFSET or TOP/FETCH)
   • Data type handling tests
   • Complex query tests
   • Case sensitivity tests

Example Test

[Test]
public async Task QueryBreakdown_SelectActiveUsers_ReturnsActiveOnly()
{
    // Arrange - Create QueryBreakdown with WHERE filter
    var query = new QueryBreakdown("id, name", "users", "active = true");

    // Act - Generate SQL and execute against containerized database
    var sql = query.GetSql();
    var results = await ExecuteQuery(sql);

    // Assert - Verify results match expected behavior
    Assert.That(results.Count, Is.GreaterThan(0));
    Assert.That(results.All(r => (bool)r["active"]), Is.True);
}

Database Schema

Both test projects create the same test schema:

users table:

- id (PRIMARY KEY, auto-increment)
- name (100+ chars)
- email (unique, 100+ chars)
- active (boolean)
- created_at (timestamp)

orders table:

- id (PRIMARY KEY, auto-increment)
- user_id (FOREIGN KEY references users.id)
- order_total (decimal)
- created_at (timestamp)

products table:

- id (PRIMARY KEY, auto-increment)
- name (100+ chars)
- price (decimal)
- in_stock (boolean)

Performance Considerations

• Container startup/teardown is performed once per test class (OneTimeSetUp/OneTimeTearDown)
• Individual test setup/teardown clears only test data (SetUp/ClearTestData)
• Test runs may take 30-60 seconds total depending on container image sizes and system performance

Continuous Integration

When running in CI/CD pipelines:

1. Ensure Docker is available in the CI environment
2. Consider caching Docker images to speed up test execution
3. Monitor disk space as containers can consume significant storage
4. Set timeouts appropriately for container startup (default is usually 60 seconds)

Future Enhancements

• Add Snowflake testcontainer tests (community image or alternative)
• Add MongoDB testcontainer tests for document-based queries
• Add performance benchmarking tests comparing query execution times
• Add stress tests with larger data sets
• Add transaction/rollback scenario tests
• Create fixtures for common test data scenarios (e.g., TpcH benchmark data)
• Add tests for advanced features (window functions, recursive CTEs, etc.)

Troubleshooting

Container fails to start:

• Ensure Docker daemon is running
• Check available disk space and RAM
• Verify firewall rules allow Docker
• Review Docker logs: docker logs <container_id>

Connection timeouts:

• Increase container startup timeout in fixture
• Check Docker resource limits
• Verify network configuration

Permission errors:

• Ensure Docker socket is accessible
• Check user group membership for Docker

Test failures on Mac M1/M2:

• SQL Server image requires specific architecture variants
• Consider using PostgreSQL for primary testing on ARM systems

References

• Testcontainers documentation
• Testcontainers .NET
• PostgreSQL Official Image
• SQL Server Official Image