Sequelize Foreign Keys

Introduction

Previously, we discussed the basics of Sequelize. The topics of the last tutorial included how to install Sequelize, configure a MySQL database for use within our application and how to write a very basic application taking advantage of some of the features the ORM offers. Now that we know how to set up Sequelize, it’s time to get into more advanced topics.

Sequelize offers a lot of functionality. Developers receive the ability to remain agnostic in regards to the specifics of their database. It also limits the amount of hand-coded SQL code; often hard to debug in the context of a larger application.

Why Are Sequelize Foreign Keys Useful

As implied by its name, object oriented programming is built around the concept of objects. This stands in contrast to how a standard database stores data. NoSQL-based solutions such as MongoDB contain a structure which is a much more accurate representation of an object. On the other hand, the SQL databases used by Sequelize are more like sophisticated spreadsheets. All entries stored in each table is flat. The only means to create parent-child relationships is via foreign keys.

Sequelize fixes this by acting as a mediator between our code and the database. It builds the stored data into full objects, complete with the ability to easily traverse the hierarchy of the relationships stored within. This saves a great deal of time both in development and debugging.

Let’s start with an example. Say you were running an online publication. In this scenario, you have two tables. One holds the author accounts and the other holds their articles.

How would you handle a user requesting every article written by a specific author? Without foreign keys you would have to write one query to get the author’s ID. A second query could then fetch the articles based on this information. However, foreign keys boil this down to one query. A properly marked field allows you to fetch all of the articles written by an author object. These articles are included with the author object itself.

What if one author wrote particularly poor content? You might want to delete their account. Without foreign key constraints, their articles would remain after their account was deleted. As a result, you would have articles with no author. However, a foreign key between the tables gives you the ability to delete articles along with their author. This is handled seamlessly in the database itself.

Types of Sequelize Foreign Keys

One-To-Many Relationships

This forms a one-to-many relationship between the two fields. Many posts can each have one and only one shared author. In this relationship, the ownerID field within each entry of the Posts table stores primary keys from the Users table. The foreign key references a unique user. The database uses this to throw an error if a query attempts to link a post to a nonexistent user.

In this tutorial we’re going to use Sequelize foreign keys to create posts owned by our users. This gives us the ability to pull the posts associated with the user without actually needing to know their id. All it will take is calling the getPosts method on a user object. The library will return an array containing all of their posts.

Writing the Code

We’re going to create a model for both the users as well as the posts. The main difference is, unlike the last tutorial, we now have a relationship between the two via the hasOne() and hasMany() methods.

const Sequelize = require("sequelize");

const databaseConnection = new Sequelize('testDB', 'testUser', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

User = databaseConnection.define('Users', {
  username: { type: Sequelize.STRING },
  password: { type: Sequelize.STRING },
  description: { type: Sequelize.STRING }
});

Post = databaseConnection.define('Posts', {
  title: { type: Sequelize.STRING },
  content: { type: Sequelize.STRING}
});

// Relationships
User.hasMany(Post, {as: 'Posts'});
Post.hasOne(User, {as: 'owner'});

databaseConnection.sync();
Code language: PHP (php)

Creating and Linking a Child

This results in a one-to-many relationship in which many models link back to one owner. These relationships store the foreign key on the children, in this case the posts. As this is a one-to-many relationship, it will also create an ‘owner’ field on the Posts table which will hold the ID of the user which owns the post. Let’s go ahead and create a new user, tying a single post to them.

User.create({
  username: 'testUsr'
}).then((myUser) => {
  Post.create({
    title: 'Test Post',
    content: 'This is the content of the post.'
  }).then((myPost) => {
    myUser.addPost(myPost).then((myUser) => {
      // myUser now contains a reference to myPost
    });
  })
})
Code language: JavaScript (javascript)

At this point myUser contains a reference to myPost. Now, run the following SQL on the database.

USE testDB;
SELECT * FROM Posts;
Code language: PHP (php)

The resulting table should look similar to the following:

id	title	content	createdAt	updatedAt	ownerID
1	My Post	This is the content of the post.	2019-10-09 13:45:57	2019-10-09 13:45:57	1

Now let’s go ahead and do the same for the Users table:

SELECT * FROM Users;

id	username	password	description	createdAt	updatedAt
1	testUsr	NULL	NULL	2019-10-09 13:45:56	2019-10-09 13:45:56

As you can see, the Users table has no equivalent to the ownerID field specified in the Posts table. Regardless of this, we are able to retrieve the post associated with out user as follows:

Users.findOne({
  where: {
    username: 'testUsr'
  }
}).then((myUser) => {
  myUser.getPosts().then((posts) => {
    console.log("Post Title: " + posts[0].title);
    console.log("Content: " + posts[0].content);
  });
});
Code language: JavaScript (javascript)

An important thing to note is that the getPosts() method created by Sequelize works very similarly to the findOne() and findAll() methods. It allows many of the same constraints including offsets and limits, and selection based on where criteria. For example, if we were to write the following:

Users.findOne({
  where: {
    username: 'testUsr'
  }
}).then((myUser) => {
  myUser.getPosts({
    where: {
      title: 'My Post'
    } 
  }).then((posts) => {
    console.log("Post Title: " + posts[0].title);
    console.log("Content: " + posts[0].content);
  });
});
Code language: JavaScript (javascript)

You will notice that the code we previously had written works correctly. However, if we were to change the title specified in the getPosts() method to anything else our program will throw an error as the entry does not exist and so Sequelize returns an empty array.

Many-to-Many Relationships

Now that we have covered one-to-many relationships, we will now use Sequelize foreign keys to handle many to many relationships. Links such as these are good for things such as photo galleries in which many photos can be part of many tags and vice-versa. They are somewhat more complicated in that they require an additional table (called a join table) in order to function correctly.

Setting up the Code

const Sequelize = require("sequelize");

const databaseConnection = new Sequelize('testDB', 'testUser', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

ImagesToTags = databaseConnection.define('ImagesToTags', {});

Image = databaseConnection.define('Images', {
  title: {
    type: Sequelize.STRING
  },
  authorName: {
    type: Sequelize.STRING
  }
});

Tag = databaseConnection.define('Tags' {
  name: {
    type: Sequelize.STRING
  }
});

Image.belongsToMany(Tag, {through: ImagesToTags});
Tag.belongsToMany(Image, {through: ImagesToTags});
Code language: JavaScript (javascript)

Now let’s create some image entries and a few tags to link to them.

Image.create({
  title: 'Image1',
  authorName: 'John Smith'
}).then((image1) => {
  Image.create({
    title: 'Image2',
    authorName: 'Jane Doe'
  }).then((image2) => {
    Tag.create({name: 'tag1'}).then((tag1) => {
      Tag.create({name: 'tag2'}).then((tag2) => {
        Tag.create({name: 'tag3'}).then((tag3) => {
          // At this point we have two images and three tags.
          image1.addTag(tag1);
          image1.addTag(tag3)
          // Image1 is now linked to tags 1 and 3.
          image2.addTag(tag1)
          image2.addTag(tag2)
          // Image2 is now linked to tags 1 and 2.
         });
      });
    });
  });
});
Code language: JavaScript (javascript)

We now have our images and tags created, and have also linked them together.

SELECT * FROM ImagesToTags;

createdAt	updatedAt	ImageId	TagId
2019-10-09 16:09:34	2019-10-09 16:09:34	1	1
2019-10-09 16:09:34	2019-10-09 16:09:34	1	3
2019-10-09 16:09:34	2019-10-09 16:09:34	2	1
2019-10-09 16:09:34	2019-10-09 16:09:34	2	2

As you can see, the join table stores the information making up the relationship. Neither the Images nor the Tags tables have foreign keys within them. Nonetheless, we are able to utilize this relationship in very similar ways to the one-to-many relationship discussed earlier.

Next, we will retrieve all images associated with a tag.

Tag.findOne({
  where: {
    name: 'tag1'
  }
}).then((tag1) => {
  tag1.getImages().then((images) => {
    for(var c = 0; c != images.length; c++) {
      console.log("Image #" + c + ":");
      console.log("Title: " + images[c].title);
      console.log("Author: " + images[c].authorName);
    }
  });
});
Code language: JavaScript (javascript)

This code will loop through all of the images associated with the specified tag. The query can also run in reverse. From an image, it is possible to retrieve the tags associated with it.

Image.findOne({
  where: {
    title: "Image1"
  }
}).then((image1) => {
  image1.getTags().then((tags) => {
    for(var c = 0; c != tags.length; c++) {
      console.log("Tag: " + tags[c].name);
    }
  });
});
Code language: JavaScript (javascript)

Conclusion

Sequelize represents a major improvement over traditional SQL code in software. By forgoing setting foreign keys manually, and instead treating all relationships as a link between two models, Sequelize allows much cleaner links between tables.