Meteor and Mysql

Some days ago, I was talking with a coworker about build a Meteor application and connect with an existent mysql database, after some research about this, I didn’t found too much documentation explaining how to do that, just what is available on numtel packages readme, so I decided to share my first experiment with Meteor and MySql. Just to understand and see things working I decided to modify that simple ‘click’ application created after a “meteor create” command, so it’s pretty simple but, I’m sure that it will clarify something for you, even if you are trying it for the first time like me.

#1 – Configure the MySql server

I’m assuming here that you already have MySql server installed somewhere, in my case, I used a virtual box with ubuntu, to simulate connections on remote hosts. Another thing is about node and npm, I’ll not explain here how to install them, to avoid the mix of subjects, but if you need to do that, I recommend you use nvm to install and manage your node installations.   So, let’s go. On your server “where is you MySql server”, install the numtel mysql-live-select npm package, this package will generate events, every time that your select statements result set change.

npm install mysql-live-select

Now edit the MySql my.cnf file and uncomment the following lines. Usually /etc/mysql/my.cnf

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

Restart your MySql server

sudo service mysql restart

or

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

And, create an account with replication privileges

GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'root'@'%';

 

#2 – Creating the MySql database

Connect on your MySql server and execute the following commands to create your new database and the table that we’ll use.

CREATE DATABASE IF NOT EXISTS `meteormysql`;
USE `meteormysql`;

CREATE TABLE IF NOT EXISTS `clicks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `counter` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

 

#3 – Connecting your Meteor application

Now, just create a new Meteor application

meteor create meteor-mysql

Remove autopublish and insecure packages

meteor remove autopublish insecure

Add the numtel mysql package

meteor add numtel:mysql

Create a new file called settings.json on the root of your project, to keep your database configurations. Do not forget to pass –settings settings.json when you start your application, eg: meteor –settings settings.json

{
  "mysql":{
    "host"      :   "192.168.1.11",
    "user"      :   "root",
    "password"  :   "",
    "port"      :   "3306",
    "database"  :   "meteormysql",
    "serverId"  :   "1",
    "minInterval":  "200"
  }
}

Ok, now your application is able to connect on MySql server,  let me show you my code after customize the default Meteor click application.

var click = undefined;
var clicks = new MysqlSubscription('clicks');

clicks.addEventListener('update', function(diff, data) {
  console.log(data[0].counter);
});

if (Meteor.isClient) {
  Meteor.methods({
    'incCounter': function(id, amount) {
      var filtered = clicks.filter(function(click) {
        return click.id === id;
      })[0];

      filtered.counter = parseInt(filtered.counter)+amount;
      clicks.changed();
    }
  });

  Template.hello.helpers({
    counter: function () {
      clicks.depend();
      var filtered = clicks.filter(function(click) {
        return true;
      });
      click = filtered[0];
      return click;
    }
  });

  Template.hello.events({
    'click button': function () {
      Meteor.call('incCounter', click.id, 1);
    }
  });
}

if (Meteor.isServer) {
  var liveDb = new LiveMysql(Meteor.settings.mysql);

  var closeAndExit = function() {
    liveDb.end();
    process.exit();
  };

  // Close connections on hot code push
  process.on('SIGTERM', closeAndExit);

  // Close connections on exit (ctrl + c)
  process.on('SIGINT', closeAndExit);

  Meteor.publish('clicks', function(){
    return liveDb.select(
    'SELECT * FROM clicks LIMIT 1',
    [ { table: 'clicks' } ]
    );
  });

  Meteor.methods({
    'incCounter': function(id, amount) {
      check(id, Number);
      check(amount, Number);

      liveDb.db.query(
        'UPDATE clicks SET counter = counter + ? WHERE id = ?', [ amount, id ]);
    }
  });
}

In the line 2, you can see an object called MysqlSubscription, this is what you’ll use for subscribe to a publication, nothing else is necessary like Meteor.subscribe. This line is subscribing on the publication at line 52, how you can see, this publication return the result of a SQL query. Subscription

var clicks = new MysqlSubscription('clicks');

Publication

Meteor.publish('clicks', function(){
    return liveDb.select(
    'SELECT * FROM clicks LIMIT 1',
    [ { table: 'clicks' } ]
    );
  });

This is a gif showing the application running. http://cl.ly/image/2F3A3d0R4431/meteor-mysql.gif

Conclusion

I hope this post could give you some idea of how to connect your Meteor app with MySql databases, there are other options like PostgreSQL and Neo4J available for Meteor, I’ll post about that in the future, but for while, I’ll try to do something more realistic with Meteor and MySql to share here.

Sources

https://github.com/allandequeiroz/meteor-mysql

https://github.com/felixge/node-mysql#connection-options

https://github.com/numtel/meteor-mysql

https://github.com/numtel/mysql-live-select

https://github.com/numtel/meteor-mysql-leaderboard/blob/master/leaderboard.html

https://github.com/creationix/nvm

 

Allan de Queiroz

Allan de Queiroz
London based software engineer

XServer forward from Linux text mode for Headless purposes.

Hello, this post is about XServer forward from Linux text mode, **not ssh forward, anything related to VNC** or things like that.Recently...… Continue reading