Use Sequelize to Manipulate Databases
Tags: javascript, database, node, sql
Published: 2019-12-30 23:24:18

**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:

```bash
$ 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:

```javascript
'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.

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

To drop this, put:

```javascript
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:

```javascript
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:

```javascript
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:

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

And for the `ChatRoom` model:

```javascript
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:

```javascript
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:

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

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

```javascript
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` .