How to Use Sequelize-CLI to Create Tables and Relationships in Node.js

December 02, 2019

Introduction

In this article, we will explore how to use Sequelize-CLI for creating database tables and setting up powerful relationships between multiple tables using foreign keys in Node.js and MySQL.

Project Structure


|--------- config
          |--------- config.json
|--------- models
          |--------- index.js
          |--------- department.js
          |--------- emp.js
|--------- migrations
          |--------- [timestamp]-create-department.js
          |--------- [timestamp]-create-emp.js
|--------- seeders
|--------- app.js
|--------- package.json

Setup The Node Project

Let's begin by initializing our workspace:

Open your console and create a new directory:
```
mkdir seqlcli
```
Navigate into that directory:
```
cd seqlcli
```
Initialize the Node.js project by running:
```
npm init
```
This will prompt you for basic project metadata like name, version, and author, generating a package.json file.

Install Packages

Install the required packages for building the application:

npm install express body-parser sequelize mysql2 sequelize-cli

What these packages do:

Express — The web framework on which the application is built.
Body-parser — Fetches form data from incoming HTTP requests.
Sequelize — Contains the core ORM files and logic.
Mysql2 — The database driver for MySQL.
Sequelize-cli — Allows executing Sequelize command-line interface tools and migrations.

Setup Sequelize

Type sequelize in your console to verify that the CLI is correctly initialized. Next, initialize the Sequelize project structure:

sequelize init

This generates the following core folders:

config — Contains config.json with database connection details.
models — Contains index.js which collects and associates all models.
migrations — Contains migration files acting as version control logs for database schema changes.
seeders — Contains files for populating default table data.

Create Models

Generate models and migration files for department and employee entities using the CLI:


sequelize model:generate --name department --attributes dbName:string
sequelize model:generate --name emp --attributes name:string,salary:integer

The model:generate command requires two options:

--name — Name of the model.
--attributes — Fields or columns of the model/table.

Note: If you need to add or remove fields later, you must update both the migration file and the model file.

Make Relationships

We establish relationships between tables using:

hasMany — Describes a 1:N or N:N relationship (e.g., a department has many employees).
belongsTo — Describes a 1:1 or N:1 relationship (e.g., an employee belongs to a department).

Open your models and apply the associations:

Department model: department.hasMany(models.emp, { foreignKey: 'depId' });
Emp model: emp.belongsTo(models.department, { foreignKey: 'id', targetKey: 'depId' });

Update the employee migration file to add the foreign key constraint referencing the department table.

Perform Migration

Run migrations to create the tables in your database:

sequelize db:migrate

This ensures a SequelizeMeta tracking table exists and runs any pending migrations (executing the up function).

To undo the most recent migration, run:

sequelize db:migrate:undo

Setup app.js & Output

Configure your app.js file to wire up routes and server listeners. When executing cascade operations (such as onDelete: 'CASCADE'), deleting a department will automatically remove its associated child records.

Node.js codes

node.js