Saulo Joab

Posted on Apr 26, 2019 • Edited on Feb 4, 2020

How to get data from an MySQL database in React Native

#sql #reactnative #node #javascript

Liquid syntax error: Unknown tag 'endraw'

Oldest comments (55)

raghavendrashanthamaraju • Jun 29 '19

Is this safe including database credentials in a client side scripting?

Saulo Joab • Jun 30 '19

The database credentials don't need to be stored in the client code. They're actually stored in the NodeJS server, and that server is supposed to be used outside of the client code (you can use services like Heroku or Glitch to host it).

I put that routes.js file (which is the NodeJS server) in the client code just for the sake of simplicity, but you can put it anywhere outside of the client code and it'll work just fine :)

Only thing you might worry about is protecting the server's URL in the fetch function that you'll use inside the client.

rassemdev • Jul 1 '19

What about storing data! Do you have any tutorial about that?

Saulo Joab • Jul 1 '19

Storing data is pretty easy as well. The main difference is that you'll need to make a POST request instead of a GET request. So it would be app.post instead of app.get.

You'll also need to install body parser so you can get your requisition body (ex: the field values) with NodeJS. I'm super busy right now so I can't make a new tutorial, but I'll make one as soon as I can :)

CPhilipse • Jul 23 '19

Super curious to see how you would do this. I found this article really helpful, but expanding it to where you can store and update these things, is something I haven't figured out yet. Can't wait!

Azghour-Saad • Jul 8 '19

Hi Saulo Joab, when i run this command 'node routes.js', i don't see any data in my browser ??

Saulo Joab • Jul 8 '19

Hello! Does your Terminal logs any errors? If all you get in the browser is "[]" it means that the query didn't return any values.

Azghour-Saad • Jul 8 '19

i get error like ' Cannot read property 'query' of
undefined', it doesn't know query method when retrieving data from db

Saulo Joab • Jul 9 '19

Are you sure that you installed the mysql module with:
npm install mysql

And imported it into the routes.js file with:

const mysql = require('mysql');

Also, make sure you create the connection with:

const connection = mysql.createPool({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});

Azghour-Saad • Jul 9 '19

I've done it all, the same problem.

Saulo Joab • Jul 9 '19

Huh. That's weird.

Try removing the comma after the database name. If that doesn't work, change mySql to lowercase (mysql). I really don't know what's wrong, maybe something with the npm installation?

Azghour-Saad • Jul 9 '19

Absolutely, this is really weird, I can't figure out this, i'm doing all what you say but he doesn't work, with npm i have this three packages completely.

Saulo Joab • Jul 9 '19

Is your MySQL database working fine on the localhost? I really can't see the problem...

Azghour-Saad • Jul 9 '19

Finally,
Problem solved !!

i have added these lines :

and MySQL port of my Database:

then it's pop up another problem these: (ER_NOT_SUPPORTED_AUTH_MODE)
then it solved with just executing this command:

alter user 'root'@'localhost' identified with mysql_native_password by 'password'

inside Query file, finally all is well.

source :

stackoverflow.com/questions/449462...

Saulo Joab • Jul 9 '19

That's awesome! I'm glad it worked out! Sorry I couldn't help you, that never happened to me...

Azghour-Saad • Jul 10 '19

No problem, thank you a lot .

glburgess • Jul 15 '19

This post was a heap of help to me, thank you so much! Could you add how someone would connect to a database with multiple tables?

Saulo Joab • Jul 16 '19

I'm super busy, but here's a video series that might help you: youtube.com/watch?v=0oXYLzuucwE

NodeJS is awesome!

mPH4NT0M • Jul 18 '19

I can see my json-formatted data in "/users" but on the homepage it says "CANNOT GET /".

I am using ReactJS with Visual Studio Code.

Saulo Joab • Jul 19 '19

That's because we only created the "users" route on this tutorial. Like this:

app.get('/users', function (req, res) {

If you want to create the homepage route, you gotta add the '/' char, like this:

app.get('/', function (req, res) {

mPH4NT0M • Jul 20 '19 • Edited

I am trying to fetch the data from '/users' and display it in the homepage.

fetch(){ 
    fetch('http://localhost:3000/users')
      .then(response => response.json())
      .then(output => (this.setState({output}))
      )}

Saulo Joab • Jul 21 '19

You can't use localhost because you're running the app in your cellphone. The server is running on your computer. If you use locahost the app will understand that the server is running on your cellphone, which is not correct. Here's how you should do it:

async fetch(){ 
    await fetch('http://yourComputerIPhere:3000/users')
      .then(response => response.json())
      .then(output => (this.setState({output}))
    )}

CPhilipse • Jul 25 '19

Hey Saulo, I've been trying to get app.post to work, so I can post the data from the registration form to the database, only I'm stuck understanding how it should work for react-native.
This is my app.post code:
app.post('/newUser', function(req, res, next)
{
// Creating our connection.
db.getConnection(function(err, connection)
{

    // Checking for errors.
    if(err) throw err;

    connection.query("INSERT INTO users (name, email, password) values('"+ req.body.name + "', '"+ req.body.email + "','" + req.body.password + "')", function (error, results, fields)
    {
        // Checking for errors.
        if(error) throw error;

        // Sending our response code.
        res.send(JSON.stringify(results));
    });
});

});
Now I'm confused to what I should put in replace for '/newUser'. If I understand correctly this is the route from where you can grab the information from. So let's say there is a form on /newUser, than you can grab the values of those input fields and put it in the query. But in my react native app you don't have url paths, so I'm confused to how I can grab the values from my form in my Registration class to put it in my query like above. Am I missing something?

Saulo Joab • Jul 25 '19

Hey, you're on the right path! If I understood correctly, you want to get values from your form and then send them to your connection.query string on the server, right?

To do that on ReactJS/React Native, all you gotta do is send a JSON object with the request configuration and your data. The JSON object with your data is called 'body'. Here's how you should do it:

async test(){
    await fetch('http://yourPCip:3000/newUser', {
      method: 'POST', // Here you're saying that you want to make a POST request. Could be any method, like a GET, for example.
      headers: '', // You can specify your requisition headers here. That line is optional.
      body: { // Here's the fun part. Put your data here.
        "name": this.state.name,
        "email": this.state.email,
        "password": this.state.password
      }
    })
    .then(response => response.json()) 
    .then(serverResponse => console.warn(serverResponse))
  }

If you really want to get the hang of it, I recommend you to study NodeJS (which is the server we're using). It's amazing and super useful!

CPhilipse • Jul 26 '19 • Edited

Yes that's what I want! Now I understand how it should work, I only have troubles with implementing it. I put my project on GitHub: github.com/CPhilipse/httprequest
I've put that code of yours in my Registration class, because that's where the state is. Now I call this async function when I click on the register button, but then I get this error: Possible Unhandled Promise Rejection (id: 0):TypeError: Cannot read property 'name' of undefined. I've looked for it on the internet and it's likely that it's because it takes time for the values to get in the state, which is why it won't find it. But when I console.log these values in a different function which I call when I click on register, it does just find it. So I've my doubts that that's the problem. I've put my PC ip in there, so it isn't that. Since it's a promise, it has to be from that function, but what am I missing?

Saulo Joab • Jul 26 '19

I created a PR there, check it out.

CPhilipse • Jul 26 '19

That's amazing! It works! I don't understand how those changes you made, calling the function differently, catch error and adding a header made it work, but thanks man! This is pretty cool! I've got one more question though, if I understand correctly this method/communication with the database will only work when the node server is started through this command: node Routes.js. Now let's say this application is going live and anyone can download this app. How is it supposed to work for those downloading it? Do I have to automatically make this command run for those people?
Also, with this code: await fetch('my_ip:3000/newUser', | Can I just leave this be? And will it still work for everyone? I guess I understand it working on localhost, but when everyone has the app, it's not localhost anymore that can work, right?
(I hope you can enlighten me on this. I'm not that familiar with the depths of networking, so excuse me if this a newbie question. Thanks in advance ^⁾

Saulo Joab • Jul 26 '19

I'm also a newbie, don't worry.

If you want other people to use your app, all you gotta do is host your database and your NodeJS server somewhere.
Tip: You can host your Node server on Glitch or Heroku for free.

Then, instead of inserting your computer ip on the fetch function, you would insert the hosted Node server URL :)

I highly recommend you to study node, and also to check out Heroku and Glitch.

CPhilipse • Jul 26 '19

Okay, that makes so much sense. I'll definitely check it out. Appreciate your help man!

GijsHendriksen03 • Jun 21 '20 • Edited

Hey man, I've been trying to get a insert to work but it won't work. I got this error in the query. And know I'm stuck. Any tips?

Veysel Baskani • Aug 6 '19

Hi guys, i need help for this topic, i have asyncstorage username data, and i need make a sql query with sql and this username. How can i do this topic? i cant understand anything to tell this solution? who can help me?

Saulo Joab • Aug 6 '19

Maybe this thread could help you: dev.to/cphilipse/comment/dckm

Batuhan Bilgin • Dec 25 '19 • Edited

Hi Saulo, thank you for the solution. In my project, I access the MySQL database. I can call and run queries through the program. If you inspect the attached image, I call queries and execute them until they become become dysfunctional or not called at all.

I found a solution and increased the maximum number of connections with the
SET GLOBAL max_connections = 150;

However, it didn't affect the outcome.
How can I overcome this situation?

I also posted this question on Stack Overflow.
If you want to inspect the code,

stackoverflow.com/questions/594727...

Saulo Joab • Feb 4 '20

Hey man, sorry I took so long. I've been absolutely busy haha

This post is pretty outdated, so instead of creating a lot of connections, you should create a connection pool.

This might help you: stackoverflow.com/questions/184965...

Tarun Bhardwaj • Jan 2 '20

I'm getting this error

module.js:549
throw err;
^

Error: Cannot find module 'express'
at Function.Module._resolveFilename (module.js:547:15)
at Function.Module._load (module.js:474:25)
at Module.require (module.js:596:17)
at require (internal/module.js:11:18)
at Object. (/home/pandat/Desktop/myapp/server.js:1:79)
at Module._compile (module.js:652:30)
at Object.Module._extensions..js (module.js:663:10)
at Module.load (module.js:565:32)
at tryModuleLoad (module.js:505:12)
at Function.Module._load (module.js:497:3)

Saulo Joab • Feb 4 '20

Are you sure that you installed Express?

npm install express

Melih Telli • Jan 14 '20

Hi I'm Sory bad englih.

I installed mysql package on my react native project but I get an error like this.

The crypto package should not exist according to the error.
But it's in node_modules.

error: bundling failed: Error: Unable to resolve module crypto from node_modules/mysql/lib/Connection.js: crypto could not be found within the project.