Setting Sequelize associations in Node - Postman throwing constraint errors - Phpmyadmin

JamesGee
April 8, 2021
211 views
1 vote
2 Answers

I am trying to set my Sequelize associations correctly so that all my "include" data is pulled when I make requests. I have seen the information in the docs relating to this however it is a bit hard to understand without seeing it in an app.

I previously had associations in however they never pulled the foreign key values and I only had "include all" in the API’s. I now have more specific "include: ["articleType"] at the start of my data pulls. As I am working without migrations I am not sure if I am updating pgAdmin correctly (I am only putting foreign keys in for ones that I am able as making a foreign key out of a primary key is not allowed due to the unique constraint error).

Ideally I just want to get my backend API’s to pull the foreign key values the most efficient way as I can’t figure out where I am going wrong. I don’t have migrations as this is just a project so this could be a cause if the information isn’t being duplicated in Pgadmin.

Firstly in PGadmin how should I reflect the constraints that I have in my models? can these be done manually? I can’t see the manyToMany, belongTo etc only the simple foreign key restraint to a primary key on another table.

Secondly, on my models below do I need keys declared on both related models? In PGadmin I am unable to declare a foreign key on a Primary key whereas it works fine from another table to a primary key.

Is there a way to manually add in SQL joins even with Sequelize so that the correct foreign key values are included in the JSON? I have previous experience with PHP / PHPmyAdmin and it was much easier with that. From what I can see my articles model is referencing the articleType model both on Pgadmin and my files in my backend and I am also referencing them in my controller API (which seems to be causing the error as the other API’s work correctly without the "include" part in the method).

article model

    const article = sequelize.define("articles", {

            articleID: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                autoIncrement: true,
                allowNull: false
            },
            articleTitle: {
                type: Sequelize.TEXT
            },
            articleContent: {
                    type: Sequelize.TEXT
            },
            photos: {
                type: Sequelize.TEXT
            },
            userID: {
                type: Sequelize.INTEGER
            },
            articleTypeID: {
                type: Sequelize.INTEGER
            }},
            {
                timestamps: false
            }, {});
            article.associate = function(models) {
                article.hasMany(models.articleType, {
                    foreignKey: "articleTypeID",
                    as: "articleType_FK"});
                article.hasMany(models.userLogin, {
                    foreignKey: "userID",
                    as: "userLogin_FK"});
            }
    return article;

articleType model

    const articleType = sequelize.define("articleTypes", {

            articleTypeID: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                autoIncrement: true,
                allowNull: false,
            },
            articleType: {
                type: Sequelize.TEXT
            }},
        {
            timestamps: false
        },{});
        articleType.associate = function(models) {
            articleType.belongsTo(models.article, {
                foreignKey: "articleTypeID",
                as: "article",
            });
        }
    return articleType;
}

article controller

exports.articleList = (req, res) => {
    // article.findByPk(articleID, { include: ["articleType"] });
    article.findAll({
        **include: [{ model: articleType, as: 'fki_articleType_FK'},
                  { model: userLogin, as: 'fki_userID_FK'}]
    })**
        .then((article) => {
            const articleList = [];

Update

I had previously created a number of associations in my index.js file in the models folder however I now only have these in the actual model. Do these need to put in both?

Answers

I probably think the issue is the model name that you provide to seuqelize and afterwards that you use in Association.

 const article = sequelize.define("articles", {...

This results in a model named – articles accessible via models.articles for your associations.
Issue occurs here:

articleType.associate = function (models) {
    articleType.belongsTo(models.article, { // models.article will be undefined
        foreignKey: "articleTypeID",
        as: "article",
    });

// This below should rather work
articleType.associate = function (models) {
    articleType.belongsTo(models.articles, { // models.articles exist as you told Sequelize to name it with that extra 's'
        foreignKey: "articleTypeID",
        as: "article",
    });
}
}

But better mention the name of model without s so that it never creates issues and confusion

So what will work?:

// ------------------------------------------------------------------
// models/article.js
const article = sequelize.define("article", {
  //attributes
}, {
  // options
});
article.associate = function (models) {
    article.hasMany(models.articleType, {
        foreignKey: "articleTypeID",
        as: "articleType_FK"
    });
  // other associations
}
return article;

// ------------------------------------------------------------------
// models/articleType.js
const articleType = sequelize.define("articleType", {
    // attributes
}, {
    // options
});
articleType.associate = function (models) {
    articleType.belongsTo(models.article, {
        foreignKey: "articleTypeID",
        as: "article",
    });
}
return articleType;

// ------------------------------------------------------------------
// controller
exports.articleList = (req, res) => {
    // article.findByPk(articleID, { include: ["articleType"] });
    article
        .findAll({
            include: [{
                model: articleType, as: 'articleType_FK',
            }, {
                model: userLogin, as: 'userID_FK',
            }],
        })
        .then((articles) => {
            console.log('articles', articles);
        })
}

Try below code, Hopefully your issue will resolve.

module.exports = (sequelize, DataTypes) => {
    const ArticleTypes = sequelize.define(
        'articleTypes',
        {
            uuid: {
                type: DataTypes.UUID,
                defaultValue: DataTypes.UUIDV4,
                unique: true
            }
            // Your article type schema ...
        },
        {
            timestamps: true,
            schema: 'articleTypes'
        }
    );
    return ArticleTypes;
};
module.exports = (sequelize, DataTypes) => {
    const Articles = sequelize.define(
        'articles',
        {
            uuid: {
                type: DataTypes.UUID,
                defaultValue: DataTypes.UUIDV4,
                unique: true
            }
            // Your article schema ...
        },
        {
            timestamps: true,
            schema: 'articles'
        }
    );
    Articles.associate = (models) => {
        Articles.belongsTo(models.ArticleTypes, {
            as: 'ArticleTypes',
            foreignKey: 'articleTypeID'
        });
    };
    return Articles;
};

create model like above. rest part is same as you used.

Please signup or login to give your own answer.

Click here to cancel reply.

Setting Sequelize associations in Node – Postman throwing constraint errors – Phpmyadmin

Answers