How to auto update record with knex.js and MySQL

Question

This is my first post, so please correct me if I do something wrong.
I have some tables in MySQL that have the updated_at columns that I want to update it values whenever the tables are updated. It is very similar to this problem that was resolved this way:

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)

Otherwise, the linked problem is made with Postgres and my RDB is MySQL, so I would want to know if there is a way to do this solution with MySQL. I tried something like this but that way I can't dynamically get the table to be changed:

const CUSTOM_FUNCTIONS = `
ALTER TABLE NEW
    CHANGE updated_at
        updated_at TIMESTAMP NOT NULL
            DEFAULT CURRENT_TIMESTAMP
            ON UPDATE CURRENT_TIMESTAMP;
`

const DROP_CUSTOM_FUNCTIONS = `
DROP FUNCTION on_update_timestamp;
`

exports.up = async (knex) => knex.raw(CUSTOM_FUNCTIONS);
exports.down = async (knex) => knex.raw(DROP_CUSTOM_FUNCTIONS);

new as table name is a very bad idea, rename your table the rest is ok — nbk, Mar 13 '21 at 21:05
@nbk `NEW` is not the table name. In Postgres the other guy used `NEW` as the input table to do it relative. The main problem is exactly here, since I don't know how to receive any table with something like a `NEW` name... I don't know if I explained fine, so please answer me if the problem was clear. I will put the linked code in the post anyway. — Devinicius, Mar 13 '21 at 21:10
please see the documentation https://dev.mysql.com/doc/refman/8.0/en/alter-table.html ALTER TABLE has no NEW as Option at this position and expects the table name — nbk, Mar 13 '21 at 21:39
Exactly. So is there a way to get the current table relatively and pass it to raw? — Devinicius, Mar 13 '21 at 21:49
Okay. Thanks anyway. I'm trying a suggestion of a guy from Reddit and if it works fine I'm going to put it here. — Devinicius, Mar 13 '21 at 21:53
You could write a stired procedure with a list of table, and use dynamic sqlto genrate all trigers. with the information_Scheam you could also automate that — nbk, Mar 13 '21 at 22:00
Sorry for the delay, @nbk. Here is the link: https://www.reddit.com/r/node/comments/m4fe61/how_to_auto_update_record_with_knexjs_and_mysql/ — Devinicius, Mar 13 '21 at 22:13
Do you have some example of this solution that you proposed? I'm very amateur in SQL as a general, so I didn't understand very well. — Devinicius, Mar 13 '21 at 22:16
i read your article, and what it says is logical, only you have to do it in mysql with a different syntax and for each and every table, you could add stored procedure that does it for you and then drop it. but that means your triggers and have always teh same norm as name — nbk, Mar 15 '21 at 23:28

score 0 · Accepted Answer · answered Mar 15 '21 at 10:09

0

I tested this solution and it worked to me. It's not the best but for my projects it's enough. Basically, when I update my record, my controller automatically update the updated_at column with the current date.

answered Mar 15 '21 at 10:09

Devinicius

11
1
5

How to auto update record with knex.js and MySQL

1 Answers1