Skip to content

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

License

Notifications You must be signed in to change notification settings

rodrigogs/mysql-events

 
 

Repository files navigation

mysql-events

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.

Install

npm install @rodrigogs/mysql-events

Quick Start

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

Usage

#constructor(connection, options)

  • 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()

  • 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()

  • 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()

  • 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()

  • resume function resumes a paused MySQL connection, so it starts to generate binlog events again
    myInstance.resume();

#addTrigger({ name, expression, statement, onEvent })

  • 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, }
      },
      ...
    });

#removeTrigger({ name, expression, statement })

  • Removes a trigger from the current instance
    instance.removeTrigger({
      name: 'My previous created trigger',
      expression: '',
      statement: MySQLEvents.STATEMENTS.INSERT,
    });

Instance events

  • 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

Tigger event object

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.

LICENSE

BSD-3-Clause © Rodrigo Gomes da Silva

About

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

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages

  • JavaScript 97.3%
  • Shell 2.7%