Use Sequelize to Manipulate Databases

Subscribe to my email list now at http://jauyeung.net/subscribe/

Follow me on Twitter at https://twitter.com/AuMayeung

Sequelize is a Node.js ORM with which has one of the most comprehensive features sets available.

It is similar to other ORMs like ActiveRecord, in that they are based on creating migrations with the Sequelize CLI, allowing you to write code to modify your database’s structure.

However, there are a few catches that someone has to be aware of. The migration functionality is not as smart as ActiveRecord. You cannot roll back database migration without creating a down migration.

Also, migrations are not transactions, which means it may fail with a partially run migration where some parts of it failed to execute, leaving you with some changes made, but others not.

Sequelize CLI has to be installed separately from the library. You can run npm run --save-dev sequelize-cli to install it. After that, run npx sequelize model:generate to create your first model with its associated migration.

Add Model with Migration

For example, to create a User model with a Users table, run:

$ npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string

You may have to have administrator privileges in Windows to run this. This will create a firstName field, a lastName field, and an email field in the User model and when npx sequelize-cli migration is run, then a Users table will be created with the columns firstName , lastName and email .

The migration file should have this code:

'use strict';

module.exports = {  
  up: (queryInterface, Sequelize) => {  
    return queryInterface.createTable('Users', {  
      id: {   
        allowNull: false,  
        autoIncrement: true,  
        primaryKey: true,  
        type: Sequelize.INTEGER  
      },  
      firstName: {  
        type: Sequelize.STRING  
      },  
      email: {  
        type: Sequelize.STRING  
      },  
    });  
   }, 

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

Note the id column is created automatically, and there is a down migration in the down function where the reverse of the up migration is included. If the code in the down function is not included, then you cannot run npx sequelize-cli db:migrate:undo to undo your migration.

You can also create migration and model files separately. They will be linked together if they are named in the correct pattern. Table name should be plural of the model name. For example Users table will map to the User model. To create migration without its associated mode, run npx sequelize migration:generate.

If you have multiple operations, you have to wrap them in an array and pass the array of operations into Promise.all and return that, since the return value of the up and down functions is a promise.

Adding Constraints

Adding constraints is simple. To do this, put the following in the up function of your migration file.

queryInterface.addConstraint(  
  "Users",  
  ["email"],  
  {  
    type: "unique",  
    name: "emailUnique"  
})

To drop this, put:

queryInterface.removeConstraint(  
  'Users',  
  'emailUnique'  
)

Associations

To make has one, has many or many to many relations between tables, you can specify that using the Model.associate function. For example, if you have a Tweets table where multiple Tweets belong to one User, you can do:

Tweet.associate = function (models) { Tweet.belongsTo(models.User, {  
    foreignKey: 'userId',  
    targetKey: 'id'  
  });  
};

foreignKey is the ID referencing the external table and targetKey is the ID column of the table you’re referencing.

And in the User model:

User.associate = function (models) {  
  User.hasMany(models.Tweet, {  
    foreignKey: 'userId',  
    sourceKey: 'id'  
  });  
};

foreignKey is the ID referencing the current table in this case and sourceKey is the ID column of the table you’re referencing.

This specifies that each User has many Tweets.

Similarly, you can replace hasMany with hasOne to specifiy one to one relationship.

To make a many to many relationships, you need a join table between the 2 tables that you want to create a relationship with, then you can use belongsToMany function of your model to create the relationship. You need this in both of your tables that you are creating the relationship with.

For example if multiple Users can belong in multiple ChatRooms , then do:

User.associate = function(models) {        
  User.belongsToMany(models.ChatRoom, {      
    through: 'UserChatRooms',      
    as: 'chatrooms',      
    foreignKey: 'userId',      
    otherKey: 'chatRoomId'    
  });  
};

And for the ChatRoom model:

ChatRoom.associate = function(models) {        
  ChatRoom.belongsToMany(models.User, {      
    through: 'UserChatRooms',      
    as: 'users',      
    foreignKey: 'chatRoomId',      
    otherKey: 'userId'    
  });  
};

foreingKey is the ID that the other table references, otherKey is the key that is in the current table.

Changing Columns

You can rename a column like this:

queryInterface.renameColumn('Tweets', 'content', 'contents')

The first argument is the table name, second is the original column, the third one is the new column name.

Changing data type is simple:

queryInterface.changeColumn(   
  'Tweets',  
  'scheduleDate', {  
    type: Sequelize.STRING  
  }  
)

If you want to change string to date or time, do:

queryInterface.changeColumn(  
  'Tweets',   
  'scheduleDate', {  
    type: 'DATE USING CAST("scheduleDate" as DATE)'  
  }  
)

queryInterface.changeColumn(  
  'Tweets',  
  'scheduleTime', {  
     type: 'TIME USING CAST("scheduleTime" as TIME)'  
  }  
)

To run migrations, run npx sequelize db:migrate .