sequelize-one-to-many-node-example-feature-image

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

In this tutorial, I will show you one of the most important Relationships that you will use in most database structures: One-to-Many Association with Sequelize in Node.js example.

Related Posts:
Sequelize Many-to-Many Association tutorial with example
Node.js Rest APIs example with Express, Sequelize & MySQL
Node.js Rest APIs example with Express, Sequelize & PostgreSQL


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:

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

Conclusion

Today we’ve know way to configure Sequelize ORM and define Sequelize Model for One-to-Many Relationship with Tutorial and Comment Entity. I hope this Sequelize Associations tutorial with example will help you implement your own data design.

Don’t miss the next tutorial:
Sequelize Many-to-Many Association

Happy Learning! See you again.

Source Code

You can find the complete source code for this example on Github.

Further Reading

7 thoughts to “Sequelize One-to-Many Association example with Node.js & MySQL”

  1. Hey, thanks for the tutorial!
    I build an api with your tutorial (https://bezkoder.com/node-js-express-sequelize-mysql/).

    How would one go about adding comment to that model? I dont see how to actually add comments because I dont see an api endpoint? In your old tutorial we could add a new tutorial with http://…/api/articles (using postman).
    I hope you understand what I mean.

    Thanks again, keep up the good content!

    1. Hi, you can create a new endpoint, for example: /api/comments.
      With POST request:

      {
      	"tutorial_id": 42
      	"name": "your name",
      	"text": "this is a comment"
      }
      

      – Create comment.controller.js, export create() method that use createComment (in this post).
      – Create new routes comment.routes.js: router.post("/", comments.create)

  2. Hello, first thank you so much for your great tutorials. I have learned so much from your posts!

    Based on your tutorial, I want to create a nested comments structure(where people can reply on comments)
    and I am struggling to get it done. In order for me to make it happen, would I need to create an index table to build a nested database structure for nested comments?

    Or is there anything that I can refer to? I have tried to search online, but nothing helpful was found!

    Thank you in advance.

  3. Hi! i’m having trouble finding out how to pass the id to get the comments for each tutorial in a non-static way. For example, i’m using this tutorial as a reference to get the tasks for a given user. When a user logs in, I need to get the id and send it to the method in the controller. How can I do this?

Leave a Reply

Your email address will not be published. Required fields are marked *