I am using a simple mysql database for tracking miles driven and time logged for work. I have a table of Users; a table of Weeks (each Week linked to a User); a table of Days (each Day linked to a Week); and a table of Miles and a table of Hours (each of these linked to a Day).

When I get data for a User, everything comes back nicely…until I have an entry for Hours. In my resolver, I get an error, ending_time is not defined. This error comes not when I do my query, but when I use sequelize’s get function to extract the data from the query’s returned object.

I am running this application in Node, with a GraphQL interface to my MySQL database. I am running node version 18.19.1, MySQL version 8.0.36, and Sequelize version 6.37.2. Here are the various code snippets:

First, the sequelize model for Hours:

const { Model, DataTypes } = require("sequelize");
const sequelize = require("../config/connection");
class Hours extends Model {}

Hours.init(
  {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    day_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: "day",
        key: "id",
      },
    },
    starting_time: {
      type: DataTypes.INTEGER,
      allowNull: true,
      defaultValue: 0,
    },
    ending_time: {
      type: DataTypes.INTEGER,
      allowNull: true,
      defaultValue: 0,
    },
    total_minutes: {
      type: DataTypes.VIRTUAL,
      get() {
        return ending_time && starting_time ? ending_time - starting_time : 0;
      },
    },
  },
  {
    sequelize,
    timestamps: true,
    freezeTableName: true,
    underscored: true,
    modelName: "hours",
  }
);

module.exports = Hours;

(though I’ve named it “Hours” I recently decided to chuck time types and just do it easier: starting_time and ending_time are integers representing minutes from midnight)

This is exactly the same model as my Miles model. Same DataTypes, same default values, just different field naming.

The relationships are set up here:

const User = require("./User");
const Week = require("./Week");
const Day = require("./Day");
const Miles = require("./Miles");
const Hours = require("./Hours");

User.hasMany(Week, {
  foreignKey: "user_id",
});

Week.belongsTo(User, {
  foreignKey: "user_id",
});

Week.hasMany(Day, {
  foreignKey: "week_id",
});

Day.belongsTo(Week, {
  foreignKey: "week_id",
});

Day.hasMany(Miles, {
  foreignKey: "day_id",
});

Miles.belongsTo(Day, {
  foreignKey: "day_id",
});

Day.hasMany(Hours, {
  foreignKey: "day_id",
});

Hours.belongsTo(Day, {
  foreignKey: "day_id",
});

module.exports = { User, Week, Day, Miles, Hours };

Within my resolver, here is the GetUser query:

getUser: async (parent, { id }) => {
  const userData = await User.findOne({
    where: { id },
    include: [
      {
        model: Week,
        include: [
          {
            model: Day,
            include: [{ model: Miles }, { model: Hours }],
            separate: true,
          },
        ],
      },
    ],
    order: [[{ model: Week, as: "weeks" }, "start_date", "DESC"]],
  });

  const user = await userData.get({ plain: true });

  return {
    user_name: user.user_name,
    created_at: user.createdAt,
    weeks: user.weeks,
  };
},

You can see I and finding the User off the their id, including the User’s Weeks, and those Weeks’ Days, and those Days’ Miles and Hours.

If there are no Hours associated with this User, then I get the error ending_time is not defined. Using some console.logs I know that the error is not coming until const user = await userData.get({ plain: true }). If there are no Hours, then I do not get the error.

Looking at my tables directly in MySQL, I see no difference between show fields from miles and show fields from hours, except for field names (“starting_time” vs. “start_miles”, “ending_time vs. “ending_miles). When I do select * from hours; I see exactly what I expect, a record with an id, a day_id, a starting_time of 0 and an ending_time of 0.

I originally had the field names as start_time and end_time, and when the error came up end_time not defined I thought maybe the field name “end_time” was somehow reserved and I shouldn’t use it…but when I changed the field to “ending_time” I got the same error with the new field name.