React, Nodejs & MySQL: Sending and receiving data from server to client (PART 1)

If you all have been keeping up with my blogs, you probably noticed i’m a huge NoSQL fan. Well, now we are going to learn a bit about how to write SQL transactions and send/receive data to/from the server. For this, we will need to know how to create a server, download the packages we will need, and a little knowledge of how to write SQL queries. In this tutorial, it is assumed you are already familiar with React, MySQL and Nodejs; this tutorial will teach you how to connect all three together, so that you can display information from the server to the client.

First things first — we need to create a server.

(I am assuming you already have Nodejs installed)

In the root folder of your React project, you should create a folder for your backend. I named mine “backend”, but you could name it whatever you prefer. Inside this folder, create a server.js file that will serve as your server. You can accomplish this in the terminal with the following commands:

(make sure you are at the root of your project)

mkdir backend

cd backend

touch server.js

After you run these commands in your terminal, if you look at your project directory, you’ll see that there is now a folder named backend with a file called server.js inside of it. Now, you need to install express and mysql2. If you have node installed, you should be able to run the following command to download these packages:

npm i express, mysql2

After you’ve installed these dependences, if you check your package.json, you’ll see that they are added under dependencies along with their version number. Now that we’ve installed our dependencies, we need to install MySQL using this link. For Mac users, be sure to download the DMG file.

When you download MySQL, it will take you through an installation wizard which will set up the software on your system. During this time, please be sure to remember the password you choose when it prompts you to enter a password. You will need this password to access your database.

After you’ve finished installing MySQL, we need to work on the server. Navigate to your server file and import both express and mysql2 like so:

const express = require(‘express’);

const app = express();

const mysql = require(‘mysql2’);

const port = process.env.port || 8080;

Now we need to tell the server to listen on port:

app.listen(port, () => {

console.log(‘Listening on port ‘ + port);

});

Now we need to set up our credentials so that we can access MySQL server:

let pool = mysql.createPool({

host:’localhost’,

user:’root’,

password:’your_password_you_assigned_at_installation’,

database:’database_you_want_to_manipulate’,

connectionLimit:10,

waitForConnections: true,

queueLimit: 0

});

After you’ve finished setting up your credentials, go to your terminal and type in:

mysql.server start

It’s going to prompt you for a password, at this time you should input your admin password (not the password you entered during MySQL installation). After you have entered your password, you should connect successfully. If you type in the terminal:

mysql -u root -p

You’re essentially telling the system that you want to use MySQL as a root user. After you enter this command, it will prompt you to input your password. This password is the one you created during installation. After getting in, you can view, update, create and delete db’s, tables, rows and columns from the terminal. Since we are here, we are going to create a database and table.

CREATE DATABASE students;

use students;

The first command creates a database called students while the second command tells the system we want to switch to the students database. Before you start writing select statements and such, you must first explicitly state which database you want to query using the “use” command. So, if you followed the above code, the students database should be ready to use.

Next, we are going to create a table:

CREATE TABLE contact (contactid int NOT NULL AUTO_INCREMENT, firstname varchar(255) NOT NULL, lastname varchar(255) NOT NULL, PRIMARY KEY(contactid));

Now, lets insert a row into our contact table:

INSERT INTO contact (firstname, lastname) values(‘Your’, ‘Name’);

If you type:

select * from contact;

into your terminal, you should see a box with the attributes and values you’ve assigned.

I think this is a good stopping point. For Part 2, I will teach you how to view data from the server in the client browser. For now, practice creating, updating and deleting rows, tables, etc. Also, look into some of the terms used that you aren’t very familiar with. Thank you for reading, and I hope to see you in Part 2

Database Administrator | Web Developer | Software Developer