Understanding Associations in Sequelize

Question

I'm trying to understand associations in Sequelize. I'm starting from existing database tables so some of the fields may not match up to the defaults in Sequelize. I've used Sequelizer to generate my models directly from the database. I'm accustomed to writing queries but now I'm trying to learn how an ORM like Sequelize works.

Here's my models.

models/user.js

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    "User",
    {
      id: {
        type: DataTypes.INTEGER(11),
        allowNull: false,
        primaryKey: true,
        field: "id"
      },
      username: {
        type: DataTypes.STRING(20),
        allowNull: false,
        field: "username"
      },
      fullname: {
        type: DataTypes.STRING(60),
        allowNull: false,
        field: "fullname"
      },
      createdat: {
        type: DataTypes.DATE,
        allowNull: false,
        field: "createdat"
      },
      updateat: {
        type: DataTypes.DATE,
        allowNull: true,
        field: "updateat"
      },
      deletedat: {
        type: DataTypes.DATE,
        allowNull: true,
        field: "deletedat"
      }
    },
    {
      tableName: "users",
      timestamps: false
    }
  );

  User.associate = function(models) {
        models.User.hasMany(models.Ticket),
        { as: "createdbyname", foreignKey: "createdby" };
  };
  return User;
};

models/ticket.js

module.exports = (sequelize, DataTypes) => {
  const Ticket = sequelize.define(
    "Ticket",
    {
      id: {
        type: DataTypes.INTEGER(11),
        allowNull: false,
        primaryKey: true,
        field: "id"
      },
      details: {
        type: DataTypes.STRING(45),
        allowNull: true,
        field: "details"
      },
      assignedto: {
        type: DataTypes.INTEGER(11),
        allowNull: true,
        field: "assignedto"
      },
      createdby: {
        type: DataTypes.INTEGER(11),
        allowNull: true,
        field: "createdby"
      },
      createdat: {
        type: DataTypes.DATE,
        allowNull: false,
        field: "createdat"
      },
      updatedat: {
        type: DataTypes.DATE,
        allowNull: true,
        field: "updatedat"
      },
      deletedat: {
        type: DataTypes.DATE,
        allowNull: true,
        field: "deletedat"
      }
    },
    {
      tableName: "tickets",
      timestamps: false
    }
  );

  Ticket.associate = function(models) {
        models.Ticket.belongsTo(models.User,
            { foreignKey: "createdby" });
  };
  return Ticket;
};

In my route handler, I'm calling User.findAll as follows:

  models.User.findAll({
    include: [models.Ticket]
  })

The result I expect to see is a query that looks like this:

SELECT 
    `User`.`id`,
    `User`.`username`,
    `User`.`fullname`,
    `User`.`createdat`,
    `User`.`updateat`,
    `User`.`deletedat`,
    `Tickets`.`id` AS `Tickets.id`,
    `Tickets`.`details` AS `Tickets.details`,
    `Tickets`.`assignedto` AS `Tickets.assignedto`,
    `Tickets`.`createdby` AS `Tickets.createdby`,
    `Tickets`.`createdat` AS `Tickets.createdat`,
    `Tickets`.`updatedat` AS `Tickets.updatedat`,
    `Tickets`.`deletedat` AS `Tickets.deletedat`
FROM
    `users` AS `User`
LEFT OUTER JOIN
    `tickets` AS `Tickets` ON `User`.`id` = `Tickets`.`createdby`

The query I see running in the console is:

SELECT 
    `User`.`id`,
    `User`.`username`,
    `User`.`fullname`,
    `User`.`createdat`,
    `User`.`updateat`,
    `User`.`deletedat`,
    `Tickets`.`id` AS `Tickets.id`,
    `Tickets`.`details` AS `Tickets.details`,
    `Tickets`.`assignedto` AS `Tickets.assignedto`,
    `Tickets`.`createdby` AS `Tickets.createdby`,
    `Tickets`.`createdat` AS `Tickets.createdat`,
    `Tickets`.`updatedat` AS `Tickets.updatedat`,
    `Tickets`.`deletedat` AS `Tickets.deletedat`,
    `Tickets`.`UserId` AS `Tickets.UserId`
FROM
    `users` AS `User`
LEFT OUTER JOIN
    `tickets` AS `Tickets` ON `User`.`id` = `Tickets`.`UserId`;

Note difference in LEFT OUTER JOIN clause. This is throwing an error as follows:

Unhandled rejection SequelizeDatabaseError: Unknown column 'Tickets.UserId' in 'field list'

I need some help figuring out where I've gone wrong here.

Answer 1

When defining associations, like belongsTo, you can specify a foreignKey and a targetKey. The foreignKey corresponds to the field in the source table (remember, the syntax is sourceModel.belongsTo(targetModel, options)). The targetKey corresponds to the field in the target table.

In your case, you made a mistake in the association in the models/ticket.js file, you used:

models.Ticket.belongsTo(models.User, { foreignKey: "createdby" });

Here, foreignKey references the source table, Ticket. Therefore, your are telling Sequelize to use the field createdBy for the Ticket table, and the default (the primary key) for the User table. As createdBy does not exists within Ticket, Sequelize falls back to the default case, where it uses Ticket.UserID.

To fix your association (and query), you need to update your belongsTo to the following:

models.Ticket.belongsTo(models.User, { targetKey: "createdby" });