Skip to main content

Entity Framework

danger

Entity Framework support is still in beta and is not suitable for production databases.

Background​

Entity Framework (EF) is an ORM framework that acts as a wrapper around a database. It allows us to support multiple (non-MSSQL) databases without having to maintain migration and query scripts for each.

Our EF implementations currently support Postgres and mySQL.

Setting up EF databases​

The workflow here is broadly the same as with the normal MSSQL implementation: set up the docker container, configure user secrets, and run the scripts in the scripts folders against their relating databases in chronological order.

Requirements​

  • A working local development server
  • Docker
  • A way to manage user secrets in the server project - see User Secrets Reference
  • Database management software (e.g. pgAdmin4 for Postgres or DBeaver for mySQL)
  • The dotnet cli
  • The dotnet cli Entity Framework Core tool

You can have multiple databases configured and switch between them by changing the value of the globalSettings:databaseProvider user secret. You don’t have to delete your connection strings.

Postgres​

  1. In the dev folder of your server repository, run:

    docker compose --profile postgres up

  2. Add the following values to your API and Identity user secrets, changing information like root password as needed. If you already have these secrets, make sure you update the existing values instead of creating new ones:

    "globalSettings:databaseProvider": "postgres",
    "globalSettings:postgreSql:connectionString": "Host=localhost;Username=postgres;Password=example;Database=vault_dev;Include Error Detail=true",

  3. In the dev folder run the following to update the database to the latest migration:

    pwsh migrate.ps1 -postgres

  4. Optional: to verify that everything worked correctly:

    • Check the database tables to make sure everything has been created
    • Run the integration tests from the root of your server project using dotnet test. Note: this requires a configured MSSQL database. You may also need to also set up a MySql database for the tests to pass

MySql​

If you are using an M1 machine, you need to add platform: linux/amd64 tag to the mysql service on the docker-compose.yml file.

  1. In the dev folder of your server repository, run:

    docker compose --profile mysql up

  2. Add the following values to your API and Identity user secrets, changing information like root password as needed. If you already have these secrets, make sure you update the existing values instead of creating new ones:

    "globalSettings:databaseProvider": "mysql",
    "globalSettings:mySql:connectionString": "server=localhost;uid=root;pwd=example;database=vault_dev",

  3. In the dev folder run the following to update the database to the latest migration:

    pwsh migrate.ps1 -mysql
    note

    pwsh migrate.ps1 -all will run migrations for all database providers

  4. Optional: to verify that everything worked correctly:

    • Check the database tables to make sure everything has been created
    • Run the integration tests from the root of your server project using dotnet test. Note: this requires a configured MSSQL database. You may also need to also set up a Postgres database for the tests to pass

Generating EF Migrations​

If you alter the database schema, you must create an EF migration script to ensure that EF databases keep pace with these changes. Developers must do this and include the migrations with their PR.

Instructions​

  1. Update your data model in Core/Entities as desired

  2. Ensure your user secrets in API have the correct provider configured in the databaseProvider field. The value should match the provider you are generating migrations for.

  3. Open a terminal in the root of the project you want to generate a migration for (either util/PostgresMigrations or util/MySqlMigrations)

  4. Generate the migration. This should have the same name as the corresponding MSSQL migration (except for the date, which the the tool will prepend automatically):

    dotnet ef migrations add [NAME_OF_MIGRATION]

  5. Generate the migration SQL script:

    dotnet ef migrations script [LATEST_PREVIOUS_MIGRATION]

  6. Run the SQL script against your database

  7. Save the script output in the Scripts folder