The API uses a Entity Framework Core as the ORM to communicate with the data-source. Currently it is configured and coded to use an MS-SQL database.

• Database Documentation
• Entity Framework Core
• Entity Provider - MS-SQL
• Docker Hub - MS-SQL

When the API starts it will attempt to setup and configure the database based on the connection string set above, if the environment is not Production (i.e. Development).

It is possible with some changes to use a different type of database.

Refer to the CLI documentation here.

The database is setup and configured through Entity Framework Code-First processes. To use the Entity Framework CLI you will need to:

• Install the .NET SDK version (download links below),

• Install dotnet-ef tool and,

• Optionally, add a connectionstrings.json configuration file in the /backend/dal folder

• Optionally, the connection string can be provided in a .env file in the same folder with the format:

  ConnectionStrings__PIMS=Server=<localhost or host.docker.internal>,<port>;User ID=sa;Database=<database name>
  

NOTES

• All dotnet ef commands must be run from the /backend/dal directory.
• Please do not commit the connectionstrings.json file to source code. It is likely to contain secret information that should not be shared. By default .gitignore will exclude it.
• To help dotnet ef find the correct database connection, you will need to select one of the following configuration options, but no single one is required:
  1. connectionstrings.json
  2. connectionstrings.Environment.json. For example, connectionstrings.Development.json
  3. .env file
  4. Environment variables

download .NET Core SDK - 3.0

download .NET Core SDK - 3.1

dotnet tool install --global dotnet-ef

You may create a connectionstrings.json configuration file within the /backend/dal project, or a .env file to contain this information. You can also create one for each environment by creating a file with the naming convention connectionstrings.Environment.json.

Enter the following information into the file;

{
  "ConnectionStrings": {
    "PIMS": "Server=<localhost or host.docker.internal>,<port>;User ID=sa;Database=<database name>"
  }
}

The default port for MS-SQL is 1433, but set it to the same value used in the docker-compose.yaml configuration file. The database name should be the same value used in the database .env file.

dotnet ef must be installed as a global or local tool. Most developers will install dotnet ef as a global tool with the following command:

Use bash, CMD.exe or PowerShell for specific version:

dotnet tool update --global dotnet-ef --version 3.1.0

or for latest version use (works also for reinstallation):

dotnet tool update --global dotnet-ef

Set the environment path so that the tool is executable.

For Linux and macOS, add a line to your shell's configuration:

export PATH="$PATH:$HOME/.dotnet/tools/"

For Windows:

You need to add %USERPROFILE%\.dotnet\tools to the PATH.

Make sure you have a properly configured connectionstrings.json or .env file in the /backend/dalfolder.

To kill your database and start over;

dotnet ef database drop --force
dotnet ef database update

The following example creates a SQL script for the Initial migration;

dotnet ef migrations script 0 Initial

IMPORTANT - In order for initial seeding and post migration SQL scripts to run, the first migration must be named Initial (case-sensitive)

The following example creates SQL scripts for all migrations after the Initial migration;

dotnet ef migrations script 20180904195021_Initial

To add a new database code migration do the following;

In your command terminal, cd to the root folder and type the following where [name] is the name of the migration or version number that you want to create:

make db-add n=[name]

This command will then create 2 new files, a "[name].Designer.cs" and a "[name].cs" as well as a modified "PimsContextModelSnapshot.cs" file. You can most likely discard the changes to the modified "PimsContextModelSnapshot.cs" file, unless you had made some structural changes to one of the models of the database which would be reflected in that file.

Then add the appropriate folders for this migration to place your SQL scripts, by using the following folder structure where {name} is the same "version number" you had used in the previous step:

mkdir backend/dal/Migrations/{name}/Up/PostUp
mkdir backend/dal/Migrations/{name}/Down/PostDown

-**Note: the folders Up/PostUp & Down/PostDown are case sensitive and need to be in "Pascal Case" format.**

Finally, add your SQL script for Up (upgrade) and Down (for rollback scripts if needed). If the need to revert the database to the previous state is not necessary such as updating records that contained incorrect data, then there is no need to create the "Down/PostDown" folders. The naming convention for your SQL scripts generally start with 01-{TableName.sql} as a database migration may contain more than one script and will run in sequential order.

-Please see /backend/dal/Migrations for the history of all migrations done or you can look in the database table called "__EFMigrationsHistory". 
-If your "__EFMigrationsHistory" database table is not up to date with the latest migration version/s just run "make db-update".

You can then test your "migration" by running the command:

make db-update

"make db-update" will run any migration/s on your local machine if it detects that the table "__EFMigrationsHistory" is out of date and does not contain the "version number" or name of the migration that you had created.

In the case you need to rollback your migration because it didn't work you can use this command:

make db-rollback n={name or version number} to rollback your database to a specific version

-**Note: All these make db commands actually call a dotnet ef database specific function which you can also see in the main Makefile in the root PIMS directory. 

Also for reference is the CLI documentation here

On a side note, the auto-generated file "[name].cs" will enable running SQL scripts during migration, for either complex database changes, or seed data.

using System;
using Microsoft.EntityFrameworkCore.Migrations;
// Add this using statement.
using Pims.Api.Helpers.Migrations;

namespace Pims.Api.Migrations
{
    // Inherit from SeedMigration
    public partial class Initial : SeedMigration
    {
        protected override void Up (MigrationBuilder migrationBuilder)
        {
            // Add the PreDeploy action.
            PreDeploy (migrationBuilder);
            ...
            // If required for complex db changes you can add additional ScriptDeploy(...).
            // ScriptDeploy("{this.DefaultMigrationsPath}/{this.Version}/some/path");
            ...
            // Add the PostDeploy action.
            PostDeploy (migrationBuilder);
        }
    }
}

Any SQL scripts that you want to run as part of a migration must be additionally included as build content in the project file.

• Edit the Pims.Dal.csproj file and;
• Add the <Content> location with a <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory> (see below example).

NOTE - By default all *.sql files within the /backend/dal/Migrations folder will be included in the project as Content.

<?xml version="1.0" encoding="utf-8"?>
<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>
  ...
  <ItemGroup>
    <Content Include="Migrations\**\*.sql">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>
  </ItemGroup>
</Project>