# SOR Database Migrations SOR uses **EF Core 8** with **6 independent DbContexts**, each owning a separate PostgreSQL schema. All migrations live in `Server/src/SOR.Database/Migrations//`. --- ## Prerequisites ```bash # Install the dotnet-ef global tool (once per machine) dotnet tool install --global dotnet-ef # Verify dotnet ef --version # must be >= 8.x ``` --- ## Contexts and Schemas | Context | Schema | Migration folder | |------------------------|--------------|-------------------------------------| | `SorIdentityDbContext` | `identity` | `Migrations/Identity/` | | `CatalogDbContext` | `catalog` | `Migrations/Catalog/` | | `CollectionDbContext` | `collection` | `Migrations/Collection/` | | `BazaarDbContext` | `bazaar` | `Migrations/Bazaar/` | | `ChatDbContext` | `chat` | `Migrations/Chat/` | | `GameDbContext` | `game` | `Migrations/Game/` | All contexts share the same PostgreSQL database (`SOR`) but are completely isolated by schema. --- ## Applying Migrations to a Remote Server Run from `Server/` directory. The `--connection` flag overrides whatever is in appsettings.json and is the correct way to target a specific environment. ```bash cd Server CONN="Host=;Port=5432;Database=SOR;Username=;Password=" # Apply all 6 contexts (run in order — schemas are independent, no cross-schema FKs) dotnet ef database update --project src/SOR.Database --context SorIdentityDbContext --connection "$CONN" --no-build dotnet ef database update --project src/SOR.Database --context CatalogDbContext --connection "$CONN" --no-build dotnet ef database update --project src/SOR.Database --context CollectionDbContext --connection "$CONN" --no-build dotnet ef database update --project src/SOR.Database --context BazaarDbContext --connection "$CONN" --no-build dotnet ef database update --project src/SOR.Database --context ChatDbContext --connection "$CONN" --no-build dotnet ef database update --project src/SOR.Database --context GameDbContext --connection "$CONN" --no-build ``` > **Note:** `--no-build` skips rebuilding and uses already-compiled binaries in `bin/`. > Omit it (or run `dotnet build` first) if you just changed migration files. ### pg-forge-01 (production) ```bash HOST=172.10.1.127 USER=SOR-DBA # password is stored in sor-app-secrets k8s Secret (Cdn__AccessKey isn't it — see deploy/secrets/) ``` --- ## Checking Migration Status ```bash CONN="Host=;..." # Lists all migrations; any marked (Pending) need to be applied dotnet ef migrations list --project src/SOR.Database --context SorIdentityDbContext --connection "$CONN" --no-build dotnet ef migrations list --project src/SOR.Database --context CatalogDbContext --connection "$CONN" --no-build dotnet ef migrations list --project src/SOR.Database --context CollectionDbContext --connection "$CONN" --no-build dotnet ef migrations list --project src/SOR.Database --context BazaarDbContext --connection "$CONN" --no-build dotnet ef migrations list --project src/SOR.Database --context ChatDbContext --connection "$CONN" --no-build dotnet ef migrations list --project src/SOR.Database --context GameDbContext --connection "$CONN" --no-build ``` --- ## Adding a New Migration ```bash cd Server # 1. Make your model changes in SOR.Database/Entities/ or Contexts/ # 2. Add the migration (pick the right context) dotnet ef migrations add \ --project src/SOR.Database \ --context \ --output-dir Migrations/ # Example: dotnet ef migrations add AddCardFoilIndex \ --project src/SOR.Database \ --context CollectionDbContext \ --output-dir Migrations/Collection # 3. Review the generated Up()/Down() in Migrations//_.cs # Make sure it only contains the delta — NOT a full recreation of existing tables. # 4. Apply to target DB dotnet ef database update --project src/SOR.Database --context CollectionDbContext --connection "$CONN" ``` > **Design-time factories** (`DesignTimeFactories.cs` in `SOR.Database`) allow the EF tools > to instantiate contexts without starting the full ASP.NET Core application. > They read the connection string from `ConnectionStrings__Default` env var, > falling back to `localhost` for local dev. --- ## How EF Tracks Applied Migrations Each schema gets its own `__EFMigrationsHistory` table: ``` identity.__EFMigrationsHistory catalog.__EFMigrationsHistory collection.__EFMigrationsHistory bazaar.__EFMigrationsHistory chat.__EFMigrationsHistory game.__EFMigrationsHistory ``` EF Core compares these tables to the migration files to determine what's pending. --- ## Known Migration History ### pg-forge-01 — last applied (2026-02-27) | Context | Last migration applied | |-----------------|-------------------------------------------------| | Identity | `20260226111950_AddRankScore` | | Catalog | `20260226012910_AddDefinitionVersionAndRetired` | | Collection | `20260225231700_AddPlayerCosmetics` | | Bazaar | `20260225231658_AddBazaarCosmetics` | | Chat | `20260226012908_AddPresenceAndNotifications` | | Game | `20260226162145_AddBoardDimensions` | ### Notes - `GameDbContext/20260226125446_AddGameRulesAndCustomLobby` is intentionally empty. It was regenerated with a stale snapshot that duplicated InitialCreate tables. The actual `game_rule_sets` and `custom_lobbies` tables were added in the following `AddBoardDimensions` migration.