Knex.JS Auto Update Trigger

by

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.

Leave a Comment