Passing data through the URL is one way to make the web application respond with dynamic content, but the most common paradigm for the dynamic web is "database backed applications", where the server queries the backend database and uses the result of that query to assemble a response to return to the client.
With this use case, the dynamic nature of the response is not coming from the data in the request, but from the "state" of the backend database.
This is a big topic, and we'll use several sections to cover it. We'll be working on creating a real web app, a blog, to walk through interactions with this paradigm.
To begin, let's give ourselves a database of blog posts to work with. We are going to use SQLite3, a lightweight database that Rails has built-in support for.
In dealing with posts in our database, we'll need to think about:
As for the structure, we'll want a posts table to hold the details of each post. Each post will have the following attributes:
id (integer)
title (string)
body (text)
author (string)
created_at (datetime)
Each of these will become a column for our posts table in our database.
To create this table, we'll be using the following SQL file:
db/posts.sql
CREATE TABLE "posts" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"title" varchar,
"body" text,
"author" varchar,
"created_at" datetime NOT NULL);
We'll then populate the resulting posts table with some dummy posts, using the following CSV (Comma-Separated Value) file:
db/posts.csv
1,Blog 1,Lorem ipsum dolor sit amet.,Brad,2014-12-07
2,Blog 2,Lorem ipsum dolor sit amet.,Chris,2014-12-08
3,Blog 3,Lorem ipsum dolor sit amet.,Kevin,2014-12-09
So, to create our database, execute our SQL against it and create a posts table inside it, we just run the following from the "Rails root directory", that is, the top-level directory in our Rails app that contains directories like app and db:
$ sqlite3 db/development.sqlite3 < db/posts.sql
For this project we'll be using SQLite3 as our DBMS (Database Management System). There are other much more powerful DBMS's out there, but we'll go with SQLite3 because it's the default development database for Rails and because it just works.
This command results in the file db/development.sqlite3 being created, which contains our SQLite3 database. And, since we executed our posts.sql file against it, it will have a posts table.
Let's run the SQLite3 console to take a look:
$ sqlite3 db/development.sqlite3
sqlite> .tables
posts
sqlite> SELECT * FROM posts;
sqlite>
Our posts table is there, but, as we can see from our blank return from that SELECT, there aren't any post rows in the table yet.
We can fix this by importing our CSV file of posts. We first have to tell SQLite3 that we're importing a CSV file, then we can perform the import like this:
sqlite> .mode csv
sqlite> .import db/posts.csv posts
Now let's check to see what we have:
sqlite> SELECT * FROM posts;
1,"Blog 1","Lorem ipsum dolor sit amet.",Brad,2014-12-07
2,"Blog 2","Lorem ipsum dolor sit amet.",Chris,2014-12-08
3,"Blog 3","Lorem ipsum dolor sit amet.",Kevin,2014-12-09
sqlite> SELECT title FROM posts;
"Blog 1"
"Blog 2"
"Blog 3"
sqlite> SELECT created_at FROM posts WHERE id=1;
2014-12-07
You can use the command .quit to exit the sqlite REPL.
There we go, we have some posts in our DB to play with now!
Before talking about how to work with this database in Rails, let's see how to interact with it with pure Ruby.
We'll need a way of connecting to our SQLite3 database from Ruby, and fortunately for us, there's a Ruby library named sqlite3 that lets us do just that.
$ irb
> require 'sqlite3'
=> true
# setup db connection
> connection = SQLite3::Database.new 'db/development.sqlite3'
=> #<SQLite3::Database:0x000000039ff288 ... >
Here we require the library to make it available to us, then we instantiate a SQLite3::Database object that will allow us to query our db/development.sqlite3 database.
So now we're all set to run SQL against our database, using Ruby:
> post_arrays = connection.execute 'SELECT * FROM posts'
=> [
[1, "Blog 1", "Lorem ipsum dolor sit amet.", "Brad", "2014-12-07"],
[2, "Blog 2", "Lorem ipsum dolor sit amet.", "Chris", "2014-12-08"],
[3, "Blog 3", "Lorem ipsum dolor sit amet.", "Kevin", "2014-12-09"]
]
We pass an SQL string to connection.execute to have it run against our database. The return from this connection.execute call is an array of arrays, with each representing a table row.
The arrays representing the post rows hold values in the following format:
[id, title, body, author, created_at]
Which makes sense, because that's the order that we declared the columns for the tables, but it's not particularly convenient. For example, if we want a post's title, we'd have to know that titles are at index 1 in these arrays, then use post[1] to grab the title string. Instead, it'd be nice to have these posts returned to us as hashes, so that if we want a post's title, we could simply use post['title'] to get it.
Conveniently enough, we can tell our connection to return these rows to us as hashes:
> connection.results_as_hash = true
=> true
> connection.execute('SELECT * FROM posts').first
=> {
"id" => 1,
"title" => "Blog 1",
"body" => "Lorem ipsum dolor sit amet.",
"author" => "Brad",
"created_at" => "2014-12-07",
0 => 1,
1 => "Blog 1",
2 => "Lorem ipsum dolor sit amet.",
3 => "Brad",
4 => "2014-12-07"
}
Now if we look at the first element in the array returned from the SELECT from before, we see that we get back a hash that'll let us get to a post title with post['title'].
Incidentally, we can still find the title with post[1], because the title is also stored in the hash with the integer key 1. These keys allow the hashes to act exactly like the arrays from before (in regard to value access using [] at least), even though they're not arrays anymore. Clever!
So now that we have some posts in a database and can get at them with Ruby, it's time to start building our web app.