Sequelize .create function includes eager loaded values

[razvan286]

I am working on a project using:

• Node.js v20.10.0
• npm v10.2.3
• sequelize v6.25.3
• sequelize-typescript v2.1.6

I have defined the models using sequelize-typescript library. One of the models is:

@Table({ tableName: 'testAttemptDate' })
export default class TestAttemptDate extends Model {
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.INTEGER)
  declare id: number;

  // Foreign key
  @AllowNull(false)
  @ForeignKey(() => TestAttemptHistory)
  @Column(DataType.INTEGER)
  declare testAttemptHistoryId: number;

  @UpdatedAt
  declare updatedAt: Date;

  @CreatedAt
  declare createdAt: Date;

  // Associations
  @HasMany(() => FinalItemAnswer, {
    foreignKey: 'testAttemptDateId',
    onDelete: 'SET NULL',
    onUpdate: 'CASCADE',
  })
  declare finalItemAnswers: ReturnType<() => FinalItemAnswer[]>;

  @BelongsTo(() => TestAttemptHistory)
  declare testAttemptHistory: ReturnType<() => TestAttemptHistory>;
}

It has a foreign key while the rest of its fields should be automatically created during .create function.

Followingly, this is the function that should create a TestAttemptDate entry:

/**
 * Track date of a test attempt
 * @param {number} testAttemptHistoryId
 */
export async function createTestAttemptDate(
  testAttemptHistoryId: number,
): Promise<ITestAttemptDate> {
  return TestAttemptDate.create({ testAttemptHistoryId });
}

However, this function fails with the following error message:

 {
    "message": {
        "name": "SequelizeDatabaseError",
        "parent": {
            "length": 111,
            "name": "error",
            "severity": "ERROR",
            "code": "42703",
            "position": "173",
            "file": "parse_relation.c",
            "line": "3563",
            "routine": "errorMissingColumn",
            "sql": "INSERT INTO \"testAttemptDate\" (\"id\",\"testAttemptHistoryId\",\"createdAt\",\"updatedAt\") VALUES (DEFAULT,$1,$2,$3) RETURNING \"id\",\"testAttemptHistoryId\",\"createdAt\",\"updatedAt\",\"activityId\",\"itemId\";",
            "parameters": [
                3,
                "2024-02-02 14:17:53.108 +00:00",
                "2024-02-02 14:17:53.108 +00:00"
            ]
        },
    }
}

Based on the sql query mentioned in the above error message in the RETURNING part it tries to return activityId and itemId which are not fields of TestAttemptDate but fields of the model associated with the TestAttemptDate.

What can cause this error?

PS: this is part of an API call and the error returned by the server is {"message":"column \"activityId\" does not exist"}.

UPDATE:
The associated models:

@Table({ tableName: 'testAttemptHistory' })
export default class TestAttemptHistory extends Model {
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.INTEGER)
  declare id: number;

  @AllowNull(false)
  @Column(DataType.INTEGER)
  declare studentId: number;

  @AllowNull(false)
  @Default(1)
  @Column(DataType.INTEGER)
  declare attempts: number;

  @UpdatedAt
  declare updatedAt: Date;

  @CreatedAt
  declare createdAt: Date;

  // Foreign keys
  @AllowNull(false)
  @ForeignKey(() => Activity)
  @Column(DataType.UUID)
  declare activityId: string;

  // Associations
  @BelongsTo(() => Activity)
  declare activity: ReturnType<() => Activity>;

  @HasMany(() => TestAttemptDate, {
    foreignKey: 'testAttemptHistoryId',
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
  })
  declare testAttemptDates: ReturnType<() => TestAttemptDate[]>;
}

@Table({ tableName: 'finalItemAnswers' })
export default class FinalItemAnswer extends Model {
  @PrimaryKey
  @IsUUID(4)
  @Default(DataType.UUIDV4)
  @Column(DataType.UUID)
  declare id: string;

  @AllowNull(false)
  @Column(DataType.INTEGER)
  // Id of the student stored in Portal's database
  declare studentId: number;

  @AllowNull(false)
  @Column(DataType.ENUM(...Object.values(TestMode)))
  declare mode: TestMode;

  @UpdatedAt
  declare updatedAt: Date;

  @CreatedAt
  declare createdAt: Date;

  @AllowNull(false)
  @ForeignKey(() => Item)
  @Column(DataType.UUID)
  declare itemId: string;

  @AllowNull(true)
  @ForeignKey(() => Activity)
  @Column(DataType.UUID)
  declare activityId: string;

  @AllowNull(true)
  @ForeignKey(() => TestAttemptDate)
  @Column(DataType.INTEGER)
  declare testAttemptDateId: number;

  @AllowNull(false)
  @ForeignKey(() => PossibleItemAnswer)
  @Column(DataType.UUID)
  declare possibleItemAnswerId: string;

  // Associations
  @BelongsTo(() => Item)
  declare item: ReturnType<() => Item>;

  @BelongsTo(() => PossibleItemAnswer)
  declare possibleItemAnswer: ReturnType<() => PossibleItemAnswer>;

  @BelongsTo(() => Activity)
  declare activity: ReturnType<() => Activity>;

  @BelongsTo(() => TestAttemptDate)
  declare testAttemptDate: ReturnType<() => TestAttemptDate>;
}

[ephys]

We'll need to see what the other model that are associated with this one look like to be able to help

[razvan286]

My bad, just updated the post

[razvan286]

I fixed the issue in the end. Regarding the error: "column \"activityId\" does not exist"} I found that there was a mismatch between the model and a migration where the TestAttemptHistory.activityId can be nullable whereas in the migration was set not nullable.

After this, the error changed to "column \"itemId\" does not exist"}. I was expecting that based on the values in the returning part of the following query:

"sql": "INSERT INTO \"testAttemptDate\" (\"id\",\"testAttemptHistoryId\",\"createdAt\",\"updatedAt\") VALUES (DEFAULT,$1,$2,$3) RETURNING \"id\",\"testAttemptHistoryId\",\"createdAt\",\"updatedAt\",\"activityId\",\"itemId\";",

The itemId exists in the FinalItemAnswer model presented above but this time I couldn't find what's wrong with this foreign key. I solved it by removing the explicit association with the FinalItemAnswer in the TestAttemptDate sequelize model.

However, it is still unclear to me why when trying to create a database entry of the TestAttemptDate using the .create function it eager loaded some of the foreign keys of the associated entities. I have other models that use the .create function and I haven't had any of these issues. Can this be caused maybe because the TestAttemptDate has only one value that needs to be specified during .create and this one is the foreign key whereas its other attributes (id, createdAt, updatedAt) are auto-generated?

[ephys]

The returning clause is not supposed to include any attribute that are not declared on the model. It's possible that they were declared on the modal by accident? You can look at the contents of TestAttemptDate.rawAttributes before you execute your query to see which attributes are declared on that model