One to One Relationships in MYSQL Database Using Sequelize ORM

Before we discuss writing one to one relationships in sequelize, let’s take a step back and make sure we grasp the concept of the one to one relationship.

Let’s say we are in a hypothetical world where a person is allowed to have only one house. So there are two models, house and person, and we want to connect them to each other using a foreign key. Now in principle, both the tables can have a foreign key, the person table can have a foreign key for the house or the house table can have a foreign key for the person.

So, in which table should you put the foreign key? The answer to this can be found only by thinking about the relationship and the independence of these models. To do this you need to ask two questions.

Can a person exist without a house? YES

Can a house exist without a person? NO, because someone must be the owner of a house.

So we will place the foreign key on the house (dependent) table, not the person (independent) table.

If you want to set up your sequelize project or read about one to many relationships you can check out my previous article: One to Many Relationships in MYSQL Database Using Sequelize ORM.

Now that you have finished the setup of your project, let’s start by building up the person model. We are starting with this one because this is the independent one.

In the models folder create a file called person.js

This model will have firstName, middleName, lastName, id, age. Paste the following code in person.js

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 Person = sequelize.define("Person", {
    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 Person;
};

This will create the person model.
Now we will create the house model, it will have an id and address. Create house.js in the models folder and paste this code.

1
2
3
4
5
6
7
8
9
10
11
12
13
module.exports = (sequelize, DataTypes) => {
  const House = sequelize.define("House", {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    address: {
      type: DataTypes.SRTING,
    },
  });
  return House;
};

Now it’s time to define the relationships inside our model.
As we discussed above, a person can haveOne house and a house must belongsTo someone. So this is exactly what we are going to do.
Add the following snippet in person.js right above the return Person; line

1
2
3
Person.associate = models => {
  Person.hasOne(models.House);
}

In the above code snippet we are associating the person model with a house and each person can have only one house.
Similarly in the house.js file add the following code snippet.

1
2
3
House.associate = models => {
  House.belongsTo(models.Person);
}

In the above code snippet, we are associating the house model to a person and each house belongs to someone.
Now if you run the server using nodemon app.js you will be able to see the SQL queries running in the terminal. These queries are creating your table in the database.
And now, open the mySQL workbench and type SELECT * from houses;

This is what you will see. As you may have noticed, we did not explicitly create a PersonId column but it was created by sequelize.
Now it’s time to create the APIs to interact with the database.

1. API to add person

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

2. API to add houses

1
2
3
4
5
6
7
8
9
10
app.post('/house', async (req, res) => {

  const data = req.body;
  try {
    const house = await db.House.create(data);
    res.send(house);
  } catch (err) {
    res.send(err);
  }
})

3. API to get people

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

Now, we are ready. Open the postman and let’s check that everything is working fine. Make a post request to http://localhost:3000/person url and in the body, add data in the following json format.

1
2
3
4
5
6
{
  "firstName": "Srajan",
  "middleName": "Kishor",
  "lastName": "Gupta",
  "age": 21
}

The following data will be returned to you

1
2
3
4
5
6
7
8
9
{
  "id": 1,
  "firstName": "Srajan",
  "middleName": "Kishor",
  "lastName": "Gupta",
  "age": 21,
  "updatedAt": "2021-09-23T04:52:26.647Z",
  "createdAt": "2021-09-23T04:52:26.647Z"
}

If you now open mySQL workbench and type SELECT * FROM people; you will see the following data. (Person became people when pluralised.)

Let’s add a bit more data to the people table then we will add data in the houses table.
Now my people table looks like this.

Now send a post request to http://localhost:3000/house url with the data in the following json format.

1
2
3
4
{
  "address": "my home",
  "PersonId": 1
}

Now if you see the houses in mySQL workbench this is what you will see

Let’s try to allot a house to a person that does not exist. Let’s change the PersonId in the request body to 10 (it does not exist) and see what happens.
You get the following error, which in short says that the foreign key constraint is not satisfied.

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
41
42
43
{
  "name": "SequelizeForeignKeyConstraintError",
  "parent": {
    "code": "ER_NO_REFERENCED_ROW_2",
    "errno": 1452,
    "sqlState": "23000",
    "sqlMessage": "Cannot add or update a child row: a foreign key constraint fails (`fakedb`.`houses`, CONSTRAINT `houses_ibfk_1` FOREIGN KEY (`PersonId`) REFERENCES `people` (`id`) ON DELETE SET NULL ON UPDATE CASCADE)",
    "sql": "INSERT INTO `Houses` (`id`,`address`,`createdAt`,`updatedAt`,`PersonId`) VALUES (DEFAULT,?,?,?,?);",
    "parameters": [
            "my home",
            "2021-09-23 05:05:16",
            "2021-09-23 05:05:16",
            10
        ]
  },
  "original": {
    "code": "ER_NO_REFERENCED_ROW_2",
    "errno": 1452,
    "sqlState": "23000",
    "sqlMessage": "Cannot add or update a child row: a foreign key constraint fails (`fakedb`.`houses`, CONSTRAINT `houses_ibfk_1` FOREIGN KEY (`PersonId`) REFERENCES `people` (`id`) ON DELETE SET NULL ON UPDATE CASCADE)",
    "sql": "INSERT INTO `Houses` (`id`,`address`,`createdAt`,`updatedAt`,`PersonId`) VALUES (DEFAULT,?,?,?,?);",
    "parameters": [
            "my home",
            "2021-09-23 05:05:16",
            "2021-09-23 05:05:16",
            10
        ]
  },
  "sql": "INSERT INTO `Houses` (`id`,`address`,`createdAt`,`updatedAt`,`PersonId`) VALUES (DEFAULT,?,?,?,?);",
  "parameters": [
        "my home",
        "2021-09-23 05:05:16",
        "2021-09-23 05:05:16",
        10
    ],
  "fields": [
        "PersonId"
    ],
  "table": "people",
  "value": 10,
  "index": "houses_ibfk_1",
  "reltype": "child"
}

Which means our one to one relationship is working fine.

Thank you for reading.