Build MySQL Node.js CRUD App #1: Setting Up

Hello and welcome to the first article of Let's Build a MySQL Node.js CRUD App series. In this series, we shall build a beginner-friendly simple CRUD (Create, Read, Update, Delete) app with MySQL and Node.js. If you are a beginner in MySQL and Node.js, I encourage you to follow along and build the app with me!

Before we start, here's a brief overview of what to expect from this series.

The Project

We will be building a simple book reviewer app. You can add, view, edit and delete reviews in this app.

Technologies we'll use

React for front-end
Node and express for back-end
MySQL for database
POSTman to test our routes

Recommended Prerequisites

Basic understanding of React and JavaScript
Basic understanding of what back-end programming is
Basic understanding of SQL (Structured Query Language) and relational databases
Visual Studio Code or any code editor installed on machine

Step 1: Install Node.js

Download and install Node.js (with npm) at: nodejs.org/en/download

After it is installed, to check if it is there, go to your command prompt:

node -v
npm -v

Step 2: Install MySQL

You can install MySQL by visiting: dev.mysql.com/downloads/installer

Choose your operating system and click 'Download'. Then, you can open the installer and follow the steps on it to complete installing MySQL into your machine.

Step 3: Create React Project

Let's initialize a React app using the command:

npx create-react-app <app-name>

We will use React as a front-end framework to create our app's client-side.

Step 4: Install packages

For the back-end, let's install some packages we will need for this project.

The following packages we will install are:

mysql: to integrate our MySQL database
express: web framework for Node.js
nodemon: automatically restarts our server when new changes are made
dotenv : to use environment variables

In the root directory of your React app, run:

npm install mysql express body-parser nodemon dotenv

Next, let's initialize our MySQL Database for our app to connect to.

Step 5: Initialize MySQL Database

Open your MySQL Workbench software installed on your machine. Click on 'Local instance MySQL80' to log in as root. Your password is the one you set up while installing MySQL.

If you have issues and need to reset or change your password, I found this article to help.

Once you've successfully authenticated yourself, click on the icon in the top-left corner of the window (see image below) to create a new schema. Then, name the schema.

For this tutorial, I'm naming it ravenbooks, which is the name of the book reviewer app I'll build.

Next, let's create a table in ravenbooks. Click on the 'Schema' tab as shown in the image below. You should see your schema in the left panel.

Right-click on Tables to create a new table under ravenbooks. Then, you can name the table and start adding columns to the table.

For this app, we will have 4 columns:

id: primary key to identify each review in the database
book_title: the book title the review is for
book_review: the content of the review
book_rating: the rating of the book

The screenshot below shows the columns and their respective properties.

And that's all for now!

In the next part, we will learn how to connect our app to our MySQL database and start creating a simple GET, POST, DELETE routes. Stay tuned and thanks for reading this article. Please ask any questions in the comments below and do like or share this around if it's helpful. Cheers!