Get belongsToMany association based on postgresql schema

Question

I'm building a multin-tenant application with Expressjs, Sequelize(sequelize-typescript) and Postgresql and I'm facing an issue getting table association with database schema.

I have 2 schemas (let's say france & italia). Everything is working fine (I can get belongsTo assocation, hasMany association etc...) except getting belongsToMany relation because sequelize doesn't select from the right schema for the association table.

Here are my models:

//Shop Model

@Table({ timestamps: true, tableName: 'shops' })
export class Shop extends Model<Shop> {

    @AllowNull(false)
    @Column
    name!: string;

    @BelongsToMany(() => Category, () => ShopCategory)
    categories!: Category[];
}


// Category

@Table({ timestamps: true, tableName: 'categories' })
export class Category extends Model<Category> {

    @AllowNull(false)
    @Column
    name!: string;

    @BelongsToMany(() => Shop, () => ShopCategory)
    shops!: Shop[];
}


// ShopCategory

@Table({ timestamps: true, tableName: 'shop_categories' })
export class ShopCategory extends Model<ShopCategory> {

    @ForeignKey(() => Shop)
    @Column
    shopId!: number;

    @ForeignKey(() => Category)
    @Column
    categoryId!: number;
}

Here is the sequelize query:

userEntity.schema('france').findOne({
   attributes: ['id'],
   where: { id: user.id },
   include:
       [{
           model: shopEntity.schema('france'),
           where: { statusId: 2 },
           include:
           [{
               model: categoryEntity.schema('france')
           }]
       }]
});

The query above select from france.users, france.shops and france.categories but not from france.shop_categories(for this association, it's using default schema).

How can I tell to sequelize to pick from the right schema for belongsToMany assocation ?

Note that I have to pick the schema dynamically (based on request origin)

Answer 1

Try to indicate schema option explicitly:

@Table({ timestamps: true, tableName: 'shop_categories', schema: 'italy' })

Answer 2

I would use 2 different sequelize instances for this matter.

export const sequelizeFr = new Sequelize({
  database: String(process.env.POSTGRE_HOSTNAME),
  username: String(process.env.POSTGRE_USERNAME),
  password: String(process.env.POSTGRE_PASSWORD),
  dialect: "postgres",
  host: "localhost",
  models: [userEntity, shopEntity, categoryEntity],
  dialectOptions: {
    schema: "France"
  }
});

export const sequelizeIt = new Sequelize({
  database: String(process.env.POSTGRE_HOSTNAME),
  username: String(process.env.POSTGRE_USERNAME),
  password: String(process.env.POSTGRE_PASSWORD),
  dialect: "postgres",
  host: "localhost",
  models: [userEntity, shopEntity, categoryEntity],
  dialectOptions: {
    schema: "Italy"
  }
});

And use the query like this:

sequelizeIt.userEntity.findOne({
   attributes: ['id'],
   where: { id: user.id },
   include:
       [{
           model: shopEntity,
           where: { statusId: 2 },
           include:
           [{
               model: categoryEntity
           }]
       }]
});