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 theupdated_at fieldsreally 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){returnknex.schema.createTable('products',function(table){table.increments('id').primary();table.string('name');table.timestamps(false,true);});};Thetable.timestamps(false, true) line addscreated_at andupdated_at columns on the table. Both columns default to being not null and using the current timestamp whentrue is passed as the second argument.
While it's sufficent for thecreated_at column, theupdated_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 updatingpretty well, but others, like PostgreSQL don't support it.
What is a Trigger Procedure in PSQL?
Think of a trigger procedures like middlewares inexpressjs. You have the opportunity to execute functions that modify the inserted valuesbefore actually committing the update. TheNEW value holds the new database row for INSERT/UPDATE operations, so setting theupdated_at field is really easy:
BEGINNEW.updated_at=CURRENT_TIMESTAMP;RETURNNEW;END;Okay, okay, just give me the code already
First you need to create this trigger function in a migration usingknex.raw:
exports.up=function(knex){returnknex.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){returnknex.raw(` DROP FUNCTION IF EXISTS update_timestamp() CASCADE; `);};To make sure everything went fine execute the following query:
SELECTroutine_name,routine_definitionFROMinformation_schema.routinesWHEREroutine_type='FUNCTION'ANDspecific_schema='public';+------------------+---------------------------------------------+|routine_name|routine_definition||------------------+---------------------------------------------||update_timestamp||||BEGIN|||NEW.updated_at=CURRENT_TIMESTAMP;|||RETURNNEW;|||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:
consttableName='products';exports.up=asyncfunction(knex){awaitknex.schema.createTable(tableName,function(table){table.increments('id').primary();table.string('name');table.timestamps(false,true);});awaitknex.raw(` CREATE TRIGGER update_timestamp BEFORE UPDATE ON${tableName} FOR EACH ROW EXECUTE PROCEDURE update_timestamp(); `);};exports.down=function(knex){returnknex.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.
>\dproducts+------------+--------------------------+--------------------------------------------------------+|Column|Type|Modifiers||------------+--------------------------+--------------------------------------------------------||id|integer|notnulldefaultnextval('products_id_seq'::regclass)||name|charactervarying(255)|||created_at|timestampwithtimezone|notnulldefaultnow()||updated_at|timestampwithtimezone|notnulldefaultnow()|+------------+--------------------------+--------------------------------------------------------+Indexes:"products_pkey"PRIMARYKEY,btree(id)Triggers:update_timestamp_on_productsBEFOREUPDATEONproductsFOREACHROWEXECUTEPROCEDUREupdate_timestamp()As always, your likes and feedbacks are much appreciated!
Top comments(2)
- LocationUkraine
- EducationMBBS
- Joined
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 anpx knex migrate:make tigger_updated_at them 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
For further actions, you may consider blocking this person and/orreporting abuse
