Use Postgres generated columns in Sequelize model

In Sequelize, using PostgreSQL's generated columns (computed columns) can significantly enhance database performance and data consistency. Generated columns are derived from the values of other columns, meaning their values are dynamically generated and do not require manual updates.

Definition of Generated Columns

In PostgreSQL, generated columns are typically specified during table definition. For example, if we have an employees table containing first and last names, we can create a generated column to automatically construct the full name.

sql
CREATE TABLE employees (
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(205) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

In the above SQL statement, full_name is a generated column that is always automatically updated based on the values of first_name and last_name.

Referencing Generated Columns in Sequelize

In Sequelize models, although we cannot directly define generated columns, we can reference them. We need to ensure that the model aligns with the database table structure. Here is how to define the above employees table in Sequelize:

javascript
const Employee = sequelize.define('employee', {
    first_name: {
        type: Sequelize.STRING,
    },
    last_name: {
        type: Sequelize.STRING,
    },
    full_name: {
        type: Sequelize.STRING,
        allowNull: false
    }
}, {
    tableName: 'employees',
    timestamps: false
});

Note that in this model, we do not specify full_name as a generated column because Sequelize currently does not support directly creating generated columns in model definitions. We simply reference it in the model to ensure the application correctly handles data retrieved from the database.

Using Generated Columns

Since generated columns are automatically managed by the database, you do not need to assign values to full_name in the application. When creating or updating first_name or last_name, PostgreSQL automatically updates full_name.

javascript
async function createEmployee(firstName, lastName) {
    const employee = await Employee.create({
        first_name: firstName,
        last_name: lastName
    });
    console.log(`Full name is automatically generated: ${employee.full_name}`);
}

createEmployee('John', 'Doe');

In this example, although we do not directly set full_name, the output will show that the full name is automatically computed and populated by PostgreSQL.

Summary

Using generated columns can make database operations more efficient and secure. Although Sequelize does not support directly defining generated columns, these features can be leveraged through appropriate table design and model definitions to maintain code cleanliness and data consistency. This approach reduces the computational load on the application layer and delegates data maintenance responsibilities to the database.