Use Postgres generated columns in Sequelize model

Question

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;

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??
    });
  }
}

How can I do this with Sequelize?

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.

Perhaps this isn't the most possible right now: github.com/sequelize/sequelize/issues/12718 — Brad
– Brad, Commented Apr 12, 2021 at 1:48

mtkopone · Accepted Answer · 2021-04-21 13:22:59Z

16

+500

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')
    },
  },
})

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

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.

edited Apr 21, 2021 at 13:22

answered Apr 16, 2021 at 6:55

mtkopone

6,4832 gold badges33 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Brad Over a year ago

This works great, thank you! One follow-up question... is using a string for a type officially documented somewhere?

mtkopone Over a year ago

I could not find any mention in the docs, so it could be behaviour that is subject to change in future versions of sequelize.

Timaayy Over a year ago

This unfortunately does not seem to work anymore as of Sequelize v6.32.1

Kaushik R Bangera Over a year ago

@Timaayy, its working in v6.37.3, my requirement was CHAR(32) GENERATED ALWAYS AS (MD5(some_text)) STORED. ( didn't notice postgres, im on MySQL )

Collectives™ on Stack Overflow

Use Postgres generated columns in Sequelize model

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related