Skip to content

0014-configurable-database-provider.md

Latest commit

 

History

History
87 lines (57 loc) · 4.31 KB

0014-configurable-database-provider.md

File metadata and controls

87 lines (57 loc) · 4.31 KB

0014. Configurable Database Provider

Date: 2026-05-02

Status

Accepted — supersedes ADR-0003

Context

The project used SQLite as its only database engine (ADR-0003). This meant all environments — local development, Docker Compose, and any deployment — ran the same SQLite file-based database. For the majority of contributors this is ideal: zero infrastructure required.

However, a fixed SQLite-only setup has limits:

  • SQLite does not support concurrent writes, so multi-instance deployments are not possible.
  • Developers who want to validate production-parity behaviour (e.g. PostgreSQL-specific query plans, type semantics, or constraint handling) have no path to do so without modifying the project.
  • A fixed SQLite-in-dev / PostgreSQL-in-prod split (a common alternative) introduces a different problem: subtle behavioral differences between environments that are hard to catch locally.

The goal is to make the database engine fully configurable with a single environment variable, so the same stack is used consistently across every environment a developer chooses to run.

Decision

We will introduce a DATABASE_PROVIDER environment variable that selects the database engine at startup:

  • DATABASE_PROVIDER=sqlite (default): SQLite everywhere. Zero infrastructure required. Works on any machine without Docker. Clone and run.
  • DATABASE_PROVIDER=postgres: PostgreSQL everywhere. Requires Docker. Opt-in for developers who want a server-based engine or full production parity.

The default is sqlite to keep the barrier to entry as low as possible.

Provider selection at startup

ServiceCollectionExtensions.AddDbContextPool reads DATABASE_PROVIDER and wires the appropriate EF Core provider:

switch (provider.ToLowerInvariant())
{
    case "postgres":
        options.UseNpgsql(Environment.GetEnvironmentVariable("DATABASE_URL"));
        break;
    default:
        options.UseSqlite($"Data Source={dataSource}");
        break;
}

Provider-specific EF Core migrations

SQLite and PostgreSQL require different column type mappings (TEXT/INTEGER vs uuid/boolean/timestamp with time zone). A single migration set cannot satisfy both providers. To resolve this:

  • SQLite migrations remain in Migrations/ (namespace ...Migrations).
  • PostgreSQL migrations are placed in Migrations/Npgsql/ (namespace ...Migrations.Npgsql).
  • A custom ProviderSpecificMigrationsAssembly (implementing EF Core's IMigrationsAssembly) filters the discovered migrations to only those in the active provider's namespace. It is registered via options.ReplaceService<IMigrationsAssembly, ProviderSpecificMigrationsAssembly>().

MigrateAsync() at startup continues to work for both providers; each sees only its own migration set.

Docker Compose profiles

The postgres service is declared under the postgres Compose profile so it only starts when explicitly requested:

# SQLite (default — no extra Docker service)
docker compose up

# PostgreSQL (opt-in)
DATABASE_PROVIDER=postgres docker compose --profile postgres up

Consequences

Positive

  • Developers choose their engine once and use it consistently across all environments.
  • SQLite remains the zero-friction default — clone, run, done.
  • PostgreSQL is a first-class option for production-parity testing without requiring project-wide changes.
  • The Compose profile approach prevents accidental PostgreSQL containers from starting in SQLite mode.
  • dotnet run continues to work unchanged with SQLite.

Negative

  • Two parallel migration sets must be maintained. Adding a schema change requires migrations in both Migrations/ and Migrations/Npgsql/.
  • ProviderSpecificMigrationsAssembly uses an EF Core internal API (MigrationsAssembly from Microsoft.EntityFrameworkCore.Migrations.Internal), annotated with #pragma warning disable EF1001. This may require updates on major EF Core version upgrades.
  • PostgreSQL support is not tested in CI (no Testcontainers integration yet — tracked in issue #353).

Neutral

  • The DATABASE_URL connection string format follows the Npgsql convention (Host=...;Database=...;Username=...;Password=...).
  • .env.example documents all three new environment variables (DATABASE_PROVIDER, DATABASE_URL, POSTGRES_PASSWORD). .env is git-ignored.