I am doing a REST tutorial with Knex.js(0.19.0) and PostgreSQL(11-alpine, [email protected]), 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.

I am doing a REST tutorial with Knex.js(0.19.0) and PostgreSQL(11-alpine, [email protected]), 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.

• javascript
• node.js
• postgresql
• knex.js

3 Answers 3

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/guide/schema-builder.html#timestamps

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/#Schema-timestamps

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.