Sequelize Foreign Keys

      No Comments on 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 which Sequelize has on offer. Now that we have covered the very basics of the library, it’s time to get into more advanced topics.

As an ORM, Sequelize has a lot to offer. It allows developers to remain agnostic in regards to database implementations. Additionally, Sequelize also allows us to limit the amount of SQL code written by hand. SQL can often be messy. It is a completely separate language than Javascript. As a result, programmers often have difficulty making the mental shift between the two. Unfortunately, this is a jump that has to be made often during development.

Why Are Sequelize Foreign Keys Useful

Sequelize allows us to treat our database as object storage rather than flat tables. It performs the work of reassembling objects on our behalf. This saves a great deal of time both in development and debugging.

The killer feature of relational databases is in their name. Linking tables together is extremely powerful. Databases perform this linkage via foreign keys. Foreign keys are specially marked in the database. They hold a unique identifier to a row in another table which can be referenced in code.

Establishing a foreign key has many advantages. Foreign keys can propagate changes to their children. For 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, Sequelize 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:

idtitlecontentcreatedAtupdatedAtownerID
1My PostThis is the content of the post.2019-10-09 13:45:572019-10-09 13:45:571

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

SELECT * FROM Users;
idusernamepassworddescriptioncreatedAtupdatedAt
1testUsrNULLNULL2019-10-09 13:45:562019-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;
createdAtupdatedAtImageIdTagId
2019-10-09 16:09:342019-10-09 16:09:3411
2019-10-09 16:09:342019-10-09 16:09:3413
2019-10-09 16:09:342019-10-09 16:09:3421
2019-10-09 16:09:342019-10-09 16:09:3422

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.

Leave a Reply

Your email address will not be published. Required fields are marked *