Serving up CRUD

Create a full fledged data-driven commenting system.

Today's DB Objectives:

Lesson 1: HTTP requests

HTTP stands for Hypertext Transfer Protocol. This is the backbone of the internet and you use it almost perpetually. Let's break down the meaning:

When you typed "" into your browser and hit enter it issued an HTTP request. The request has headers and content, and the request got passed off from your machine to a whole range of other machines. A complex (distributed) lookup was done by computers in many places to find my server and ask my server to deliver (serve) what it deems most appropriate for someone searching for "".

To see the request you can open your javascript console and hit refresh.

To see which machines the request got forwarded to you can use a webapp which will show you: HTTP is intended to be stateless, so each request will get its response independently of all previous requests.

The HTTP request might look like this:

GET /index.html HTTP/1.1
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0) Gecko/20100101 Firefox/38.0
This is asking to GET (as opposed to PUT or DELETE or POST) the "resource" at "/" + "index.html" using HTTP specification 1.1 at the host GET is the method of the request, and the host plus the resource form the URL (uniform resource locator).

The HTTP Response might look like this:

HTTP/1.1 200 OK
Connection:  keep-alive
Content-Encoding:  gzip
Content-Language:  en
Content-Length:  35435
Content-Type:  text/html; charset=UTF-8
Date:  Thu, 11 Jun 2015 05:51:29 GMT
Last-Modified:  Tue, 09 Jun 2015 20:53:47 GMT
Server: Apache
Accept-Ranges: bytes
Connection: close

Random HTML document/resource/hypertext
Hi there, this is hypertext!

In general there are headers, the key: value pairs before the content, and content. There is also a STATUS CODE which is given in the first line, 200 OK is good 404 Not Found means a bad URL and 500 Server Error means the server screwed up.

The big idea is that each URL will identify some stateless resource out there, and all of the web pages (or in our case data) that someone might want will have a specific way to access it. In this way we can all refer to each other's data and have a standard protocol to speak.

Go ahead and try to look at some HTTP requests using javascript console from Chrome or Firebug from Firefox. Safari has a developer console, I think.

Lesson 2: REST APIs

An API (Application Programming Interface) is a program's contract with the world. If you ask in exactly THIS way I will give you exactly THAT. For instance we've seen that a GET request to will get an HTTP response including the contents of the file index.html on my server. This is a type of API. When you wrote your C++ program last time you made a simple API which was, run my executable and you'll get some random scrabble racks.

REST (Representational State Transfer) is a programming philosophy really. It is really a way of designing an API which is to let "resources" be given locations and let those resources get acted on by verbs. A REST API typically refers to having a URL be a resource and the HTTP Method by be the verb.

For instance, I could make a REST API for a program on my server that supports the following interface:

Please note that in most API specs a variable in the URL is preceded by a colon, so /items/:ItemID simultaneously refers to /items/2 for item number 2, and items/5 for item number 5.

Now many internet folk debate what constitutes a proper REST API and what doesn't but the general idea is an open HTTP driven way of interacting with your program. There are only a few rules that I stick to when designing such an API:

HTTP requests can send key: value pairs to the server for parsing. To practice the basic idea of an HTTP API I encourage you to try my AJAX (asynchronous javascript) playground. This lets you construct various requests with various payloads, send them to the server at a specified URL and have your request mirrored back to you. The code is available on GitHub in our class organization. Can you fork it, get it running on your cloud9, and make it respond to a request of GET /bananas with the string "Daylight come"?

Node version of an API

Lesson 3: New SQL commands

CRUD stands for Create Read Update Destroy, and they make the four most essential tasks of any data-driven program. Last time we learned about Reading from a database using SELECT. Now I want to add commands for the other three using SQL.

As a complete SQL API for select here is a flowchart of SQLite's reading of SELECT:

For Create, we have two types, creating tables, and creating records in the tables. In SQL the command to create a new table is CREATE and here is a pleasant diagram showing how SQLite3 parses a CREATE command:

A typical create command in SQLite might look like: CREATE TABLE tableNameHere (id integer primary key, column1 string, column2 integer). We will look at this far more in-depth when start designing databases in the next project.

This is our first database "key", which is a column (or set of columns) designed to uniquely identify a row. As we move into future commands this will be very important.

When creating a record we use an INSERT command to add to that table. It typically looks like this: INSERT INTO tableNameHere (column1, column2) VALUES ("andy is number ", 1). Here is a complete insert diagram:

For Updating a record that is already in a table we use something like: UPDATE tableNameHere SET (column1 = "relax") WHERE column2 = 1. Complete diagram here:

Destroying a record is very similar: DELETE FROM tableNameHEre WHERE column2 = 1. Here is the flowchart:

Let's build a super simple "blog" to practice these skills.

Do the following from the command line in a cloud9 session:

Lesson 4: Implement a CRUD REST API

In the page for project 1 there are some sample codebases for starter APIs, likewise earlier in today's work we tried the AJAX playground which has an API of sorts in it.

Last time we connected programs to the database, so this time let's see if we can react to the four different types of HTTP verbs.

Write six simple DB procedures as functions in either PHP or NodeJS:

Now try to call those functions in reaction to the various types of requests:

Lesson 5: Testing the API

Use POSTMAN to make requests of various types with various parameters to either /posts or /posts/:id.