For most of our projects, we’ll need to be able to store complex information. Using sessions or application variables just won’t cut it. Application variables will be lost if and when we restart our servers and session information can expire after a certain length of time too. These don’t make them ideal to maintain information.

Instead, we’ll use a database connected to our application to house the information we want to keep. Think of them as Excel on steroids. They’ve got tables (like sheets) and each table typically corresponds to an object or thing in our application that we want to keep info on. Each table then breaks down into columns (that describes the kinds of things we’ll store) and rows (the records of information we’ve got in our table).

There’s lots of options for databases too. Postgres, Mysql and SQLite are all good options. I’d recommend Postgres or MySQL if you’re going to do anything at scale, but SQLite is ideal for small self contained applications. It’s also perfect for working locally; because unlike postgres or mysql it creates a file you can see and inspect in your project folder. This means you can move the data with your project too. Another advantage is that it’s preinstalled on OSX (sorry window’s users!)

What is Postgres

Postgres is a powerful relational database. You can learn more about it on it’s main site.

Postgres it just one implementation of a SQL-based database, a store of information. You might have noticed the word ‘relational’ above. And that’s key. This kind of database allows us to make links across tables too (that’s something Excel doesn’t do). And it’s incredibly useful.

Imagine Instagram as a data store for a moment. Each person has an account- they’re a user of the system. That information is stored in a table. It’ll have a column for the user’s name, their email address, the user name they’ve selected, the date the signed up, etc. Each individual user will be stored as a row in the database with each required column populated with the needed bit of information. Each row will also have an ID that’s generated by the database. This is a number that uniquely identifies an individual user in the database table. This is known in database speak as a primary key - a unique field that makes it easy to identify a record.

But each person is also uploading photos. They’re probably in a table somewhere too. That table will have a link to the image (perhaps in multiple formats), when it was uploaded, etc. We’ll want to be able to associate these with users.

Thankfully, relational databases lets you make the link between users and photos. We’ll use the primary key for users (the id number) and add it as a required column for photos. Now each record doesn’t just have the information about a photo, but it says what user it belongs to based on the unique user id. It looks a little like this:

Users (id) ---- have many ----> Photos (user_id)
Photos (user_id) ---- belong_to ----> Users (id)

But users also follow other users. We can make those kinds of associations too. But more on that later.

The Relational Model & SQL

Relational databases use a program interface called SQL (Standard Query Language). SQL is currently used on practically all relational databases. Relational databases are extremely easy to customize to fit almost any kind of data storage. Relations can easily be created for items for sale, employees in a company, etc.

SQL is short for “Structured Query Language” – a special language to send requests to the database and get some results out of it (or data into it). While there is some standard for SQL – every SQL-database uses a slightly different dialect. Don’t be afraid – the basics are the same.

Accessing Information with a Database

While storing data is a great database feature, many database users consider quick and simple information retrieval the most important. It is extremely easy to pull up information regarding an employee in a relational database. Relational databases also add the power of running queries. Queries are requests to pull specific types of information and either show them in their natural state or create a report using the data. For instance, if someone had a database of employees that included tables such as salary and job description, he/she could easily run a query of which jobs pay over a certain amount. No matter what kind of information is stored on a database, queries can be created with SQL to help answer important questions.

Databases and Sinatra

There’s lots of way’s to play with databases in Sinatra. I personally like ActiveRecord because

• you don’t need to mess with SQL or write your own queries;
• it translates your database tables into Ruby objects; and
• it builds in most of the common tasks for accessing databases into a much nicer syntax and makes it really quick to connect your application to live data.

The downside is that it does have a lot of conventions built in and you’ll need to learn them to work with it effectively. Once you do, it’s fantastic.