Eager Loading: Fetching Associated Data with Sequelize

I just built a database using Sequelize for the first time. I had to solve the problem of not just fetching one record from the database, but much of its associated data as well. My first attempt was long and unwieldy, and involved nested Promises, and mapping, and manually adding properties to the AJAX response object -- it was kind of a mess! And...it didn't work.

I spent about an hour Googling around the topic of eager loading, but not quite landing on it. It's amazing how much a vocabulary limitation can hinder efficient research. Once eager loading entered my scope, it was smooth sailing. (Okay, not really-- but it did make my life a lot easier).

Do not fear the jargon

Eager loading in Sequelize is similar to a JOIN in raw SQL queries. It just means that with a few extra lines of code, you can fetch a given table AND its associated data. Per the Sequelize docs, it basically just means joining two tables.

The problem

So for our schema, we have, among other things, a Show model. Each show has a few associations, or relationships: one Venue where the show takes place, and potentially many Bands who are playing at the show:

• A show takes place at one venue, and a venue can have many shows (one to many
• A show can have several bands, and a band can play many shows (many to many)

The task: using the Sequelize ORM, fetch the info for an individual show, plus its associated venue and bands.

The associations

Venues and shows (one-to-many)

Show.belongsTo(Venue, { foreignKey: { name: 'id_venue', allowNull: false } });
Venue.hasMany(Show, { foreignKey: { name: 'id_venue', allowNull: false } });

I wanted to be able to query for a venue and its associated shows, as well as query a show for its associated venue, so I needed to declare the relationship in both directions. Initially, I didn't not include the Venue.hasMany(Show), and thus, when I attempted a search for a venue and its associated shows, I got the error show is not associated to venue!

So if you want to query bidirectionally, declare the associations bidirectionally.

Shows and bands (many-to-many)

Show.belongsToMany(User, { as: 'bands', through: ShowBand, foreignKey: { name: 'id_show', allowNull: false } });
User.belongsToMany(Show, { through: ShowBand, foreignKey: { name: 'id_band', allowNull: false } });

When declaring many-to-many associations, Sequelize requires a through parameter, in which you pass in the name of the join table that represents that many-to-many relationship. (You can create your own model, as I did, but if you don't, Sequelize will auto-generate that join table for you-- but you still have to give it a name in through).

I also wanted to declare an alias for how the User model actually related to the Show table. My User table contains two types: Fans and Bands. And I wanted to make it clear that only the users who were of type Band belonged to Shows in this particular way. So with as, I gave User an alias of bands.

Again, you'll want to declare the relationship in both directions.

The queries

Retrieve a single show and its associated bands and venue

// get detailed info for single show
const getSingleShow = async (req, res) => {
    try {
        const { id } = req.params;
        const show = await Show.findOne({
            where: {
                id
            },
            include: [
                { model: User, as: 'bands', attributes: ['id', 'name'] },
                { model: Venue, attributes: ['name'] },
            ]
        })
        res.send(show);
    }
    catch () {
        res.sendStatus(400);
    }
}

The function above is for retrieving information about a show given its id, passed in through the endpoint parameters.

The include is the important part of eager loading. We find the given show, then with include, we can request any associated info we want. (Note that we must specify the alias that we assigned the particular related record in our association declaration.)

We can also filter out what's returned with the attributes keyword. In this case, I'm just returned the name and id of the venue and each band.

My returned object looks something like this:

{
     "id": 4,
    "name": "cool show",
    "date": "7/20",
    "time": "9:00PM",
    "bands": [
        {
            "id": 5,
            "name": "The Yeahs",
        },
        {
            "id": 6,
            "name": "Shark Attack",
        }
    ],
    "venue": {
        "id": 1,
        "name": "gasa"
}

You can see that the show bands are returned as an array with a plural name, because there could be many bands that belong to a show. Venue, on the other hand, is a singular object, because a show can only have one venue.

Similarly, we can perform a query for a venue and its associated shows, and nest another include to get back those shows' associated bands.

Here's the query:

const getSingleVenue = async (req, res) => {
    try {
        const { id } = req.params;
        const venue = await Venue.findOne({
            where: {
                id
            },
            attributes: ['id', 'name'],
            include: [
                { model: Show, 
                    include: [
                        { model: User, as: 'bands', attributes: ['id', 'name'] }
                    ]
                }
            ]
        })
        res.send(venue);
    }
    catch() {
        res.sendStatus(400);
    }
}

Here's the result:

{
    {
        "id": 1,
        "name": "gasa",
        "shows": [
            {
                "id": 1,
                "name": "cool show",
                "date": "7/20",
                "time": "9:00PM",
                "bands": [
                    {
                        "id": 1,
                        "name": "The Yeahs",
                    }
                ]
            },
            {
                "id": 2,
                "name": "another cool show",
                "date": "7/20",
                "time": "9:00PM",
                "bands": [
                    {
                        "id": 1,
                        "name": "The Yeahs",
                    },
                    {
                        "id": 2,
                        "name": "Shark Attack",
                    }
                ]
            }
        ]
    }
}

For some perspective, here's the raw SQL query that is performed from the getSingleVenue Sequelize query above:

SELECT `venue`.`id`, `venue`.`name`, `shows`.`id` AS `shows.id`, `shows`.`name` AS `shows.name`, `shows`.`date` AS `shows.date`, `shows`.`time` AS `shows.time`, `shows`.`photo` AS `shows.photo`, `shows`.`description` AS `shows.description`, `shows`.`createdAt` AS `shows.createdAt`, `shows`.`updatedAt` AS `shows.updatedAt`, `shows`.`id_venue` AS `shows.id_venue`, `shows->bands`.`id` AS `shows.bands.id`, `shows->bands`.`name` AS `shows.bands.name`, `shows->bands->shows_band`.`createdAt` AS `shows.bands.shows_band.createdAt`, `shows->bands->shows_band`.`updatedAt` AS `shows.bands.shows_band.updatedAt`, `shows->bands->shows_band`.`id_show` AS `shows.bands.shows_band.id_show`, `shows->bands->shows_band`.`id_band` AS `shows.bands.shows_band.id_band` FROM `venues` AS `venue` LEFT OUTER JOIN `shows` AS `shows` ON `venue`.`id` = `shows`.`id_venue` LEFT OUTER JOIN ( `shows_bands` AS `shows->bands->shows_band` INNER JOIN `users` AS `shows->bands` ON `shows->bands`.`id` = `shows->bands->shows_band`.`id_band`) ON `shows`.`id` = `shows->bands->shows_band`.`id_show` WHERE `venue`.`id` = '1';

Nuts, huh? So, if your schema features a number of associations and you want to perform efficient but complicated join queries, an ORM like Sequelize is a great way to make that happen.

Comments

[Tyler]

is there a way to achieve this without using "Include"