mysql-events


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

``sh
npm install @rodrigogs/mysql-events
`

Quick Start

`javascript
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
`javascript
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
`javascript
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
`javascript
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
`javascript
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
`javascript
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
`javascript
myInstance.pause();
`
### #resume()
- resume function resumes a paused MySQL connection, so it starts to generate binlog events again
`javascript
myInstance.resume();
`
### #addTrigger({ name, expression, statement, onEvent })
- Adds a trigger for the given expression/statement and calls the onEvent function when the event happens
`javascript
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
`javascript
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
`javascript
instance.addTrigger({
name: 'Name updates from table USERS at SCHEMA2',
expression: 'SCHEMA2.USERS.name',
...
});
`
`javascript
instance.addTrigger({
name: 'All database events',
expression: '*',
...
});
`
`javascript
instance.addTrigger({
name: 'All events from SCHEMA2',
expression: 'SCHEMA2.*',
...
});
`
`javascript
instance.addTrigger({
name: 'All database events for table USERS',
expression: '*.USERS',
...
});
`
`javascript
instance.addTrigger({
name: 'All database events for table USERS',
expression: '*.USERS',
...
});
`
- The statement argument indicates in which database operation an event should be triggered
`javascript
instance.addTrigger({
...
statement: MySQLEvents.STATEMENTS.ALL,
...
});
`
Allowed statements
- The onEvent argument is a function where the trigger events should be threated
`javascript
instance.addTrigger({
...
onEvent: (event) => {
console.log(event); // { type, schema, table, affectedRows: [], affectedColumns: [], timestamp, }
},
...
});
`
### #removeTrigger({ name, expression, statement })
- Removes a trigger from the current instance
`javascript
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
`javascript
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:
`javascript
{
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