Sequelize error "Unknown column in 'field list'" during CREATE for One-to-Many Association

Question

I have been using the following tutorial to learn how to implement one-to-many relationship in Sequelize where a Tutorial has many Comment and Comment belongs to a Tutorial: https://www.bezkoder.com/sequelize-associate-one-to-many/

While I have the code modeling what is documented for setting up the relationship between the two models, I receive the following error during creating the Comment model:

Unknown column 'tutorialId' in 'field list'

Additionally, I receive the following SQL output:

Executing (default): INSERT INTO `comments` (`id`,`name`,`text`,`createdAt`,`updatedAt`,`tutorialId`) VALUES (DEFAULT,?,?,?,?,?);

app.js

const tutorialsRouter = require('./routes/api/tutorials');
const commentsRouter  = require('./routes/api/comments');

app.use('/api/tutorials', tutorialsRouter);
tutorialsRouter.use('/:tutorialId/comments', commentsRouter);

/models/index.js

db.tutorials = require("./tutorial.model")(sequelize, Sequelize);
db.comments  = require("./comment.model")(sequelize, Sequelize);

db.tutorials.hasMany(db.comments, { as: "comments" });
db.comments.belongsTo(db.tutorials, {
  foreignKey: "tutorialId",
  as: "tutorial",
});

/models/comment.model.js

module.exports = (sequelize, DataTypes) => {
  
  const Comment = sequelize.define('comment', {
    name: {
      type: DataTypes.STRING,
    },
    text: {
      type: DataTypes.STRING,
    },
  });

  return Comment;
}

/routes/comments.js

const comments = require('../../controllers/comments.controller');
const router = require('express').Router({ mergeParams: true });

router.post('/', comments.create);

module.exports = router;

/controllers/comments.controller.js

const db = require('../models');
const Comment = db.comments;

exports.create = (req, res) => {
  ...
  Comment.create({
    name:       req.body.name,
    text:       req.body.text,
    tutorialId: req.params.tutorialId,
  })
    .then( ... )
    .catch( ... );
}

Then in Postman I receive a 500 (of the error message above) when issuing the request: POST localhost:3000/api/tutorials/1/comments

{
    "name": "John Doe",
    "text": "Lorem ipsum..."
}

I don't think I should have to define a tutorialId field on the Comment model. Grr...

This may be very obvious to some of you, but it's tripping me up trying to learn. Any help is very much appreciated. :)

Answer 1

The issue that you're having is a result of using aliases via the as property. See the docs for belongsTo and hasMany. Here's a code sample that performs the inserts without the error.

let {
        Sequelize,
        DataTypes,
    } = require('sequelize')

async function run () {
    let sequelize = new Sequelize('dbname', 'username', 'password', {
            host:       'localhost',
            port:       5555,
            dialect:    'postgres',
            logging:    console.log
        })

    let Comment = sequelize.define('comment', {
        name: {
            type: DataTypes.STRING,
        },
        text: {
            type: DataTypes.STRING,
        },
    })

    let Tutorial = sequelize.define('tutorial', {
        title: {
            type: DataTypes.STRING,
        },
        content: {
            type: DataTypes.STRING,
        }
    })

    Tutorial.hasMany(Comment)
    Comment.belongsTo(Tutorial)

    // This just recreates the tables in the database.
    //  You would really only want to use a force sync
    //  in a development environment, since it will destroy
    //  all of the data....
    await sequelize.sync({ force: true })

    let tutorial = await Tutorial.create({
        title:      'Tutorial',
        content:    'Hmm....'
    })

    let comment = await Comment.create({
        name:       'Comment',
        text:       'Something, something....',
        tutorialId: tutorial.id,
    })

    await sequelize.close()
}

run()

Edit

This is just an edit to my original answer above. The OP Tom Doe discovered that the issue was being caused by a mismatch between the definitions of the tables in the database and the models defined via sequelize (see comments below). As we discovered, one way to troubleshoot the mismatch is to force sync a new version of the database, and then compare the new version of the database with the original version. There may be differences in the definitions of the columns or the contraints. Force syncing the database can be done via the command

await sequelize.sync({ force: true})

Important Note: The above statement will overwrite the existing database and all of its data. See the docs for more information.