To implement auto-updating timestamps in Postgres, you can utilize a trigger. Below is an effective method that I have successfully used:
Step 1: Add a Function
If you have multiple migration files and need to ensure a specific order, you may have to adjust the datestamp in the filename or include it in the first migration file. For new projects, follow these steps:
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)
This function will now be available for all future migrations.
Step 2: Define a knex.raw Trigger Helper
To keep your migration files clean, define a helper function using knexfile.js or any preferred location:
module.exports = {
development: {
// ...
},
production: {
// ...
},
onUpdateTrigger: table => `
CREATE TRIGGER ${table}_updated_at
BEFORE UPDATE ON ${table}
FOR EACH ROW
EXECUTE PROCEDURE on_update_timestamp();
`
}
Step 3: Implement the Auto-Update Trigger
Easily set up auto-updating triggers with the following code snippet:
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')
Simply dropping the table will remove the trigger without needing a separate DROP TRIGGER command.
Although this setup may appear extensive, it becomes a seamless process once implemented, offering a non-ORM solution for automatic timestamp updates.