sequelize: support VIRTUAL/GENERATED columns (eg Postgres STORED). Allow querying, never write.

Feature Description

In Postgres, STORED columns aren’t updatable, but need to be read from db.

Is your feature request related to a problem? Please describe.

    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

Describe the solution you’d like

A new readOnly option in model, or a VIRTUAL/GENERATED column type (see comments)

        total_amount: {
        	type: DataTypes.DOUBLE,
        	readOnly: true
        },

This should be easy to implement. Here https://github.com/sequelize/sequelize/blob/042cd693635ffba83ff7a2079974692af6f710a7/src/dialects/abstract/index.js add

/* features specific to readOnly values */
  readOnly: {
    /* does the dialect require modification of insert queries when inserting */
    identityInsert: false,

    /* does the dialect support inserting default/null values */
    defaultValue: false,

    /* does the dialect support updating fields */
    update: false
  },

Why should this be in Sequelize

In databases, some fields are read-only. eg Postgres STORED columns. This might also be used for views in future.

Describe alternatives/workarounds you’ve considered

Using autoIncrement, but that doesn’t work properly and it’s not autoIncrement, it’s a read-only column.

Feature Request Checklist

Is this feature dialect-specific?

No. This issue is relevant to Sequelize as a whole.
Yes. This issue only applies to the following dialect(s): XXX, YYY, ZZZ

Would you be willing to implement this feature by submitting a Pull Request?

Yes, I have the time and I know how to start.
Yes, I have the time but I don’t know how to start, I would need guidance.
No, I don’t have the time, although I believe I could do it if I had the time…
No, I don’t have the time and I wouldn’t even know how to start.

About this issue

Original URL
State: open
Created 4 years ago
Reactions: 7
Comments: 20 (8 by maintainers)

Most upvoted comments

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the “stale” label. 🙂

github-actions[bot] on Apr 10, 2024

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the “stale” label. 🙂

github-actions[bot] on Apr 10, 2024

Now for API design. I think generated columns should be a DataType

We already have a VIRTUAL DataType but it’s handled by Sequelize instead of the database

Maybe we could add DataTypes.GENERATED:

// GENERATED ALWAYS AS CONCAT(first_name,' ',last_name) VIRTUAL
DataTypes.GENERATED(`CONCAT(first_name,' ',last_name)`)

// GENERATED ALWAYS AS CONCAT(first_name,' ',last_name) STORED
DataTypes.GENERATED(`CONCAT(first_name,' ',last_name)`).STORED

Seems to be supported in most dialects:

mysql - https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
postgres - https://www.postgresql.org/docs/12/ddl-generated-columns.html
mariadb - https://mariadb.com/kb/en/generated-columns/
mssql - https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15
db2 / ibmi - wikipedia says it’s supported but can’t find the resource. Maybe https://www.ibm.com/docs/en/ias?topic=concepts-generated-columns
sqlite - https://www.sqlite.org/gencol.html
snowflake - looks like it doesn’t support it

ephys on Mar 11, 2022

Still no support for GENERATED columns in Sequelize?

MatiFalcone on Apr 10, 2024

That sounds like it would be better handled by adding proper support for VIRTUAL/GENERATED columns than adding a readOnly option.

imo a readOnly option would have the following behavior:

when a row is created, the user is allowed to set the value of those readOnly columns
updating the row (save, update, bulkUpdate) throws an error if the user tries to modify a readOnly column

This is basically the behavior described in https://github.com/sequelize/sequelize/issues/4603

whereas a VIRTUAL/GENERATED column would have the following behavior:

when a row is created, don’t include a default value for the generated columns. (I assume this is the issue you’re experiencing)
- if the user tries to set a value manually, let the dbms throw an error
if the user tries to set the column’s value when updating (save, update, bulkUpdate), let the dbms throw an error

ephys on Mar 11, 2022

Tangentially related: https://stackoverflow.com/a/67120448/362536

bradisbell on Apr 17, 2021