Options for Web Applications with Ruby on Rails - 15.6 Integrating a Database with Your Rails Application

(Page 3 of 4 )

Problem

You want your web application to store persistent data in a relational database.

Solution

The hardest part is setting things up: creating your database and hooking Rails up to it. Once that's done, database access is as simple as writing Ruby code.

To tell Rails how to access your database, open your applications config/database.yml file. Assuming your Rails application is called mywebapp, it should look something like this:

  development:
  adapter: mysql
  database: mywebapp_development
  host: localhost
  username: root
  password:

  test:
  adapter: mysql
  database: mywebapp_test
  host: localhost
  username: root
  password:

  production:
  adapter: mysql
  database: mywebapp
  host: localhost
  username: root
  password:

For now, just make sure the development section contains a valid username and password, and that it mentions the correct adapter name for your type of database (see Chapter 13 for the list).

Now create a database table. As with so much else, Rails does a lot of the database work automatically if you follow its conventions. You can override the conventions if necessary, but for now its easiest to go along with them.

The name of the table must be a pluralized noun: for instance, "people", "tasks", "items".

The table must contain an auto-incrementing primary key field called id.

For this example, use a database tool or a CREATE DATABASE SQL command to create a mywebapp_development database (see the chapter introduction for Chapter 13 if you need help doing this). Then create a table in that database called people. Here's the SQL to create a people table in MySQL; you can adapt it for your database.

  use mywebapp_development;

  DROP TABLE IF EXISTS 'people';
  CREATE TABLE 'people' (
  'id' INT(11) NOT NULL AUTO_INCREMENT,
  'name' VARCHAR(255),
  'email' VARCHAR(255),
  PRIMARY KEY (id)
  ) ENGINE=InnoDB;

Now go to the command line, change into the web application's root directory, and type ./script/generate model Person. This generates a Ruby class that knows how to manipulate the people table.

  $ ./script/generate model Person
  exists app/models/
  exists test/unit/
  exists test/fixtures/
  create app/models/person.rb
  create test/unit/person_test.rb
  create test/fixtures/people.yml

Notice that your model is named Person, even though the table was named people. If you abide by its conventions, Rails automatically handles these pluralizations for you (see Recipe 15.7 for details).

Your web application now has access to the people table, via the Person class. Again from the command line, run this command:

  $ ./script/runner 'Person.create(:name => "John Doe", \
  :email => "john@doe.com")'

That code creates a new entry in the people table. (If you've read Recipe 13.11, you'll recognize this as ActiveRecord code.)

To access this person from your application, create a new controller and a view to go along with it:

  $ ./script/generate controller people list
  exists app/controllers/
  exists app/helpers/
  create app/views/people
  exists test/functional/
  create app/controllers/ people_controller.rb
  create test/functional/ people_controller_test.rb
  create app/helpers/people_helper.rb
  create app/views/people/list.rhtml

Edit app/view/people/list.rhtml so it looks like this:

  <!-- list.rhtml -->
  <ul>
  <% Person.find(:all).each do |person| %>
  <li>Name: <%= person.name %>, Email: <%= person.email %
  ></li>
  <% end %>
  </ul>

Start the Rails server, visit http://localhost:3000/people/list/, and you'll see John Doe listed.

The Person model class is accessible from all parts of your Rails application: your controllers, views, helpers, and mailers.

Discussion

Up until now, the applications created in these recipes have been using only controllers and views.* The Person class, and its underlying database table, give us for the first time the Model portion of the Model-View-Controller triangle.

A relational database is usually the best place to store real-world models, but it's difficult to program a relational database directly. Rails uses the ActiveRecord library to hide the people table behind a Person class. Methods like Person.find let you search the person database table without writing SQL; the results are automatically converted into Person objects. The basics of ActiveRecord are covered in Recipe 13.11.

The Person.find method takes a lot of optional arguments. If you pass it an integer, it will look for the person entry whose unique ID is that integer, and return an appropriate Person object. The :all and :first symbols grab all entries from the table (an array of Person objects), or only the first person that matches. You can limit or order your dataset by specifying :limit or :order; you can even set raw SQL conditions via :conditions.

Here's how to find the first five entries in the people table that have email addresses. The result will be a list containing five Person objects, ordered by their name fields.

  Person.find(:all,
       :limit => 5,
       :order => 'name',
       :conditions => 'email IS NOT NULL')

The three different sections of config/database.yml specify the three different databases used at different times by your Rails application:

Development database
   The database you use when working on the
   application. Generally filled with test data.

Test database
   A scratch database used by the unit testing
   framework when running tests for your application.
   Its data is populated automatically by the unit
   testing framework.

Production database
   The database mode to use when your web site is
   running with live data.

Unless you explicitly setup Rails to run in production or test mode, it defaults to development mode. So to get started, you only need to make sure the development portion of database.yml is set up correctly.

See Also

1. Chapter 13
   
2. Recipe 13.11, "Using Object Relational Mapping with ActiveRecord"
   
3. Recipe 13.13, "Building Queries Programmatically"
   
4. Recipe 13.14, "Validating Data with ActiveRecord"
   
5. ActiveRecord can't do everything that SQL can. For complex database operations, you'll need to use DBI or one of the Ruby bindings to specific kinds of database; these topics too are covered in Recipe 13.15, "Preventing SQL Injection Attacks," which gives more on the format of the database.yml file

Next: 15.7 Understanding Pluralization Rules >>
 

More Ruby-on-Rails Articles
More By O'Reilly Media

Comments On: Options for Web Applications with Ruby on Rails

· This article is an excerpt from the book "Ruby Cookbook," published by O'Reilly. We...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 15 of the Ruby Cookbook, written by Lucas Carlson and Leonard Richardson (O'Reilly, 2006; ISBN: 0596523696). Check it out today at your favorite bookstore. Buy this book now.