1. Introduction to Databases
Introduction to working with data through databases
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!)
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.
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
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.
This series guides you through working with databases, from setting up and installing your database, storing and retrieving data and deploying to heroku
Introduction to working with data through databases
Getting started - installing your database engine
Getting started - adding dependencies to your project
Getting started - adding activerecord rake commands
Migration files are small Ruby scripts that make changes to your database
Schemas define the structure of your database table and allow ActiveRecord to structure requests and changes for data
Use Models to map a database table onto native ruby objects.
Linking Models to Routes. Making a CRUD API
Linking Models to Models - Adding Associations
How to deploy your project to Heroku and add a database
How to check the information you're adding to the database
How to create initial data with a Seed File