Sequelize schema relationships and magic methods by example

This is part one of a three-part series on Sequelize schema relationships and “magic methods” (aka getters and setters)

Are you writing a Javascript web app and need to read and write data to a SQL database? You have many options to do this, but one popular choice is to use Sequelize.

Sequelize has been around since about 2016 and has over 700 Github contributors. Using it for your SQL backend is as easy as running npm install sequelize.

In this series, I will explain how to create a One-to-One, One-to-Many, and Many-to-Many association in Sequelize. I assume you have already installed and configured Sequelize for your SQL server (e.g. MySQL, Postgres, etc). Also, I assume you are somewhat familiar with SQL concepts like foreign keys and primary keys.

Before we begin, please note that in DB parlance, the term “association” is the correct term, but “relationship” has evolved to be the more common term to use. In this article, I will use the two words interchangeably.

A Vegetable Garden Database

To start, we will create a Vegetable database that will allow us to keep track of 3 entities (tables):

• Vegetables. The items we will be growing.
• Gardeners. The people who will be growing the vegetables.
• Plots. The plots where the gardener will grow their vegetables.

For this project, we seed a database of vegetables, garden plots, and gardeners. We will have a bunch of vegetables that are inside of plots. And a gardener will have a favorite vegetable.

Visualizing the entities’ relationships

We can visualize our data models for this project in two ways: using bubbles and using tables. Our bubble diagram might look like this:

In the above diagram, we have two gardeners: gardener1and gardener2. The gardeners are associated with plots and vegetables. In relation to a plot, a gardener can only have one plot — this is a one-to-one association, or simple 1:1. And in relation to a vegetable, a gardener can plant many vegetables, so this is a one-to-many relationship, or 1:m.

One-To-One Association

We will first explore the 1:1 relationship, we will say that a gardener can only be associated with one plot — this is a one-to-one relationship. This means that a gardener can have only one plot and one plot can only have one gardener. This relationship is enforced when you try to add more than one gardener to a plot (which we will see when discuss the magic methods). So to create the relationship we would write the following :

const Gardener = db.define('gardener', {
  name: {type: Sequelize.STRING}
})
const Plot = db.define('plot', {
  name: {type: Sequelize.STRING}
})
Gardener.hasOne(Plot)
Plot.belongsTo(Gardener)

Notice that we have to invoke two built-in Sequelize methods: hasOne and belongsTo . The hasOne method is optional, but for clarity we still write it. The required method is belongsTo which creates a gardenerId foreign key in the table for Plots. Below is the resulting table schema after Plot has been associated with Gardener.

The columns in the Plot and Gardener tables after association in defined

After writing the above code, Sequelize will then translate the above code to raw SQL and execute it. If you are curious to see the raw SQL code, you can turn logging by setting logging: true in the initial declaration of Sequelize.

const db = new Sequelize('postgres://localhost/trip_planner', {
   logging: true
});

Once you’ve started your Sequelize app, you will see that a new gardenerId field is created in the plots table. Sequelize automatically pluralizes table names that you’ve defined in the db.define() method. But take note that Sequelize pluralizes in a naive way, so for example, a model called hero will result in a heros table which is not the correct spelling. Just something to be mindful of.

Customizing foreign keys

By default, Sequelize names the foreign key the schema name with “Id” concatenated to that name. For example, gardener results in thecolumn name gardenerId . But what if you want a custom, foreign key name? You can easily customize that foreignkey by passing a object parameter to hasOne with key foreignKey and value that is the name you want the foreign key to be. So it could look like this:

// Customizing a foreign key using
Gardener.hasOne(Plot, {foreignKey: 'gardener_id'})

Summary

In this article, we introduced an example database to keep track of vegetables and the gardeners growing them. We introduced the one-to-one relationship between a gardener and their plot, and how to code that relationship/association using Sequelize. Lastly, we covered what columns are created in a Sequelize one-to-one relationship, and how to customize the column names (foreignkey names).

We can summarize the result of creating a one-to-one relationship with the following table:

In the next to this article, we will cover the One-to-Many relationship between a plot and vegetables.