在 Sequelize 模型中使用 Postgres 生成的列 [英] Use Postgres generated columns in Sequelize model

问题描述

我有一个表，它可以在数据库引擎中而不是在我的应用程序代码中生成预先计算的值.为此，我使用 Postgres 生成的列 功能.SQL是这样的:

I have a table where it's beneficial to generate a pre-calculated value in the database engine rather than in my application code. For this, I'm using Postgres' generated column feature. The SQL is like this:

ALTER TABLE "Items"
ADD "generatedValue" DOUBLE PRECISION GENERATED ALWAYS AS (
  LEAST("someCol", "someOtherCol")
) STORED;

这很好用，但我在这个数据库中使用了 Sequelize.我想找到一种方法在我的模型定义中定义此列，以便 Sequelize 对其进行查询，而不是尝试更新该列的行值，并且理想情况下将在同步时创建该列.

This works well, but I'm using Sequelize with this database. I want to find a way to define this column in my model definition, so that Sequelize will query it, not attempt to update a row's value for that column, and ideally will create the column on sync.

class Item extends Sequelize.Model {
  static init(sequelize) {
    return super.init({
      someCol: Sequelize.DOUBLE,
      someOtherColl: Sequelize.DOUBLE,
      generatedValue: // <<<--  What goes here??
    });
  }
}

如何使用 Sequelize 做到这一点?

How can I do this with Sequelize?

我可以将列指定为 DOUBLE，Sequelize 会读取它，但不会在同步时正确创建列.也许我可以使用一些同步后挂钩?我正在考虑 afterSync 删除该列并使用我生成的 value 语句重新添加它，但我首先需要检测该列尚未转换，否则我会丢失我的数据.(我在每次应用启动时都运行同步 [without force: true].)

I can specify the column as a DOUBLE, and Sequelize will read it, but the column won't be created correctly on sync. Perhaps there's some post-sync hook I can use? I was considering afterSync to drop the column and re-add it with my generated value statement, but I would first need to detect that the column wasn't already converted or I would lose my data. (I run sync [without force: true] on every app startup.)

任何想法或替代想法将不胜感激.

Any thoughts, or alternative ideas would be appreciated.

推荐答案

在 Sequelize 支持只读字段和 GENERATED 数据类型之前，您可以使用自定义数据类型绕过 Sequelize:

Until Sequelize supports readOnly fields and the GENERATED datatype, you can get around Sequelize with a custom datatype:

const Item = sequelize.define('Item', {
  someCol: { type: DataTypes.DOUBLE },
  someOtherCol: { type: DataTypes.DOUBLE },
  generatedValue: {
    type: 'DOUBLE PRECISION GENERATED ALWAYS AS (LEAST("someCol", "someOtherCol")) STORED',
    set() {
      throw new Error('generatedValue is read-only')
    },
  },
})

这将在使用 sync() 时在 postgres 中正确生成列，并通过引发错误来防止在 javascript 中设置 generatedValue.

This will generate the column correctly in postgres when using sync(), and prevent setting the generatedValue in javascript by throwing an Error.

假设 sequelize 永远不会尝试更新未更改的字段，如 https://sequelize.org/master/manual/model-instances.html#change-awareness-of-save，那么它应该可以工作了.

Assuming that sequelize never tries to update the field if it hasn't changed, as specified in https://sequelize.org/master/manual/model-instances.html#change-awareness-of-save, then it should work.

这篇关于在 Sequelize 模型中使用 Postgres 生成的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！