I'm a newbie in Nodejs and I want to send data to the client when an update occurs on MySQL. So I found the ORM, Sequelize.
Can I know an update event from MySQL using Sequelize? Or how can I know an update event on MySQL using Nodejs with MySQL?
In case of MySql, triggers are the best option.
MySQL Triggers: a trigger or database trigger is a stored program executed automatically to respond to a specific event e.g., insert, update or delete occurred in a table.
For example:- You can have an audit table to save information regarding DATABASE updates or inserts.
Audit table sample for a employee table.
CREATE TABLE employees_audit (
id INT AUTO_INCREMENT PRIMARY KEY,
employeeNumber INT NOT NULL,
lastname VARCHAR(50) NOT NULL,
changedat DATETIME DEFAULT NULL,
action VARCHAR(50) DEFAULT NULL
);
Defining a trigger on employees table
DELIMITER $$
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_audit
SET action = 'update',
employeeNumber = OLD.employeeNumber,
lastname = OLD.lastname,
changedat = NOW();
END$$
DELIMITER ;
Then, to view all triggers in the current database, you use SHOW TRIGGERS statement as follows:
SHOW TRIGGERS;
At you backend you can have a polling mechanism (interval based db check) for audit table updates and notify the client accordingly. This can be done with a simple query to check for employees_audit update either by checking the row cound or based on created date time.
In case you donot need this extra table, you can have the same polling logic to check for updates on the employees table itself based on the update_at date time filed.