How to access mysql database with socket.io

Question

I'm just getting into coding server side javascript and have been reading tutorials on socket.io and node.js, but I haven't come across anything demonstrating how to use node.js to access a mysql database.

Say for instance I want to create a method that listens to a table in my database at mysql.something.com (with database: database, username: username, etc), how would I get socket.io with node.js to connect to that database and listen for new input to that table and then subsequently return that input?

I'm wondering if anyone could give me a specific example that uses a publish subscribe model.

Thanks for the help.

Equinox

Socket.IO can't. You need to use Node's MySQL module (npm install mysql IIRC) and use that. It sounds like you've misunderstood the basic structure – Phillida

Oh alright. I guess I'll edit my question then. – Equinox

Answer 1

You have to poll mysql database for changes at regular interval and when detect a change emit a socket.io event. Here's a pseudo code

var mysql = require('mysql');
var connect = mysql.createConnection({
      host: 'localhost'
    , database: 'your_database'
    , username: 'user'
    , password: 'password'});

var initial_result;

// check for changes after 1 second

setTimeout(function(){

    connect.query('select * from your_table', function(err, result) {
        if(err) { throw new Error('Failed');}
        initial_result = initial_result || result;

        if(Changed(initial_result, result)) { socket.emit('changed', result); }

    });

    function Changed(pre, now) {
  // return true if pre != now
    }


}, 1000); 

Milicent

Thanks for the example but I was kind of looking for something that used more of a publish subscribe model rather than a polling one. – Equinox

As far as I know Mysql does not have that sort of feature – Milicent

can i do this by using mongodb. If i give setinterval means it will get database frequently. – Zapata

This is Node.js code ( server side js ) and recides on your server, not client ( i.e. web browser ). – Milicent

@Jack It's 100% secure if you do it 100% correctly. For example, it's not secure at all if your database allows anyone to log in without a password, or your password is publicly viewable, or if you don't escape queries, or don't have firewall rules, or don't harden your installation. I've seen too many databases in my time get hacked because of having poor authentication setups. But if you follow basic security practices then it's safe. In other words, it's no different than any other programming language in that regard. What's bad in practice in JS is also bad in PHP, Rust, Java, Python, etc. – Enchiridion