SequelizeForeignKeyConstraintError on nested findOrCreate inside a loop

Question

I am trying to insert multiple rows on 2 related tables using sequelize with mysql.

Based on sequelize transaction cannot insert because of foreign key? I tried to create a transaction and it seems to work if I execute the promise only once, but it fails if I execute the code on a loop showing a foreign key issue.

Here is the code:

const config = {
    "username": "root",
    "password": "",
    "database": "sequelize_test",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "pool": {
        "max": 1,
        "min": 0,
        "idle": 20000,
        "acquire": 20000
    }
};

const Sequelize = require('sequelize')
const sequelize = new Sequelize(config.database, config.username, config.password, config)

const Project = sequelize.define('projects', {
    id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
    },
    authorId: {
        type: Sequelize.INTEGER
    },
    name: {
        type: Sequelize.STRING
    }
})

const Author = sequelize.define('authors', {
    id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
    },
    name: {
        type: Sequelize.STRING,
    }
})

Author.hasMany(Project, {
    foreignKey: 'authorId',
    sourceKey: 'id'
})
Project.belongsTo(Author, {
    foreignKey: 'id'
})

sequelize
    .sync({
        force: true
    })
    .then(() => {

        var projectAuthors = [{
            projectName: "Proj 1",
            authorName: "Author 1"
        }, {
            projectName: "Proj 2",
            authorName: "Author 1"
        }, {
            projectName: "Proj 3",
            authorName: "Author 1"
        }, {
            projectName: "Proj 4",
            authorName: "Author 2"
        }, {
            projectName: "Proj 5",
            authorName: "Author 3"
        }];

        //Insert all the records on the array
        projectAuthors.forEach(function(project) {

            sequelize
                .transaction(function(t) {
                    //First insert the author
                    return Author.findOrCreate({
                        where: {
                            name: project.authorName
                        },
                        transaction: t

                    }).spread(function(author) {
                        //With the id obtained on the previous step, insert the project
                        return Project.findOrCreate({
                            where: {
                                name: project.projectName,
                                authorId: author.id
                            },
                            transaction: t
                        });
                    })

                });

        });
    });

Edit

Following the suggestion from Ellebkey, here is my controller using include.

sequelize
.sync({
    force: true
})
.then(() => {

    var projectAuthors = [{
        projectName: "Proj 1",
        authorName: "Author 1"
    }, {
        projectName: "Proj 2",
        authorName: "Author 1"
    }, {
        projectName: "Proj 3",
        authorName: "Author 1"
    }, {
        projectName: "Proj 4",
        authorName: "Author 2"
    }, {
        projectName: "Proj 5",
        authorName: "Author 3"
    }];

    //Insert all the records on the array
    projectAuthors.forEach(function(project) {

        sequelize
            .transaction(function(t) {
                //First insert the author
                return Project.findOrCreate({
                    where: {
                        name: project.projectName,
                        author: {
                            name: project.authorName
                        }
                    },
                    include: [{
                        association: Project.Author,
                    }],
                    transaction: t
                });

            });
    });
});

It does fail with:

Invalid value { name: 'Author 3' }

Edit 2 (table creation works)

Thanks to the suggestions from Ellebkey and some playing around, my model looks like this:

const Project = sequelize.define('projects', {
    name: {
        type: Sequelize.STRING
    }
});

const Author = sequelize.define('authors', {
    name: {
        type: Sequelize.STRING,
    }
});

Project.belongsTo(Author, { as: 'Author', foreignKey : 'authorId'});
Author.hasMany(Project, { as: 'Author', foreignKey : 'authorId'});

But using the create() code from the answer, I still get this:

Unhandled rejection Error: Invalid value { name: undefined }

Btw I am using sequelize 4.37.6

Thanks in advance

Answer 1

Ok first, for my experience is not recommended use foreignKey for the associations, it gets confuse. Use as instead, and also you don't need to create the id of each model, sequelize do it for you.

const Project = sequelize.define('projects', {
    name: {
        type: Sequelize.STRING
    }
})

Now, going back to the associations, this will create a AuthorId column on your Project table

Project.belongsTo(Author, { as: 'Author'})

const Author = sequelize.define('authors', {
    name: {
        type: Sequelize.STRING,
    }
})

Author.hasMany(Project, { as: 'Author'})


sequelize
    .sync({
        force: true
    })
    .then(() => {

And last, for your create with include you have to create your object according how you declared before. Think it as you will do it with a Find you get your Author with the Projects so each object should be like this:

        var projectAuthors = [{
            name: "Author 1",
            //This must have the same syntax as you declared on your asociation
            Author: [{
                name: "Proj 1"
            },{
                name: "Proj 2"
            }]
        },{
            name: "Author 2",
            Author: [{
                name: "Proj 3"
            },{
                name: "Proj 4"
            }]
        },];

        projectAuthors.forEach(function(project) {

        sequelize
            .transaction(function(t) {
                //First insert the author
                return Author.findOrCreate({
                    where: {
                        //check this I'm not sure
                        name: project.name, //this is the Author name
                    },
                    include: [{
                        model: Project,
                        as: 'Author'
                    }],
                    transaction: t
                });

            });
        });
    });

The error that you was getting was because you never declared authorName on your models.