Sequelize One-to-Many Association example with Node.js & MySQL

Sequelize One-to-Many example Overview

In systems analysis, a one-to-many relationship refers to the relationship between two entities A and B in which an element of A may be linked to many elements of B, but a member of B is linked to only one element of A.

For example, assume that you want to design a Tutorial Blog data model, you can think about one Tutorial has many Comments, but one Comment only belongs to one Tutorial.

So the relationship between Tutorial entity and Comment entity is one-to-many. That’s what we’re gonna make in this article, and here are the step by step:

• First, we setup Node.js App

• Next, configure MySQL database & Sequelize

• Define the Sequelize Model and initialize Sequelize

• Then we create the Controller for creating and retrieving Entities

• Finally we run the app to check the result

Use Sequelize for One-to-Many Association

We’re gonna define the models using Sequelize Model:

const Tutorial = sequelize.define("tutorial", { ... })
const Comment = sequelize.define("comment", { ... })

Tutorial.hasMany(Comment, { as: "comments" });
Comment.belongsTo(Tutorial, {
  foreignKey: "tutorialId",
  as: "tutorial",
});

Using Model.create() for creating new objects:

Tutorial.create({
  title: "title",
  description: "description",
})

Comment.create({
  name: "name",
  text: "text",
  tutorialId: 42,
})

Then show the tutorial with comments inside using Model.findByPk() and Model.findAll():

Tutorial.findByPk(tutorialId, { include: ["comments"] });
Tutorial.findAll({ include: ["comments"] });

The result will look like this:

{
  "id": 1,
  "title": "Tut#1",
  "description": "Tut#1 Description",
  "comments": [
    {
      "id": 1,
      "name": "bezkoder",
      "text": "Good job!",
      "tutorialId": 1
    },
    {
      "id": 2,
      "name": "zkoder",
      "text": "One of the best tuts!",
      "tutorialId": 1
    }
  ]
}

Sequelize One-to-Many Implementation

Create Node.js App

First, we create a folder:

$ mkdir nodejs-sequelize-one-to-many
$ cd nodejs-sequelize-one-to-many

Next, we initialize the Node.js App with a package.json file:

name: (nodejs-sequelize-one-to-many) 
version: (1.0.0) 
description: Node.js Sequelize One to Many Relationship
entry point: (index.js) server.js
test command: 
git repository: 
keywords: nodejs, sequelize, one-to-many, association
author: bezkoder
license: (ISC)

Is this ok? (yes) yes

We need to install necessary modules: sequelize, mysql2. Run the command:

npm install sequelize mysql2 --save

The package.json file should look like this:

{
  "name": "nodejs-sequelize-one-to-many",
  "version": "1.0.0",
  "description": "Node.js Sequelize One to Many Relationship",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "nodejs",
    "sequelize",
    "one-to-many",
    "association"
  ],
  "author": "bezkoder",
  "license": "ISC",
  "dependencies": {
    "mysql2": "^2.1.0",
    "sequelize": "^5.21.6"
  }
}

Let’s create Node.js project structure like following directory tree:

sequelize-one-to-many-node-example-project-structure

Configure Sequelize and MySQL database

In the app folder, we create a separate config folder with db.config.js file like this:

module.exports = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "123456",
  DB: "testdb",
  dialect: "mysql",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

First five parameters are for MySQL connection. pool is optional, it will be used for Sequelize connection pool configuration:

• max: maximum number of connection in pool

• min: minimum number of connection in pool

• idle: maximum time, in milliseconds, that a connection can be idle before being released

• acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Define the Sequelize Model

In models folder, create tutorial.model.js file like this:

module.exports = (sequelize, DataTypes) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: DataTypes.STRING
    },
    description: {
      type: DataTypes.STRING
    }
  });

  return Tutorial;
};

This Sequelize Model represents tutorials table in MySQL database. These columns will be generated automatically: id, title, description, createdAt, updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them:

• create a new Tutorial: create(object)

• find a Tutorial by id: findByPk(id)

• get all Tutorials: findAll()

• update, destroy,…

We’re gonna use Sequelize Model functions in our Controller.

Now continue create Comment model in comment.model.js:

module.exports = (sequelize, DataTypes) => {
  const Comment = sequelize.define("comment", {
    name: {
      type: DataTypes.STRING
    },
    text: {
      type: DataTypes.STRING
    }
  });

  return Comment;
};

Initialize Sequelize

Let’s initialize Sequelize in app/models folder. Now create app/models/index.js with the following code:

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle,
  },
});

const db = {};

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

db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);
db.comments = require("./comment.model.js")(sequelize, Sequelize);

db.tutorials.hasMany(db.comments, { as: "comments" });
db.comments.belongsTo(db.tutorials, {
  foreignKey: "tutorialId",
  as: "tutorial",
});

module.exports = db;

We use hasMany() to help one Tutorial have many Comments, and belongsTo() to indicate that one Comment only belongs to one Tutorial.

Create the Controller

Inside app/controllers folder, let’s create tutorial.controller.js and export these functions:

• createTutorial

• createComment

• findTutorialById

• findCommentById

• findAll

First we need to import database object:

const db = require("../models");
const Tutorial = db.tutorials;
const Comment = db.comments;

Create and Save new Tutorials

exports.createTutorial = (tutorial) => {
  return Tutorial.create({
    title: tutorial.title,
    description: tutorial.description,
  })
    .then((tutorial) => {
      console.log(">> Created tutorial: " + JSON.stringify(tutorial, null, 4));
      return tutorial;
    })
    .catch((err) => {
      console.log(">> Error while creating tutorial: ", err);
    });
};

Create and Save new Comments

exports.createComment = (tutorialId, comment) => {
  return Comment.create({
    name: comment.name,
    text: comment.text,
    tutorialId: tutorialId,
  })
    .then((comment) => {
      console.log(">> Created comment: " + JSON.stringify(comment, null, 4));
      return comment;
    })
    .catch((err) => {
      console.log(">> Error while creating comment: ", err);
    });
};

Get the comments for a given tutorial

exports.findTutorialById = (tutorialId) => {
  return Tutorial.findByPk(tutorialId, { include: ["comments"] })
    .then((tutorial) => {
      return tutorial;
    })
    .catch((err) => {
      console.log(">> Error while finding tutorial: ", err);
    });
};

Get the comments for a given comment id

exports.findCommentById = (id) => {
  return Comment.findByPk(id, { include: ["tutorial"] })
    .then((comment) => {
      return comment;
    })
    .catch((err) => {
      console.log(">> Error while finding comment: ", err);
    });
};

Get all Tutorials include comments

exports.findAll = () => {
  return Tutorial.findAll({
    include: ["comments"],
  }).then((tutorials) => {
    return tutorials;
  });
};

Check the result

Open server.js and write the following code:

const db = require("./app/models");
const controller = require("./app/controllers/tutorial.controller");

const run = async () => {

};

// db.sequelize.sync();
db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
  run();
});

– First, we import our database object and controller above. – Then we call Sequelize sync() method.

db.sequelize.sync();

In development, you may need to drop existing tables and re-sync database. Just use force: true as following code:

db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
});

Now, we’re gonna test the result by putting the next lines of code inside run() function. You can run the this Sequelize one-to-many Node.js application with command: node server.js.

Create Tutorials

const tut1 = await controller.createTutorial({
  title: "Tut#1",
  description: "Tut#1 Description",
});
/*
>> Created tutorial: {
    "id": 1,
    "title": "Tut#1",
    "description": "Tut#1 Description",     
    "updatedAt": "2020-04-14T09:49:14.021Z",
    "createdAt": "2020-04-14T09:49:14.021Z" 
}
*/

const tut2 = await controller.createTutorial({
  title: "Tut#2",
  description: "Tut#2 Description",
});
/*
>> Created tutorial: {
    "id": 2,
    "title": "Tut#2",
    "description": "Tut#2 Description",
    "updatedAt": "2020-04-14T09:49:14.052Z",
    "createdAt": "2020-04-14T09:49:14.052Z"
}
*/

Create Comments

const comment1 = await controller.createComment(tut1.id, {
  name: "bezkoder",
  text: "Good job!",
});
/*
>> Created comment: {
    "id": 1,
    "name": "bezkoder",
    "text": "Good job!",
    "tutorialId": 1,
    "updatedAt": "2020-04-14T09:49:14.071Z",
    "createdAt": "2020-04-14T09:49:14.071Z"
}
*/

await controller.createComment(tut1.id, {
  name: "zkoder",
  text: "One of the best tuts!",
});
/*
>> Created comment: {
    "id": 2,
    "name": "zkoder",
    "text": "One of the best tuts!",
    "tutorialId": 1,
    "updatedAt": "2020-04-14T09:49:14.081Z",
    "createdAt": "2020-04-14T09:49:14.081Z"
}
*/

const comment2 = await controller.createComment(tut2.id, {
  name: "aKoder",
  text: "Hi, thank you!",
});
/*
>> Created comment: {
    "id": 3,
    "name": "aKoder",
    "text": "Hi, thank you!",
    "tutorialId": 2,
    "updatedAt": "2020-04-14T09:49:14.855Z",
    "createdAt": "2020-04-14T09:49:14.855Z"
}
*/

await controller.createComment(tut2.id, {
  name: "anotherKoder",
  text: "Awesome tut!",
});
/*
>> Created comment: {
    "id": 4,
    "name": "anotherKoder",
    "text": "Awesome tut!",
    "tutorialId": 2,
    "updatedAt": "2020-04-14T09:49:15.478Z",
    "createdAt": "2020-04-14T09:49:15.478Z"
}
*/

Get Tutorial by given id

const tut1Data = await controller.findTutorialById(tut1.id);
console.log(
  ">> Tutorial id=" + tut1Data.id,
  JSON.stringify(tut1Data, null, 2)
);
/*
>> Tutorial id=1 {
  "id": 1,
  "title": "Tut#1",
  "description": "Tut#1 Description",
  "createdAt": "2020-04-14T09:49:14.000Z",
  "updatedAt": "2020-04-14T09:49:14.000Z",
  "comments": [
    {
      "id": 1,
      "name": "bezkoder",
      "text": "Good job!",
      "createdAt": "2020-04-14T09:49:14.000Z",
      "updatedAt": "2020-04-14T09:49:14.000Z",
      "tutorialId": 1
    },
    {
      "id": 2,
      "name": "zkoder",
      "text": "One of the best tuts!",
      "createdAt": "2020-04-14T09:49:14.000Z",
      "updatedAt": "2020-04-14T09:49:14.000Z",
      "tutorialId": 1
    }
  ]
}
*/

const tut2Data = await controller.findTutorialById(tut2.id);
console.log(
  ">> Tutorial id=" + tut2Data.id,
  JSON.stringify(tut2Data, null, 2)
);
/*
>> Tutorial id=2 {
  "id": 2,
  "title": "Tut#2",
  "description": "Tut#2 Description",
  "createdAt": "2020-04-14T09:49:14.000Z",
  "updatedAt": "2020-04-14T09:49:14.000Z",
  "comments": [
    {
      "id": 3,
      "name": "aKoder",
      "text": "Hi, thank you!",
      "createdAt": "2020-04-14T09:49:14.000Z",
      "updatedAt": "2020-04-14T09:49:14.000Z",
      "tutorialId": 2
    },
    {
      "id": 4,
      "name": "anotherKoder",
      "text": "Awesome tut!",
      "createdAt": "2020-04-14T09:49:15.000Z",
      "updatedAt": "2020-04-14T09:49:15.000Z",
      "tutorialId": 2
    }
  ]
}
*/

Get Comment by given id

const comment1Data = await controller.findCommentById(comment1.id);
console.log(
  ">> Comment id=" + comment1.id,
  JSON.stringify(comment1Data, null, 2)
);
/*
>> Comment id=1 {
  "id": 1,
  "name": "bezkoder",
  "text": "Good job!",
  "createdAt": "2020-04-14T09:49:14.000Z",
  "updatedAt": "2020-04-14T09:49:14.000Z",
  "tutorialId": 1,
  "tutorial": {
    "id": 1,
    "title": "Tut#1",
    "description": "Tut#1 Description",
    "createdAt": "2020-04-14T09:49:14.000Z",
    "updatedAt": "2020-04-14T09:49:14.000Z"
  }
}
*/

const comment2Data = await controller.findCommentById(comment2.id);
console.log(
  ">> Comment id=" + comment2.id,
  JSON.stringify(comment2Data, null, 2)
);
/*
>> Comment id=3 {
  "id": 3,
  "name": "aKoder",
  "text": "Hi, thank you!",
  "createdAt": "2020-04-14T09:49:14.000Z",
  "updatedAt": "2020-04-14T09:49:14.000Z",
  "tutorialId": 2,
  "tutorial": {
    "id": 2,
    "title": "Tut#2",
    "description": "Tut#2 Description",
    "createdAt": "2020-04-14T09:49:14.000Z",
    "updatedAt": "2020-04-14T09:49:14.000Z"
  }
}
*/

Get all Tutorials

const tutorials = await controller.findAll();
console.log(">> All tutorials", JSON.stringify(tutorials, null, 2));
/*
>> All tutorials [
  {
    "id": 1,
    "title": "Tut#1",
    "description": "Tut#1 Description",
    "createdAt": "2020-04-14T09:49:14.000Z",
    "updatedAt": "2020-04-14T09:49:14.000Z",
    "comments": [
      {
        "id": 1,
        "name": "bezkoder",
        "text": "Good job!",
        "createdAt": "2020-04-14T09:49:14.000Z",
        "updatedAt": "2020-04-14T09:49:14.000Z",
        "tutorialId": 1
      },
      {
        "id": 2,
        "name": "zkoder",
        "text": "One of the best tuts!",
        "createdAt": "2020-04-14T09:49:14.000Z",
        "updatedAt": "2020-04-14T09:49:14.000Z",
        "tutorialId": 1
      }
    ]
  },
  {
    "id": 2,
    "title": "Tut#2",
    "description": "Tut#2 Description",
    "createdAt": "2020-04-14T09:49:14.000Z",
    "updatedAt": "2020-04-14T09:49:14.000Z",
    "comments": [
      {
        "id": 3,
        "name": "aKoder",
        "text": "Hi, thank you!",
        "createdAt": "2020-04-14T09:49:14.000Z",
        "updatedAt": "2020-04-14T09:49:14.000Z",
        "tutorialId": 2
      },
      {
        "id": 4,
        "name": "anotherKoder",
        "text": "Awesome tut!",
        "createdAt": "2020-04-14T09:49:15.000Z",
        "updatedAt": "2020-04-14T09:49:15.000Z",
        "tutorialId": 2
      }
    ]
  }
]
*/

Check MySQL Database:

– tutorials table:

sequelize-one-to-many-node-example-table-1

– comments table:

sequelize-one-to-many-node-example-table-2