Lab 9—Web applications

Objectives

• Build a web application connecting to a MySQL database
• See how CRUD and REST methods can work together
• Please pair-program today!

Pre-lab setup

This lab uses the MySQL database and schema from Lab 5. Make sure you have MySQL running and the store table schema created.

Background

Node.js is a popular JavaScript runtime. It uses Google Chrome’s V8 JavaScript engine (what’s built into Chrome to execute JavaScript on web pages). It uses npm as its package manager, which is similar to apt-get for Linux distributions like Ubuntu and Homebrew for macOS.

Exercise

First we will have to set up our working environments. This includes installing nodejs, npm, and build-essential.

Ubuntu

curl -sL https://deb.nodesource.com/setup_7.x | sudo -E bash -
sudo apt-get install -y nodejs build-essential

macOS

brew install node

We can make sure they’re both installed by checking their versions.

node -v
npm -v

Express

Express is a popular minimalist web framework for Node.js. It is completely written in JavaScript and provides helpful function and methods for your web application. We’re going to utilize their app generator to make our base app.

To install it we use npm (our node package manager) and we will be installing it globally (-g).

npm install express-generator -g

Now that we have our app generator installed let’s go ahead and make an app with our view engine set to pug (formerly known as jade).

express --view=pug tutorial

Let’s cd into our tutorial folder and check out a couple of files and folders.

• app.js - The main configuration for your web app, this is where “routes” to different pages can be declared and if wanted other packages can be ‘required’ and used.
• bin/www - This is our “server” file, it’s the main configuration for our websites server. You can change things like what port it’s on, etc…
• package.json - This is another configuration for your app, it’s kind of like meta data for the app, it holds all of the links to packages needed for the app to run, helpful information regarding the app, and commands to test it, one of them is npm start.
• public/ - This is where you can store publicly available scripts, images, or stylesheets. Your users will be able to see files in this directory on their browsers so storing sensitive information here is not recommended.
• routes/ - Routes are like links for your web app. If you want a subpage like localhost:3000/birds, you can make a route in this folder to help direct to the correct page and subpages. Also routes can do work for you, our routes will access our MySQL database and send (route) information to our pug files.
• views/ - This is where your page scripts are held. They are in pug (jade) and are how you display things, they can take and display variables passed from your routes.

Note that we might have a bunch of files but no actual server files or packages or anything, so we’ll type:

npm install

What this does is it checks our package.json file for any dependencies (packages) that are required for our server to run, and you’ll see there are several, and downloads the required files into a node_modules directory. This holds all of the methods that our packages need.

We’re going to need one more package saved to really get this show going though and that’s mysql.

npm install mysql --save

Now that all of the setup (it’s a lot of work to get running but pays off in the long run) let’s see if it works!

npm start

In this step we’re going to be setting up our routes for our website. We’re adding two called update and delete.

Create and display

In this step we make our functions for our possible HTTP methods. On our home page we’re only going to print (Read of CRUD) and submit (Create of CRUD).

First we make sure to require packages that will make this easy for us and also set our bodyParser to use json.

router.get

The landing page for our website, anytime you go to a html page the first call is most likely a get.

Inside we first make a connection with our database, go ahead and fill out the fields for this and play around with it! The docs for mysqljs are here.

Then we connect! After a quick error check we send our first query to our MySQL connection and then end the connection. It’s important to end the connection because if you don’t you might end up out of sync with servers and express/mysqljs will whine a lot. Either way, we have a quick error check again and if everything is good we render the index page (in our views folder) with our MySQL response in mysql_res.

router.post

This is also a function of our landing page as we will have the ability to add items to the store from the main page. The steps are similar to router.get but out query now is pass req.body. This is the information set with the put request from our HTML form. Then we redirect back to our home page to show the new item instead of rendering a new page.

But wait! How do we delete and update?

Remember making those two routes in our app.js file? Let’s actually make those now. Inside of the routes folder make update.js and delete.js. We’ll work on update.js first (Update of CRUD).

This looks a lot like your get and post functions but since we are in the update route our URL path will look like localhost:3000/update?id=5&name=pineapple&qty=100&price=1 due to our HTML form. Like usual we connect and make a query but this time thanks to express and mysqljs we can pass values from our URL straight to the query with req.query.<>.

We end the connection and redirect to ‘/.’ Now this might be confusing because this router is getting ‘/’ and the index router gets ‘/’ so how do we know which we are redirecting to? Redirecting is in terms of URL so localhost:3000 is index’s ‘/’ while localhost:3000/update is update’s ‘/’.

Delete

Now we’re working on delete.js (Delete of CRUD)

This one looks a lot like the update but now we have a different URL string to handle. ‘/:id’ requests look like localhost:3000/delete/54 for instance. With this functionality we can call delete on a specific item (by id) and delete it. Having :id also lets us pass the value into our get method as req.params.id. Just like last update we redirect to ‘/’ being the base URL localhost:3000.

Let’s see our finished product!

So this is pug (jade) and sure, you’re wondering where all of < these and those /> are but don’t worry, we’re working with something a little smarter now. So from the top we extend layout; layout.pug is another file in the directory and it has simple presets like a title and a style sheet. We start our block of content, saying now we’re done with declaring the header and the initial body tag.

We make h1 equal to a variable called title (passed from index.js) and then start a paragraph under that. In the paragraph we also embed the variable but we have to tell pug that it’s a variable and not just the word title with #{variable_name}.

Next we’ll start an un-ordered list and inside you can read it as for each value in MySQL (a variable that we passed from index.js) make a div. Inside of that div make a paragraph with values id, name, qty and price. After the paragraph generate a link that directs to /delete/val.id (maybe something like /delete/54?). And well, that was our Read of CRUD output!

The two forms that follow are Create and Update of CRUD. They’re given names (whatever you want really), our update is told to send to /update and then the method (whether it was post or put). The inputs are fairly explanatory, they have a given type (make sure this matches with your table columns), a placeholder to help identify them on the page and their “variable” name.

Credit

To get credit for this lab exercise, show the TA and sign the lab’s completion log.

Lab material by Kyle Helmick.