Knex.JS Auto Update Trigger

Question

I am using Knex.JS migration tools. However, when creating a table, I'd like to have a column named updated_at that is automatically updated when a record is updated in the database.

For example, here is a table:

knex.schema.createTable('table_name', function(table) {
    table.increments();
    table.string('name');
    table.timestamp("created_at").defaultTo(knex.fn.now());
    table.timestamp("updated_at").defaultTo(knex.fn.now());
    table.timestamp("deleted_at");
})

The created_at and updated_at column defaults to the time the record is created, which is fine. But, when that record is updated, I'd like the updated_at column to show the new time that it was updated at automatically.

I'd prefer not to write in raw postgres.

Thanks!

Answer 1

With Postgres, you'll need a trigger. Here's a method I've used successfully.

Add a function

If you have multiple migration files in a set order, you might need to artificially change the datestamp in the filename to get this to run first (or just add it to your first migration file). If you can't roll back, you might need to do this step manually via psql. However, for new projects:

const ON_UPDATE_TIMESTAMP_FUNCTION = `
  CREATE OR REPLACE FUNCTION on_update_timestamp()
  RETURNS trigger AS $$
  BEGIN
    NEW.updated_at = now();
    RETURN NEW;
  END;
$$ language 'plpgsql';
`

const DROP_ON_UPDATE_TIMESTAMP_FUNCTION = `DROP FUNCTION on_update_timestamp`

exports.up = knex => knex.raw(ON_UPDATE_TIMESTAMP_FUNCTION)
exports.down = knex => knex.raw(DROP_ON_UPDATE_TIMESTAMP_FUNCTION)

Now the function should be available to all subsequent migrations.

Define a knex.raw trigger helper

I find it more expressive not to repeat large chunks of SQL in migration files if I can avoid it. I've used knexfile.js here but if you don't like to complicate that, you could define it wherever.

module.exports = {
  development: {
    // ...
  },

  production: {
    // ...
  },

  onUpdateTrigger: table => `
    CREATE TRIGGER ${table}_updated_at
    BEFORE UPDATE ON ${table}
    FOR EACH ROW
    EXECUTE PROCEDURE on_update_timestamp();
  `
}

Use the helper

Finally, we can fairly conveniently define auto-updating triggers:

const { onUpdateTrigger } = require('../knexfile')

exports.up = knex =>
  knex.schema.createTable('posts', t => {
    t.increments()
    t.string('title')
    t.string('body')
    t.timestamps(true, true)
  })
    .then(() => knex.raw(onUpdateTrigger('posts')))

exports.down = knex => knex.schema.dropTable('posts')

Note that dropping the table is enough to get rid of the trigger: we don't need an explicit DROP TRIGGER.

This all might seem like a lot of work, but it's pretty "set-and-forget" once you've done it and handy if you want to avoid using an ORM.

Answer 2

You can create a knex migration using timestamps:

exports.up = (knex, Promise) => {
  return Promise.all([
    knex.schema.createTable('table_name', (table) => {
      table.increments();
      table.string('name');
      table.timestamps(false, true);
      table.timestamp('deleted_at').defaultTo(knex.fn.now());
    })
  ]);
};

exports.down = (knex, Promise) => {
  return Promise.all([
    knex.schema.dropTableIfExists('table_name')
  ]);
};

With timestamps a database schema will be created which adds a created_at and updated_at column, each containing an initial timestamp.

To keep the updated_at column current, you'll need knex.raw:

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

To skip the knex.raw solution, I suggest using a high level ORM like Objection.js. With Objection.js you could implement your own BaseModel which then updates the updated_at column:

Something.js

const BaseModel = require('./BaseModel');

class Something extends BaseModel {
  constructor() {
    super();
  }

  static get tableName() {
    return 'table_name';
  }
}

module.exports = Something;

BaseModel

const knexfile = require('../../knexfile');
const knex = require('knex')(knexfile.development);
const Model = require('objection').Model;

class BaseModel extends Model {
  $beforeUpdate() {
    this.updated_at = knex.fn.now();
  }
}

module.exports = BaseModel;

Source: http://vincit.github.io/objection.js/#timestamps

Answer 3

This is my way of doing that in Mysql 5.6+

The reason I didn't use table.timestamps is because I use DATETIME instead of timestamp.

table.dateTime('created_on')
        .notNullable()
        .defaultTo(knex.raw('CURRENT_TIMESTAMP'))

table.dateTime('updated_on')
        .notNullable()
        .defaultTo(knex.raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

Answer 4

This is not a feature of Knex. Knex only creates the columns, but does not keep them up to date for you.

If you use, the Bookshelf ORM, however, you can specify that a table has timestamps, and it will set & update the columns as expected:

• Bookshelf docs
• Github issue

Answer 5

exports.up = (knex) => { return knex.raw(create or replace function table_name_update() RETURNS trigger AS $$ begin new.updated_at = now(); RETURN NEW; end; $$ language 'plpgsql'; create or replace trigger tg_table_name_update on table_name before update for each row execute table_name_update();) };

exports.down = (knex) => { return knex.raw(drop table if exists table_name; drop function if exists table_name_update;) };

Answer 6

You can directly use this function

table.timestamps()

This will create the 'created_at' and 'updated_at' columns by default and update them accordingly

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