6. Update Database models

The classes stored in the Models directory are the skeleton for the database. We use something called Code First to define our database structure in code relationships and then generate the database modifications automatically.

Changing or adding a Model

If you need to change a Model (adding a new field for example) or create a new one it is as simple as creating a new class in the Models directory. If you are adding a new Model you need to add the supporting classes (the repositories) as well and add the correct bindings that make them a new table. Take a look at how the others are connected, it should only be about two lines of code in ApplicationDbContext to add it as a table and then in Startup to add the repository to the dependency injector.

After the code changes you have to generate the database update code, the so called Migration. A migration is a small script that transforms the database from the old state to the new to match what you added or removed in the code. For example, if you added a field to a Model, the transformation will generate the necessary SQL to alter the table to include the new column. Migrations can also be reverted since they contain the information to undo the changes. To create a new migration for your changes, run the following command:

dotnet ef migrations add <a descriptive name for your changes>

After the migration is created you can take a look at the generated code to see if it is correct or if you need to make some changes (to the Model, don't edit the generated migration). The migration is applied on development server startup but if you want to apply it manually you can run:

dotnet ef database update

Create backup of the db

To create a backup dump of the Postgres database, run the following shell command:

docker exec -t name_of_db_container pg_dumpall -c -U nexpo > name_of_dump.sql

NOTE: Store the dump file on a secure and private instance as it contains sensitive data about the content of the db.

Restore db from backup

To restore the db from a backup dump, run the following shell command:

cat name_of_dump.sql | docker exec -i name_of_db_container psql -U nexpo

Generate migration-script

To generate a script that applies generated migrations to the db, run the following shell command:

dotnet ef migrations script --idempotent > name_of_migration.sql

NOTE: Remove the two first lines in the generated script as they are simply just output from the build-process.

Apply migration to db

To apply the migration to the production db, use the generated script and run the following shell command:

cat name_of_migration.sql | docker exec -i name_of_db_container psql -U nexpo