operator does not exist: character varying = integer in sequelize node js

Question

I am trying to connect two tables with sequelize, but the primary key and foreign key have different data types, I am getting errors when I triggered the query. it is not possible to change the schema, it will affect the whole data. Can you provide some possible solutions to fix this error?

Answer 1

I stumbled on the same issue here is how i resolved it: The cause of the issue is that Sequlize doing typecasting from integer to string when you create in one schema primaryKey as id and give it type of integer and in another schema you use it as secondaryKey with alias for instance userId so when you reference to connected schema from main schema you receive error operator does not exist: character varying = integer
Code examples (i am using Sequilze-typescript):

main schema where id is id

@Table({ tableName: 'users' })
export class User extends Model<User,> {
     @Column({
        type: DataType.INTEGER,
        primaryKey: true,
        unique: true,
        autoIncrement: true
      })

      id: number
      @HasMany(() => Post)
      posts:Post[]
}

secondary schema that uses User id as secondaryKey

@Table({ tableName: 'posts' })
export class Task extends Model<Task, TaskCreationAttributes> {
  @Column({
    type: DataType.INTEGER,
    primaryKey: true,
    unique: true,
    autoIncrement: true
  })
  id: number

  @BelongsTo(() => User)
    creator: User
  @ForeignKey(() => User)
  @Column({ type: DataType.INTEGER, allowNull: false })
  userId: number
 }

so here even-through we explicitly telling that our secondaryKey is number when we query User schema, Sequlize casts id -> userId, integer -> string

so in order to prevent the typecasting from id -> userId we can change main schema(User) to

@Column({
    type: DataType.INTEGER,
    primaryKey: true,
    unique: true,
    autoIncrement: true
  })

  userId: number

and secondary(Post) schema to:

  @Column({
    type: DataType.INTEGER,
    primaryKey: true,
    unique: true,
    autoIncrement: true
  })
  postId: number

  @BelongsTo(() => User)
    creator: User
  @ForeignKey(() => User)
  @Column({ type: DataType.INTEGER, allowNull: false })
  userId: number

so no collision and typecasting will be done