Builidng an App Series Part 7: Databases and SQL

Imagine returning from a tropical vacation where you read almost all of the Game of Thrones books while laying on the beach. You walk into your apartment and open your backpack, wanting to complete the last 5 chapters of A Dance with Dragons.

OH SH*T! You left the book on the airplane. How are you going to finish it?

Well, you probably do what everyone else does when they lose something these days. You log on to Amazon and enter “dance dragons” in the top search bar.

For the sake of brevity, let’s say you finish ordering the book–choosing next day delivery, of course. In thinking about your ordering experience, you wonder what exactly happened on Amazon’s backend that allowed you to purchase the book so quickly.

To make a long, complicated explanation simple and not-so-complicated, Amazon took your query, “dance dragons”, and compared it to entries in its database. Amazon then showed you their inventory that matched your query.

When you clicked “Add to Cart”, entered your credit card information, and clicked “Place Order”, all of the information pertaining to the order–shipping address, shipping type, vendor, etc.—was also saved on the database.

In this article, we will talk about database persistence and using SQL queries to create, retrieve, update, and destroy database entries.

What is a Database?

Web applications store any and all pertinent information on databases. When you play your favorite song on Spotify, Spotify retrieves the necessary file from a database and serves it on either your web browser or desktop application. When we create dynamic web applications in Ruby, we need to store and access all sorts of information on a database.

When we send HTTP requests to an application’s server, the server uses SQL (Structured Query Language) to communicate with the database. SQL commands retrieve the requested information, which is sent back to the client’s browser.

Database Structure

A database’s structure is often referred to as its schema. When building dynamic web applications, software developers rely on intelligent schema design for quick information retrieval.

Within a database’s schema, tables organize data based on an application’s objects. In Part 3, we talked about how Ruby is an object oriented language and how we classify and organize different elements of our applications as objects (or classes). Well, each table in a database directly relates to an object. For CodeBuddy, our code resource aggregator, we want to storeReference objects in our database.

Columns and Column Types

Objects oftentimes have many attributes. If we were to store a reference object, we would probably want to store a reference’s a title, URL, and description. These attributes are stored in different table columns. Let’s take a look at what ourReference table looks like.

   Column    |            Type             |                        Modifiers
-------------+-----------------------------+---------------------------------------------------------
 id          | integer                     | not null default nextval('references_id_seq'::regclass)
 title       | character varying           | not null
 url         | character varying           | not null
 description | text                        |
 created_at  | timestamp without time zone |
 updated_at  | timestamp without time zone |

In our example, a reference’s attributes are organized in columns. A column’s “type” determines the type of input a database entry can have. Via intelligent schema design, developers can limit the types of input on different columns. If you need to perform mathematical functions on an object’s attribute, like counting inventory, you would designate a column’s type as “integer” or “float”.

“Character varying” is a database term for “string”–note that this column type is limited to 255 characters in the database. “Strings” are best used for email addresses, passwords, names, or anything that has otherwise established character limit (we can actually set the number of allowable characters in a database column).

A reference’s description is of type “text”, allowing it to accept a large amount of characters. The “text” type is best used for storing essays, comments, articles, and other large-bodied texts.

Attribute Modifiers

We can also set whether a an object’s attribute is required for creation. Going back to our Amazon example, say we fill out the shipping address form and forget to enter the city. Upon clicking “continue”, we are brought back to that page and reminded to fill in the “city” section of the form. How will Amazon know where to ship our book without a city?

We can protect against missing data by adding a “not null” modifier to an object’s column. This modifier tells the database that a record cannot be created unless this attribute is provided.

In future articles, we will discuss how to further specify the types of content that can be saved to an object’s column.

Serial ID

Before moving on, it’s important to discuss serial ids, which are represented in the first column of the reference table. Every object in a table has a serial id, which is assigned upon its creation in the database. Serial ids increment by 1 for every object added to the database. Knowing an object’s serial id will be helpful when we discuss object relations.

SQL

SQL (Structured Query Language) is a specialized programming language used for maintaining data in relational databases. Developers configure SQL statements to access certain information from a database. SQL controls the creation, retrieval, maintenance, and destruction of that information.

We have briefly touched on the different types of actions we would want to enact upon an object. In software development, we call them CRUD actions.

• CREATE
• RETRIEVE
• UPDATE
• DESTROY

As software developers, we have the power to dictate how, where, when, and why users perform a CRUD action on an item. If you think about it, we work with applications that perform these functions all of the time.

For every CRUD action, there is a SQL command that directly correlates with that action. Let’s explore those the most common SQL command, SELECT, together.

SELECT Statements

SELECT statements are used to retrieve information based on certain conditions. For this exercise, we’ll explore a books table in a database. See if you can notice any patterns.

Books

As discussed, we know that an element’s attributes are organized in columns. Say we wanted to retrieve the entry pertaining toGreen Eggs and Ham. We would write a SQL statement to do so.

SELECT * FROM books WHERE title="Green Eggs and Ham";

The SELECT * operator retrieves all information associated with a given query. We can retrieve individual columns of information as well. Let’s isolate the Of Mice and Men’s description.

SELECT description FROM books WHERE title="Of Mice and Men";

Here, we’ve isolated the description for Of Mice and Men in the SELECT Description declaration. The FROM Books part of the statement identifies which table we are retrieving our information from. The WHERE Title="Of Mice and Men" part of the statement tells the database to use the query assigned to Title to further narrow the query’s focus.

SELECT statements also retrieve multiple entries based on the attribute(s) assigned in the WHERE clause. Let’s isolate the titles and descriptions of the books in our database written by George R.R. Martin.

SELECT title, description FROM books WHERE author="George R.R. Martin"

SELECT statements are very powerful. When you search for something on Amazon, Amazon is comparing your query to entries in its database. Upon finding a match, it returns all of the entries that match your query. Think of your query as aSELECT statement.

SQL statements can very complicated based on what you’re trying to do. When we start building CodeBuddy, our code resource aggregator, we will utilize INSERT, UPDATE, and DELETE statements, in addition to SELECT statements, to effectively manage our application’s functionality. As discussed earlier, these SQL commands directly correlate with CRUDactions.

Check out SQLCourse.com for an awesome in depth tutorial on working with SQL databases.

PostgreSQL

There are a number of database programs at our disposal. At Launch Academy, we prefer to use PostgreSQL because both free and configurable with Heroku (we use Heroku to deploy our web applications to the web). Check out the PostgreSQL documentation for an in depth guide to PostgreSQL.

In Summary

The majority of dynamic web applications on the internet use relational databases to manage user generated data. Web developers use SQL statements to perform all sorts of task with relational databases. It may seem chaotic right now, but it is actually a very organized and methodical in its functionality. An understanding of these fundamental concepts is essential for becoming an effective web developer.

Next time, we will begin constructing CodeBuddy on the Sinatra framework. We will put all of the puzzle pieces together, constructing an object-oriented application in Sinatra.

Additional Resources

SQLCourse.com
The Bastard’s Book of Ruby
The Odin Project