How to set `updatedAt` timestamp in Knex.js properly?

Question

I am doing a REST tutorial with Knex.js(0.19.0) and PostgreSQL(11-alpine, pg@7.11.0), and I notice that the updatedAt column does not work when I make PUT request and update the data.

Currently this is my users table:

// users_migration.js

exports.up = function(knex) {
  return knex.schema.createTable('users', function(table) {
    table
      .increments('id')
      .primary()
      .unsigned();
    table.string('firstName');
    table
      .string('lastName')
      .index()
      .notNullable();
    table
      .string('email')
      .unique()
      .index()
      .notNullable();
    table.string('password').notNullable();
    table.string('role').defaultTo('STAFF');
    table.boolean('isActive').defaultTo(false);
    table.timestamp('createdAt').defaultTo(knex.fn.now());
    table.timestamp('updatedAt').defaultTo(knex.fn.now());
  });
};

I have tried this:

    table.timestamp('createdAt').defaultTo(knex.raw('CURRENT_TIMESTAMP'));
    table
      .timestamp('updatedAt')
      .defaultTo(knex.raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

But it doesn't work either.

How do I make it work? Please help.

score 4 · Answer 1 · answered Aug 10 '21 at 13:58

4

When defining your schema, you can make use of the timestamps() method to automatically add a created_at and updated_at column.

knex.schema.createTable('users', (table) => {
  table.timestamps(true, true);
});

timestamps — table.timestamps([useTimestamps], [defaultToNow])

Adds created_at and updated_at columns on the database, setting each to datetime types. When true is passed as the first argument a timestamp type is used instead. Both columns default to being not null and using the current timestamp when true is passed as the second argument. Note that on MySQL the .timestamps() only have seconds precision, to get better precision use the .datetime or .timestamp methods directly with precision.

Source: https://knexjs.org/#Schema-timestamps

answered Aug 10 '21 at 13:58

marcobiedermann

4,317
3
24
37

why does it set the time with a weird format? `2022-01-22 03:25:28.464542+00` – Tim Bogdanov Jan 22 '22 at 03:26
4

@TimBogdanov Today's your lucky day, you get to learn about ISO-8601, arguably the most standard (and thus least "weird" :) ) format in existence: https://en.wikipedia.org/wiki/ISO_8601 - can you be more specific about what you mean by "weird" and what you expected? If you read the linked doc, you can see that if you set the first argument to false, it will use `DATETIME` fields instead of `TIMESTAMP`, which may be what you're expecting? – cincodenada Feb 23 '22 at 20:30
Further reference on ISO-8601: https://xkcd.com/1179/ – cincodenada Feb 23 '22 at 20:31

score 2 · Answer 2 · answered Jul 25 '19 at 07:39

postgresql does not support syntax CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

Only way to make updatedAt to be updated automatically when column is updated is to use triggers.

This might work (copy-paste from Update timestamp when row is updated in PostgreSQL):

exports.up = function(knex) {
  return knex.schema.createTable('users', function(table) {
    ...
    table.timestamp('updatedAt').defaultTo(knex.fn.now());
  })
  .raw(`
    CREATE OR REPLACE FUNCTION update_updated_at_column()
    RETURNS TRIGGER AS $$
    BEGIN
     NEW."updatedAt"=now(); 
     RETURN NEW;
    END;
    $$ language 'plpgsql';
  `)
  .raw(`
    CREATE TRIGGER update_user_updated_at BEFORE UPDATE
    ON ?? FOR EACH ROW EXECUTE PROCEDURE 
    update_updated_at_column();
  `, ['users']);

};

Please let me know if there is some syntax errors or anything like that.

score 2 · Answer 3 · answered Oct 15 '21 at 14:54

2

According to the documentation,

table.timestamps(true, true);

Add created_at and also updated_at alone. The second argument as true, refers to the date of now.

Source: https://knexjs.org/#Schema-timestamps

answered Oct 15 '21 at 14:54

Luiz Jr

21
5

How to set `updatedAt` timestamp in Knex.js properly?

3 Answers3