Trigger to update mysql with current_timestamp() in knexjs

Issue

I’m using MySQL and would like that when a record is changed, the trigger is executed and updates my updated_at column in my database.

this is my migration file:

const CUSTOM_FUNCTIONS = `
  CREATE OR REPLACE FUNCTION on_update_timestamp()
  RETURNS trigger AS $$
  BEGIN
      NEW.updated_at = now();
      RETURN NEW;
  END;
`

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)

And this my knexfile.js

onUpdateTrigger: (table) => `
    CREATE TRIGGER ${table}_updated_at
    BEFORE UPDATE ON ${table}
    FOR EACH ROW
    EXECUTE PROCEDURE on_update_timestamp();
  `

And this my file knex to users:

exports.up = async (knex) => {
  await knex.schema.createTable('users', (table) => {
    table.increments('id')
    table.string('email').notNullable()
    table.string('password').notNullable()
    table.string('reset_token')
    table.timestamp('reset_expires')
    table.timestamp('created_at').defaultTo(knex.fn.now())
    table.timestamp('updated_at').defaultTo(knex.fn.now())
  }).then(() => knex.raw(onUpdateTrigger('users')))
}

When I run npx knex migrate:latest I get an error:

migration file "20210918131335_add_custom_functions.js" failed
migration failed with error:
  CREATE OR REPLACE FUNCTION on_update_timestamp()
  RETURNS trigger AS $$
  BEGIN
      NEW.updated_at = now();
      RETURN NEW;
  END;
 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FUNCTION on_update_timestamp()
  RETURNS trigger AS $$
  BEGIN
      NEW.updated' at line 1

Solution

I solved the problem with:

table.timestamp('updated_at').defaultTo(
  knex.raw('NULL ON UPDATE CURRENT_TIMESTAMP')
)

Answered By – João Lucas

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published