sequelizescheatsheet.md

Sequelize v5

Init project

In your express project folder

yarn global add sequelize-cli
yarn add sequelize mysql2 dotenv

other available dialect : pg pg-hstore | mariadb | sqlite3 | tedious

sequelize init

Sequelize CLI [Node: 8.11.3, CLI: 5.4.0, ORM: 5.10.1]

Created "config/config.json"
Successfully created models folder at "/Users/eth3rnit3/code/WildCodeSchool/fevrier-2019/P3/test/models".
Successfully created migrations folder at "/Users/eth3rnit3/code/WildCodeSchool/fevrier-2019/P3/test/migrations".
Successfully created seeders folder at "/Users/eth3rnit3/code/WildCodeSchool/fevrier-2019/P3/test/seeders".

Configure project

By default, add your DB credentials into config/config.json

Use env variables for config file

touch .sequelizerc // at the root of project

.sequelizercYou can change sequelize folder if you want

const path = require('path');

module.exports = {
  'config': path.resolve('config', 'config.js'),
  'models-path': path.resolve('models'),
  'seeders-path': path.resolve('seeders'),
  'migrations-path': path.resolve('migrations')
}

config/config.js

require('dotenv').config();

module.exports = {
  development: {
    username: 'database_dev',
    password: 'database_dev',
    database: 'database_dev',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  test: {
    username: 'database_test',
    password: null,
    database: 'database_test',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  production: {
    username: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    host: process.env.DB_HOSTNAME,
    dialect: 'mysql'
  }
};

IF YOU USE JS FILE INSTEND OF JSON, DONT FORGET TO CHANGE THE CONFIG IMPORT IN models/index.js

const config = require(__dirname + '/../config/config.json')[env];
// Replace by
const config = require(__dirname + '/../config/config.js')[env];

Models

Generation

Generate a new model (sequelize model + migration)

Be careful to use commas as attribute separation but no space

sequelize model:generate --name ModelName --attributes field:type
// example
sequelize model:generate --name User --attributes email:string,username:string,password:string,birthdate:date,isAdmin:boolean

Relationship

We will take as an example 3 models.

• Company

• Employee

• Working day

• Employee has one company (belongsTo) 1:1

• Company has many employees (hasMany) 1:n

• Employee has many WorkingDay and WorkingDay has many employees (manyToMany) n:n

To create many to many relationship, we will need a joining table that we will call WorkingDaysEmploye.

sequelize model:generate --name Company --attributes name:STRING
sequelize model:generate --name User --attributes email:STRING,firstName:STRING,lastName:STRING,companyId:INTEGER
sequelize model:generate --name WorkingDay --attributes weekDay:STRING,workingDate:DATE,isWorking:BOOLEAN
sequelize model:generate --name UsersWorkingDay --attributes userId:INTEGER,workingDayId:INTEGER

In User migration file, replace companyId field by

companyId: {
  type: Sequelize.INTEGER,
  allowNull: false,
  references: {         // User belongsTo Company 1:1
    model: 'Companies', // use the table name here NOT MODEL (sequelize  pluralize tables)
    key: 'id'
  }

In UserWorkingDay migration file, replace userId and workingDayId by

userId: {
  type: Sequelize.INTEGER,
  allowNull: false,
  references: {         // User hasMany WorkingDays n:n
    model: 'Users',     // use the table name here NOT MODEL (sequelize  pluralize tables)
    key: 'id'
  }
},
workingDayId: {
  type: Sequelize.INTEGER,
  allowNull: false,
  references: {           // WorkingDays hasMany Users n:n
    model: 'WorkingDays', // use the table name here NOT MODEL (sequelize  pluralize tables)
    key: 'id'
  }
},

Now configure models for activate relationships

models/user.js

User.associate = function(models) {
    User.belongsTo(models.Company, {foreignKey: 'companyId', as: 'company'})
    User.belongsToMany(models.WorkingDay, {through: 'UsersWorkingDays', foreignKey: 'userId', as: 'days'})

};

models/company.js

Company.associate = function(models) {
    Company.hasMany(models.User, {as: 'employees'})
};

models/usersWorkingDay.js

UsersWorkingDay.associate = function(models) {
    UsersWorkingDay.belongsTo(models.User, {foreignKey: 'userId'})
    UsersWorkingDay.belongsTo(models.WorkingDay, {foreignKey: 'workingDayId'})
};

models/workingDay.js

WorkingDay.associate = function(models) {
    WorkingDay.belongsToMany(models.User, {through: 'UsersWorkingDays', foreignKey: 'workingDayId', as: 'employees'})
};

Usage

Now that the relationships are well configured, you can use a whole method state provided by sequelize. Be careful their syntax depends on the type of relationship as well as the alias given to it

Example on an instance of Company

company.setEmployees([1, 2, 3, 4]) // deletes all existing relationships and replaces them with the ids provided in the array
company.addEmployee(8) // Adds the employee with id 8 to existing relationships
company.removeEmployee(8) // Remove the employee with id 8 of relationships
company.getEmployees() // Get all associated employees

• Medium article of sequelize relationships here
• Sequelize documentation of associations here

Modify an existing model

Be careful, when a model is generated, do not modify the migration that creates it.

To do this, it will be necessary to create a new migration that will add, modify or delete columns from the table

Generation

sequelize migration:create --name addFieldToTable

In the generated migration file, there are two keys. up and down

up will be executed after the sequelize db:migrate command, down will be executed in case of sequelize db:migrate:undo

• up must contain the desired change queryInterface.addColumn('Tables', 'column', Sequelize.DATATYPE)
• down must be the opposite of this change queryInterface.removeColumn('Tables', 'column')

Here are some common examples

module.exports = {
  up: (queryInterface, Sequelize) => {
    return Promise.all([
      queryInterface.addColumn('MyTables', 'field1', Sequelize.STRING), // Add field2 with type string to MyTables
      queryInterface.changeColumn('MyTables', 'field2', Sequelize.INTEGER), // Change field2 type to integer
    ]);
  },

  down: (queryInterface, Sequelize) => {
    return Promise.all([
      queryInterface.removeColumn('MyTables', 'field1'), // Remove field2 from MyTables (opposite of up)
      queryInterface.changeColumn('MyTables', 'field2', Sequelize.TEXT), // Reset defautl field2 type
    ]);
  }
};

Conclusion

Even if the documentation of sequelize is not the best in the world, you will find a lot of interesting info in it. for all the rest GOOGLE and STACKOVERFLOW

• http://docs.sequelizejs.com/
• https://stackoverflow.com/search?q=sequelize+5