Best way to retrieve data from an external MySQL DB?

Discussion Ask a question
,
1

I’m new to Wix! Please forgive my ignorance. I’m trying to retrieve data from an external MySQL DB. I first approached the task by creating a back end “web module” on Wix using Node.js. I was able to install the node MySQL package and successfully connect to the external DB.

However, I’ve since been advised that this is not the smartest or safest way to go about doing this since credentials are exposed on the client side. Does anyone have suggestions for how this can be achieved?

I just want to make sure my efforts are being spent pursuing the correct approach. I’ve heard mention of creating an API on my DB and consuming on Wix via HTTP requests? This is new to me and I would have to research this but if it the preferred method then I will certainly look into.

Thanks

2

Hi,
The right approach depends on what you are trying to achieve, what is the use case?
There is no reason for credentials to be exposed on the client side unless you expose them, check out this article about working with npm modules.

3

Check the article Sync your MySQL Database to Wix finally working (Article) from @andreas-kviby .

4

Thanks for the reply - Just trying to pull a few fields of data and display the contents in an HTML frame. I was successfully able to connect to the DB and console.log the output but I cannot pass the data to the front end. I keep getting - “Uncaught (in promise) Error: Bad Gateway”. I’ve mimicked what I want to do locally with Node.js and it works so I’m wondering if it just something with the Wix environment. Do you have any ideas?

5

Thank you! I have taken a look and am using npm mysql . I was successfully able to connect to the DB and console.log the output but I cannot pass the data to the front end. I keep getting - “Uncaught (in promise) Error: Bad Gateway”. I’ve mimicked what I want to do locally with Node.js and it works so I’m wondering if it just something with the Wix environment. Do you have any ideas?

6

@polignano_francis How are you “passing” the data to the front end? You should have your backend code in web modules. Web modules are called from Frontend code. Keep in mind that Web Modules return a Promise. See the article Wix Code: How to Call Server-Side Code from the Front-End with Web Modules .

7

@yisrael-wix Thanks for the response! Yes, I’m using a backend “web module”. It uses node.js and the npm-mysql package. I have a function in the backend web module that connects to my DB and queries data. In this backend function, I can console.log the query from the DB and I can then call the function from the frontend and view the results of that console.log.

However, when I try to return the query from the backend code and store that in a variable in the front end, I get “promise pending”. I did a lot of googling and found that it’s returning a promise (which you mention in your post) which I think has to do with the asynchronous nature of JavaScript.

I modified my code a few different ways using promises and callbacks. I successfully get the result I want if I run it locally on node.js (outside of the Wix environment). This leads me to believe that I’m using the correct code for promises and I’ve figured out/fixed the promise pending issue. However, when I run in the Wix environment, I get the strange “Uncaught (in promise) Error: Bad Gateway”. I’m not really sure how to troubleshoot from here with this error. Do you have any ideas?

Thanks again

8

@yisrael-wix I found an old post of yours talking about promises and I tried to run it to make sure it worked and again it worked on my local node.js but not on Wix environment. I get error TypeError: Cannot read property ‘then’ of undefined.

Backend

export let promise = new Promise(function(resolve, reject) {
  setTimeout(() => resolve("test"), 1000); // wait 1000 milliseconds
});

Frontend

export function button1_mouseIn(event) {

promise.then(
  result => console.log(result), // shows "done!" after 1 second
  error => console.log(error) // doesn't run
);

}

Any ideas? I feel like I am so close! Thanks

9

All, I was able to figure it out!!! Below is the code. Thanks to all for the help

Backend

export function getEmployeeNames() {
 return new Promise(function (resolve, reject) {

 var mysql = require('mysql');
 var connection = mysql.createConnection({
 host: 'xxxxxx',
 user: 'bob',
 password: 'xxx',
 database: 'xxx'
        });

 connection.connect(function (err) {

 if (err) throw err;
 
 connection.query("SELECT Title FROM datasets", function (err, result) {
 if (result === undefined) {
 reject(new Error("Error result is undefined"));
                } else {
 resolve(result);
                }
            })
 connection.end();
        });
    });
}

FrontEnd

export function button1_mouseIn(event) {

getEmployeeNames()
    .then(function (results) {
        console.log(results);
    })
    .catch(function (err) {
        console.log("Promise rejection error: " + err);
    })
	//$w("#html1").postMessage(result);

}
10

@polignano_francis - Thanks for posting your code, I’ve been attempting to do the same thing for a while now. Using your code as a guide I’m able to connect to my external DB make a static query, retrieve data and then pass the data to the front end, Perfect!

My issue is when I try and pass a variable from the front end to the back end to change the actual query I’m making to the DB. The query is made, the data is retrieved in the backend, but then everything stops and the data is not passed back to the front end. Any thoughts? I do not receive any errors or any thing that looks wrong, my code just stops. Have you had any experience like this?

11

@polignano_francis With your code what modules to import, as at 3rd line in backend code it does not recognise ‘mysql’