Knex.js is the most popular SQL query builder around and the go-to solution for most of us working with PostgreSQL. You can find dozens of articles on dev.to about how to get started, so I decided to focus on a more advanced and often overlooked topic on how to keep the updated_at fields really updated - automatically.
What does table.timestamps() do?
If you read along the documentation, upon creating a new table you will probably write a migration like this:
exports.up = function(knex) {
return knex.schema.createTable('products', function(table) {
table.increments('id').primary();
table.string('name');
table.timestamps(false, true);
});
};
The table.timestamps(false, true) line adds created_at and updated_at columns on the table. Both columns default to being not null and using the current timestamp when true is passed as the second argument.
While it's sufficent for the created_at column, the updated_at will remain unchanged even after an update query executed and it's your responsibility to keep it in sync.
There is a good reason behind this behaviour: different SQL dialects - like MySQL - handle automatic updating pretty well, but others, like PostgreSQL don't support it.
What is a Trigger Procedure in PSQL?
Think of a trigger procedures like middlewares in expressjs. You have the opportunity to execute functions that modify the inserted values before actually committing the update. The NEW value holds the new database row for INSERT/UPDATE operations, so setting the updated_at field is really easy:
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
Okay, okay, just give me the code already
First you need to create this trigger function in a migration using knex.raw:
exports.up = function(knex) {
return knex.raw(`
CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$;
`);
};
exports.down = function(knex) {
return knex.raw(`
DROP FUNCTION IF EXISTS update_timestamp() CASCADE;
`);
};
To make sure everything went fine execute the following query:
SELECT routine_name, routine_definition
FROM information_schema.routines
WHERE routine_type='FUNCTION' AND specific_schema='public';
+------------------+---------------------------------------------+
| routine_name | routine_definition |
|------------------+---------------------------------------------|
| update_timestamp | |
| | BEGIN |
| | NEW.updated_at = CURRENT_TIMESTAMP; |
| | RETURN NEW; |
| | END; |
| | |
+------------------+---------------------------------------------+
But how to use the function?
This function alone does nothing, we also need to tell the database engine where and when to use it. The best place for this the upcoming migrations where you create a new table - going back to my first example the code will be this:
const tableName = 'products';
exports.up = async function(knex) {
await knex.schema.createTable(tableName, function(table) {
table.increments('id').primary();
table.string('name');
table.timestamps(false, true);
});
await knex.raw(`
CREATE TRIGGER update_timestamp
BEFORE UPDATE
ON ${tableName}
FOR EACH ROW
EXECUTE PROCEDURE update_timestamp();
`);
};
exports.down = function(knex) {
return knex.schema.dropTable(tableName);
};
If you run the \d products command, at the bottom of the table you will see that the trigger function will be executed on each row update on this table.
> \d products
+------------+--------------------------+--------------------------------------------------------+
| Column | Type | Modifiers |
|------------+--------------------------+--------------------------------------------------------|
| id | integer | not null default nextval('products_id_seq'::regclass) |
| name | character varying(255) | |
| created_at | timestamp with time zone | not null default now() |
| updated_at | timestamp with time zone | not null default now() |
+------------+--------------------------+--------------------------------------------------------+
Indexes:
"products_pkey" PRIMARY KEY, btree (id)
Triggers:
update_timestamp_on_products BEFORE UPDATE ON products FOR EACH ROW EXECUTE PROCEDURE update_timestamp()
As always, your likes and feedbacks are much appreciated!
Top comments (2)
For anyone confused about the triger function,
If you have multiple migration files in a set order, you might need to artificially change the date stamp in the filename to get this to run first (or just add it to your first migration file).
I suggest moving the already made migrations, create a
npx knex migrate:make tigger_updated_atthem past the trigger funciton here and remake your sequence of migrations so the trigger function works first.But if you can't roll back, you might need to do this step manually via psql.
Thankyou for the post. It was quit informative
Thank you!