Skip to content

README_POSTGRES.md

Latest commit

 

History

History
146 lines (109 loc) · 4.82 KB

README_POSTGRES.md

File metadata and controls

146 lines (109 loc) · 4.82 KB
Raw

PostgreSQL Integration for JobTracker

This document describes the PostgreSQL integration setup for the JobTracker application.

Overview

The JobTracker application has been updated to support PostgreSQL as the primary database for development and production environments, while maintaining backward compatibility with in-memory databases for testing.

Components Added

1. Docker Compose Setup

  • File: docker-compose.yml
  • Purpose: Provides a PostgreSQL database container for local development
  • Database: PostgreSQL 15 Alpine
  • Connection Details:
    • Host: localhost
    • Port: 5432
    • Database: jobtracker
    • Username: jobtracker
    • Password: jobtracker_dev_password

2. Entity Framework Configuration

  • Package Added: Npgsql.EntityFrameworkCore.PostgreSQL (version 8.0.10)
  • Configuration: Updated Program.cs to use PostgreSQL for non-testing environments
  • Environment Detection: Automatically switches to in-memory database when environment is "Testing"

3. Connection String Management

  • Configuration: Added to appsettings.json
  • Connection String: Host=localhost;Database=jobtracker;Username=jobtracker;Password=jobtracker_dev_password

4. Test Infrastructure Enhancement

  • Package Added: Testcontainers.PostgreSql (version 3.10.0)
  • Fallback Mechanism: Tests use PostgreSQL test containers when Docker is available, otherwise fall back to in-memory database
  • Isolation: Each test class gets its own PostgreSQL container instance when using Testcontainers

Usage

Development Environment

  1. Start PostgreSQL Database:

    docker-compose up -d

  2. Run the Application:

    dotnet run --project JobTracker

The application will automatically connect to the PostgreSQL database running in Docker.

Testing

Tests automatically detect Docker availability:

  • With Docker: Uses PostgreSQL test containers for full integration testing
  • Without Docker: Falls back to in-memory database for unit testing

Run tests:

dotnet test

Production

Update the connection string in production environment configuration:

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=your-postgres-host;Database=jobtracker;Username=your-username;Password=your-password"
  }
}

Database Configuration

Environment Detection Logic

The application uses environment-based database selection:

if (builder.Environment.IsEnvironment("Testing"))
{
    // Use InMemory database for testing
    options.UseInMemoryDatabase("JobTracker");
}
else
{
    // Use PostgreSQL for development and production
    var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
    options.UseNpgsql(connectionString);
}

Database Initialization

The application uses EnsureCreated() for development simplicity. For production environments, consider using Entity Framework migrations:

dotnet ef migrations add InitialCreate
dotnet ef database update

Package Dependencies

Main Application (JobTracker.csproj)

  • Microsoft.EntityFrameworkCore (8.0.10)
  • Microsoft.EntityFrameworkCore.InMemory (8.0.10)
  • Microsoft.EntityFrameworkCore.Relational (8.0.10)
  • Microsoft.EntityFrameworkCore.Design (8.0.10)
  • Npgsql.EntityFrameworkCore.PostgreSQL (8.0.10)

Test Project (JobTracker.Tests.csproj)

  • Microsoft.EntityFrameworkCore.InMemory (8.0.10)
  • Npgsql.EntityFrameworkCore.PostgreSQL (8.0.10)
  • Testcontainers.PostgreSql (3.10.0)

Benefits

  1. Production-Ready: PostgreSQL provides robust data persistence and scalability
  2. Development Consistency: Same database technology used across all environments
  3. Test Reliability: Tests run against actual PostgreSQL when available, ensuring database-specific behavior is tested
  4. Flexibility: Graceful fallback to in-memory database when Docker is unavailable
  5. Isolation: Each test gets a fresh database instance, preventing test interference

Troubleshooting

Docker Not Available

If Docker is not running or available, tests will automatically fall back to in-memory database. This ensures the test suite can run in any environment.

Connection Issues

  1. Verify PostgreSQL container is running: docker ps
  2. Check container logs: docker logs jobtracker-postgres
  3. Verify connection string in appsettings.json

Test Failures

  1. Ensure sufficient disk space for test containers
  2. Check Docker daemon is running
  3. Verify no port conflicts (port 5432)

Future Enhancements

  1. Migrations: Implement Entity Framework migrations for production deployments
  2. Connection Pooling: Configure connection pooling for high-load scenarios
  3. Read Replicas: Support for read replica databases for scaling read operations
  4. Backup Strategy: Implement automated backup procedures for production