At the Forge - Rails and Databases

The third alternative, namely that of having an object-relational mapper, has become increasingly popular. The basic idea is pretty simple. Your program uses objects, and those objects are automatically transformed into rows, columns and tables in a relational database.

For many years, object-relational mappers have had all sorts of difficulties, particularly when working with sophisticated data sets. But they are now increasingly robust and impressive; and though I have not worked with either of them, Hibernate (for Java programmers) and SQLObject (for Python programmers) offer just these sorts of services, and Alzabo (described in this column several years ago) provides such services for Perl programmers. When implemented correctly, object-relational mappers provide the best of both worlds, including all of the speed, cross-language and maintenance benefits of a relational database along with the flexibility and consistency of working with objects from within the code.

When Rails burst onto the Web development scene about a year ago, its proponents touted the fact that Rails allows you to produce a Web/database application with almost no configuration and with very little code. And indeed, this is the case, thanks to several different features. One of the key features that makes this possible, however, is a sophisticated object-relational mapper known as ActiveRecord.

ActiveRecord is a Ruby class that is traditionally used as the parent of model classes within a Rails application. As you may recall, Rails uses the traditional model-view-controller (MVC) paradigm to build Web applications. Unlike some MVC application frameworks, Rails makes the differences between these explicit, creating models, views and controllers subdirectories within the application's app directory. A model class in Rails doesn't have to inherit from ActiveRecord, in which case it functions like any other data structure or class. But if it does inherit from ActiveRecord (or more precisely, from ActiveRecord::Base), the object knows how to store and retrieve its values from a table in a relational database.

At this point, you might be asking, “Wait a second—how is it possible that inheritance alone can provide an object-relational mapping? Don't I need to configure something?” The short answer, amazing as it might seem, is “no”. There is, of course, a slight trade-off, one that might bruise your ego if you aren't careful. Rails is able to accomplish this magic by forcing all programs to adhere to a particular set of conventions. Indeed, one of the Rails mantras is “convention over configuration.” If you are willing to name your tables, columns and objects according to the accepted convention, Rails will reward you handsomely. If you insist on using your own conventions, or if you want to connect Rails to an existing set of tables, you might find yourself struggling to implement even the simplest application.

So, how do we connect Rails to our database? Much of the documentation I have seen uses the popular open-source MySQL database for its examples; I strongly prefer PostgreSQL, and thus use it in my examples instead. However, you will soon see that the choice of a back-end database is almost invisible when it comes to Rails.

If you haven't done so already, install the Ruby Gems package, and then use the gem command to install Rails, all of its dependent classes and postgres-pr:

$ gem install --remote rails
$ gem install --remote postgres-pr

Now we use the rails command to create a new Rails application. If you still don't have the Weblog application we began last month, you can create it by typing:

$ rails blog

In many Web/database frameworks, the individual page or program must connect to the database each time. In Rails, the underlying system connects to the database for us, automatically tying the database connection to the ActiveRecord object class. The configuration is kept under the application directory in config/database.yml. No, that's not a typo; the extension is yml (YAML, or Yet Another Markup Language, or YAML Ain't a Markup Language), a simplified text format that is easier to read, write and parse than XML.

Traditionally, every Rails application uses three different databases, one each for development, testing and production. These three databases are created with a prefix that reflects the application name and a suffix that reflects its use (either development, test or production). For example, this is the database.yml file for the blog application:

development:
  adapter: postgresql
  database: blog_development
  host: localhost
  username: blog
  password:

test:
  adapter: postgresql
  database: blog_test
  host: localhost
  username: blog
  password:

production:
  adapter: postgresql
  database: blog_production
  host: localhost
  username: blog
  password:

Notice how the database adapter name is postgresql, even though I used the postgres-pr gem to connect to it. Also notice that the database is accessed by a user named blog. For this to work correctly, I now have to create the blog user in PostgreSQL (not as a Linux user):

$ /usr/local/pgsql/bin/createuser -U postgres blog
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

Now that we have created the blog user, we use it to create the three databases:

$ /usr/local/pgsql/bin/createdb -U blog blog_development
CREATE DATABASE
$ /usr/local/pgsql/bin/createdb -U blog blog_test
CREATE DATABASE
$ /usr/local/pgsql/bin/createdb -U blog blog_production
CREATE DATABASE

Finally, we should create a table in our database. We use only the development database for now, but we adhere to the convention of writing our table definitions in the blog/db directory, in a file named create.sql:

CREATE TABLE Blogs (
id           SERIAL   NOT NULL,
title        TEXT     NOT NULL,
contents     TEXT     NOT NULL,

 PRIMARY KEY(id)
);

I have already mentioned the importance of following Rails conventions when working with the ActiveRecord object-relational mapper, and the above table definition, as simple as it seems, already uncovers two of them. To begin with, every row has a unique ID field named id. (PostgreSQL, following SQL standards, has case-insensitive table and column names by default.) In PostgreSQL, we ensure that every row has a unique value of id by declaring it to be a SERIAL type. If you're like me, and have always used more explicit names (such as, blog_id) for the primary key, you'll need to change in order to work with Rails.

Another convention, and one that is a bit more subtle to notice, is that our table name is Blogs, a plural word. A class descended from ActiveRecord::Base is automatically mapped to a database table with the same name, but pluralized. So if we create a blog class that inherits from ActiveRecord::Base in models/blog.rb, it is automatically mapped to the blogs table in our database. As you can see, your choice of a name can affect the readability of your code; be sure to choose a name that makes sense in a number of different contexts, both singular and plural. (In this case, my choice of words was admittedly unfortunate, because each row of the Blogs table represents one posting, rather than one Weblog.)

But it gets better—we don't need to create blog.rb ourselves, at least not at first. We can ask Rails to create it for us, using script/generate. script/generate can be used to create a model, controller or view; in this case, we create our model:

ruby script/generate model blog

You will see some output that looks like this:

exists  app/models/
exists  test/unit/
exists  test/fixtures/
create  app/models/blog.rb
create  test/unit/blog_test.rb
create  test/fixtures/blogs.yml

If we open up app/models/blog.rb, we see that it's nearly empty:

class Blog < ActiveRecord::Base
end

Although we can (and will) add new methods to our Blog class, we can actually leave it as it stands. That's because ActiveRecord provides our class with enough skeleton methods that we can get by without them.

Although it's nice that we now have a Ruby class that is automatically mapped to our Blogs table in the database, we still have to access our table via the Web. This means we need to create a controller class, because controllers (the C in MVC) are the components in Rails that handle incoming HTTP requests. We can generate a controller automatically:

ruby script/generate controller blogadmin

Unfortunately, this controller isn't tied to our class at all. And although we could make such a connection ourselves, the fact that we're at the very beginning of our application definition means we can take a bit of a shortcut, asking Rails to generate an entire set of scaffolding, or bare-bones classes, that will do much of what we want. Creating such scaffolding is a great way to get jump-started with Rails development or even for working on a new project. At the same time, generating the scaffolding means blowing away class definitions you already have written. Because we have (so far) used only the default classes, this shouldn't be much of a problem.

We generate the scaffolded application with:

ruby script/generate scaffolding Blog Admin

(You should answer “Y” or “a” to replace one or all of the existing files, as appropriate.)

This creates a controller class named Admin that gives us basic access to a Blog class. The latter then connects to the Blogs table in the database.

With only the scaffolding in place, we can now start the server:

ruby script/server