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.