Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
using Include in sequelize

'Include' in Sequelize: The One Confusing Query That You Should Memorize

When querying your database in Sequelize, you'll often want data associated with a particular model which isn't in the model's table directly. This data is usually typically associated through join tables (e.g. a 'hasMany' or 'belongsToMany' association), or a foreign key (e.g. a 'hasOne' or 'belongsTo' association).

When you query, you'll receive just the rows you've looked for. With eager loading, you'll also get any associated data. For some reason, I can never remember the proper way to do eager loading when writing my Sequelize queries. I've seen others struggle with the same thing.

Eager loading is confusing because the 'include' that is uses has unfamiliar fields is set in an array rather than just an object.

So let's go through the one query that's worth memorizing to handle your eager loading.

The Basic Query

Here's how you would find all the rows of a particular model without eager loading.

Albums.findAll()
.then(albums => console.log(albums))
.catch(console.error)

Vanilla Include

Here's how you would find all the Artists associated with your albums.

Albums.findAll({
  include: [{// Notice `include` takes an ARRAY
    model: Artists
  }]
})
.then(albums => console.log(albums))
.catch(console.error)

This will take you a long way. Sequelize is smart enough to pull in any rows from your Artists model that are associated with your Albums.

Include takes an array of objects. These objects have properties like model, as, and where which tell Sequelize how to look for associated rows.

Customized Include with an Alias

Now, let's customize how we receive our eagerly-loaded rows.

Albums.findAll({
  include: [{
    model: Artists,
    as: 'Singer' // specifies how we want to be able to access our joined rows on the returned data
  }]
})
.then(albums => console.log(albums))
.catch(console.error)

In this query, we have specified that the instances we receive back from Sequelize should have a property called 'Singer'. We'll be able to access any rows from our Artists table associated with our Albums through this .Singer property.

Customized Include with Alias and Where

Finally, let's layer a where onto our include, so we can narrow down the rows that we'll receive.

Albums.findAll({
  include: [{
    model: Artists,
    as: 'Singer',
    where: { name: 'Al Green' } //
  }]
})
.then(albums => console.log(albums))
.catch(console.error)

Our where query should look familiar if you've used Sequelize before: it has the same format as any typical Sequelize query. Our query will now only return joined rows where 'Al Green' is the name of the associated artist. We can access Al Green's artist data for relevant Album instances on our aliased .Singer property.

To wrap up, include takes an array of objects. These objects are queries of their own, essentially just Sequelize queries within our main query. Inside each include query we specify the associated model, narrow our results with where, and alias our returned rows with as. Memorizing model, where, as, and that include takes an array will make your next experience with eager loading much more pleasant.

@ajbraus
Copy link

ajbraus commented Dec 7, 2018

Found a fix to this ^^^. just add nested: true to the query options.

@luisfrocha
Copy link

luisfrocha commented Feb 14, 2019

I have an extension of this problem. I'm trying to associate two tables on different, disparate databases altogether. Below is info:
DB1:

// PostgreSQL
CREATE TABLE "public"."CourseCatalog" (
    "courseid" varchar(255) NOT NULL,
    "title" varchar(255) NOT NULL,
    PRIMARY KEY ("courseid")
);

DB2:

// MySQL DB
CREATE TABLE `completions` (
  `member_id` varchar(10) NOT NULL,
  `module_id` varchar(10) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

where DB2.module_id = DB1.courseid. So, what I'm trying to do is something like the below:

db1.completions.findAll({
  include: [
    {
      model: db2.CourseCatalog,
      where: { db1.completions.module_id = db2.CourseCatalog.courseid }
    }
  ],
  group: 'module_id',
  where: {...}
});

I know this line above doesn't work, but it's simply to show my intent. Is it even possible to achieve this?

@manojbharti
Copy link

manojbharti commented Feb 24, 2019

I have one product table and another is stock of products table but i am getting this result by joining these both tables using "include", I want both table's specific attributes in one object how can I do that please help. Thanks in advance
"data": [
{
"id": 1,
"product_id": 1,
"product_code": "sun1200",
"stock_quantity": 1200,
"is_del": 0,
"created_by": 1,
"modified_by": null,
"createdAt": "2019-02-24T16:40:17.000Z",
"updatedAt": null,
"product": {
"id": 1,
"product_name": "Sun-Glass",
"product_code": "sun1200",
"mrp_price": 120000,
"product_img": "",
"discount": 2000,
"category": "eye-glass",
"subcategory": "eye-glass",
"model_no": "sunglass1222",
"tax": 20,
"created_by": 1,
"modified_by": null,
"createdAt": "2019-01-19T13:30:25.000Z",
"updatedAt": "2019-01-19T13:30:25.000Z"
}
}
]

@bobber205
Copy link

bobber205 commented Mar 5, 2019

Does include support ordering the results like the find() queries do via

order: [['updatedAt', 'DESC']]

@Jadex1
Copy link

Jadex1 commented Apr 5, 2019

This has been very helpful. does include work on findByPk or findOne? I have a business and I always want to retrieve the business address, address is a separate table.

@patryk-wlaz
Copy link

patryk-wlaz commented May 15, 2019

@Jadex1 yeah, according to docs you can pass option object to findByPk, which I just did and it worked ;)

const ownerWithDoggo = await this.ownerEntity.findByPk<OwnerEntity>(ownerId, {
  include: [
    {
      model: DoggoEntity,
    },
  ],
});

@waldyrious
Copy link

waldyrious commented Jun 17, 2019

@zcaceres I think this sentence needs a little reworking:

Eager loading is confusing because the 'include' that is uses has unfamiliar fields is set in an array rather than just an object.

It seems that it has two possible terminations:

  • has unfamiliar fields

  • is set in an array rather than just an object.

Did you mean to use only one of these? Or maybe combine them with an "and"?

Eager loading is confusing because the 'include' that is uses has unfamiliar fields, and it is set in an array rather than just an object.

@avtarnanrey
Copy link

avtarnanrey commented Jun 27, 2019

@bprivateer to include more than one model with include, you would add them to the include array.
Let's say you had another association called Genre that you wanted to include with your Album query, it would look something like this:

Album.findAll({
  include: [
    {
      model: Artist,
      as: 'Singer',
    },
    {
      model: Genre
    }
]

To include all associations use include: [{all: true}]

Extra:

If your Genre model has a many-to-many association with model Album by a join table called albumgenre (i.e. the join table contains all the Album records associated with that specific Genre) and you didn't want to fetch all attributes from the join table albumgenre with your query. You can then pass through: { attributes: [] } with your include.

Album.findAll({
  include: [
    {
      model: Artist,
      as: 'Singer',
    },
    {
      model: Genre
      through: { attributes: [] }
    }
]

Thank you for your help. You should write your own blog post for most common associations queries.

@mnezmah
Copy link

mnezmah commented Jul 17, 2019

If you have nested relations for example:

Group.belongsTo(Invite)
Invite.belongsTo(Issue)

then your query looks like this:

Issue.find({
    include: [
        {
            model: Invite,
            include: [Group]
        }
    ]
});

@bprivateer
Copy link

bprivateer commented Jul 17, 2019

@jamesperi
Copy link

jamesperi commented Nov 6, 2019

I have an interesting problem related to your example, maybe you have some experience with dealing with where conditionals and the include?

image

@patryk-wlaz
Copy link

patryk-wlaz commented Nov 7, 2019

@jamesperi try experimenting with required: false in your include, like this:

// the beginning part
include: [{
        model: mdls.LabSizing,
        required: false,
// the rest part

I think this should get RequestGenerals managed or submitted by John with sizings connected to request and without sizings if request doesn't have any, although I didn't test it and I am not sure if that puts OR in the desired place ;)

@DylKid
Copy link

DylKid commented Feb 7, 2020

Ran into this one today, couldn't find it anywhere online:

If you want to write a query where you want to filter the results of the query based on a column in a foreign key's table, e.g. get all the Albums with the genre 'rock', but don't return the album you can do it like this:

Album.findAll({
  include: [
    {
      model: Genre,
      through: { 
        where: {
          name: 'rock'
      },
      required: true,
      attributes: []
    },
  ]
)}

@ShakeelAhmadDev
Copy link

ShakeelAhmadDev commented Apr 13, 2020

//User.js Model
const db = require('../config/db');
const seq = require('sequelize');

const User = db.define(
'User',
{
user_id:{
type:seq.INTEGER,
primaryKey: true,
autoIncrement: true
},
user_name:{
type :seq.STRING
}
},
{
freezeTableName: true ,
timestamps: false,
}
)

const Product = require('./product');
User.removeAttribute('id');
// User.belongsTo(Product); commented..
module.exports = User;

//Product.js Model
const db = require('../config/db');
const User = require('./user');
const seq = require('sequelize');

const Product = db.define(
"product",
{
product_id:{
type:seq.INTEGER,
primaryKey: true,
autoIncrement: true
},
product_name:{
type:seq.STRING
},
user_id:{
type:seq.INTEGER
}
},
{
freezeTableName: true ,
timestamps: false,
}
)
Product.removeAttribute('id');
Product.hasMany(User)
module.exports = Product;

//Getting Product... Nothing to return...
router.get('/getproducts' , async (req,res)=>{
try{
let pr = await Product.findAll({
// include:[{all: true}]
include:{
model:'User'
}
})

    return res.send(pr);
}
catch(err){
    return res.send(err)
}

})

@linxingwu
Copy link

linxingwu commented Jun 1, 2020

When Artists is null ,I want it looks likes

Album{
  id:1
  Artists :{
   id :null
 }
}

is there any option?

@ail3ngrimaldi
Copy link

ail3ngrimaldi commented Jul 15, 2020

Thanks, it helped me a lot.

@mrhpn
Copy link

mrhpn commented Jul 26, 2020

Do you know?

We can access wanted attributes via through. But when we have 50 other properties and when we want to exclude only 2 attributes, how can we exclude those unwanted attributes?

@mrhpn
Copy link

mrhpn commented Jul 26, 2020

Thanks.

include: [ { all: true, attributes: { exclude: ["createdAt", "updatedAt"] } }, ],

@kailashyogeshwar85
Copy link

kailashyogeshwar85 commented Jan 1, 2021

how to use limit, offset in include query ?

@mrhpn
Copy link

mrhpn commented Jan 1, 2021

Please give it a try.
user.findAll({ offset: 5, limit: 5, ... })

@TamimEhsan
Copy link

TamimEhsan commented Feb 8, 2021

How can I remove nesting when including columns from multiple table?
raw: true
option gives something like Model_name.column_name, But I want just column_name
image
How can I just get autor_id from here?

@fasikaWalle
Copy link

fasikaWalle commented Mar 18, 2021

Thank you

@ajbraus
Copy link

ajbraus commented Mar 28, 2021

What about if the foreign key is different from the associated model name. E.g. I have a Trip model with a destinationId and a originId columns that both associate with a City model. So how do I include the destination and the origin of the trip. i.e. two city records?

@ricardoscotti
Copy link

ricardoscotti commented May 28, 2021

Hello,

I have a get with sequelize, which I'm using an include, as an association is being made, and the returning is coming as:

{"nome": "digao", "nota": 10}
{"nome": "digao", "nota": 9}

but, I would need it to be returned as

{
“nome”: “digao”,
“notas”: [{“nota”: 9},{“nota”: 10}]
}

Any ideas how could I normalize it?
Any tought is highly appreciated

@KOD3X
Copy link

KOD3X commented Jun 8, 2021

hello, I have the following problem, I have the people model that indicates the role of a person in a project, when I try to use the include I get an error. Please i need help.

module.exports = (sequelize, type) => {
    return sequelize.define("people", {
        userId: {
            type: type.INTEGER,
            allowNull: false,
            primaryKey: true,
            onDelete: "CASCADE",
            references: {
                model: "users",
                key: "id",
            },
        },
        rolId: {
            type: type.INTEGER,
            allowNull: false,
            primaryKey: true,
            onDelete: "CASCADE",
            references: {
                model: "roles",
                key: "id",
            },
        },
        projectId: {
            type: type.INTEGER,
            allowNull: false,
            primaryKey: true,
            onDelete: "CASCADE",
            references: {
                model: "projects",
                key: "id",
            },
        },
    });
};

and I am trying to do this:

const test = await People.findAll({
        where: {
            userId: req.userId,
        },
        include: [
            {
                model: Project,
            },
        ],
    });
    console.log(test);

but I'm getting this error:

(node:3952) UnhandledPromiseRejectionWarning: SequelizeEagerLoadingError: project is not associated to people!

is it required to use the belongsTo?

@GioAceto
Copy link

GioAceto commented Jun 20, 2021

Thank you, this was very helpful.

@dvalley56
Copy link

dvalley56 commented Jul 13, 2021

I have one product table and another is stock of products table but i am getting this result by joining these both tables using "include", I want both table's specific attributes in one object how can I do that please help. Thanks in advance
"data": [
{
"id": 1,
"product_id": 1,
"product_code": "sun1200",
"stock_quantity": 1200,
"is_del": 0,
"created_by": 1,
"modified_by": null,
"createdAt": "2019-02-24T16:40:17.000Z",
"updatedAt": null,
"product": {
"id": 1,
"product_name": "Sun-Glass",
"product_code": "sun1200",
"mrp_price": 120000,
"product_img": "",
"discount": 2000,
"category": "eye-glass",
"subcategory": "eye-glass",
"model_no": "sunglass1222",
"tax": 20,
"created_by": 1,
"modified_by": null,
"createdAt": "2019-01-19T13:30:25.000Z",
"updatedAt": "2019-01-19T13:30:25.000Z"
}
}
]

Hello mate, I myself want something like this, did you find any solution?

@lexyfeitopayrix
Copy link

lexyfeitopayrix commented Sep 8, 2021

This doesnt seem to be working when using association instead of model

[{ association: 'relatedUser', as: 'user' }]

anyone knows if this is possible?

@imongault
Copy link

imongault commented Nov 11, 2021

very helpful btw

@AnkurBansal18
Copy link

AnkurBansal18 commented Mar 28, 2022

hello, I have the following problem, I have the people model that indicates the role of a person in a project, when I try to use the include I get an error. Please i need help.

module.exports = (sequelize, type) => {
    return sequelize.define("people", {
        userId: {
            type: type.INTEGER,
            allowNull: false,
            primaryKey: true,
            onDelete: "CASCADE",
            references: {
                model: "users",
                key: "id",
            },
        },
        rolId: {
            type: type.INTEGER,
            allowNull: false,
            primaryKey: true,
            onDelete: "CASCADE",
            references: {
                model: "roles",
                key: "id",
            },
        },
        projectId: {
            type: type.INTEGER,
            allowNull: false,
            primaryKey: true,
            onDelete: "CASCADE",
            references: {
                model: "projects",
                key: "id",
            },
        },
    });
};

and I am trying to do this:

const test = await People.findAll({
        where: {
            userId: req.userId,
        },
        include: [
            {
                model: Project,
            },
        ],
    });
    console.log(test);

but I'm getting this error:

(node:3952) UnhandledPromiseRejectionWarning: SequelizeEagerLoadingError: project is not associated to people!

is it required to use the belongsTo?

Yes, you need to define a association between people and project model to use include in sequelize.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment