CircleCI Code Climate Test Coverage

A node.js package that watches a MySQL database and runs callbacks on matched events.

This package is based on the original ZongJi and the original mysql-events modules. Please make sure that you meet the requirements described at ZongJi, like MySQL binlog etc.

Check @kuroski's mysql-events-ui for a mysql-events UI implementation.

npm install @rodrigogs/mysql-events

const mysql = require('mysql');
const MySQLEvents = require('@rodrigogs/mysql-events');
const program = async () => {
 const connection = mysql.createConnection({
 host: 'localhost',
 user: 'root',
 password: 'root',
 });
 const instance = new MySQLEvents(connection, {
 startAtEnd: true,
 excludedSchemas: {
 mysql: true,
 },
 });
 await instance.start();
 instance.addTrigger({
 name: 'TEST',
 expression: '*',
 statement: MySQLEvents.STATEMENTS.ALL,
 onEvent: (event) => { // You will receive the events here
 console.log(event);
 },
 });
 
 instance.on(MySQLEvents.EVENTS.CONNECTION_ERROR, console.error);
 instance.on(MySQLEvents.EVENTS.ZONGJI_ERROR, console.error);
};
program()
 .then(() => console.log('Waiting for database events...'))
 .catch(console.error);

Check the examples

• Instantiate and create a database connection using a DSN

  const dsn = {
   host: 'localhost',
   user: 'username',
   password: 'password',
  };
  const myInstance = new MySQLEvents(dsn, { /* ZongJi options */ });

• Instantiate and create a database connection using a preexisting connection

  const connection = mysql.createConnection({
   host: 'localhost',
   user: 'username',
   password: 'password',
  });
  const myInstance = new MySQLEvents(connection, { /* ZongJi options */ });

• Options(the second argument) is for ZongJi options

  const myInstance = new MySQLEvents({ /* connection */ }, {
   serverId: 3,
   startAtEnd: true,
  });

  See more about ZongJi options

• start function ensures that MySQL is connected and ZongJi is running before resolving its promise

  myInstance.start()
   .then(() => console.log('I\'m running!'))
   .catch(err => console.error('Something bad happened', err));

• stop function terminates MySQL connection and stops ZongJi before resolving its promise

  myInstance.stop()
   .then(() => console.log('I\'m stopped!'))
   .catch(err => console.error('Something bad happened', err));

• pause function pauses MySQL connection until #resume() is called, this it useful when you're receiving more data than you can handle at the time

  myInstance.pause();

• resume function resumes a paused MySQL connection, so it starts to generate binlog events again

  myInstance.resume();

• Adds a trigger for the given expression/statement and calls the onEvent function when the event happens

  instance.addTrigger({
   name: 'MY_TRIGGER',
   expression: 'MY_SCHEMA.MY_TABLE.MY_COLUMN',
   statement: MySQLEvents.STATEMENTS.INSERT,
   onEvent: async (event) => {
   // Here you will get the events for the given expression/statement.
   // This could be an async function.
   await doSomething(event);
   },
  });

• The name argument must be unique for each expression/statement, it will be user later if you want to remove a trigger

  instance.addTrigger({
   name: 'MY_TRIGGER',
   expression: 'MY_SCHEMA.*',
   statement: MySQLEvents.STATEMENTS.ALL,
   ...
  });
  instance.removeTrigger({
   name: 'MY_TRIGGER',
   expression: 'MY_SCHEMA.*',
   statement: MySQLEvents.STATEMENTS.ALL,
  });

• The expression argument is very dynamic, you can replace any step by * to make it wait for any schema, table or column events

  instance.addTrigger({
   name: 'Name updates from table USERS at SCHEMA2',
   expression: 'SCHEMA2.USERS.name',
   ...
  });

  instance.addTrigger({
   name: 'All database events',
   expression: '*',
   ...
  });

  instance.addTrigger({
   name: 'All events from SCHEMA2',
   expression: 'SCHEMA2.*',
   ...
  });

  instance.addTrigger({
   name: 'All database events for table USERS',
   expression: '*.USERS',
   ...
  });

• The statement argument indicates in which database operation an event should be triggered

  instance.addTrigger({
   ...
   statement: MySQLEvents.STATEMENTS.ALL,
   ...
  });

  Allowed statements
• The onEvent argument is a function where the trigger events should be threated

  instance.addTrigger({
   ...
   onEvent: (event) => {
   console.log(event); // { type, schema, table, affectedRows: [], affectedColumns: [], timestamp, }
   },
   ...
  });

• Removes a trigger from the current instance

  instance.removeTrigger({
   name: 'My previous created trigger',
   expression: '',
   statement: MySQLEvents.STATEMENTS.INSERT,
  });

• MySQLEvents class emits some events related to its MySQL connection and ZongJi instance

  instance.on(MySQLEvents.EVENTS.CONNECTION_ERROR, (err) => console.log('Connection error', err));
  instance.on(MySQLEvents.EVENTS.ZONGJI_ERROR, (err) => console.log('ZongJi error', err));

Available events

It has the following structure:

{
 type: 'INSERT | UPDATE | DELETE',
 schema: 'SCHEMA_NAME',
 table: 'TABLE_NAME',
 affectedRows: [{
 before: {
 column1: 'A',
 column2: 'B',
 column3: 'C',
 ...
 },
 after: {
 column1: 'D',
 column2: 'E',
 column3: 'F',
 ...
 },
 }],
 affectedColumns: [
 'column1',
 'column2',
 'column3',
 ],
 timestamp: 1530645380029,
 nextPosition: 1343,
 binlogName: 'bin.001',
}

Make sure the database user has the privilege to read the binlog on database that you want to watch on.

BSD-3-Clause © Rodrigo Gomes da Silva