多租户Sequelize应用M:N关联中间表Schema匹配错误问题

问题背景 #

在基于Node.js、Sequelize和PostgreSQL的单数据库多Schema多租户应用中，查询User模型并关联Permission模型时，中间表UserPermission未使用指定的租户Schema，默认走public Schema，导致查询报错：no existe la relación «user_permissions»。

解决方案 #

方案1：查询时显式指定中间表Schema（最直接） #

在调用getAll方法时，修改Permission的include配置，手动指定中间表的租户Schema：

users = await super.getAll(
  tenantId,
  [
    Role.schema(schemaName),
    Profile.schema(schemaName),
    Subscription,
    {
      model: Permission.schema(schemaName),
      through: {
        model: UserPermission.schema(schemaName)
      }
    }
  ],
  body.page,
  body.size,
  {},
);

此方法直接在查询关联时为中间表绑定租户Schema，确保生成的SQL中中间表会带上租户前缀，比如"free_trial"."user_permissions"。

方案2：基类中自动处理所有关联的中间表Schema（适合多关联场景） #

如果应用中有大量多对多关联，可以在基类的getAll方法中自动遍历并设置中间表的Schema，避免重复手动配置：

修改基类getAll方法中的include处理逻辑：

public async getAll(
  tenantId: string,
  includeModel?: object[],
  page?: string,
  size?: string,
  where?: Record<string, unknown>,
) {
  try {
    let records: unknown;
    const free = tenantId.split('_');
    const tenantName: string = getTenantName(tenantId);

    if (free[0] === 'free') {
      where.tenantId = free[1];
    }

    const { limit, offset } = paginate(page, size);

    if (!includeModel) {
      records = await this.model.schema(tenantName).findAndCountAll({
        where,
        limit,
        offset,
      });
      return new HttpResponse.getSuccessful(records);
    }

    // 处理include中的关联，自动设置中间表Schema
    const processedInclude = includeModel.map(item => {
      // 如果是带model的配置项
      if (typeof item === 'object' && item['model']) {
        const model = item['model'];
        // 检查模型的多对多关联
        Object.values(model.associations).forEach(assoc => {
          if (assoc.associationType === 'BelongsToMany') {
            // 给中间表设置租户Schema
            assoc.options.through = {
              model: assoc.options.through.model.schema(tenantName)
            };
          }
        });
        return {
          ...item,
          model: model.schema ? model.schema(tenantName) : model
        };
      }
      // 如果是直接的模型类
      return item.schema ? item.schema(tenantName) : item;
    });

    records = await this.model.schema(tenantName).findAndCountAll({
      where,
      limit,
      offset,
      include: processedInclude,
    });

    return new HttpResponse.getSuccessful(records);
  } catch (e) {
    throw boom.badRequest(e);
  }
}

此方法会自动处理所有包含多对多关联的模型，为其中间表绑定当前租户的Schema，无需每次手动配置。

方案3：模型定义时支持动态Schema（全局化处理） #

如果希望在模型层面统一处理，可以修改多对多关联的定义，通过动态函数返回带Schema的中间表模型：

在User模型中修改关联定义：

// 定义动态获取中间表的函数
export const getTenantUserPermission = (schemaName: string) => UserPermission.schema(schemaName);

@Table({"timestamps": false,"tableName": USERS_TABLE,})
export class User extends Model {
  // ... 其他字段定义

  // 修改多对多关联，使用动态中间表
  @BelongsToMany(() => Permission, () => getTenantUserPermission(schemaName))
  Permissions: Permission[];

  // ... 其他关联定义
}

注意：此方法需要确保schemaName能被模型访问到，可通过全局上下文或模型初始化时注入的方式传递，适合需要全局统一处理的场景。

验证效果 #

修改后执行查询，生成的SQL中中间表user_permissions会正确带上租户Schema前缀，示例如下：

LEFT OUTER JOIN ( "free_trial"."user_permissions" AS "Permissions->UserPermission" INNER JOIN "free_trial"."permissions" AS "Permissions" ON ... )

内容的提问来源于stack exchange，提问作者Cesar Ruiz