Close Up of a mining server rack.

Simple HTTP Server With NodeJS and MySQL

This tutorial will walk you through the process of creating a simple web server using NodeJS and MySQL. After finishing the tutorial, you will be able to run a local HTTP server and display data from a connected MySQL database. A basic understanding of the JavaScript programming language, SQL and Node environment is expected from the reader.

Preparation

Before getting started, make sure that everything is installed correctly. We will be using an Ubuntu 18.04 installation but you can easily adopt the steps for any other distribution or version.

Confirm that NodeJS and MySQL are installed correctly:

Now install the MySQL drivers for Node

Depending on your installation, you might have to run the installation command as super user. It is important to set a password for your user from within MySQL or you might have trouble connecting the server to it. Execute the code below to start MySQL from the command line and set your password.

Setting Up a Node HTTP Server

Creating a HTTP Server in NodeJS is fairly simple. All required libraries are natively included. We will start ‘importing’ the required modules.


We will use the ‘http’ module to handle incoming requests. Feel free to check out the official documentation for more information.

Note how the second import is optional. We will use child processes only to open a browser upon starting our Node server but this can also be done manually.

Here we create the server that will be used to handle incoming requests. For now, not much is happening. We add a listener that will respond to every request with a 200 Http status code. More of these codes and their meanings can be found here.

Incoming requests are answered with a simple response to display “Server running” without any further HTML content. We will later replace this response with content from the database.

Last thing left to do is choosing a port and starting the server. The above code will do exactly that, starting a local Http Server at the default port 8080 and start listening for incoming connections. The last line will simply print a message to indicate that the server is running. To start the server, run node server.js

Finally, add the below code at the end of your file to open up a web browser and see the “Server running” message. If this snippet, for some reason, fails to execute, do not worry. You can just leave it out and open the browser manually. Try to access ‘http://localhost:8080’ and proceed.

You can get the source code up until here from this repository. Download the files and checkout the tut_v1.0 tag. And, if you’re already on it, check out the other Social Media Accounts of codetowrite.

Creating a Database

The next step will be creating a simple database with MySQL. The example database will hold a selection of Items which are identified by their ID number. These items have a Price and a stock amount called Stock. Without regarding proper normalization, a single table will be enough to hold this information for now..

Start mysql mysql -u root -p(you might need to replace root with a different username, depending on your installation).

Now create a database and start using it:

Create a table with all the required columns:

The above code creates the columns id, price, stock and name in that order. Note how id is marked as primary key and will be used to identify rows within the table. All columns are set to be non-nullable to avoid missing entries once we start working with that data.

Lastly, enter some values into our new table. The INSERTcommand below can be repeated with entries of your personal liking.



Note how we do not specify an id number for the entries. This is because we have chosen it to increment itself with every new insertion (starting at number one), using AUTO_INCREMENT. This has the benefit of avoiding collisions that can happen if you try to use an id which is already in use.

If something went wrong or you simply don’t feel like entering your own values, feel free to check out items_table.sql. The file is located in the repository’s mysqlsubdirectory. Overwrite (!) your table like this:

Connecting NodeJS and MySQL

The final step is to connect the database to our web server. Import the required mysqlmodule like we already did with httpat the beginning of server.js.

Now set up the database connection by creating a connection object (database handle) and connecting it. Make sure mysql is up and running. Change the username and password according to what you have used previously.

It is important to write this code before you start the server (above server.addListener(...)that is). You do not want to create a new connection every time your server handles a http request.

Now remove the line that reads response.write("Server running);as we want to return all rows from the ITEMS table instead. These results are fetched by using a simple SELECTquery. Note how it is important to write DATABASE.TABLEthis time. Without having a database selected beforehand, like we did earlier by USE DATABASE ..., MySQL needs its queries to give precise information about the tables they are accessing. Otherwise the statement would be ambiguous if there was another database containing a table with the same name.

The code above shows how the code after response.writeHead(200)should look now. It is important to move response.endinside the inner callback ( .querycallback)! Leaving it at the end of the outer function ('request'listener) might lead to the response being sent before the SQL query returned. This introduces critical bugs that can be hard to debug, given the asynchronous nature of these callbacks.

If everything works well, run node server.jsagain. The output should look somewhat similar to what is shown below. If you seem to be stuck, download the GitHub repository and check for differences.

Finally, we can see the contents of our database in the browser, provided by our very own HTTP Server!

Outlook

This tutorial has set the foundation for future work on our web server. In the following tutorial we will redesign the website to be more useful by adding interactive elements and styling. In further parts we will implement a REST api perform operations on the data from our website. Also, we will expand and redesign the database itself quite a bit, so make sure to come back for the next tutorials!

Further Reading

HTTP Status Code Definitions: Link

NodeJS Documentation: Link

MySQL Documentation: Link


Leave a comment

Your email address will not be published. Required fields are marked *