Sequelize: Foreign Keys

      No Comments on Sequelize: Foreign Keys

How Foreign Keys Work

One of the key features which make relational databases so powerful is their ability to relate bits of data to each other. This is done via foreign keys, fields in a table which store a reference to fields in another table. For example, if you were developing a custom forum, you would have multiple users. Each user would have the ability to make one or more posts on the forum, each of which would have said user as their owner. In relational databases such as MySQL, you would have a model similar to the following:

One-To-Many Relationships

This is what is referred to as a one-to-many relationship. Many posts can each have one and only one shared author. In this case, the ownerID field of the Posts table is marked as a foreign key which ties back to the id field of the Users table. As the id field is set as the primary key of the table, each value it contains is unique. In this way, the foreign key will always reference a unique user and in addition, the database will throw an error if a query attempts to link a post to a nonexistent user.

In this tutorial we’re going to use Sequelize’s support of foreign keys to create posts owned by our users. This will provide us with the ability to pull the posts associated with the user without actually needing to know their id. Simply pulling the user and calling the getPosts method on the resulting object will return us an array with all of their posts contained within.

Writing the Code

To get started, let’s go ahead and set up our starting 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();

Creating and Linking a Child

This results in a one-to-many relationship in which many models link back to one owner. In this form of relationship the foreign key will be created 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
    });
  })
})

At this point myUser contains a reference to myPost. Go ahead and run the following SQL on the database.

USE testDB;
SELECT * FROM Posts;

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);
  });
});

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);
  });
});

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, it is time to cover 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});

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.
         });
      });
    });
  });
});

We now have our images and tags created, and have also linked them together. Let’s check to make sure that the join table has been populated correctly.

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 is used to store the information which makes 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);
    }
  });
});

This code will loop through all of the images associated with the specified tag. This can also been flipped around so that an image can be used 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);
    }
  });
});

Conclusion

Compared to traditional SQL, Sequelize allows foreign keys to be managed in a much easier way. 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 *