---
name: database-migrations
description: How to change the data model and add an EF Core migration for the SQLite database, including the namespace, folder and naming fixes the tools need. Use when adding a column, table, index or relationship.
metadata:
  scope: shared
---

# Database migrations

## The rule

The schema comes only from migrations in `src/Infrastructure/Databases/MovieReviews/Migrations`. The app applies pending migrations at startup in `InitialiseInfrastructure()`, and the tests build their databases from the same migrations, so a model change without a migration fails the tests. `MovieReviewsDbContextFactory` lets the EF Core tools create the context at design time.

## Steps

1. Change the database model in `Models/` and its configuration in `Configuration/`. Relationships, keys, indexes and collations are configured with the fluent API in an `EntityConfiguration<T>` subclass. Text people search or sort by uses `UseCollation(Collations.NoCase)`, and columns we sort or filter by get a `HasIndex`.
2. Install the tool once: `dotnet tool install --global dotnet-ef`.
3. Add the migration from the repository root:

   ``` bash
   dotnet ef migrations add <Name> \
     --project ./src/Infrastructure \
     --output-dir Databases/MovieReviews/Migrations \
     --namespace CleanMinimalApi.Infrastructure.Databases.MovieReviews.Migrations
   ```

4. Check where the files landed. If `MovieReviewsDbContextModelSnapshot.cs` was written somewhere else or in another namespace, move it into `Migrations/` and fix its namespace.
5. Strip the byte order mark the tool adds, so `dotnet format` and the diff stay clean, and make sure the migration file starts with `// <auto-generated />` so the analysers skip it. The tool adds that line to the `.Designer.cs` file but not always to the migration itself:

   ``` bash
   sed -i '1s/^\xEF\xBB\xBF//' src/Infrastructure/Databases/MovieReviews/Migrations/*.cs
   ```

6. Rename both files and the `[Migration]` attribute to `<yyyyMMdd>000000_<Name>`, so the id is the day it was written and not the second you ran the tool.

7. Name the migration for what it does (`AddReviewComment`), in PascalCase.
8. Update `MovieReviewsDbContextExtensions.AddData` and the test data if the new column needs a value (`sample-data`).
9. Check nothing is left over with `dotnet ef migrations has-pending-model-changes --project ./src/Infrastructure`, then run every test project. Migrations are excluded from coverage.

## Don't

- Edit a migration that has already been released. Add a new one.
- Call `EnsureCreated()` anywhere. It skips migrations.
- Use `ValueGeneratedOnAdd()` on `DateCreated` or `DateModified`. SQLite then tries to insert NULL into a NOT NULL column; the repository sets both.
- Add data annotations like `[Required]` or `[MaxLength]` to models. Configure in `Configuration/`.

## Check

``` bash
dotnet build
dotnet test
dotnet format --verify-no-changes
```
