Active Record: The Rails Database Bridge

In the past, to build a web application, you required the skills to code in your business logic language and your database language. More recently, however, back-end frameworks are leaning toward using Object-Relational Mapping (ORM); this is a technique that lets you manage your database in the business logic language that you're most comfortable with.

Rails uses an ORM in the form of Active Record. In this tutorial, we'll dive into Active Record and see what it can do for us!


What Active Record Is, Exactly

Like I said, Active Record is an ORM. This means it's a layer of Ruby code that runs between your database and your logic code. When you need to make changes to the database, you'll write Ruby code, and then run migrations, which we'll review soon. These migrations make the actual changes to the database. The cool part is that it doesn't matter what database you're using: Rails can handle pretty much all of 'em. For example, Rails uses SQLite locally when you're developing. However, let's say you're deploying to Heroku, which uses PostreSQL. All you have to do is add this to your Gemfile:

Now, when you deploy, Heroku will run those same migrations (using the PostgreSQL adapter). Same code, different database, which is pretty cool, in my opinion.

So, that's what Active Record is; let's dig deeper and see how it all works.


Getting Started

While it is technically possible to use Active Record outside of a Rails app, nine times out of ten you'll be using it within a Rails app, so that's what we'll do here. I'm using Rails 3.2.12, the latest version as I type this. If you've got that installed, you can start by creating a new Rails app.

Now, we can start by creating a model.


Creating Models and Migrations

As you might expect, since Active Record interacts with the database, it is the M in Rails' MVC: models. From the command line, we'll create a model for, say, a Person. Actually, the rails generate command is pretty flexible: all the following commands work:

The first two lines here do the same thing; rails g is just a shortcut for rails generate. The third one gives Rails a little more information, so it can do a little more work for us. We're saying we want this model to have three fields: first_name, last_name, and age. For first_name and last_name, we don't specify a type, so it defaults to a string. For age, we say that it should be an integer.

So, what's this actually do? You should see some output, explaining that. The important bits are this:

The first file is your migration file (of course, the timestamp will be different); the second is your Ruby class.

You should be comfortable with the migration file syntax, because you'll often want to adjust the code. Let's check it out.

Each migration is a class; this one has a single method: change. If you're familiar with Ruby, this should be pretty self explanatory: this migration creates a table named "people" in the database. This table has six columns. That's right, six. There's the first_name, last_name, and age fields we added from the command line; in the create_table block, we use methods named after the data type and pass them a symbol with the column name.

Then, there's t.timestamps. This creates two more columns in our table: created_at and updated_at. Of course, these are of type datetime (more on your type options later). Active Record takes care of these fields throughout the lifetime of your records, setting and updating them when appropriate.

The sixth and final column is id, which isn't listed here because it's added by default. That's the unique primary key for the table.

Creating tables is just one use for a migration class; they're your method of tweaking the database, remember, so they can do any database job you might ever need do. But one of the important ideas with migrations is that you can roll them back, or undo their effects. This means that each migration class needs to have enough information to undo its effects. Several methods can "undo themselves"; for example, the opposite of adding a table is removing it, which doesn't take any extra information. This is why we can use the change method here. However, if we were doing something that can't be automatically undone, we have to specify the actions for doing and undoing our migration. In these cases, our migration class should have two methods: up and down. The up method will detail what to do when running the migration, and the down method will explain how to roll back the migration.

Let's write our own migration from scratch to give this rollback feature a try. We start by generating a blank migration:

(Normally, you'll give your migration a sensible name.)

Now, we can open db/migrate/<timestamp>_do_stuff.rb. It will have the up/down methods by default, but do ahead and replace that with a single change method.

We start by creating a useless table, with similar syntax to our people table above. Then, we use the add_column method to add a column to the people table: specifically, a job column of type string. Both of these actions can easily be undone, by dropping the table and removing the column.

Now, let's run our two migrations. We do this via a rake task:

You should see output like this:

You can see from the output exactly what was done. The first migration created the people table; the second created the nothing table and added the column. Now, let's undo the last migration we ran. We can do this by running the following:

Again, the output confirms:

And now, the migration we wrote from scratch has been undone.

An important note here: if you're relatively new to Rails, you might forget that migrations aren't run automatically when you create a new model. You have to run them manually. Yes, I've forgotten this my fair share of times, and wondered what was going on, only to realize that the table I was trying to work with didn't even exist yet.

Besides create_table and add_column, there are a bunch of other methods that you can use in your migration files. We can't go into them all in this tutorial, but if they look like something you'll need, check out the migration docs.

  • add_column
  • add_index
  • add_timestamps
  • change_column
  • change_table
  • create_table
  • drop_table
  • remove_column
  • remove_index
  • remove_timestamps
  • rename_column
  • rename_index
  • rename_table

Last note on migrations: here's a list of the supported types that you can use in your migration classes:

  • binary
  • boolean
  • date
  • datetime
  • decimal
  • float
  • integer
  • primary_key
  • string
  • text
  • time
  • timestamp

Looking at the Active Record Class

Now that we've set up the database, we're ready to look at the other part of our model: the Active Record class. This is the piece that you'll actually interact with from your Rails controllers. When we created the Person model, a file app/models/person.rb was created; it looks like this:

If you've worked with Ruby before, you might be familiar with the attr_accessor method, which makes the getter and setter methods for the attributes in question. Well, the attr_accessible method is different; it's actually Rails-specific. Any properties that are attr_accessible-ized can be set via mass assignment. This just means setting a bunch of properties on an object at once; this is often done when creating an object, like so:

Each of the properties defined with attr_accessible must be one of the fields we defined in our database tables, in our migrations (there are a few exceptions to this). But this doesn't mean that all our properties should be defined as accessible; there may be some properties that you want to be set more intentionally; for example, an admin property that gives administration privileges to a user record should probably not be allowed in mass assignment, where it could be set accidentally / maliciously.

For simple Active Record classes, just that line of attr_accessible properties will suffice. There's actually a lot more we can add to our model class to make it more robust, but let's first take this Person model round-trip, and see how to create model instances.


Creating Records

In "A Normal Day in the Life of a Rails app," all the database records will be created in the controller. However, we'll be using the Rails console in this tutorial. In the terminal, you can open the Rails console by running one of the following:

This opens a Ruby console in which you can use all your Model classes.

As you saw above, we can create new database records by creating a class instance:

The second line is the value of our variable p: a new Person object. Notice that three of the six properties have been set, while the other three haven't. Those will be set when the record is saved to the database, which it currently isn't (if you typed exit right now, nothing will have been stored in the database). You can confirm that it isn't saved by running

To save the record to the database, you can call the save method:

Notice this part of the output:

Yes, that's an SQL statement. Remember, Active Record is using the database API underneath, so the SQL still does need to be executed. That's one of the features of the Rails console: you can experiment with different Active Record methods and see exactly how they're touching the database. This will be helpful when you're running methods that pull a lot of data, perhaps from several tables: you can choose the right methods to get the most efficient SQL query.

But now, check out our record

The id, created_at, and updated_at fields have been set.

If you'd like, you can create and save a new record all at once, with the create method:

When working with individual records, you can set or change any properties by using their individual methods; just remember that setting a property doesn't save it to the database; that requires the save call.

If you want to update multiple attributes at a time, you can use the update_attributes, which takes a hash of all the attribute you want to change:

An important difference about update_attributes is that save is run inside that method; no need to save the changes on your own.

Like I said, though, there's more that we can do in the model class. So let's go back and look at some of those other features.


Validations

Validations are an important part of any web app; this is where we make sure that the data we're putting into the database is clean and correct.

Before we begin, it's important to realize that validation rules created in your model classes don't change the actually database. For example, saying a given property is required in your model class doesn't make it required at the database level (that kind of thing you would set in your—wait for it—migrations). This isn't really something you have to worry about, I just want to make sure you understand the big picture here.

So. Validations. In current versions of Rails, we use the validates method to set up all our validations (it was otherwise in the past). First, we pass it the field or fields we are validating. Then, we can pass it a hash will the validation properties. There are a bunch of these validation helper (as they are called) we can use; here are several that you'll use all the time.

Probably the most common one is just validating that a given field has been filled; for this, we do a presence validation:

Here, we've validating on the first_name and last_name attributes. In our validation properties hash, we set presence to true, which means that those attributes must not be left nil when the record is saved.

Another common validation is making sure a field is unique. We can do this with the uniqueness helper.

Several validation helpers don't just take true or false; they need a few more options. Actually, the uniqueness helper is one of them; when we just set it to true, as above, case sensitivity is on. Really, though, with usernames, bob and BOB should be the same. So, we should do this:

Now, bob and BOB would be considered the same.

Sometimes you'll want a property to be one of a given set of options. Try inclusion, which takes an array (or any other enumerable object).

The opposite of inclusion is exclusion, which makes sure the field value is not in the given set:

If you want to make sure a field is of a given length? Enter length. There are a bunch of ways to use this one. Here are a couple of examples:

You can even set thresholds:

You might want to confirm that a value is a number. For this, use numericality:

numericality can also handle a "no decimals" policy:

The last one I'll show you is format, which let's you set a regular expression for the field to match:

There are a few others, but that'll get you started. A last tidbit: you can create your own validation helper classes. Check out the documentation for the details on that.

There are several common options that go with almost all the validation helpers. First, we can set allow_nil to, well, allow a property to be unfilled.

If a blank string is acceptable, you can use the allow_blank instead.

A more common one is message; if a validation fails, a message will be attached to the object (more on this later). Of course, all the validation helpers have default messages, but you can set your own with message.

The last one I'll mention is on, which decides on what conditions the validation will be run. The values can be :create (the validation is only run when saving new records). :update (it's only run when saving previously saved records), or :save (it's run in both cases). Of course, it defaults to :save.


Validation Errors

A model instance's save method returns true if it was successfully saved and false if it wasn't. If you do get a false back, you'll want to know what the errors were, right?

Your model instance has an error property, which is an ActiveModel::Errors instance. After the validations are run, this object is populated with all the error message for failed validations; these are kept in its own messages proptery. Observe:

Usually, you'll want to display these errors to the user, probably next to a form that they submitted. One of the easiest ways to do it is to loop over the full_messages property from inside your form_for block, and print them in a list or something. Even easier is to let Rails handle all that markup by running form.error_messages.


Callbacks

Callbacks are another cool part of Active Record; they let you run custom methods at given times. If you read my Building Ribbit in Rails tutorial, you might remember that we used the before_save callback to set the avatar_hash property on users before saving them. We first create the method we want to run:

And then we just register the callback:

This means that when we call save, our method will be run just before the save is actually done.

We also used a before_validation callback to strip and downcase the email address. While these are only two of the callbacks you can use, they're good examples of these callbacks can be handy. Here are the others:

  • before_validation / after_validation
  • before_save / around_save / after_save
  • before_create / around_create / after_create
  • before_update / around_update / after_update
  • before_destroy / around_destroy / after_destroy

Wondering about the around_* callbacks? These are pretty cool. They are called before the action is done, but then you can perform the action from within the method by calling yield. Once the action is done, the reset of the callback method is run. Cool, eh?


Associations

Most relational databases will have multiple tables that are related in some way, so it's only natural that Active Record will be able to handle this: it does so through Active Record Associations. Let's say we want to have an Order table in our database, and that each Person can have multiple orders. How do get this set up?

We have to start by creating our Order model:

The important part here is the person_id field; as you might expect, this will be our foreign key, the connection between the two classes. We're calling it person_id because, once we tell our model classes about the relationship, the Order class will look for a field by that name by default. If we wanted to call it something else, like orderer_identifier, we'd have to tell Order that the field isn't named after the class it's connecting to. It's easier to go with the defaults.

The migration created by this command will be everything we need, so I'll migrate now:

Now, we need to inform the classes about the relationship. Inside the app/model/person.rb, add this line:

Now, in app/model/order.rb, add this:

In Person, we're saying there can be many orders for each person record; put differently, each person has_many orders. Conversely, each order belongs_to a person instance. These just add a few handy methods to our Person and Order instances, which we'll see in a bit.

So, let's test this relationship, shall we? In the Rails console:

The interesting lines here are 3 and 5. We could have done o.person_id = p.id, but because of our additions to the model classes, o.person = p is a shorter way of doing the same thing. Then, line 5: this is another one of those added methods: it returns an array of all the orders that our person has. Handy, no?

This is a good summary of the kind of things you can do with Active Record Associations; there's a ton more, and it can get pretty complex. Check out the Associations documentation for all the goodness.


Selecting Records

All this time we've been building Active Record classes or creating model records. However, a big part of working with an Active Record class is getting those records back out. As you might expect, there are a dozen or so methods we can use.

First, and simplest, is the methods that returns all the records:

The all method returns an array of all the records.

There's also find, which takes the id as a parameter, and returns the record with that id; it can also take an array of ids and return the matching records:

If you just want the first or last record in the collection, there are specific methods for that:

One of Active Record's coolest feature is the custom find methods; underneath, this is just using method_missing, but from the top it looks like pure magic. Here's how it works: use the method name find_by_<property> and pass the value for that property as a parameter. For example:

You can even chain properties; this command will return the same record:

If you look at the SQL queries for those last two methods, you'll see that we're only returning the first result (LIMIT 1). To find all records that match, use the find_all_by prefix instead. So:

It gets even better: you can use the find_or_create_by prefix to create a record if no matching one is found:

Yes, this actually works. However, realize that if no record is found, this is just like running Person.create, which does run the validations. So, for example, since last_name and age are required, running this will result in an unsaved record will errors atttached:

If you don't want the new record to be saved right away, use the find_or_initialize_by prefix.

There are a few other methods you might find useful when selecting records. A really useful one is where which takes a WHERE clause from an SQL statement.

If you're taking values from the user for use in where, you shouldn't interpolate them, for fear of SQL injections. You should use question marks in place of them, and then pass the values as other parameters:

. . . or something similar.

All these methods we've looked at so far return all the fields of the returned records. You can use the select method before any of them to only return model instances with a few selected properties:

Other common SQL statement activities include limiting and offsetting; a great use case for these is paging results. The limit and offset methods take a single number parameter each. If you use them alone, they work on the whole collection; for example, this returns five records, skipping the first two:

But you can choose the records to limit and offset with one of the other functions:

There are a few other query methods, but we've covered the ones you're going to be using 90% of the time.

If you're doing complex queries like this, it would be a good idea to create a query method of your own, in your model classes. Let's say we let people search for other users by their last name, and we page the results with 10 users per page. The query might look something like this:

Besides this being rather long for the controller, that offset has to change for each page; 0 only works for the first page. So, we write a method in our Person class:

This returns the set we want, and we can choose which page and how many results per page we'd like (or leave them to their sensible defaults. And even better, it's a nice neat method that we can call from our controller like this:

For more on querying, check the Active Record querying documentation.


Conclusion

If you're just getting into Rails, you've just learned nearly everything you'll need to know about Active Record for a while. Of course, like every other part of Rails, Active Record is deep and rich; there are a lot of pieces that I haven't mentioned in this tutorial. For more on those, look at the Rails Guides for models. Thanks for reading!

Tags:

Comments

Related Articles