Integrating a Database with Your Rails Application

Table of contents:

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 thats 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. Heres 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,
	
ame VARCHAR(255),
	email VARCHAR(255),
	PRIMARY KEY (id)
	) ENGINE=InnoDB;

Now go to the command line, change into the web applications 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 youve read Recipe 13.11, youll 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:

	
	
	<% Person.find(:all).each do |person| %>
	Name: <%= person.name %>, Email: <%= person.email %
	>
	<% end %>

Start the Rails server, visit http://localhost:3000/people/list/, and youll 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.^[3] The Person class, and its underlying database table, give us for the first time the Model portion of the Model-View-Controller triangle.

^[3] More precisely, our models have been embedded in our controllers, as ad hoc data structures like hardcoded shopping lists.

A relational database is usually the best place to store real-world models, but its 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.

Heres 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 => 
ame,
	: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.

Integrating a Database with Your Rails Application

Problem

Solution

Discussion

See Also