Home

Workshop

Install the Sequelize CLI (command line interface) globally

$ npm i -g sequelize-cli

Using the Sequelize CLI

One of the great things about Sequelize is that it comes with a command line interface that generates skeletal models and migrations for us to expand and customize. Sequelize adds some convention to the configuration-heavy Node environment to make our lives much easier.

Start by running sequelize init in your project’s root directory. This will add a bunch of directories and files to your project. For now, we’re interested in the models directory. Open models. You should find an index.js file that looks something like this:

'use strict';

var fs        = require('fs');
var path      = require('path');
var Sequelize = require('sequelize');
var basename  = path.basename(module.filename);
var env       = process.env.NODE_ENV || 'development';
var config    = require(__dirname + '/../config/config.json')[env];
var db        = {};

if (config.use_env_variable) {
  var sequelize = new Sequelize(process.env[config.use_env_variable]);
} else {
  var sequelize = new Sequelize(config.database, config.username, config.password, config);
}

fs
  .readdirSync(__dirname)
  .filter(function(file) {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(function(file) {
    var model = sequelize['import'](path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(function(modelName) {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

This model is the root model that will allow Sequelize to read all the other models you make. It connects your app to the database by way of the lowercase sequelize instance of the Sequelize constructor, which, for now, reads the connection configuration set in the config file, places it in the db object, and exports that connection along with all of your models to be read by other files in your app (module.exports = db) That db variable also includes the Sequelize constructor itself.

Sequelize CLI Part II: Generating Models

We’ve got our index file connecting our connection and our models. Now, to generate models and migrations: on the CLI, type:

$ sequelize model:create --name User --attributes "name:string, email:string, password:string, role:string"

This will create an incomplete User model and an incomplete User migration that we will flesh out. Let’s follow suit with the Company and Product models:

$ sequelize model:create --name Company --attributes "name:string, founded:integer, address:string"

$ sequelize model:create --name Product --attributes "name:string, price:integer, description:string"

And, like magic, you should have several files that weren’t there before in your models and migrations directories. The default for generated Sequelize files is camelCase. However, you can specify snake_case by adding the --underscored flag, and you can keep Sequelize from automatically pluralizing the table name by adding --freeze-table-names.

Now we’re going to edit these files so that they fully reflect the relationships between the data that we intend and validate our data as it’s saved (or not saved if invalid).

Editing Models and Migrations (Not Including Associations/Foreign Keys)

Let’s start with the User model file. The generated file reads as follows:

'use strict';
module.exports = (sequelize, DataTypes) => {
  var User = sequelize.define('User', {
    name: DataTypes.STRING,
    email: DataTypes.STRING,
    password: DataTypes.STRING,
    role: DataTypes.STRING
  }, {});
  User.associate = function(models) {
    // associations can be defined here
  };
  return User;
};

We want to make a few changes here. What are they? Well, first it’s important to understand that Sequelize automatically adds three columns to each table: a unique id that acts as the primary key for the table, and createdAt and updatedAt columns, which are self-explanatory. We want to specify that each email must be unique to the database and each User must have a password. Additionally we want to add other fields

So, make the following changes:

'use strict';
module.exports = (sequelize, DataTypes) => {
  var User = sequelize.define('User', {
    name: DataTypes.STRING,
    email: {
      type: Sequelize.STRING,
      unique: true
    },
    password: {
      type: Sequelize.STRING,
      allowNull: false
    },
    role: {
      type: Sequelize.STRING,
      defaultValue: 'user'
    },
    provider: DataTypes.STRING,
    salt: DataTypes.STRING
  }, {});
  User.associate = function(models) {
    // associations can be defined here
  };
  return User;
};

Now we need to edit the boilerplate User migration to reflect the changes we’ve made to the model in the table’s structure. Notice that while the id, createdAt, and updatedAt columns weren’t present in the original boilerplate User model, they are in the boilerplate User migration. They’re not in the model because the Sequelize node package specifies them as a default part of each model, but they have to be in the migration so that the columns are created in the table. Remember, SQL databases can’t save data unless you’ve previously created columns for that data. Here is the generated boilerplate User migration:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      password: {
        type: Sequelize.STRING
      },
      role: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Users');
  }
};

And here is the migration edited to reflect the changes in the User model.

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING,
        unique: true
      },
      password: {
        type: Sequelize.STRING,
        allowNull: false
      },
      role: {
        type: Sequelize.STRING,
        defaultValue: 'user'
      },
      provider: Sequelize.STRING,
      salt: Sequelize.STRING,
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Users');
  }
};

Here’s our Company model once we’ve expanded the boilerplate. By default, the id column will start at 1 and assign numbers in ascending order. This pattern is okay for small projects, but it lacks sophistication. Deleted rows will leave obvious gaps in the numbering, rows in different tables will have the same ids, and the unique ids won’t all have the same number of digits. So we’re going to alter the id column to assign a UUID to each row. Sequelize has a UUID datatype that will make this a snap.

'use strict';
module.exports = (sequelize, DataTypes) => {
  var Company = sequelize.define('Company', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    founded: DataTypes.INTEGER,
    address: {
      type: DataTypes.STRING
    }
  }, {});
  Company.associate = function(models) {
    // associations can be defined here
  };
  return Company;
};

And the migration:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Companies', {
      id: {
        type: Sequelize.UUID,
        primaryKey: true,
        defaultValue: Sequelize.UUIDV4
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false
      },
      founded: {
        type: Sequelize.INTEGER
      },
      address: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Companies');
  }
};

Model Associations (for Foreign Keys): hasMany, hasOne, belongsTo

The section on associations in the Sequelize docs is confusing. When should you use hasMany, hasOne, belongsTo, and belongsToMany? The Sequelize docs talk about the source and target models and tables, but doesn’t clearly explain which is which.

In Sequelize, an association must be defined on both the target and the source models. For clarity, let’s call them the “model containing the primary key” and the “model containing the foreign key”. The model containing the primary key uses hasMany (1:many) and hasOne (1:1) while the model containing the foreign key uses belongsTo (1:1, 1:many). The name of the foreign key column must be specified on both models, in the associations section. Do not put the foreign key column in the list of columns on the model containing the foreign key. You will get the error that the column already exists, because Sequelize will try to create it twice. Instead, only define the foreign key in the associations section. This project only uses one-to-many relationships. As of this writing, the docs text seems to be right, if not clear or laden with examples. I’ve added a short summary on many-to-many associations in models and migrations a few sections down.

Let’s define the model associations for the User. We’ll handle migrations separately in the section below. We want to associate the User model with the Context model by specifying that a User has many Contexts, and pass the name of the foreign key in the Context model (model containing the foreign key) to the User model (model containing the primary key). We’ve also specified onDelete: CASCADE, which tells Sequelize that if we delete a User, it should delete all that User’s Contexts. I’m not sure if this needs to be specified on the model containing the primary key, but I’ve included it to be future-proof. The other option is onDelete: 'SET NULL'.

Model Company

'use strict';
module.exports = (sequelize, DataTypes) => {
  var Company = sequelize.define('Company', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    founded: DataTypes.INTEGER,
    address: {
      type: DataTypes.STRING
    }
  }, {});

  Company.associate = (models) => {
    Company.belongsTo(models.User, {
      onDelete: 'CASCADE',
      onUpdate: 'CASCADE',
      foreignKey: {
        userId: {
          type: DataTypes.INTEGER,
          allowNull: false
        }
      }
    });

    Company.hasMany(models.Product, {
      foreignKey: 'companyId',
      targetKey: 'id'
    });
  };

  return Company;
};

Product Model

'use strict';
module.exports = (sequelize, DataTypes) => {
  var Product = sequelize.define('Product', {
    name: DataTypes.STRING,
    price: DataTypes.INTEGER,
    description: DataTypes.STRING
  }, {});
  Product.associate = (models) => {
    Product.belongsTo(models.Company, {
      foreignKey: {
        name: 'companyId',
        allowNull:false
      },
      targetKey: 'id',
      onDelete: 'CASCADE',
      onUpdate: 'CASCADE'
    });
  };
  return Product;
};

Creating Foreign Keys in Migrations

Just like you include the id, createdAt, and updatedAt columns in the migration, but not the model, you must explicitly create the foreign key column on the migration containing the foreign key, along with info about which table the foreign key references. Where the association implies the connection on the model, you must tell the SQL DB itself how to constrain the columns you create in the migration

Company migrate

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Companies', {
      id: {
        type: Sequelize.UUID,
        primaryKey: true,
        defaultValue: Sequelize.UUIDV4
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false
      },
      founded: {
        type: Sequelize.INTEGER
      },
      address: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      userId: {
        type: Sequelize.INTEGER,
        allowNull:false,
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
        references: {
          model: 'Users',
          key: 'id'
        }
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Companies');
  }
};

Product Migrate

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Products', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      price: {
        type: Sequelize.INTEGER
      },
      description: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      companyId: {
        type: Sequelize.UUID,
        allowNull: false,
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
        references: {
          model: 'Companies',
          key: 'id'
        }
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Products');
  }
};

Create Seeds

Seeds are used to populate the database with some data. Instead of using forms to populate database or using insert commands manually, this is a much better technique for doing so.

User Seed

$ sequelize seed:generate --name user

Sequelize-cli generates a file similar to this one that we must complete

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    /*
      Add altering commands here.
      Return a promise to correctly handle asynchronicity.

      Example:
      return queryInterface.bulkInsert('Person', [{
        name: 'John Doe',
        isBetaMember: false
      }], {});
    */
  },

  down: (queryInterface, Sequelize) => {
    /*
      Add reverting commands here.
      Return a promise to correctly handle asynchronicity.

      Example:
      return queryInterface.bulkDelete('Person', null, {});
    */
  }
};

Complete User seed

'use strict';

const USERS = [
  {
    name: 'Hicks Kidd',
    role: 'user',
    password: '5b0f78cb5bf11b30a51931e1',
    salt: 'Rachael',
    provider: 'Waller',
    email: 'rachael.waller@waller.info'
  },
  {
    name: 'Cassie Wyatt',
    role: 'admin',
    password: '5b0f78cb4ee116caac2330e3',
    salt: 'Fay',
    provider: 'Manning',
    email: 'fay.manning@manning.net'
  },
  {
    name: 'Campos Barron',
    role: 'admin',
    password: '5b0f78cb83057777b5cc81bf',
    salt: 'Gibbs',
    provider: 'Robertson',
    email: 'gibbs.robertson@robertson.me'
  }
];

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('Users', USERS, {})
    .then(() => console.log('finished populating users'))
    .catch(err => console.log('error populating users', err))
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('Users', null, {})
  }
};

The complete guide of this workshop will continue ...