Ruby ActiveRecord and MySQL

Objective

We shall look at code examples explaining how to connect to a MySQL database using Ruby and ActiveRecord.

ActiveRecord

Rails uses ORM, which stands for object-relational mapping. ORM libraries map database tables to classes. If a database has a table called orders, our program will have a class named Order. Rows in this table correspond to objects of the class - a particular order is represented as an object of class Order. Within that object, attributes are used to get and set the individual columns. Our Order object has methods to get and set the amount, the sales tax, and so on. In addition, the Rails classes that wrap our database tables provide a set of class-level methods that perform table-level operations. For example, we might need to find the order with a particular id. This is implemented as a class method that returns the corresponding Order object.

So an ORM layer maps tables to classes, rows to objects, and columns to attributes of those objects. Class methods are used to perform table-level operations, and instance methods perform operations on the individual rows.

Active Record is the ORM layer supplied with Rails. Active Record relieves us of the hassles of dealing with the underlying database, leaving us free to work on business logic. ActiveRecord relies on convention over configuration. Wherever possible, ActiveRecord guesses the correct configuration by reflecting against the data schema. When you do need a specific override, you specify the override directly in your class.

Also, Rails assumes that:

  • database table names, like variable names, have lowercase letters and underscores between the words.
  • table names are always plural.
  • files are named in lowercase with underscores.

Although ActiveRecord is part of Rails, you can also install it as a freestanding gem:

MySQL

Ruby programs can interact with relational databases like MySQL via adapters in ActiveRecord. An adapter can be a pure Ruby implementation or a hybrid Ruby/C extension. From your Ruby code, you need to specify only the name of the adapter you want to use; ActiveRecord will provide the Ruby bridge code and worry about loading the native extension (if necessary).

MySQL is a one of the most popular open source databases around and is used by many prominent organizations from Yahoo to NASA. You can use these MySQL download and installation instructions.

Creation of students database and rubyists table

Once you have installed MySQL, we shall create a database called students and a table called rubyists (this holds the names and cities of all those who have downloaded my Free Ruby Study Notes). To do this, open a command window and type:

You should now get a mysql prompt. Next at the mysql prompt, type as follows:

It will respond with:

Next, on the mysql prompt, type:

Next let us create our table called rubyists by typing the following on the mysql prompt:

We are through with the database creation. Now type:

Rails assumes that every table it handles has as its primary key an integer column called id internally, Rails uses the value in this column to keep track of the data it has loaded from the database and to link between data in different tables.

Ruby code to access the table rubyists

Let's write a Ruby program and name it as p080dbconnect.rb

So far, in the above code we are:

  • Using the ActiveRecord library, available as the activerecord gem.
  • Using the ActiveRecord adapter namely mysql
  • Establishing a connection to the database students
  • Creating a class called Rubyist following the conventions mentioned above

Next we create entries in the table without writing any SQL. If you refer the ActiveRecord documentation, we can use the create method of ActiveRecord::Base (this method creates an object, and instantly saves it as a record) to do the same:

You can now query the table using find of ActiveRecord::Base.

You will observe ActiveRecord examines the database tables themselves to find out which columns are available. This is how we were able to use accessor methods for participant.name without explicitly defining them: we defined them in the database, and ActiveRecord picked them up.

If you want to delete an item from the database, you can use the destroy (Deletes the record in the database) method of ActiveRecord::Base:

The complete program is:

Note: The Ruby Logo is Copyright (c) 2006, Yukihiro Matsumoto. I have made extensive references to information, related to Ruby, available in the public domain (wikis and the blogs, articles of various Ruby Gurus), my acknowledgment and thanks to all of them.

Free Online Ruby Programming Course