Create real-time applications with Node.js and MySQL

In the previous example, Creating a real-time application with Node.js data was not stored permanently. This means that every time the Node server.js we restart, the data will be empty again. To be able to store the inputted data, we must use a database.

In this article, we will use the mySQL database that is already quite famous. The same technique you can use if you use another database.

The project we will create is a continuation of the project on Creating real-time applications with Nodes.js. If you don’t already have one, please follow the article.

Steps to create a realtime application with Node.js and MySQL

1. Download and install MySQL.
2. Create a MySQL database like the following script

CREATE TABLE 'voting' (
  'id' INTEGER(11) NOT NULL AUTO_INCREMENT,
  'name' VARCHAR(12) COLLATE utf8mb4_general_ci NOT NULL,
  'time' DATETIME NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY USING BTREE ('id')
) ENGINE=InnoDB
AUTO_INCREMENT=1 ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci'
;

3. Once we have a MySQL database, all we need next is a driver that connects Node.js with MySQL.
   Open cmd in the project folder we created (“C:bardimin), then type the command

npm install mysql

4. The next step we do is to edit the “server.js” file.
5. Next we add a script for the connection to MySQL on the file server.js

Creating MySQL connection.
var sqlCon = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: "',
    database: 'myDatabase', //Change to your database name
});

6. When the server is first run, we need to query the voting table as the initial data and assign the value of the variable “myData”.

Create initial data
var initData = function (_callback) {
    sqlCon.getConnection(function (err, connection) {
        if (err) {
            _callback (false)
        }
        connection.query('SELECT 'name', COUNT('name') AS 'total' FROM 'voting' GROUP BY 'name' ORDER BY 'name'', function (error, result) {
            connection.release();
            if (!error) {
                myData.akbar = result [0] .total;
                myData.goldie = result [1] .total;
                _callback (true);
            }
            if (error) {
                _callback (false);
            }
        });
    });
};

7. And when the user submits, we do the storage process on the voting table and update the variable “myData”

var addData = function (data, _callback) {
    sqlCon.getConnection(function (err, connection) {
        if (err) {
            _callback (false)
        }
        connection.query('INSERT INTO voting SET name= ?', [data] , function (error) {
            connection.release();
            if (!error) {
                if (data === "akbar") {
                    myData.akbar++;
                }
                if (data === "goldie") {
                    myData.goldie++;
                }
                _callback (true);
            }
            if (error) {
                _callback (false);
            }
        });
    });
};

8. The full script of the server.js file is as follows:

var app = require("express")();
var http = require('http'). Server(app);
var io = require("socket.io")(http);
var mysql = require("mysql");

Listening port
http.listen(3000, function () {
    console.log("Listening on 3000");
});

html file
app.get("/", function (req, res) {
    res.sendFile(__dirname + '/index.html');
});

Creating MySQL connection.
var sqlCon = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: "',
    database: 'yii2.socket', //Change to your database name
});

Global Variable
var myData = {akbar: 0, goldie: 0};
var isInitData = false;

Create initial data
var initData = function (_callback) {
    sqlCon.getConnection(function (err, connection) {
        if (err) {
            _callback (false)
        }
        connection.query('SELECT 'name', COUNT('name') AS 'total' FROM 'voting' GROUP BY 'name' ORDER BY 'name'', function (error, result) {
            connection.release();
            if (!error) {
                myData.akbar = result [0] .total;
                myData.goldie = result [1] .total;
                _callback (true);
            }
            if (error) {
                _callback (false);
            }
        });
    });
};

var addData = function (data, _callback) {
    sqlCon.getConnection(function (err, connection) {
        if (err) {
            _callback (false)
        }
        connection.query('INSERT INTO voting SET name= ?', [data] , function (error) {
            connection.release();
            if (!error) {
                if (data === "akbar") {
                    myData.akbar++;
                }
                if (data === "goldie") {
                    myData.goldie++;
                }
                _callback (true);
            }
            if (error) {
                _callback (false);
            }
        });
    });
};

This is auto initiated event when Client connects to Your Machien.
io.on("connection", (socket) => {
    console.log("A user is connected");

if (!isInitData) {
        initData(function (res) {
            if (res) {
                socket.emit('newData', myData);
                isInitData = true;
            } else {
                console.log("Initial data error");
            }
        });
    } else {
        socket.emit('newData', myData);
    }

socket.on('addData', (data) => {
        addData(data, function (res) {
            if (res) {
                io.emit('newData', myData);
                console.log("Add new data success");
            } else {
                console.log("Add new data error");
            }

});
    })
});

Good luck…