Sequelize, Postgresql, and created/modified timestamp management-CodePudding

I'm really new to Sequelize, but I've used another ORM in another language for a long time. I'm trying to figure out how the magic stuff behind getting model timestamps to be managed automatically, and I feel like I'm going around in circles.

What I'd like is for Sequelize to manage the job of knowing when to update "createdAt" and "modifiedAt" for me. However, I would strongly prefer that it would use statement_timestamp() (mostly out of long habit and the advice of a much smarter database person than me). I don't want important stuff like those to rely on the client clocks.

My previous ORM just maintained two prepared statements (per table), one for creation and one for update, and the queries just updated the right fields the right way because they were coded like that. Of course I could write raw SQL but that kind-of defeats the purpose of using something like Sequelize.

I've found some material online that seems to suggest that if I want to do what I'm talking about, I'm on my own, because would have to turn timestamps off on the models. That linked post is about MySQL, which has some trick to make that work.

I would love to post code, but it's a chicken and egg issue. Oh, also: I am creating my schema manually with scripts I run through psql. In the scripts I define the columns like I have been doing for years:

CREATE TABLE unit_type (
    utyp_id serial NOT NULL PRIMARY KEY,
    utyp_name varchar(32) NOT NULL,
    utyp_description varchar(512) NOT NULL,
    created timestamp without time zone NOT NULL DEFAULT statement_timestamp(),
    modified timestamp without time zone NOT NULL DEFAULT statement_timestamp(),
    version SMALLINT NOT NULL DEFAULT 0
);

(I don't care about the column names; "createdAt" would be fine.) My old ORM didn't pay attention to the column defaults; the timestamp stuff was an intrinsic feature of it. (It was home-made so it has no name. It worked though.)

My current code via Sequelize all works fine, but I'm interested in getting the real database stuff worked out, and I just don't understand how the timestamp feature is supposed to work.

CodePudding user response：

You can overwrite the Sequelize's default timestamp function with hooks.

const sequelize = new Sequelize(
  ...
  {
    host: 'localhost',
    port: 5432,
    dialect: 'postgres',
    hooks : {
      beforeCreate : (record, options) => {
        record.dataValues.createdAt = Sequelize.fn('statement_timestamp');
        record.dataValues.updatedAt = Sequelize.fn('statement_timestamp');
      },
      beforeUpdate : (record, options) => {
        record.dataValues.updatedAt = Sequelize.fn('statement_timestamp');
      }
    }
  }
);