# 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](https://bezkoder.com/wp-content/uploads/2020/04/sequelize-one-to-many-node-example-project-structure.png)

#### 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](https://sequelize.org/master/class/lib/sequelize.js~Sequelize.html#instance-constructor-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`](https://sequelize.org/master/class/lib/model.js~Model.html#static-method-create)`(object)`
* find a Tutorial by id: [`findByPk`](https://sequelize.org/master/class/lib/model.js~Model.html#static-method-findByPk)`(id)`
* get all Tutorials: [`findAll`](https://sequelize.org/master/class/lib/model.js~Model.html#static-method-findAll)`()`
* [`update`](https://sequelize.org/master/class/lib/model.js~Model.html#static-method-update), [`destroy`](https://sequelize.org/master/class/lib/model.js~Model.html#static-method-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()`](https://sequelize.readthedocs.io/en/latest/api/associations/has-many/) to help one Tutorial have many Comments, and [`belongsTo()`](https://sequelize.readthedocs.io/en/latest/api/associations/belongs-to/) 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](https://bezkoder.com/wp-content/uploads/2020/04/sequelize-one-to-many-node-example-table-1.png)

– **comments** table:

![sequelize-one-to-many-node-example-table-2](https://bezkoder.com/wp-content/uploads/2020/04/sequelize-one-to-many-node-example-table-2.png)
