One-to-Many Relationships in MYSQL Database Using Sequelize ORM

Relationships in databases are a key concept. As a beginner, writing relationships in plain SQL might be a bit intimidating, but using object-relational mapping (ORM) can make the task really easy and can drastically reduce the time it takes for you to design a database.

In this article I will be designing a one-to-many relationship using the Sequelize ORM in MYSQL. The framework on which I will be building is Nodejs.

You will need to install in the beginning, and keep on installing packages as we move forward

1. Express

2. Sequelize

3. Sequelize-cli (It will create all the necessary files required for you to work with Sequelize).

4. dotenv to store database password.

First, let’s set up our project. Create an empty folder and make sure you have node installed on your machine.
Open your command line tool of choice, (for Windows users the Git bash is recommended). And run

npm init -y

This will initialize the npm in your current folder, and you will see a package.json file which will look like the following. The name will depend on your project folder’s name.

1
2
3
4
5
6
7
8
9
10
11
12
{
  "name": "one-to-many",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

Now we need to install some packages. For this, run the following command.

npm i express dotenv sequelize sequelize-cli

Now we are ready to start our project.
Create a file called app.js in your project’s root folder. And write the following code in the file.

1
2
3
4
5
6
7
8
9
10
11
12
const express = require('express')
const app = express();
const PORT = 3000;
//Following lines are to make sure our app can parse the json data
app.use(express.json());
app.use(express.urlencoded({
  extended: false
}));

app.listen(PORT, () => {
  console.log('Server started on port 3000');
})

Here we are importing the express module and initializing it with an app variable. After doing that we started the server on port 3000.
Now install nodemon, it will make the development a lot easier. Type npm install --save-dev nodemon in your terminal.
And now run nodemon app.js
If you see a message that the server started on port 3000, then you did everything right.

Now we will start with Sequelize. First, we need a database paradigm, I will be using MYSQL. Run the following command.

npm i mysql2

Make sure it’s MYSQL2 and not MYSQL. Now, run

sequelize init

It will create four folders for you, by now the folder directory should be looking like this.

You can delete the seeders and migrations folders, we won’t be needing them in this tutorial.
Now we will be connecting to our database. So, open your MYSQL workbench and create a database. I have created a database named fakedb, and as you can see in the image below it is empty for now.

Now open the config/config.json file. There will be a key called development that looks like this.

1
2
3
4
5
6
7
8
9
{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
}

Now we have to replace the above values with correct values. In my case it looks as follows: you will have to change the values according to the sql setup of yours, but the host and dialect will be the same as mine.

Now, let’s create our first model and check whether our db connection works or not. In the models folder create a file called user.js and write the following code in that file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define("User", {
    firstName: {
      type: DataTypes.STRING,
    },
    middleName: {
      type: DataTypes.STRING,
    },
    lastName: {
      type: DataTypes.STRING,
    },
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    age: {
      type: DataTypes.INTEGER,
    },
  });
  return User;
};

Here, we have created a model for the user, which will have firstName, middleName, and lastName, all as string, and an auto-incrementing id which will act as primary key, along with an integer age. After creation of this model, we will export it under the name of User.
Add the following line below the import lines in app.js

const db = require('./models');

This will allow us to use the models in the app.js file. Now, wrap the app.listen in the following way.

1
2
3
4
5
6
7
8
9
db.sequelize.sync()
  .then((result) => {
    app.listen(3000, () => {
      console.log('Server started');
    })
  })
  .catch((err) => {
    console.log(err);
  })

Now when you save the file you will see something like this in the terminal:

And if you read this, you will realise that an SQL query was just run and the users (lowercase and plural) table has been created.
Go to the MYSQL workbench and refresh like this. Right click on tables then refresh.

A users table has been created. And you can verify this in the SQL workbench itself, write, following in the workbench SQL file and run.

1
2
Use fakedb;
SELECT * FROM users;

Fakedb db is my database name, use according to yours.
You will get the following output.

Of course, it’s empty but you can see createdAt and updatedAt have been created by themselves.
Let’s insert some data. Write the following route above the app.listen line in app.js

1
2
3
4
5
6
7
8
9
app.post("/user", async (req, res) => {
  const data = req.body;
  try {
    const user = await db.User.create(data);
    res.send(user);
  } catch (err) {
    res.send(err);
  }
});

Now open postman and send a post request to
URL = http://localhost:3000/user,

1
2
3
4
5
6
Body = this json object {
  "firstName": "Srajan",
  "middleName": "Kishor",
  "lastName": "Gupta",
  "age": 21
}

Make sure to send the correct data in the body because we are not validating in the backend.
Now running the SQL select query will show that the data has been created.

In a similar way, create a model for posts (something similar to social media posts). Its model will look like this. Also create an API in a similar way to insert data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
module.exports = (sequelize, DataTypes) => {
  const Post = sequelize.define("Post", {
    title: {
      type: DataTypes.STRING,
    },
    content: {
      type: DataTypes.STRING,
    },
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
  });
  return Post;
};

If you made it this far then give yourself a pat on the back and drink some water. If you already knew all this and only wanted to learn about the relationship, then your wait is over.
Now, since each user has many posts, we will add the following syntax right above the return statement in the user model.

1
2
3
4
5
User.associate = models => {
  User.hasMany(models.Post, , {
    foreignKey: 'creatorId'
  });
}

And similarly, right above the return statement of the post model add the following syntax. Add another field called creatorId of type integer in the posts model.

1
2
3
4
5
Post.associate = (models) => {
  Post.belongsTo(models.User, {
    foreignKey: 'creatorId'
  })
}

Now save these two files. We will have to delete the tables from our database because our database will not itself make the changes we did in our models.
Use the command DROP TABLE tableName; to delete a table in MYSQL.
That’s it, you have done it. It will create a column called creatorId in the posts table that will have the id (primaryKey) of the user who created it. Now create a bunch of users using the user API.
Now, add a few posts and while sending post data make sure to send the creatorId in the body as well (in a production type application you will use something like Joi to validate the incoming request), which should be equal to a user’s id.

For now my users table looks like this.

And this is my post’s body.

1
2
3
4
5
{
  "title": "My post title",
  "content": "My post content",
  "creatorId": 1
}

If you send a creatorId as some user who does not exist, let’s say in my case 5, then you will get an error. This assures us that the posts table is properly connected to users, but we still don’t know whether the users table is connected or not. To do this we will make a get request to the users database and ask it to send all the data including posts related to a specific user.

To fetch the data from Sequelize we use the following code snippet.

1
2
3
4
5
6
7
8
app.get('/user', async (req, res) => {
  try {
    const user = await db.User.findAll();
    res.send(user);
  } catch (err) {
    res.send(err);
  }
})

This will give you back all the users (but not their posts).
To view all the posts use the following

1
2
3
4
5
6
7
8
9
10
app.get('/user', async (req, res) => {
  try {
    const user = await db.User.findAll({
      include: [db.Post]
    });
    res.send(user);
  } catch (err) {
    res.send(err);
  }
})

Now, hit this query with the postman and see the magic.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
[
  {
    "firstName": "Srajan",
    "middleName": "Kishor",
    "lastName": "Gupta",
    "id": 1,
    "age": 21,
    "createdAt": "2021-09-13T15:27:14.000Z",
    "updatedAt": "2021-09-13T15:27:14.000Z",
    "Posts": [
      {
        "title": "My post title",
        "content": "My post content",
        "id": 1,
        "creatorId": 1,
        "createdAt": "2021-09-13T15:34:18.000Z",
        "updatedAt": "2021-09-13T15:34:18.000Z"
            }
        ]
    },
  {
    "firstName": "Random",
    "middleName": "mid",
    "lastName": "Person",
    "id": 2,
    "age": 23,
    "createdAt": "2021-09-13T15:30:39.000Z",
    "updatedAt": "2021-09-13T15:30:39.000Z",
    "Posts": [
      {
        "title": "My post title",
        "content": "My post content",
        "id": 2,
        "creatorId": 2,
        "createdAt": "2021-09-13T15:34:23.000Z",
        "updatedAt": "2021-09-13T15:34:23.000Z"
            }
        ]
    }
]

As you can see, all the users with all the posts are here. So, our relationship is working. This was enough for some basic work. But, if you want some customization, read along.

What if instead of using the user’s primary key you want to use some other attribute to be stored in the posts table? To do this, modify the relationship in the post like this.

1
2
3
4
5
6
Post.associate = (models) => {
  Post.belongsTo(models.User, {
    foreignKey: 'creatorId',
    targetKey: 'differentUniqueAttribute'
  })
}

And user model like this.

1
2
3
4
5
6
User.associate = models => {
  User.hasMany(models.Post, {
    foreignKey: 'creatorId',
    sourceKey: 'differentUniqueAttribute'
  });
}

Make sure that differentUniqueAttribute has unique values in the user table. That was it, now use it in your project whatever way you like.