Database

The underlying data store of CARE is a relational database. The PostgresSQL database is managed and accessed via Sequelize. The backend services and sockets of CARE access the database via predefined methods provided in with the MetaModel class in backend/db directory, which in turn utilize Sequelize as an layer of abstraction to query and update the database.

The database schema is modified via Sequelize’s migration system. make init adds any new migrations to the database. Modifying the database schema therefore means adding a new migration and potentially a new Sequelize model.

All relevant files can be found in the backend/db folder.

Models

Models are defined in the models folder. They are defined using the Sequelize ORM.

The following entity relationship model (ERM) shows the relations between the different models:

Additionally, the following attributes are added to each table:

• deleted: Boolean indicating whether the entry is deleted or not.

• createdAt: The time when the entry was created.

• updatedAt: The time when the entry was last updated.

• deletedAt: The time when the entry was deleted.

Migrations

Working with migrations is done using the Sequelize CLI.

To install the CLI, run npm install --save-dev sequelize-cli inside of the db folder. After that, you can run npx sequelize --help to see all available commands.

To create a new migration, run npx sequelize migration:generate --name <name>. This will create a new file in the migrations folder, containing a up and a down function.

Adding a New Model

1. Use the CLI to create a model and a migration file.

npx sequelize migration:generate --name <name>-nav

2. In the newly generated migration file under migrations change the table name. It should be a singular lower-case term.

3. Now you need to add a new model file for this table. Create a new file in backend/db/models that exports a function for generating the model as an instance of a MetaModel from a given Sequelize object. This looks as follows for a table called simpletable (the name introduced in 2.):

'use strict';
const MetaModel = require("../MetaModel.js");

module.exports = (sequelize, DataTypes) => {
    class SimpleTable extends MetaModel {
        static associate(models) {
        }
    }

    SimpleTable.init({
        updatedAt: DataTypes.DATE,
        deleted: DataTypes.BOOLEAN,
        deletedAt: DataTypes.DATE,
        createdAt: DataTypes.DATE
    }, {
        sequelize: sequelize,
        modelName: 'simpletable',
        tableName: 'simpletable'
    });
    return SimpleTable;
};

4. Add the foreign keys and constraints to the migration file as desired. For examples check out the other migrations. For a very simple table simpletable this would look like this:

module.exports = {
async up(queryInterface, Sequelize) {
    await queryInterface.createTable('simpletable', {
        id: {
            allowNull: false, autoIncrement: true, primaryKey: true, type: Sequelize.INTEGER
        },
        deleted: {
            type: Sequelize.BOOLEAN, defaultValue: false
        },
        createdAt: {
            allowNull: false, type: Sequelize.DATE
        },
        updatedAt: {
            allowNull: false, type: Sequelize.DATE
        },
        deletedAt: {
            allowNull: true, defaultValue: null, type: Sequelize.DATE
        }
    });
}, async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('simpletable');
}

5. If necessary update the model file accordingly (if fields have been changed). Make sure adding additional columns like deleted, createdAt, updatedAt and deletedAt is done in the migration file.

Accessing a Model

The above steps are necessary to create a new migration and associated model. To encapsulate the database interactions, the MetaModel class already contains several convenience functions for accessing the respective table. For instance, getting a single row entry by a provided ID (getById(id)). Accessing the DB via these default functions is as simple as accessing the table model and executing the static method.

// usually, you just access the models loaded in the web server; for completeness we provide the imports here:
const {DataTypes} = require("sequelize")
const db = require("./db/index.js")
const SimpleTable = require("./db/models/simpletable.js")(db.sequelize, DataTypes);

SimpleTable.getById("x");

Note

Generally you should not import the db object yourself and load a model as in the provided example. Instead, the web server object already holds the respective models in a class attribute. Please check out the Sockets for the details.

In case you need more specific functions, you may simply add static access methods to the new model class. For instance:

'use strict';
const MetaModel = require("../MetaModel.js");

module.exports = (sequelize, DataTypes) => {
    class SimpleTable extends MetaModel {
        static associate(models) {
        }

        // new access function specific to this model
        static async getDefaultRow() {
            //do error management -- ommitted for brevity
            return await this.findOne({ where: {'id': "x"}, raw: true});
        }
    }
    //... initialization etc.
};

Populating a Table

To populate a table with basic data rows, you can use seeds to test and debug and then convert it into a regular migration.

1. Create a seeder

npx sequelize-cli seed:generate --name <seeder-name>

2. Adapt the seeder in the respective file under directory “seeders”.

3. Move it to the migrations folder.

4. Double check that you have set the down method accordingly – you don’t want to drop an entire table if you only add a few rows. Delete just these rows.