Ruby-on-Rails

15.6 Integrating a Database with Your Rails Application

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