how to make a realtime database with express app and socket io

Question

i'm create an application with express create app, the application designed to run in plesk hosting, i'm also already configure it so can run in plesk hosting by follow example from official plesk node express project(there's no problem with it). in this application have some important function:
- /routes/api.js rest API, retrieve data from client with post method, then save it into database.
- /routes/index.js dashboard, displaying data from database to dashboard views.
- realtime update, this is the function that need relationship between rest API and dashboard. So when client send the data from android client, it will captured and saved by restAPI, the dashboard also apdated too.

i'm ever use some package like socket.io just for test a simple chat application but dont know how to implement it in recent project.

so far, i just looking for solution from this forum, but doesnt found any answer that related to my problem.

note:
i'm using mysql database.

UPDATE:

here's my code in receive data from client /routes/api.js:

const conn      = require('../connection');
const express   = require('express');
const router    = express.Router();
//here is initiate socket.io, ok i know, but then for next i dont know

router.get('/', (req, res, next) => {
    res.send("test get page");
})

router.post('/', (req, res, next) => {
    var token       = req.body.token;
    var nmea        = req.body.nmea;
    var datetime    = req.body.datetime;

    conn.query("SELECT token FROM token WHERE token='" + token + "'", function(err, res){
        if(err)
        {
            console.error("ERROR: " + err);
        }
        else
        {
            result = res[0];
            if(result  != undefined)
            {
                //i think there's must be any code from socket.io around here?
                conn.query(" INSERT INTO nmea_data(nmea, datetime) VALUES ('" + nmea + "', '" + datetime + "')", function(err, res, fields)
                {
                    if(err)
                    {
                        console.error("ERROR: " + err);
                    }
                    else
                    {
                        console.info('data entered');
                    }
                });
            }
            else
            {
                console.error("token invalid");
            }
        }
    });
    res.status(200).json({
        message: "success",
    })
})

module.exports = router;

and how i get data from database /routes/index.js:

var express = require('express');
var router  = express.Router();
var conn    = require('../connection');

/* GET home page. */
router.get('/', function(req, response, next) {
  if(!req.session.admin)
  {
    response.redirect('/login');
  }
  else
  {
    var nmealist = [];
    //also around here.. like io.emit but i'm not sure
    conn.query("SELECT * FROM nmea_data", (err, rows, fields) => {
      if(err)
      {
        console.error(err);
      }
      else
      {
        for(i = 0; i < rows.length; i++)
        {
          var nmeadata = {
            'nmea' : rows[i].nmea,
            'datetime': rows[i].datetime
          }
          nmealist.push(nmeadata);
        }
        response.render('index', {"nmeaList": nmealist});
      }
    })
  }
});

module.exports = router;

please help me friends :)

Answer 1

You are going about this the wrong way. You want to capture data from the android client with socket.io but you are using routes for that. It doesn't make sense. Remove the routes and add something like:

io.on("connection", socket => {
    socket.on("postWhatever", payload => {
        // Do database stuff
    })
})

As for getting data from the database when updates. I'm not sure if this could be done with MySQL. But I know PostgreSQL has features like Notify/Listen.

If you found something unclear or I didn't answer your concern please express it in the comments.

EDIT: Seems like MySQL doesn't have real-time features and you have to keep pinging the database to update your user interface. Source