academies-api

Simple api for getting data from the central Academies Database datastore.

NB: Historically this API was used to support the operations of the legacy TRAMS application, and references in legacy documentation will at times refer to it as the 'TRAMS API', etc.. This usage is entirely historical, however, and is now deprecated.

Development Setup

You can use Docker compose to start the dependant services for this api.

• Make a copy of .env.database.example and rename it to .env.database
• Make a copy of .env.example and rename it to .env.development
• Start your local database using the Docker command docker compose -f "docker-compose.yml" up -d --build db

Important. Locally running the api via Docker compose requires a secret containing a GitHub PAT token to allow access to the DfE Nuget registry. For this reason it is not recommended anymore and instead you should setup a Nuget package source on your develoment machine and run the api without docker compose.

You can connect to the MSSQL Server on port 1433.

API testing

One of the problems we have is that this project doesn't control the migrations of the SIP database, that means it is really easy for the docker image to go out of date

In the latest v4 endpoints, the decision was made to have a migration that enables us to rebuild the database for just the tables the API uses

V2 and V3 tests will run against the docker image (legacy)

V4 uses the migrations specified in trams-data-api\Dfe.Academies.Api.Infrastructure/Migrations/<SchemaName>

Now we can easily rebuild the environment on each test run and also build an environment for testing, if we need to

The trade off is that it might not be exactly accurate with the SIP database that is on DEV, but we can test that with some smoke tests when we deploy to dev

Likely we might encounter some small differences very occasionally, but the benefit of being able to control a database environment, is too beneficial to worry about this

EntityFramework and Migrations

We currently have a number of database contexts:

trams-data-api

LegacyTramsDbContext is used to manage our models for tables which exist in the sip database and we have no control over - we treat these tables as read-only and don't commit migrations for them. If you do generate migrations for this context, it should not be commited to the repository.

TramsDbContext is the db context for models that we do control, and we can generate migrations for. These migrations will be applied to the database in dev, pre-prod, and prod, and so should be commited to the repository when changes are made to models.

trams-data-api\Dfe.Academies.Api.Infrastructure (local development and testing only)

MstrContext is used to create a database for all tables in the mstr schema

EdperfContext is used to create any updates to the edperf schema that do not exist in the docker image. Right now the majority of the schema exists in the docker image and only the latest changes do not. Since this context does not contain all the tables, it can only be used to update the docker image. In future if we have a clean break like the mstr schema, we could rebuild all objects in the schema.

Generating Migrations

To generate migrations for TramsDbContext, use the following command:

dotnet ef migrations add <MIGRATION_NAME> --project TramsDataApi --context TramsDataApi.DatabaseModels.TramsDbContext -o Migrations/TramsDb

And to generate migrations for LegacyTramsDbContext use:

dotnet ef migrations add <MIGRATION_NAME> --project TramsDataApi --context TramsDataApi.DatabaseModels.LegacyTramsDbContext -o Migrations/LegacyTramsDb

Migrations put into the Migrations/LegacyTramsDb directory will not be committed to git.

Applying Migrations

To apply a set of migrations to the database, use dotnet ef database update:

dotnet ef database update --connection <CONNECTION_STRING> --project TramsDataApi --context TramsDataApi.DatabaseModels.<CONTEXT>

For example, to apply the migrations generated by TramsDbContext to the database Docker image:

dotnet ef database update --connection "Server=localhost,1433;Database=sip;User=sa;Password=Your_password123" --project TramsDataApi --context TramsDataApi.DatabaseModels.TramsDbContext

API Key Management

Api Key management is done through the use of config files. There are currently placeholder entries in the various forms of appsettings.json, but new keys should not be added to these files, or committed to this repository.

For more information on how this decision was reached, please refer to this ADR.

Api Keys are provisioned at the environment level, and are stored as JSON objects in the following format:

{
    "userName": "<the user name>",
    "apiKey": "<the unique api key>"
}

If injected through the environment, use ApiKeys__x naming conventions for the variables, as .NET will automatically configure this for us. e.g. export ApiKeys__0=xxxx will define the first API in the array.

Adding new census data csv file

The current census .csv file can be found a TramsDataApi/CensusData/

If you need to replace this file with a newer one then add the new file to the TramsDataApi/CensusData/ folder and then update the CensusDataGateway.cs file to reflect the new filename.