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.

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 — Bojangles
– Bojangles, Commented Jul 12, 2013 at 15:25

az7ar · Accepted Answer · 2013-07-12 16:12:07Z

2

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);

answered Jul 12, 2013 at 16:12

az7ar

5,2372 gold badges21 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gavin Sellers Over a year ago

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.

az7ar Over a year ago

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

Vinoth Kumar Over a year ago

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

az7ar Over a year ago

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

aggregate1166877 Over a year ago

@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.

Collectives™ on Stack Overflow

How to access mysql database with socket.io

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related