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