postgres: STORED columns aren't updatable

[mariusa]

eg

    total_amount NUMERIC(12, 2) NOT NULL GENERATED ALWAYS AS (amount * quantity) STORED,

gives ERROR unhandledRejection column "total_amount" can only be updated to DEFAULT

When extracting the model, this should be recognized as not being updatable. Values should be read from db, but not written.

[steveschmitt]

I think this column should have

type: DataTypes.VIRTUAL

If you manually change it in the generated file, does it behave correctly?

[mariusa]

I made the change, but it still doesn't read from db.

Quoting from https://sequelize.org/master/class/lib/data-types.js~VIRTUAL.html

If a virtual attribute is present in attributes it will automatically pull in the extra fields as well

It looks like it still needs to be manually included in attributes. Could VIRTUAL columns always be read, like other columns ?

Thanks

[steveschmitt]

You are right, VIRTUAL is not the right choice.

There doesn't seem to be a read-only option, which is what we need.

[mariusa]

How about introducing a new property, readOnly: true ? Shall I enter this request in https://github.com/sequelize/sequelize ?

[steveschmitt]

You could try adding autoIncrement: true to your field definition. I think that will tell sequelize to read but not write the field.

[mariusa]

That won't work either (on UPDATE it will set values), and it's confusing. But I found it would be ~easy to add, similar to autoIncrement, so thanks for the suggestion :)

Here's a request to track: https://github.com/sequelize/sequelize/issues/12718

[mariusa]

Here's a workaround: https://stackoverflow.com/questions/67051281/use-postgres-generated-columns-in-sequelize-model/67120448#67120448

Would it be possible to implement this in sequelize-auto, until proper readOnly support comes to sequelize?

[steveschmitt]

Yes, I think if the type definition has "GENERATED" or "STORED", then we could add a set() method that throws an Error.