mysql-live-select
NPM Package to provide events when a MySQL select statement result set changes.
Built using the zongji
Binlog Tailer and node-mysql
projects.
- Example Application using Express, SockJS and React
- Meteor package for reactive MySQL
- NPM Package for Sails.js connection adapter integration
- Analogous package for PostgreSQL,
pg-live-select
This package has been tested to work in MySQL 5.1, 5.5, 5.6, and 5.7. Expected support is all MySQL server version >= 5.1.15.
Installation
-
Add the package to your project:
$ npm install mysql-live-select -
Enable MySQL binlog in
my.cnf
, restart MySQL server after making the changes.# Must be unique integer from 1-2^32 server-id = 1 # Row format required for ZongJi binlog_format = row # Directory must exist. This path works for Linux. Other OS may require # different path. log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = employees # Optional, limit which databases to log expire_logs_days = 10 # Optional, purge old logs max_binlog_size = 100M # Optional, limit log size
-
Create an account, then grant replication privileges:
REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'user'@'localhost'
LiveMysql Constructor
The LiveMysql
constructor makes 3 connections to your MySQL database:
- Connection for executing
SELECT
queries (exposednode-mysql
instance asdb
property) - Replication slave connection
information_schema
connection for column information
Arguments
Argument | Type | Description |
---|---|---|
settings |
object |
An object defining the settings. In addition to the node-mysql connection settings, the additional settings below are available. |
callback |
function |
Deprecated: callback on connection success/failure. Accepts one argument, error . See information below about events emitted. |
Additional Settings
Setting | Type | Description |
---|---|---|
serverId |
integer |
Unique number (1 - 232) to identify this replication slave instance. Must be specified if running more than one instance. Default: 1 |
minInterval |
integer |
Pass a number of milliseconds to use as the minimum between result set updates. Omit to refresh results on every update. May be changed at runtime. |
checkConditionWhenQueued |
boolean |
Set to true to call the condition function of a query on every binlog row change event. By default (when undefined or false ), the condition function will not be called again when a query is already queued to be refreshed. Enabling this can be useful if external caching of row changes. |
Events Emitted
Use .on(...)
to handle the following event types.
Event Name | Arguments | Description |
---|---|---|
error |
Error |
An error has occurred. |
ready |
None | The database connection is ready. |
Quick Start
// Example:var liveConnection = settings;var table = 'players';var id = 11; liveConnection;
See example.js
for full source...
LiveMysql.prototype.select(query, triggers)
Argument | Type | Description |
---|---|---|
query |
string or function |
SELECT SQL statement. See note below about passing function. |
triggers |
[object] |
Array of objects defining which row changes to update result set |
Returns LiveMysqlSelect
object
query
Function as A function may be passed as the query
argument that accepts two arguments.
- The first argument,
esc
is a function that escapes values in the query. - The second argument,
escId
is a function that escapes identifiers in the query.
Trigger options
Name | Type | Description |
---|---|---|
table |
string |
Name of table (required) |
database |
string |
Name of database (optional) Default: database setting specified on connection |
condition |
function |
Evaluate row values (optional) |
Condition Function
A condition function accepts up to three arguments:
Argument Name | Description |
---|---|
row |
Table row data |
newRow |
New row data (only available on UPDATE queries, null for others) |
rowDeleted |
Extra argument for aid in external caching: true on DELETE queries, false on INSERT queries, null on UPDATE queries. |
Return true
when the row data meets the condition to update the result set.
LiveMysql.prototype.pause()
Temporarily skip processing of updates from the binary log.
LiveMysql.prototype.resume()
Begin processing updates after pause()
. All active live select instances will be refreshed upon resume.
LiveMysql.prototype.end()
Close connections and stop checking for updates.
LiveMysql.applyDiff(data, diff)
Exposed statically on the LiveMysql object is a function for applying a diff
given in an update
event to an array of rows given in the data
argument.
LiveMysqlSelect object
Each call to the select()
method on a LiveMysql object, returns a LiveMysqlSelect
object with the following methods:
Method Name | Arguments | Description |
---|---|---|
on , addListener |
event , handler |
Add an event handler to the result set. See the following section for a list of the available event names. |
update |
callback |
Update the result set. Callback function accepts error, rows arguments. Events will be emitted. |
stop |
None | Stop receiving updates |
active |
None | Return true if ready to recieve updates, false if stop() method has been called. |
As well as all of the other methods available on EventEmitter
...
Available Events
Event Name | Arguments | Description |
---|---|---|
update |
diff , data |
First argument contains an object describing the difference since the previous update event with added , removed , moved , and copied rows. Second argument contains complete result set array. |
error |
error |
Unhandled errors will be thrown |
Running Tests
Tests must be run with a properly configured MySQL server. Configure test settings in test/settings/mysql.js
.
Execute Nodeunit using the npm test
command.
License
MIT