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 add semantics to data, as well as to link records together. Internally, this is facilitated by the database in the form of foreign key relationships. Foreign keys are 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 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 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

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

At this point myUser contains a reference to myPost. Now, 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.

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

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

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 *