Schema style guide - GatechVIP/gatech-swapr-provisioning GitHub Wiki
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 thedeleted_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 theupdated_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.