Real-time database view on HTML page with Socket.io

Question

I have a raspberry Pi that is constantly pushing data to a MySQL database via PHP. I am trying to create a website where I can see the contents of this database realtime.

I've been following this tutorial : http://markshust.com/2013/11/07/creating-nodejs-server-client-socket-io-mysql which shows an example on using socket.io for this purpose. This is working fine from 2 clients, when I add a new note it updates on both browsers. The problem is when I manually add a record to the database from mysql CLI, it does not update. I'm guessing this is because there is no emit happening. How can I implement this?

Server.js:

var mysql = require('mysql')
// Let’s make node/socketio listen on port 3000
var io = require('socket.io').listen(3000)
// Define our db creds
var db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'node'
})

// Log any errors connected to the db
db.connect(function(err){
    if (err) console.log(err)
})

// Define/initialize our global vars
var notes = []
var isInitNotes = false
var socketCount = 0

console.log("connected");

io.sockets.on('connection', function(socket){
    // Socket has connected, increase socket count
    socketCount++
    // Let all sockets know how many are connected
    io.sockets.emit('users connected', socketCount)

    socket.on('disconnect', function() {
        // Decrease the socket count on a disconnect, emit
        socketCount--
        io.sockets.emit('users connected', socketCount)
    })

    socket.on('new note', function(data){
        // New note added, push to all sockets and insert into db
        notes.push(data)
        io.sockets.emit('new note', data)
        // Use node's db injection format to filter incoming data
        db.query('INSERT INTO notes (note) VALUES (?)', data.note)
    })

    // Check to see if initial query/notes are set
    if (! isInitNotes) {
        // Initial app start, run db query
        db.query('SELECT * FROM notes')
            .on('result', function(data){
                // Push results onto the notes array
                notes.push(data)
            })
            .on('end', function(){
                // Only emit notes after query has been completed
                socket.emit('initial notes', notes)
            })

        isInitNotes = true
    } else {
        // Initial notes already exist, send out
        socket.emit('initial notes', notes)
    }
})

Index.html:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script src="http://localhost:3000/socket.io/socket.io.js"></script>
<script>
$(document).ready(function(){
    // Connect to our node/websockets server
    var socket = io.connect('http://localhost:3000');

    // Initial set of notes, loop through and add to list
    socket.on('initial notes', function(data){
        var html = ''
        for (var i = 0; i < data.length; i++){
            // We store html as a var then add to DOM after for efficiency
            html += '<li>' + data[i].note + '</li>'
        }
        $('#notes').html(html)
    })

    // New note emitted, add it to our list of current notes
    socket.on('new note', function(data){
        $('#notes').append('<li>' + data.note + '</li>')
    })

    // New socket connected, display new count on page
    socket.on('users connected', function(data){
        $('#usersConnected').html('Users connected: ' + data)
    })

    // Add a new (random) note, emit to server to let others know
    $('#newNote').click(function(){
        var newNote = 'This is a random ' + (Math.floor(Math.random() * 100) + 1)  + ' note'
        socket.emit('new note', {note: newNote})
    })
})
</script>
<ul id="notes"></ul>
<div id="usersConnected"></div>
<div id="newNote">Create a new note</div>

You need to have a separated Thread ( https://stackoverflow.com/questions/18613023/how-to-create-threads-in-nodejs ) that monitors the database for change so you can use socket emit when using MySQL as database... But it's is better and eazier to implement using PostgreSQL as database. Because PostgreSQL supports LISTEN/NOTIFY so SQL clients can get a notify with a event happens from within the database some examples ( http://bjorngylling.com/2011-04-13/postgres-listen-notify-with-node-js.html http://darrenoneill.co.uk/post/real-time-web-apps-postgresql-and-node/ ) .. — Raymond Nijland, Jun 08 '18 at 11:29
After some looking around i also found a UDF for MySQL which makes it possible a notify to a websocket server https://github.com/Cyclonecode/mysql-notification — Raymond Nijland, Jun 08 '18 at 11:34
Sounds easier to simply poll the database periodically (500ms or whatsoever), there is no such thing as real-time anyways. — Daniel W., Jun 08 '18 at 11:36

score 0 · Answer 1 · answered Jun 08 '18 at 11:32

This is similar to a previous question, where it appears there was no simple way to do this with MySQL.

If you are in an early enough stage of development that you are not tied to MySQL, then I will point out that you can solve this problem with postgresql:

Be pushed to from PHP via PDO library (see docs).
Runs on the Raspberry Pi.
Can detect updates pushed from anywhere on the command-line via pg_notify on a trigger (see docs).
Updates can be subscribed to with NodeJS via the pg package.

On a technical level this will work, but databases in general are not efficient as messaging systems (watch out for the Database-as-IPC anti-pattern). The PHP client could also emit its own notification when things happen, via a message queue, UDP socket, or something else.

Real-time database view on HTML page with Socket.io

1 Answers1