CODEWITHSUNDEEP
node.jssequelize.jsreal-time-updates
godzner
godzner

Reputation: 13

How can I know an update event on mysql using nodejs with mysql?

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?

Upvotes: 1

Views: 4871

Answers (3)

Inês Gomes
Inês Gomes

Reputation: 4821

You can use https://www.npmjs.com/package/mysql-events A Node JS NPM package that watches a MySQL database and runs callbacks on matched events.

Upvotes: 0

sudheeshcm
sudheeshcm

Reputation: 3418

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.

Upvotes: 1

mscdex
mscdex

Reputation: 106696

For MySQL, the easiest solution would be to set up something to 'tail' MySQL binlogs, such as zongji.

The other, less ideal/trivial solution would be to set up triggers in your database that call out to a custom database plugin that communicates with your process in some way.

Upvotes: 0

Related Questions