Sequelize: Association with alias does not exist

Question

I am using sequelize as ORM in a node project and running into some issues when querying.

Below is the code for the sequelize models in question. The table names are singular--see the use of freezable: true in the model. But when I use include it does not work and fails with the following error message

Error: Association with alias "Client" does not exist on Invoice

Model - client

module.exports = (sequelize, DataTypes) => {
const Client = sequelize.define("Client", {
    Id: {
        primaryKey: true,
        type: "INTEGER",
        autoIncrement:true
    },

    Name:{
        type: "VARCHAR(250)",
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },

    AddressLine1:{
        type: "VARCHAR(500)",
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },

    AddressLine2:{
        type: "VARCHAR(500)",
        allowNull: true,
    },

    AddressLine3:{
        type: "VARCHAR(500)",
        allowNull: true,
    },

    Postcode:{
        type: "VARCHAR(10)",
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },

    City:{
        type: "VARCHAR(100)",
        allowNull: true,
    },

    
    County:{
        type: "VARCHAR(50)",
        allowNull: true,
    },


    Country:{
        type: "VARCHAR(100)",
        allowNull: true,
    },

    ContactNumber : {
        type: "VARCHAR(20)",
        allowNull: true,
    },


    Email : {
        type: "VARCHAR(500)",
        allowNull: true,
    },

    CreatedAt :{
        type:"datetimeoffset(7) DEFAULT GETDATE()",
        allowNull: false
    },

    UpdatedAt :{
        type:"datetimeoffset(7)",
        allowNull: true
    }

},
{freezeTableName: true,  createdAt: false,updatedAt: false}
);

Client.associate=models=>{
    Client.hasMany(models.Invoice,{foreignKey:"ClientId"})
}

  return Client;
}

Model Invoice

module.exports = (sequelize, DataTypes) => {
const Invoice = sequelize.define("Invoice", {
    Id: {
        primaryKey: true,
        type: "INTEGER",
        autoIncrement:true
    },

    ClientId: {
        type: DataTypes.INTEGER,
    },

    CompanyId: {
        type: DataTypes.INTEGER,
    },

    InvoiceNumber:{
        type: "VARCHAR(10)",
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },

    InvoiceDate : {
        type: DataTypes.DATEONLY,
    },

    Total: {
        type: DataTypes.DECIMAL(18,2),
        allowNull: true
    },


    CreatedAt :{
        type:"datetimeoffset(7) DEFAULT GETDATE()",
        allowNull: false
    },

    UpdatedAt :{
        type:"datetimeoffset(7)",
        allowNull: true
    }

},
{freezeTableName: true,  createdAt: false,updatedAt: false}   );

Invoice.associate=models=>{
    Invoice.belongsTo(models.Client,{
        foreignKey:{
            name:"ClientId",
            allowNull:false
        }
    })
};

Invoice.associate=models=>{
    Invoice.belongsTo(models.Company,{
        foreignKey:{
            name:"CompanyId",
            allowNull:false
        }
    })
}

Invoice.associate=models=>{
    Invoice.hasMany(models.InvoiceDetails,{foreignKey:"InvoiceId"})
}

  return Invoice;
}

Query

let invoiceResult= await db.Invoice.findOne({where: {"InvoiceNumber":"INV001"}
                                ,include:"Client"});

The above query does not work. However, the following one works

let invoiceResult= await db.Invoice.findOne({where: {"InvoiceNumber":"INV001"} ,include:"InvoiceDetails"});

The model for InvoiceDetails is as follows

module.exports = (sequelize, DataTypes) => {
const InvoiceDetails = sequelize.define("InvoiceDetails", {
    Id: {
        primaryKey: true,
        type: "INTEGER",
        autoIncrement:true
    },

    InvoiceId: {
        type: DataTypes.INTEGER,
    },


    Description:{
        type: DataTypes.STRING(500),
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },

    Quantity:{
        type: DataTypes.INTEGER,
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },

    AmountPerUnit : {
        type: DataTypes.DECIMAL(18,2),
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },

    SubTotal: {
        type: DataTypes.DECIMAL(18,2),
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },

    VatPercentage: {
        type: DataTypes.DECIMAL(5,2),
        allowNull: false,
        validate: {
            notEmpty: true
        }
    },

    VatAmount: {
        type: DataTypes.DECIMAL(18,2),
        allowNull: false,
        validate: {
            notEmpty: true
        }
    }

    ,

    CreatedAt :{
        type:"datetimeoffset(7) DEFAULT GETDATE()",
        allowNull: false
    },

    UpdatedAt :{
        type:"datetimeoffset(7)",
        allowNull: true
    }

},
{freezeTableName: true,  createdAt: false,updatedAt: false}
);

InvoiceDetails.associate=models=>{
    InvoiceDetails.belongsTo(models.Invoice,{
        foreignKey:{
            name:"InvoiceId",
            allowNull:false
        }
    })
}
 return InvoiceDetails;
}

Any pointers will be much appreciated.

Answer 1

i fixed it with the following changes

in the client model changed the following

Client.associate=models=>{
    Client.hasMany(models.Invoice,{as:'Invoice',foreignKey:"ClientId"})
}

and in the invoice model

    Invoice.associate=models=>{
    Invoice.belongsTo(models.Client,{
        as: 'Client',
        foreignKey:{
            name:"ClientId",
            allowNull:false
        }
    })
};

After this change both the following queries work. The point being when using include as alias needs to be present in both the models. Atleast that's how I got it working.

let invoiceResult= await db.Invoice.findOne({where: {"InvoiceNumber":invoiceNumber}
                                ,include:[
                                    "Client"
                                ]
                            });

let client= await db.Client.findAll({include:[
                                "Invoice"
                            ]
                        });

Thanks for the help and taking the time to post an answer

Answer 2

When using the "include" option in a query, you need to include a model object and not just the name of the table/model.

As an example I'll use a mock controller "InvoiceController.js":

const {Invoice} = require("Path to the invoice model") // or = 'db.Invoice'
const {Client} = require("Path to the client model") // or '= db.Client'

module.exports = {
    async indexInvoiceWithClient (req, res) {
        try {
            let invoiceResults = await Invoice.findOne({
                where: {
                    InvoiceNumber: "INV001" // req.query.invoiceNum if you request it over http/s
                },
                include: {
                    model: Client
                }
            })

            console.log(invoiceResults)
            
            res.status(200).send(invoiceResults)
        } catch (err) {
            console.log(err)
            res.status(500).send({
                error: "An error occured while fetching the clients on this Invoice etc..."
            })
        }
    }
}

Answer 3

I think this two way can help you

1: Sequelize pluralized models try "Clients" instead

let invoiceResult= await db.Invoice.findOne({where: {"InvoiceNumber":"INV001"}
                                ,include:"Clients"});

2: if "Clients" not work try this

let invoiceResult= await db.Invoice.findOne({where: {"InvoiceNumber":"INV001"}
                                ,include:[{model: Client, as: "client"}]});