NodeJs MySQL Observer

Hello,

In this blog we are going to learn how to use NodeJs to observe changes in MySql databases. This is useful when you want to track MySQL changes and based on that want to send some events to frontends or want to do any other actions.

For this first of all you have to enable binary logging in you database. Binary logging is very much useful for real time MySQL replication. In Amazon RDS, it's by default available and you can switch on it from configurations. For your local database if you are using MAMP, you can do following trick.

Create a file with name my.cnf and add following content to it.

[mysqld]
server-id = 1
default-storage-engine = InnoDB
log-bin=bin.log
log-bin-index=bin-log.index
max_binlog_size=100M
expire_logs_days = 10
binlog_format=row
socket=mysql.sock

Add this file to conf folder of your MAMP directory and restart MySQL server. This will enable binary logging in your database.

Now to observe this changes we use npm package called zongji . Install it with NPM.

Add following code to your NodeJs script.

var ZongJi = require('zongji');
var _underScore = require('underscore');

var zongji = new ZongJi({

    user : 'YOUR_USERNAME',

    password : "YOUR_PASSWORD",

    database: 'YOUR_DATABASE',

    socketPath : '/Applications/MAMP/tmp/mysql/mysql.sock'

});

Now add event on binlog.

zongji.on('binlog', function(evt) {

});

This event is triggered whenever there is a change in any of your database tables.

Inside this event you can have logic of checking new rows, updates rows, deleted rows.

zongji.on('binlog', function(evt) {

if (evt.getEventName() === 'writerows' || evt.getEventName() === 'updaterows' || evt.getEventName() === 'deleterows') {

var database = evt.tableMap[evt.tableId].parentSchema; 

        var table =  evt.tableMap[evt.tableId].tableName; 

        var columns = evt.tableMap[evt.tableId].columns; 

        _underScore.each(evt.rows, function(row) {
        });
}
});

At last start the process and pass the events you want to watch.
zongji.start({
  includeEvents: ['tablemap', 'writerows', 'updaterows', 'deleterows']
});