Problem defining associations in sequelize, I get 'Unknown Column' for the foreignKey used in the association

13 views Asked by At

I am trying to use sequelize to interface with my MySQL db. The Database connection is setup fine, I can add, get, update entries. The main problem is when I define associations.

I want to define an 'Owner' association between my 'Chain' and 'User' models, where a 'Chain' can have one Owner User, but a 'User' can own many 'Chains'.

function initModels(sequelize) {
  var chains = _chains(sequelize, DataTypes);
  var users = _users(sequelize, DataTypes);
  chains.belongsTo(users, {foreignKey: "OwnerID", as : 'owner'});
}

Here is where I create the sequelize instance, and I wonder if I need to Sequelize.sync() here at some point?

var initModels = require("../models/init-models");


const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const process = require('process');
const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = require(__dirname + '/../config/config.json')[env];
const db = {};

let sequelize;
if (config.use_env_variable) {
  sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
  sequelize = new Sequelize(config.database, config.username, config.password, config);
}

var models = initModels(sequelize);

And here is the post request.

router.post("/addOwner" , async (req, res) => {

    const { ChainID, UserID } = req.body;

  try {
    // Find the chain by ID
    const chain = await models.chains.findByPk(ChainID);
    if (!chain) {
      return res.status(404).json({ error: 'Chain not found' });
    }

    // Find the user by ID
    const user = await models.users.findByPk(UserID);
    if (!user) {
      return res.status(404).json({ error: 'User not found' });
    }

    // Set the owner of the chain
    await chain.setOwner(user);

    return res.status(200).json({ message: 'Owner added successfully' });
  } catch (error) {
    console.error('Error:', error); //error caught here
    return res.status(500).json({ error: 'Internal server error' });
  }


})

I get the error

{
    code: 'ER_BAD_FIELD_ERROR',
    errno: 1054,
    sqlState: '42S22',
    sqlMessage: "Unknown column 'OwnerID' in 'field list'",
    sql: 'SELECT `ChainID`, `Title`, `Bio`, `DateCreated`, `ImagesCount`, `ChainType`, `Latitude`, `Longitude`, `createdAt`, `updatedAt`, `OwnerID` FROM `chains` AS `chains` WHERE `chains`.`ChainID` = 1;',
    parameters: undefined
  },

For some reason sequelize isn't creating the necessary columns for the defined association?

Thank you in advance

0

There are 0 answers