191 lines
6.2 KiB
Markdown
191 lines
6.2 KiB
Markdown
# testContainers - Database Integration Tests
|
|
|
|
This directory contains comprehensive integration tests using [Testcontainers](https://testcontainers.com/) 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:**
|
|
```bash
|
|
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:**
|
|
```bash
|
|
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
|
|
|
|
```csharp
|
|
[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:**
|
|
```sql
|
|
- id (PRIMARY KEY, auto-increment)
|
|
- name (100+ chars)
|
|
- email (unique, 100+ chars)
|
|
- active (boolean)
|
|
- created_at (timestamp)
|
|
```
|
|
|
|
**orders table:**
|
|
```sql
|
|
- id (PRIMARY KEY, auto-increment)
|
|
- user_id (FOREIGN KEY references users.id)
|
|
- order_total (decimal)
|
|
- created_at (timestamp)
|
|
```
|
|
|
|
**products table:**
|
|
```sql
|
|
- 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](https://testcontainers.com)
|
|
- [Testcontainers .NET](https://github.com/testcontainers/testcontainers-dotnet)
|
|
- [PostgreSQL Official Image](https://hub.docker.com/_/postgres)
|
|
- [SQL Server Official Image](https://hub.docker.com/_/microsoft-mssql-server)
|