Knex.JS automatic update trigger

Question

I am using the Knex.JS migration tools . However, when creating the table, I would like to have a column called updated_at , which is automatically updated when the record in the database is updated.

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"); }) 

By default, the created_at and updated_at correspond to the time the record was created, which is good. But, when this record is updated, I would like the updated_at column to show the new time when it was updated automatically.

I would rather not write to raw postgres.

Thanks!

Answer 1

You can create a knex move 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 that adds the created_at and updated_at , each of which contains an initial timestamp.

To save the current updated_at column, you 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 can 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.imtqy.com/objection.js/#timestamps

Answer 2

You can directly use this function.

 table.timestamps() 

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

http://knexjs.org/#Schema-integer

Answer 3

This is not a feature of Knex. Knex only creates columns, but does not update them.

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

• Documents in a bookshelf
• Github problem

Answer 4

Benny's answer is completely correct. However, I noticed that you specifically mention Postgres, in which case its syntax knex.raw will not work for you. Here is the method that I have used successfully.

Add function

If you have multiple migration files in the given order, you may need to artificially change the date in the file name to run it first (or just add it to the first migration file). If you cannot go back, you may need to complete this step manually using 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) 

The function should now be available for all subsequent migrations.

Define an auxiliary trigger knex.raw

I find it more expressive not to repeat large chunks of SQL in the migration files if I can avoid this. I used knexfile.js here, but if you don't like complicating this, you can define it everywhere.

 module.exports = { development: { // ... }, production: { // ... }, onUpdateTrigger: table => ` CREATE TRIGGER ${table}_updated_at BEFORE UPDATE ON ${table} FOR EACH ROW EXECUTE PROCEDURE on_update_timestamp(); ` } 

Use helper

Finally, we can quite conveniently define automatic update 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 deleting the table is enough to get rid of the trigger: we do not need an explicit DROP TRIGGER .

This may seem like a lot of work, but it is quite “remembered” after you have done this, and is useful if you want to avoid using ORM.