How to connect to a MySQL database in Electron Framework

Electron Framework allow you to create Desktop apps easily using HTML, CSS and Javascript, that's already pretty impressive, however using the Javascript Engine of Chromium wouldn't be enough to create more dynamic apps that could accomplish with the same tasks that a native app does. Thanks to Node.js you'll be able to access a lot of features to create awesome apps, in this case we are going to use the mysql module to access a MySQL database within Electron.

Requirements

To interoperate with MySQL (in our case, we are using Xampp which includes PHPMyAdmin) using Node.js, you'll need the following node package named mysql.

You can add this package to your project executing the following command in the Node.js command prompt:

npm install mysql

Then you'll be able to require the mysql module using Javascript.

Note: this module is exception safe. That means you can continue to use it, even if one of your callback functions throws an error which you're catching using 'uncaughtException' or a domain.

Implementation

To get started, you need to learn first how to create a basic connection with your database with the mysql module. You need to create a connection using the retrieven object of require() and then provide as first parameter an object with the basic information about the connection (host,username,password and the name of the database):

var mysql = require('mysql');

// Add the credentials to access your database
var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : null, // or the original password : 'apaswword'
    database : 'ourcodeworld-database'
});

// connect to mysql
connection.connect(function(err) {
    // in case of error
    if(err){
        console.log(err.code);
        console.log(err.fatal);
    }
});

// Perform a query
$query = 'SELECT * FROM `myTableName` LIMIT 10';

connection.query($query, function(err, rows, fields) {
    if(err){
        console.log("An error ocurred performing the query.");
        console.log(err);
        return;
    }

    console.log("Query succesfully executed", rows);
});

// Close the connection
connection.end(function(){
    // The connection has been closed
});

When you create the connection, proceed to connect using the connect method and later perform a query using the query method of the connection. Finally close the connection using the end method.

You can customize and enable more complex functionalities reading the official documentation of the mysql module for node.js. Now that you have a little idea of how a connection with mysql works, you'll be able to perform your own queries, is up to you how to write and optimize them.

The following snippet, given a table with structure "ID,NAME" will display the first 10 rows of the Articles table in a html table:

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Hello World!</title>
    </head>
    <body>
        <h1>Connecting to MySQL</h1>
        <br>
        <input type="button" id="action-btn" value="Retrieve 10 first rows in the database" />
        <table id="table" border="1">
            <tbody>
                
            </tbody>
        </table>
    </body>
    <script>
        var mysql = require('mysql');

        function el(selector) {
            return document.getElementById(selector);
        }

        el('action-btn').addEventListener('click', function(){
            // Get the mysql service
            getFirstTenRows(function(rows){
                var html = '';

                rows.forEach(function(row){
                    html += '<tr>';
                    html += '<td>';
                    html += row.id;
                    html += '</td>';
                    html += '<td>';
                    html += row.name;
                    html += '</td>';
                    html += '</tr>';
                    console.log(row);
                });

                document.querySelector('#table > tbody').innerHTML = html;
            });
        },false);

        function getFirstTenRows(callback){
            var mysql = require('mysql');

            // Add the credentials to access your database
            var connection = mysql.createConnection({
                host     : 'localhost',
                user     : 'root',
                password : null,
                database : 'ourcodeworld'
            });

            // connect to mysql
            connection.connect(function(err) {
                // in case of error
                if(err){
                    console.log(err.code);
                    console.log(err.fatal);
                }
            });

            // Perform a query
            $query = 'SELECT `id`,`name` FROM `articles` LIMIT 10';

            connection.query($query, function(err, rows, fields) {
                if(err){
                    console.log("An error ocurred performing the query.");
                    console.log(err);
                    return;
                }

                callback(rows);

                console.log("Query succesfully executed");
            });

            // Close the connection
            connection.end(function(){
                // The connection has been closed
            });
        }
    </script>
</html>

And the result should look like: