sails-mysql-live-select

A Sails connection adapter to use the mysql-live-select NPM Package in order to provide live (real time) result sets for models.

Example

See the chat room example application for a full working demonstration of this package.

Installation

To be used alongside the sails-mysql connection adapter.

MySQL server configuration

A MySQL server properly configured to output the binary log in ROW mode is required to use this adapter:

• Enable MySQL binlog in my.cnf, restart MySQL server after making the changes.

  # binlog config
  server-id        = 1
  binlog_format    = row
  log_bin          = /var/log/mysql/mysql-bin.log
  binlog_do_db     = employees   # optional
  expire_logs_days = 10          # optional
  max_binlog_size  = 100M        # optional

• Create an account with replication privileges:

  GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'user'@'localhost'

Package installation

1. Install the package:

   npm install --save sails-mysql-live-select

2. Add the connection settings to your config/connections.js:

     liveMysql: {
       adapter: 'sails-mysql-live-select',
       host: '127.0.0.1',
       // This user must have REPLICATION SLAVE, REPLICATION CLIENT and SELECT
       // privileges.
       user: 'USERNAME',
       password: 'PASSWORD',
       database: 'DATABASE',
       // Other settings used by mysql-live-select
       serverId: [Unique positive integer 1 - 2^32, default 1337]
       minInterval: [Minimum time in milliseconds between refresh]
     }

3. Add the adapter to be used on your models, for example in config/models.js:

     connection: [ 'mysql', 'liveMysql' ],

Usage

YourModel.liveFind(options, [condition])

This adapter adds a liveFind method to your models.

Argument | Type | Description ----------|------|----------- options | Object | Find options as defined by Waterline Query Language condition | Function | Optional function for validating if result set should be refreshed on row change. See condition function documentation for mysql-live-select

The liveFind method returns a LiveMysqlSelect object as defined by mysql-live-select.

For example, listen for the update event in your controller actions to send changes to the client:

var myLiveSelect = MyModel.liveFind({},
  function(row, newRow, rowDeleted) {
    // Optional data invalidation callback
    // Check if data is invalidated by this row change
    console.log('Row data', row);
    return true;
  }
).on('update',
  function(diff, data) {
    // Results have changed, send to client
    sails.sockets.emit(req.socket.id, 'chatDiff', diff);
  }
);

When done listening for updates to a query, be sure to call the stop() method on the returned LiveMysqlSelect object to prevent memory leaks.

Running Tests

# Configure MySQL server settings
vim test/config/connections.js
# Run suite
npm test

Test execution code is in test/config/bootstrap.js.

License

MIT