2

I am having some trouble related to the time it takes to get a response from a SQL query through an electron html frontend interface. The scenario involves a mysql server at localhost and a web application that eventually makes queries to the sql server, everything in the same device. The simplest query like

SELECT first_name FROM users WHERE id_no=1;

can even take more than 5s to resolve. However the same instruction "outside" the electron environment, i.e., in a .js file executed directly by nodejs is resolved in terms of msec, as expected. I have used both mysql and mysql2 node packages. So I have concluded that I am doing something wrong with electron. I am using the following code as a module for the sql queries, from How to provide a mysql database connection in single file in nodejs

sql_db.js code

// Dependencies
var mysql = require('mysql'); // also tried mysql2 with same results

/*
 * @sqlConnection
 * Creates the connection, makes the query and close it to avoid concurrency conflicts.
 */
var sqlConnection = function sqlConnection(sql, values, next) {

    // It means that the values hasnt been passed
    if (arguments.length === 2) {
        next = values;
        values = null;
    }

    var connection = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'root',
        database: 'office'
    });
    connection.connect(function (err) {
        if (err !== null) {
            console.log("[MYSQL] Error connecting to mysql:" + err + '\n');
        }
    });

    connection.query(sql, values, function (err) {

        connection.end(); // close the connection

        if (err) {
            throw err;
        }

        // Execute the callback
        next.apply(this, arguments);
    });
}

module.exports = sqlConnection;

Also, SQL queries are triggered from frontend events like clicking on a button or pressing the enter key, so the script reference in the HTML file is located at the latest line inside the body tag. As an example, in the .js file the code is like

page2.js code (example)

var mysql_query = require('./js/sql_db');

document.querySelector('#button01').addEventListener('click', function(e){
  // some code
  var query_instruction = 'SELECT firstname FROM users WHERE id_no=123 LIMIT 1;';
  mysql_query(query_instruction, { id: '1' }, function (err, rows) {
    if (err) throw err;
    console.log(rows[0].firstname) // this takes incredibly long!!
  });
});

To sum up, when running electron and clicking "button01" (as in the page2.js example) the result takes too long and not being always the same amount of time. However, if I run the following code

test2.js code

var query_instruction = 'SELECT firstname FROM users WHERE id_no=123 LIMIT 1;';
  mysql_query(query_instruction, { id: '1' }, function (err, rows) {
    if (err) throw err;
    console.log(rows[0].firstname) // this is resolved as fast as expected
  });

Running > node test2.js shows the result quickly, just as expected.

Any hints?

I have set nodeIntegration: true and the packages versions are "mysql": "^2.18.1" and "electron": "^9.0.2"

UPDATE So I have been trying different alternatives, being one of them the use of a python sql connector through the python-shell node package. While testing it, I was also keeping the javascript/sql related code and surprisingly, it started to behave as fast as expected. An example of what I currently have in my code would be something like

var mysqlwrapper_query = require('./js/sql_wrapper'); // this is using mysql package
var mysql_pywrapper_query = require('./js/sql_pywrapper'); // this is using python-shell package and runs a python mysql connector script
// some code
// ...
mysql_pywrapper_query (query_msg);
mysqlwrapper_query(query_msg, { id: '1' }, function (err, rows) {
  if (err) throw err;
  console.log(rows); // this now works perfectly, which is confusing
});

Python code run via python-shell in nodejs

import sys
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="office"
)

mycursor = mydb.cursor()

query = sys.argv[1]

mycursor.execute(query)

myresult = mycursor.fetchall()

print(myresult)

I would think this problem has to do with sql connection handling, but I am totally confused as I am theoretically starting & closing the SQL connection everytime I send a query (see sql_db.js code), so... Sending the same query twice (via javascript and via python) is not a solution at all.

UPDATE2 Also working perfectly if the python code ends at mycursor = mydb.cursor(). So it turns out to be something related to the way I connect to the database via nodejs. Anybody has any clue? For what I have seen, the way I establish the connection and send the query is correct... Everytime I need to send a query I require the module and reuse (copy/paste) the related code shown in page2.js example. For instance if I need to send a query in page2.html and page3.html, I have the page2.js and page3.js with that same code. I know I should avoid duplication but that will come after solving this.

damarre89
  • 21
  • 3

0 Answers0