Schema style guide

In order to keep consistency in how our database tables and columns are named, this document contains rules that should be followed when creating new models or fields.

On the database

This section deals with schema naming on the database side.

Note: Since database seeders and migrations deal directly with the database, those files should be written using these rules.

Tables

Tables are named in snake case, meaning that the name of each table should be lowercase with underscores separating any words in the model name. Table names should be singular, not plural.

Acceptable: user, course, assignment_info

Unacceptable: Users, courses, AssignmentInfo

Columns

Columns are names similarly to tables (in snake case).

Acceptable: id, name, open_date

Unacceptable: ID, Name, openDate

Relations

Relations follow the naming convention of <related_table>_id.

Examples: course_id, grade_id, user_id

On the backend

This section deals with style rules that are applied to backend code, like model files.

Models

Models are named in camel case, starting with an uppercase letter. Model names are always singular.

Acceptable: User, Course, AssignementInfo

Unacceptable: users, Courses, assignment_info

Model fields (attributes)

Model fields are named in snake case. In most (all) cases, the model field name should be the same in the database schema as it is on the model.

Acceptable: id, name, open_date

Unacceptable: ID, Name, openDate

Backend model design information

Model files

We use a couple of options in Sequelize to keep our models compliant style guide. Here's an example of a model that conforms to our style:

module.exports = (sequelize, DataTypes) => {
  var Institute = sequelize.define('Institute', {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: {
        notEmpty: true
      }
    },
    created_at: {
      type: DataTypes.DATE,
      allowNull: false
    },
    updated_at: {
      type: DataTypes.DATE,
      allowNull: false
    },
    deleted_at: {
      type: DataTypes.DATE
    }
  }, {
    classMethods: {
      associate: function(models) {
        // associations can be defined here
      }
    },
    paranoid: true,
    underscored: true,
    freezeTableName: true,
    tableName: 'institute'
  });
  return Institute;
};

There are a couple of things to note in this model definition:

• We use the paranoid: true option in Sequelize. This means that objects deleted from our database do not get wholly deleted, but rather the deleted_at field is set, which hides the object from the backend. This is in essence a form of "insurance policy" so that accidentally-deleted data can be restored without much effort.
• The options underscored, freezeTableName should be set as they are above.
• The tableName option should be set to the name of the table in the database, which should follow the naming conventions in the "Tables" section above.
• Sequelize automatically updates the created_at time whenever the object is created, as well as the updated_at time whenever the object is updated.

Migration files

When creating, changing, or deleting models, a migration file is necessary provide the backend with information on how to alter the database schema to match the models. Here's the initial migration that creates the Institute model shown above:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('institute', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true,
        validate: {
          notEmpty: true
        }
      },
      created_at: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updated_at: {
        allowNull: false,
        type: Sequelize.DATE
      },
      deleted_at: {
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('institute');
  }
};

As you can see, the table names in the migration above match the tableName in the model file, and they comply with the "tables" section above.