Node.js with MySQL (continued)

Hello there, user. Welcome to my second and final blog post describing my learning experiences with node.js and MySQL. My previous post went over the basics of what node.js is used for, and how to get a basic hello world project running. Additionally, I explored the tools that node provides used for querying the database and discussed briefly how they work. Today, I wanted to put all this research into one project to give a general demonstration on how MySQL can be utilized with a framework like node.js.

The following project I will be demonstrating has this structure:

What I wanted to do for this project was create a web application that would allow a user to view data which is stored in the database, and also add rows into the database. For this example I came up with the following database script:

Just a simple user table which takes a user id a first and a last name. Also added some test values.
The next step was to make sure I had all of the correct modules, so I went ahead and installed express, and the mysql2 modules. Another module that will be very important is the body-parser module
After I had all of the modules I would need I began writing my scripts, beginning with the app.js file, I set up a basic web server following the same steps I did in my first blog post. Then I added the database.js script.

This gets us connected to the database to prepare us for the queries we will write.
Now I could begin my attempt at displaying data to the front end. To do so, I created an ejs file titled queryPage.ejs which was written as:

userData will be explained in the next step which will be to create the route for queryPage. It will look something like this:

As you can see, userData is the json variable being set to the data that is returned from our query. And the query is just selecting all of the data from the users table. If you go back and take a look at the ejs page, you can see that the embedded javascript is looping through the userData and displaying the user id and first name of each user recorded in the database. This is a great example of how ejs can utilize embedded javascript to manipulate html elements.
Now that all the code for the queryPage was done we could move onto the addUsers page. Now this one was a little bit tricky and confusing as it uses some express elements that are not used while just doing select statements and displaying that data. This is the finalized app.js page which contains these extra functions:

You can see them written down on lines 10 and 11. One very important thing to note is that these must be written in the script before any of the routes are defined.
That addUsers.ejs page was very simple, just an html page with a form that takes data which will be inserted into the database:

The route that we will use for this page is the addUser.js script, and for this one we needed to do a post request rather than a get when we were retrieving data. The file should look something like this:

You can see on lines 12 14 that we are taking the fields that have been input and setting their value equal to their respective variable names. These will be used below on line 16 in our query to add the inputted values into the database.
Now that all of the scripts are written and we have all of our views set up it is time to run the project. To do so, make sure that all files are saved, then type the command into the terminal:
-node app.js

This will run the app.js file. To use the web application, go to that ip and port that is set and at the end of the url type: /queryPage
This will display the page with our select query showing all of the users. If you would like to look at the addUsers page you change the end of the URL to /addUsers.

Thank you for reading my blog on my experiences and studies of node.js with MySQL. Overall this was a mainly straightforward study with exceptions to some of the express module functions that I didnt know I needed until very late. If you are looking into making some sort of website using Node.js and MySQL I highly recommend it as it means that basically everything will be written in java script. There are also many other modules which can be downloaded to make your development environment the best for you to increase workflow and comfort. To conclude, Node with MySQL are absolutely great and useful softwares for any developer making web applications.