Introduction

In this article, we will see how to use Sequelize CLI to create a table for our database and how can we set up a relationship between the two tables using foreign keys.

Prerequisites

Project structure

Project structure

Setup the node project

Setup Node in the project

Install packages

Now, we will install the package required for building our application.

Setup sequelize

Now we will set up a Sequelize structure required for building the application.

On console type

Bash
# sequelize

This will give you the following output.

Output

This shows that sequelize CLI is properly initialized.

Now initialize Sequelize by typing

Bash
#sequelize init

Sequelize

It will generate the following folder.

Create a Model

Now we will generate two models or tables for our database.

The model generates a command requires two options

Now we will get two models in our model folder with department, and emp name.

Two files will be added to the migration folder, which will contain the table schema of both these models.

Remember if we want to add or remove any field from the table then we will have to make changes in both the migration file and the model file of that particular model.

Make relationship

Now we will make a relationship between the department and the emp table through foreign Key.

To make a relationship between the two we use.

Here, the department model can have multiple relationships. One will be with the emp model.

Now we will specify the name of the column/attribute of the emp model which will contain the department references in the emp model.

So we have to tell the department model which column/attribute in emp will contain its references.

Here the depId will contain the reference of the department in the emp model.

We do it by providing it in foreign-key

Open the emp model/table schema.

Model or table schema

JavaScript
emp.belongsTo(models.department, { foreignKey: 'id', targetKey: 'depId' });

Emp will have a relationship with the department. These two will be linked with each other through the depId column/attribute present in the emp model.

The column/attribute of the department which will be used for the reference will be the ID.

Here target _Key will contain the linking column/attribute and foreignKey will contain the reference column/attribute.

Now we have to make changes in the migration file.

Open the migration file for the emp model and update it.

Emp model and update

Here we add the deptId attribute/column on which we apply the foreign key constraint.

The references will contain the model and key

Perform Migration

This command will execute the following steps

  1. Ensures a table called SequelizeMeta is in the database. This table is used to record which migrations have run on the current database.
  2. Starts looking for any migration files that haven't run yet. This is possible by checking the SequelizeMeta table.
  3. This will create a table in our database. Here, it will create two tables in the database department, emp.

Note. When we run db: migrate then the up function int the migration file will be called.

Undo Migration

We can use db:migrate:undo, this command will revert the most recent migration.

When we run the above command then the down function in the migration file will be called.

Setup app.js

Add the below code in the app.js.

JavaScript
var express = require('express');
var bodyParser = require('body-parser');
var deptModel = require('./models').department;
var empModel = require('./models').emp;
var app = express();
// fetch form data from the request
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
app.post('/adddept', (req, res) => {
    // it will add data to department table
    deptModel.create(req.body)
        .then(function(data) {
            res.json({ da: data });
        })
        .catch(function(error) {
            res.json({ er: error });
        });
});
app.post('/addemp', (req, res) => {
    // it will add data to emp table
    empModel.create(req.body)
        .then(function(data) {
            res.json({ da: data });
        })
        .catch(function(error) {
            res.json({ er: error });
        });
});
app.post('/deldept/:id', (req, res) => {
    // it will delete particular department data
    deptModel.destroy({ where: { id: req.params.id } })
        .then(function(data) {
            res.json({ da: data });
        })
        .catch(function(error) {
            res.json({ er: error });
        });
});
app.get('/', (req, res) => {
    // this will join the tables and display data
    empModel.findAll({ include: [{ model: deptModel }] })
        .then(function(data) {
            res.json({ da: data });
        })
        .catch(function(error) {
            res.json({ er: error });
        });
});
// assign the port
var port = process.env.port || 3000;
app.listen(port, () => console.log('server running at port' + port));

Output

Here we see when we delete the record from the department table then the same records from its child will also be deleted.

As you have seen above we are using onDelete:'CASCADE'. You can read about it more from the links given in the references.

Click to watch tutorial

References