DEV Community

Saulo Joab
Saulo Joab

Posted on • Updated on

How to get data from an MySQL database in React Native

DISCLAIMER

This article is pretty outdated.
I wrote it a long time ago and I didn't knew NodeJS that well back then :)

React Native is a wonderful tool that I've been using to develop my startup's app, AlluGo! Sometimes I would find myself struggling to do something as simple as getting data from a local MySQL database. So I decided to write this article to help anyone struggling with the same problem. Allons-y!

(By the way, I wrote this article in PT-BR here in case you guys are fellow brazilians)

Step[0]: Importing packages.

First of all, on your command prompt (yes I use windows plz dont kill me), go to your React Native project's folder, and use the following commands:

npm install express

npm install body-parser

npm install mysql

That might take a little while, but that's how it goes. And don't worry, I'll explain what each of these packages do later on.

Step[1]: Create your MySQL database.

I mean, you probably did that already... I use Wamp Server to handle my local MySQL databases, just in case you need it.

Step[2]: Routes.js.

In your React Native project (you can place it anywhere you want tho), create a file named ‘routes.js’. This will be our server (NodeJS), which we'll use to get data from the database.

Yes, that's right. You're creating a (simple) NodeJS server! You can do a full CRUD if you want but that's not the point of this article! :p
Here's my files:

Step[3]: Database connection.

Now things may get a little tricky, but don't worry. First, I'll show you the code then I'll explain what everything's doing. Check this out:

// This is the routes.js file!

const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql');

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

Ok. Beautiful, right? Let's go step by step. The first three lines are simply imports.

  • On the first line, we're importing Express. It's a Node framework, which will create our server routes. He's great for creating APIs and stuff.

  • On the second line, we're importing Body Parser. It will help us getting data from our request's body.

  • From the third line on, we're importing the MySQL module and creating a connection with our database. Once you do that, just fill it with your database's data (no pun intended). Follow this example:

const connection = mysql.createPool({
  host     : 'localhost', // Your connection adress (localhost).
  user     : 'root',     // Your database's username.
  password : '',        // Your database's password.
  database : 'my_db'   // Your database's name.
});

Step[4]: Getting data from a table.

Ok, now this may get a little tricky (I hope not). Again, I'll show the code and then explain it:

// We're still in routes.js! Right below everything else.

// Starting our app.
const app = express();

// Creating a GET route that returns data from the 'users' table.
app.get('/users', function (req, res) {
    // Connecting to the database.
    connection.getConnection(function (err, connection) {

    // Executing the MySQL query (select all data from the 'users' table).
    connection.query('SELECT * FROM users', function (error, results, fields) {
      // If some error occurs, we throw an error.
      if (error) throw error;

      // Getting the 'response' from the database and sending it to our route. This is were the data is.
      res.send(results)
    });
  });
});

// Starting our server.
app.listen(3000, () => {
 console.log('Go to http://localhost:3000/users so you can see the data.');
});

  • First of all, we start our Express app.
  • Then, we create a GET route named 'users', and that route will execute a query that will get all data from the 'users' table for us.
  • SIDE NOTE: The route and table name don't need to be equal!
  • After that, we start our server on the 3000 port.

Step[5]: Running everything.

Ok, cool. Now, how do we run our server? To do that, you don't need to run your React Native app yet.

  • Open your Command Prompt, navigate to your 'routes.js' and execute it. To do that, use this command: node routes.js

As you can see, there's a little message on your CMD like this: Go to http://localhost:3000/users so you can see the data.
Now, open your browser and go there. As you can see, IT'S WORKING!!11!! (probably)

Now, how do we get that data on our React Native App?

  • That's simple, we use the fetch function.
  • To do that, instead of using 'localhost:3000', you'll have to directly insert your PC's ip adress. If you use 'localhost', you're acessing your smartphone/emulator's localhost. And that's not what we want. Follow this example:
test(){
    fetch('http://yourPCip:3000/users')
      .then(response => response.json())
      .then(users => console.warn(users))
  }

All the data will be stored on the users variable. I added that function to a onPress event from a button. And as you can see, there's the data:

Go ahead and do whatever you want with that data! I hope everything worked out and I didn't confuse you... If something went wrong or you have any questions/etc, feel free to comment! This is my first ever post on dev.to :)

Top comments (55)

Collapse
 
baytelli profile image
Melih Telli

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.

Collapse
 
lamaalqasem profile image
lamaalqasem

Have you solved it ?

Collapse
 
saulojoab profile image
Saulo Joab

Hello, that's pretty weird. Have you tried running npm install crypto?

Sorry I took long to answer, this article is pretty outdated and I've been quite busy haha

Collapse
 
cphilipse profile image
CPhilipse

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?

Collapse
 
saulojoab profile image
Saulo Joab

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!

Collapse
 
cphilipse profile image
CPhilipse • 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?

Thread Thread
 
saulojoab profile image
Saulo Joab

I created a PR there, check it out.

Thread Thread
 
cphilipse profile image
CPhilipse

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 )

Thread Thread
 
saulojoab profile image
Saulo Joab

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.

Thread Thread
 
cphilipse profile image
CPhilipse

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

Collapse
 
gijshendriksen03 profile image
GijsHendriksen03 • 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?

Collapse
 
saadazghour profile image
Azghour-Saad

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

Collapse
 
saulojoab profile image
Saulo Joab

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.

Collapse
 
saadazghour profile image
Azghour-Saad

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

Thread Thread
 
saulojoab profile image
Saulo Joab

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'
});
Thread Thread
 
saadazghour profile image
Azghour-Saad

I've done it all, the same problem.

Alt text of image

Alt text of image

Alt text of image

Thread Thread
 
saulojoab profile image
Saulo Joab

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?

Thread Thread
 
saadazghour profile image
Azghour-Saad

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.

Alt text of image

Thread Thread
 
saulojoab profile image
Saulo Joab

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

Thread Thread
 
saadazghour profile image
Azghour-Saad

Finally,
Problem solved !!

i have added these lines :

Alt text of image

and MySQL port of my Database: Alt text of image

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...

Thread Thread
 
saulojoab profile image
Saulo Joab

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

Thread Thread
 
saadazghour profile image
Azghour-Saad

No problem, thank you a lot .

Collapse
 
tarunpandat profile image
Tarun Bhardwaj

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)

Collapse
 
saulojoab profile image
Saulo Joab

Are you sure that you installed Express?

npm install express

Collapse
 
vbaskani23 profile image
Veysel Baskani

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?

Collapse
 
saulojoab profile image
Saulo Joab

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

Collapse
 
bilginba profile image
Batuhan Bilgin • 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...

Collapse
 
saulojoab profile image
Saulo Joab

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...

Collapse
 
servicelevel profile image
servicelevel

Hi, I also get this error:

$ node app.js
Go to localhost:3000/users so you can see the data.
/home/gg/dev/sb/API/node_modules/mysql/lib/protocol/Parser.js:437
throw err; // Rethrow non-MySQL errors
^

TypeError: Cannot read property 'query' of undefined
at /home/gg/dev/sb/API/app.js:25:16
at Handshake.onConnect (/home/gg/dev/sb/API/node_modules/mysql/lib/Pool.js:58:9)
at Handshake. (/home/gg/dev/sb/API/node_modules/mysql/lib/Connection.js:526:10)
at Handshake._callback (/home/gg/dev/sb/API/node_modules/mysql/lib/Connection.js:488:16)
at Handshake.Sequence.end (/home/gg/dev/sb/API/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
at Handshake.ErrorPacket (/home/gg/dev/sb/API/node_modules/mysql/lib/protocol/sequences/Handshake.js:125:8)
at Protocol._parsePacket (/home/gg/dev/sb/API/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/home/gg/dev/sb/API/node_modules/mysql/lib/protocol/Parser.js:433:10)
at Parser.write (/home/gg/dev/sb/API/node_modules/mysql/lib/protocol/Parser.js:43:10)
at Protocol.write (/home/gg/dev/sb/API/node_modules/mysql/lib/protocol/Protocol.js:38:16)

the query method is undefined, but mysql module was installed with npm install mysql and is present

const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql');

const connection = mysql.createPool({
host : 'localhost',
port : 3306,
user : 'mysql user',
password : 'mysql password',
database : 'db_name'
});

// Starting our app.
const app = express();
app.use(bodyParser.json({type: 'application/json'}));
app.use(bodyParser.urlencoded({ extended: true }));
// Creating a GET route that returns data from the 'users' table.
app.get('/users', function (req, res) {
// Connecting to the database.
connection.getConnection(function (err, connection) {

// Executing the MySQL query (select all data from the 'users' table).
connection.query('SELECT * FROM customers', function (error, results, fields) {
  // If some error occurs, we throw an error.
  if (error) throw error;

  // Getting the 'response' from the database and sending it to our route. This is were the data$
  res.send(results)
});
Enter fullscreen mode Exit fullscreen mode

});
});

// Starting our server.
app.listen(3000, () => {
console.log('Go to localhost:3000/users so you can see the data.');
});

when i go to localhost:3000/users I the get following message on my browser

This site can’t be reached localhost refused to connect.
Try:

Checking the connection
Checking the proxy and the firewall
ERR_CONNECTION_REFUSED

when I launch the script with node app.js the program listens to port 3000 and outputs:
Go to localhost:3000/users so you can see the data.

then listens on...

when i go to localhost:3000/users and get the connection refused message, I can THEN see the query undefined error on my terminal

I added the

app.use(bodyParser.json({type: 'application/json'}));
app.use(bodyParser.urlencoded({ extended: true }));

as proposed before in the comments but still nothing.. added
port : 3306, in the connection info and still nothing as well...
any idea?

Collapse
 
garmanbeau profile image
garmanbeau

Hey I was getting this problem too, and this is more for any other researchers later on. I was able to resolve the problem by uninstalling mysql (npm uninstall mysql), and then I used yarn (which is another packet manager, if you don't have it you'll have to do something like npm install --global yarn) and then I switched the code to require mysql2 and I was able to view the data in the browser. Using different packet managers in a project is not recommended, so you could probably use npm install mysql2, but I think the main solution here is to use the mysql2 package.

Collapse
 
be__kind365 profile image
Be Kind!!!

Hey! If by chance anyone finds my comment, I'm a bit new to this and needed a little help with printing my data.
When it says "All the data will be stored on the users variable," how do I use "users" to get my data? Could someone show me an example code to do that? thank you so much!!

Collapse
 
rassemdev profile image
rassemdev

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

Collapse
 
saulojoab profile image
Saulo Joab

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 :)

Collapse
 
cphilipse profile image
CPhilipse

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!

Collapse
 
zackthedev profile image
mPH4NT0M

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.

Collapse
 
saulojoab profile image
Saulo Joab

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) {
Collapse
 
zackthedev profile image
mPH4NT0M • 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}))
      )} 

Thread Thread
 
saulojoab profile image
Saulo Joab

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}))
    )} 
Collapse
 
lamaalqasem profile image
lamaalqasem

Thanks a lot man! this is so helpful.
I installed all the libraries required, now it gives me an error "Unable to resolve module 'crypto' " Because it is used in mysql module. Do you know how to solve that? Because in npm page it says that it's no longer supported. npmjs.com/package/crypto

I have been struggling with this since yesterday :(

Collapse
 
yannb9 profile image
Yann Bohbot • Edited

Hey Saulo,

First off thanks for the great tutorial!
Unfortunately im stuck at a point where im using my react native app.
So first off, i built the node js and express and mysql code as you mentioned as follows:
Note that I created mysql database on a digitalocean server and have all the accesses possible for the user

const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql');

const connection = mysql.createConnection({
    host: '134.122.22.176',
    user: 'yannb_9',
    password: 'yannb_9',
    database: 'tiomanGrow'
});

// Starting our app.
const app = express();

connection.connect((err) => {
    if (err) {
        console.log('Connection error message: ' + err.message);
        return;
    }
    console.log('Connected!')
    // Creating a GET route that returns data from the 'users' table.
    app.get('/', function (req, res) {
        // Connecting to the database.
        // Executing the MySQL query (select all data from the 'users' table).
        connection.query('SELECT * FROM farmers', function (error, results, fields) {
            // If some error occurs, we throw an error.
            if (error) throw error;
            // Getting the 'response' from the database and sending it to our route. This is were the data is.
            res.send(results)
        });
    });
});

// Starting our server.
app.listen(3000, () => {

    console.log('Go to http://localhost:3000/farmers so you can see the data.');
});

no worries this is for testing so i don't care to share my mysql credentials.
So far when i run my route.js code and check my localhost:3000 i see the data i need. Great!

My issue is when i run the RN app.
this is my code:

 import React from "react";
 import { View, Text, StyleSheet, TextInput, TouchableOpacity } from "react-native";
 import { HeaderImg } from '../components/HeaderImg';
 import { Button } from '../components/Button';

export default class DB extends React.Component {
    state = {
        email: "",
        password: "",
        errorMessage: null
    };

    fetchData = async() => {
        fetch('http://134.122.22.176:3000/farmers')
        .then(response => response.json())
        .then(users => console.dir(users))
        .catch(error=> console.log(error))
    }


    render() {
        return (
        <View>
            <HeaderImg />

            <View style={styles.errorMessage}>
            {this.state.errorMessage && (
                <Text style={styles.error}>{this.state.errorMessage}</Text>
            )}
            </View>
            <Button 
            onPress={this.fetchData}
            />
        </View>
        );
    }
}



const styles = StyleSheet.create({

});

The error that i get is Network request failed. do you have any idea whatsoever that it could return this error to me?

Collapse
 
yannb9 profile image
Yann Bohbot

Hi !
So i followed every step of what you posted but I get a direct "Network request failed"
I'm using expo but should work right?

The routes.js works perfectly fine but when i try to use fetch i get the "Network request failed"

Collapse
 
ajeb2222 profile image
ajeb2222

Hey sorry if this is stupid question

im trying to capture data from MySQL and graph it using Victory. Already done most of the instruction above and i can see my data on local host but i still can't use the mysql data as the graph reference

this is part of the code to make the chart
Code

i always get this error when i try to run it
Code

i thought your fetch function already make users a variable and i can use it immediately for the another function. Could you tell me what should i do to fix it or point out where i went wrong?

Collapse
 
cubebank profile image
Cube Bank

pure shit

Some comments may only be visible to logged-in visitors. Sign in to view all comments.