Sequelize事务中创建对象后include关联查询失效问题

我使用Sequelize向SQL Server数据库插入对象，所有操作均在单个事务中执行。查询已插入的对象时，单独查询关联模型能找到目标数据，但通过include语句获取关联模型却无法返回结果。

可正常获取数据的代码 #

const model = await SequelizeModel.findOne({
  where: {
    name: step.relation.model,
  },
  transaction,
});
const modelField = await SequelizeModelField.findOne({
  where: {
    name: step.relation.field,
  },
  transaction,
});

const referenceModel = await SequelizeModel.findOne({
  where: {
    name: step.relation.referenceModel,
  },
  transaction,
});

const referenceModelField = await SequelizeModelField.findOne({
  where: {
    name: step.relation.referenceField,
  },
  transaction,
});

无法返回关联模型的Include查询代码 #

const modelField = await SequelizeModelField.findOne({
  where: {
    name: step.relation.field,
  },
  include: [
    {
      model: SequelizeModel,
      where: {
        name: step.relation.model,
      },
    },
  ],
  transaction,
});

const referenceModelField = await SequelizeModelField.findOne({
  where: {
    name: step.relation.referenceField,
  },
  include: [
    {
      model: SequelizeModel,
      where: {
        name: step.relation.referenceModel,
      },
    },
  ],
  transaction,
});

生成的SQL查询语句 #

第一条查询 #

SELECT
    [SequelizeModelField].[id],
    [SequelizeModelField].[name],
    [SequelizeModelField].[type],
    [SequelizeModelField].[length],
    [SequelizeModelField].[searchable],
    [SequelizeModelField].[default],
    [SequelizeModelField].[nullable],
    [SequelizeModelField].[unique],
    [SequelizeModelField].[primary],
    [SequelizeModelField].[autoIncrement],
    [SequelizeModelField].[tokenField],
    [SequelizeModelField].[encryption],
    [SequelizeModelField].[modelId],
    [SequelizeModelField].[created_at],
    [SequelizeModelField].[updated_at],
    [model].[id] AS [_0],
    [model].[name] AS [_1],
    [model].[tableName] AS [_2],
    [model].[ignoreFields] AS [_3],
    [model].[auditEnabled] AS [_4],
    [model].[created_at] AS [_5],
    [model].[updated_at] AS [_6]
FROM
    [__model_fields] AS [SequelizeModelField]
    INNER JOIN [__models] AS [model] ON [SequelizeModelField].[modelId] = [model].[id]
    AND [model].[name] = N'endpoints'
WHERE
    [SequelizeModelField].[name] = N'appconfig_id'
ORDER BY
    [SequelizeModelField].[id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

第二条查询 #

SELECT
    [SequelizeModelField].[id],
    [SequelizeModelField].[name],
    [SequelizeModelField].[type],
    [SequelizeModelField].[length],
    [SequelizeModelField].[searchable],
    [SequelizeModelField].[default],
    [SequelizeModelField].[nullable],
    [SequelizeModelField].[unique],
    [SequelizeModelField].[primary],
    [SequelizeModelField].[autoIncrement],
    [SequelizeModelField].[tokenField],
    [SequelizeModelField].[encryption],
    [SequelizeModelField].[modelId],
    [SequelizeModelField].[created_at],
    [SequelizeModelField].[updated_at],
    [model].[id] AS [_0],
    [model].[name] AS [_1],
    [model].[tableName] AS [_2],
    [model].[ignoreFields] AS [_3],
    [model].[auditEnabled] AS [_4],
    [model].[created_at] AS [_5],
    [model].[updated_at] AS [_6]
FROM
    [__model_fields] AS [SequelizeModelField]
    INNER JOIN [__models] AS [model] ON [SequelizeModelField].[modelId] = [model].[id]
    AND [model].[name] = N'appconfig'
WHERE
    [SequelizeModelField].[name] = N'id'
ORDER BY
    [SequelizeModelField].[id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

modelField.get({ plain: true })输出结果 #

{
  id: 20,
  name: "appconfig_id",
  type: "INT",
  length: null,
  searchable: null,
  default: null,
  nullable: null,
  unique: null,
  primary: null,
  autoIncrement: null,
  tokenField: null,
  encryption: false,
  modelId: 2,
  created_at: "2024-02-17T11:14:40.807Z",
  updated_at: "2024-02-17T11:14:40.807Z"
}

模型关联定义 #

SequelizeModel #

@HasMany(() => SequelizeModelField, {
  onDelete: "CASCADE",
})
fields: SequelizeModelField[];

SequelizeModelField #

@ForeignKey(() => SequelizeModel)
@Column
modelId: number;

@BelongsTo(() => SequelizeModel)
model: SequelizeModel;

Sequelize初始化代码 #

if (environment.databaseType == "mssql") {
  dialectOptions["options"] = {
    trustServerCertificate: environment.databaseDialectSSL,
    appName: environment.swaggerTitle ?? "No Code API",
  };
}

this.sequelize = new Sequelize({
  host: environment.databaseType == "sqlite" ? null : environment.databaseHost,
  dialect: environment.databaseType as Dialect,
  dialectOptions: dialectOptions,
  database: environment.databaseName,
  port: environment.databasePort,
  username: environment.databaseUsername,
  password: environment.databasePassword,
  storage: environment.databaseType == "sqlite" ? environment.databaseHost : null,
  minifyAliases: environment.databaseType != "sqlite" ? true : false,
  logging: console.log,
  pool: {
    max: 5,
    min: 0,
    idle: 10000,
  },
  models: [
    SequelizeMigration,
    SequelizeMigrationStep,
    SequelizeMigrationStepField,
    SequelizeMigrationStepModel,
    SequelizeMigrationStepRelation,
    SequelizeMigrationStepRoute,
    SequelizeMigrationStepRole,
    SequelizeMigrationStepRoleCheck,
    SequelizeMigrationStepRoleField,
    SequelizeModel,
    SequelizeModelField,
    SequelizeModelFieldRelation,
    SequelizeModelRoute,
    SequelizeModelRouteTrigger,
    SequelizeModelRole,
    SequelizeModelRoleCheck,
    SequelizeModelRoleField,
    SequelizeView,
    SequelizeViewRoute,
    SequelizeViewRole,
    SequelizeViewRoleCheck,
  ],
  define: {
    createdAt: "created_at",
    updatedAt: "updatedAt",
  },
});

await this.sequelize.authenticate();
await this.sequelize.sync();

return this.sequelize;

解决方案 #

1. 关闭别名压缩配置 #

你的初始化代码中开启了minifyAliases: true（SQL Server环境下），这个配置会将查询中的关联字段别名压缩为_0、_1这类格式，可能导致Sequelize无法正确将查询结果映射到关联模型的属性上。尝试将该配置改为false：

minifyAliases: false,

2. 显式指定关联别名 #

在include配置中添加as属性，确保和模型定义中的关联属性名一致：

include: [
  {
    model: SequelizeModel,
    as: 'model', // 对应SequelizeModelField中的model属性
    where: {
      name: step.relation.model,
    },
  },
]

3. 检查事务一致性 #

虽然同一个事务内应该能读取到自身未提交的修改，但可以尝试在查询时添加lock: true或者临时将事务隔离级别改为READ UNCOMMITTED（仅用于排查，不建议生产环境使用），验证是否为事务隔离规则导致的问题。

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