Creating, Reading, Updating, and Deleting (CRUD)
Names such as SQLite and MySQL emphasize that all access to a database is via the Structured Query Language (SQL). In most cases, Rails will take care of this for you, but that is completely up to you. As you will see, you can provide clauses or even entire SQL statements for the database to execute.
If you are familiar with SQL already, as you read this section take note of how Rails provides places for familiar clauses such as select, from, where, group by, and so on. If you are not already familiar with SQL, one of the strengths of Rails is that you can defer knowing more about such things until you actually need to access the database at this level.
In this section, we’ll continue to work with the Order model from the Depot application for an example. We’ll be using Active Record methods to apply the four basic database operations: create, read, update, and delete.
Creating New Rows
Given that Rails represents tables as classes and rows as objects, it follows that we create rows in a table by creating new objects of the appropriate class. We can create new objects representing rows in our orders table by calling Order.new. We can then fill in the values of the attributes (corresponding to columns in the database). Finally, we call the object’s save method to store the order back into the database. Without this call, the order would exist only in our local memory.
| | an_order = Order.new |
| | an_order.name = "Dave Thomas" |
| | an_order.email = "[email protected]" |
| | an_order.address = "123 Main St" |
| | an_order.pay_type = "check" |
| | an_order.save |
Active Record constructors take an optional block. If present, the block is invoked with the newly created order as a parameter. This might be useful if you wanted to create and save an order without creating a new local variable.
| | Order.new do |o| |
| | o.name = "Dave Thomas" |
| | # . . . |
| | o.save |
| | end |
Finally, Active Record constructors accept a hash of attribute values as an optional parameter. Each entry in this hash corresponds to the name and value of an attribute to be set. This is useful for doing things like storing values from HTML forms into database rows.
| | an_order = Order.new( |
| | name: "Dave Thomas", |
| | email: "[email protected]", |
| | address: "123 Main St", |
| | pay_type: "check") |
| | an_order.save |
Note that in all of these examples we did not set the id attribute of the new row. Because we used the Active Record default of an integer column for the primary key, Active Record automatically creates a unique value and sets the id attribute as the row is saved. We can subsequently find this value by querying the attribute:
| | an_order = Order.new |
| | an_order.name = "Dave Thomas" |
| | # ... |
| | an_order.save |
| | puts "The ID of this order is #{an_order.id}" |
The new constructor creates a new Order object in memory; we have to remember to save it to the database at some point. Active Record has a convenience method, create, that both instantiates the model object and stores it into the database:
| | an_order = Order.create( |
| | name: "Dave Thomas", |
| | email: "[email protected]", |
| | address: "123 Main St", |
| | pay_type: "check") |
You can pass create an array of attribute hashes; it’ll create multiple rows in the database and return an array of the corresponding model objects:
| | orders = Order.create( |
| | [ { name: "Dave Thomas", |
| | email: "[email protected]", |
| | address: "123 Main St", |
| | pay_type: "check" |
| | }, |
| | { name: "Andy Hunt", |
| | email: "[email protected]", |
| | address: "456 Gentle Drive", |
| | pay_type: "po" |
| | } ] ) |
The real reason that new and create take a hash of values is that you can construct model objects directly from form parameters:
| | @order = Order.new(order_params) |
If you think this line looks familiar, it’s because you have seen it before. It appears in orders_controller.rb in the Depot application.
Reading Existing Rows
Reading from a database involves first specifying which particular rows of data you are interested in—you’ll give Active Record some kind of criteria, and it will return objects containing data from the row(s) matching the criteria.
The most direct way of finding a row in a table is by specifying its primary key. Every model class supports the find method, which takes one or more primary key values. If given just one primary key, it returns an object containing data for the corresponding row (or throws an ActiveRecord::RecordNotFound exception). If given multiple primary key values, find returns an array of the corresponding objects. Note that in this case a RecordNotFound exception is raised if any of the IDs cannot be found (so if the method returns without raising an error, the length of the resulting array will be equal to the number of IDs passed as parameters).
| | an_order = Order.find(27) # find the order with id == 27 |
| | |
| | # Get a list of product ids from a form, then |
| | # find the associated Products |
| | product_list = Product.find(params[:product_ids]) |
When you use a finder driven by primary keys, you’re looking for a particular record. You expect it to exist. A call to Person.find(5) is based on our knowledge of the people table. We want the row with an ID of 5. If this call is unsuccessful—if the record with the ID of 5 has been destroyed—we’re in an exceptional situation. This mandates the raising of an exception, so Rails raises RecordNotFound.
On the other hand, finders that use criteria to search are looking for a match. So, Person.where(name: ’Dave’).first is the equivalent of telling the database (as a black box) “Give me the first person row that has the name Dave.” This exhibits a distinctly different approach to retrieval; we’re not certain up front that we’ll get a result. It’s entirely possible the result set may be empty. Thus, returning nil in the case of finders that search for one row and an empty array for finders that search for many rows is the natural, nonexceptional response.
Often, though, you need to read in rows based on criteria other than their primary key value. Active Record provides additional methods enabling you to express more complex queries.
SQL and Active Record
To illustrate how Active Record works with SQL, pass a string to the where method call corresponding to a SQL where clause. For example, to return a list of all orders for Dave with a payment type of “po,” we could use this:
| | pos = Order.where("name = 'Dave' and pay_type = 'po'") |
The result will be an ActiveRecord::Relation object containing all the matching rows, each neatly wrapped in an Order object.
That’s fine if our condition is predefined, but how do we handle it when the name of the customer is set externally (perhaps coming from a web form)? One way is to substitute the value of that variable into the condition string:
| | # get the name from the form |
| | name = params[:name] |
| | # DON'T DO THIS!!! |
| | pos = Order.where("name = '#{name}' and pay_type = 'po'") |
As the comment suggests, this really isn’t a good idea. Why? It leaves the database wide open to something called a SQL injection attack, which the Ruby on Rails Guides[94] describe in more detail. For now, take it as a given that substituting a string from an external source into a SQL statement is effectively the same as publishing your entire database to the whole online world.
Instead, the safe way to generate dynamic SQL is to let Active Record handle it. Doing this allows Active Record to create properly escaped SQL, which is immune from SQL injection attacks. Let’s see how this works.
If we pass multiple parameters to a where call, Rails treats the first parameter as a template for the SQL to generate. Within this SQL, we can embed placeholders, which will be replaced at runtime by the values in the rest of the array.
One way of specifying placeholders is to insert one or more question marks in the SQL. The first question mark is replaced by the second element of the array, the next question mark by the third, and so on. For example, we could rewrite the previous query as this:
| | name = params[:name] |
| | pos = Order.where(["name = ? and pay_type = 'po'", name]) |
We can also use named placeholders. We do that by placing placeholders of the form :name into the string and by providing corresponding values in a hash, where the keys correspond to the names in the query:
| | name = params[:name] |
| | pay_type = params[:pay_type] |
| | pos = Order.where("name = :name and pay_type = :pay_type", |
| | pay_type: pay_type, name: name) |
We can take this a step further. Because params is effectively a hash, we can simply pass it all to the condition. If we have a form that can be used to enter search criteria, we can use the hash of values returned from that form directly:
| | pos = Order.where("name = :name and pay_type = :pay_type", |
| | params[:order]) |
We can take this even further. If we pass just a hash as the condition, Rails generates a where clause using the hash keys as column names and the hash values as the values to match. Thus, we could have written the previous code even more succinctly:
| | pos = Order.where(params[:order]) |
Be careful with this latter form of condition: it takes all the key-value pairs in the hash you pass in when constructing the condition. An alternative would be to specify which parameters to use explicitly:
| | pos = Order.where(name: params[:name], |
| | pay_type: params[:pay_type]) |
Regardless of which form of placeholder you use, Active Record takes great care to quote and escape the values being substituted into the SQL. Use these forms of dynamic SQL, and Active Record will keep you safe from injection attacks.
Using Like Clauses
We might be tempted to use parameterized like clauses in conditions:
| | # Doesn't work |
| | User.where("name like '?%'", params[:name]) |
Rails doesn’t parse the SQL inside a condition and so doesn’t know that the name is being substituted into a string. As a result, it will go ahead and add extra quotes around the value of the name parameter. The correct way to do this is to construct the full parameter to the like clause and pass that parameter into the condition:
| | # Works |
| | User.where("name like ?", params[:name]+"%") |
Of course, if we do this, we need to consider that characters such as percent signs, should they happen to appear in the value of the name parameter, will be treated as wildcards.
Subsetting the Records Returned
Now that we know how to specify conditions, let’s turn our attention to the various methods supported by ActiveRecord::Relation, starting with first and all.
As you may have guessed, first returns the first row in the relation. It returns nil if the relation is empty. Similarly, to_a returns all the rows as an array. ActiveRecord::Relation also supports many of the methods of Array objects, such as each and map. It does so by implicitly calling the all first.
It’s important to understand that the query is not evaluated until one of these methods is used. This enables us to modify the query in a number of ways, namely, by calling additional methods, prior to making this call. Let’s look at these methods now.
order
SQL doesn’t require rows to be returned in any particular order unless we explicitly add an order by clause to the query. The order method lets us specify the criteria we’d normally add after the order by keywords. For example, the following query would return all of Dave’s orders, sorted first by payment type and then by shipping date (the latter in descending order):
| | orders = Order.where(name: 'Dave'). |
| | order("pay_type, shipped_at DESC") |
limit
We can limit the number of rows returned by calling the limit method. Generally when we use the limit method, we’ll probably also want to specify the sort order to ensure consistent results. For example, the following returns the first ten matching orders:
| | orders = Order.where(name: 'Dave'). |
| | order("pay_type, shipped_at DESC"). |
| | limit(10) |
offset
The offset method goes hand in hand with the limit method. It allows us to specify the offset of the first row in the result set that will be returned:
| | # The view wants to display orders grouped into pages, |
| | # where each page shows page_size orders at a time. |
| | # This method returns the orders on page page_num (starting |
| | # at zero). |
| | def Order.find_on_page(page_num, page_size) |
| | order(:id).limit(page_size).offset(page_num*page_size) |
| | end |
We can use offset in conjunction with limit to step through the results of a query n rows at a time.
select
By default, ActiveRecord::Relation fetches all the columns from the underlying database table—it issues a select * from... to the database. Override this with the select method, which takes a string that will appear in place of the * in the select statement.
This method allows us to limit the values returned in cases where we need only a subset of the data in a table. For example, our table of podcasts might contain information on the title, speaker, and date and might also contain a large BLOB containing the MP3 of the talk. If you just wanted to create a list of talks, it would be inefficient to also load the sound data for each row. The select method lets us choose which columns to load:
| | list = Talk.select("title, speaker, recorded_on") |
joins
The joins method lets us specify a list of additional tables to be joined to the default table. This parameter is inserted into the SQL immediately after the name of the model’s table and before any conditions specified by the first parameter. The join syntax is database-specific. The following code returns a list of all line items for the book called Programming Ruby:
| | LineItem.select('li.quantity'). |
| | where("pr.title = 'Programming Ruby 1.9'"). |
| | joins("as li inner join products as pr on li.product_id = pr.id") |
readonly
The readonly method causes ActiveRecord::Resource to return Active Record objects that cannot be stored back into the database.
If we use the joins or select method, objects will automatically be marked readonly.
group
The group method adds a group by clause to the SQL:
| | summary = LineItem.select("sku, sum(amount) as amount"). |
| | group("sku") |
lock
The lock method takes an optional string as a parameter. If we pass it a string, it should be a SQL fragment in our database’s syntax that specifies a kind of lock. With MySQL, for example, a share mode lock gives us the latest data in a row and guarantees that no one else can alter that row while we hold the lock. We could write code that debits an account only if there are sufficient funds using something like the following:
| | Account.transaction do |
| | ac = Account.where(id: id).lock("LOCK IN SHARE MODE").first |
| | ac.balance -= amount if ac.balance > amount |
| | ac.save |
| | end |
If we don’t specify a string value or we give lock a value of true, the database’s default exclusive lock is obtained (normally this will be "for update"). We can often eliminate the need for this kind of locking using transactions (discussed starting in Transactions).
Databases do more than simply find and reliably retrieve data; they also do a bit of data reduction analysis. Rails provides access to these methods too.
Getting Column Statistics
Rails has the ability to perform statistics on the values in a column. For example, given a table of products, we can calculate the following:
| | average = Product.average(:price) # average product price |
| | max = Product.maximum(:price) |
| | min = Product.minimum(:price) |
| | total = Product.sum(:price) |
| | number = Product.count |
These all correspond to aggregate functions in the underlying database, but they work in a database-independent manner.
As before, methods can be combined:
| | Order.where("amount > 20").minimum(:amount) |
These functions aggregate values. By default, they return a single result, producing, for example, the minimum order amount for orders meeting some condition. However, if you include the group method, the functions instead produce a series of results, one result for each set of records where the grouping expression has the same value. For example, the following calculates the maximum sale amount for each state:
| | result = Order.group(:state).maximum(:amount) |
| | puts result #=> {"TX"=>12345, "NC"=>3456, ...} |
This code returns an ordered hash. You index it using the grouping element ("TX", "NC", … in our example). You can also iterate over the entries in order using each. The value of each entry is the value of the aggregation function.
The order and limit methods come into their own when using groups.
For example, the following returns the three states with the highest orders, sorted by the order amount:
| | result = Order.group(:state). |
| | order("max(amount) desc"). |
| | limit(3) |
This code is no longer database independent—to sort on the aggregated column, we had to use the SQLite syntax for the aggregation function (max, in this case).
Scopes
As these chains of method calls grow longer, making the chains themselves available for reuse becomes a concern. Once again, Rails delivers. An Active Record scope can be associated with a Proc and therefore may have arguments:
| | class Order < ApplicationRecord |
| | scope :last_n_days, ->(days) { where('updated < ?' , days) } |
| | end |
Such a named scope would make finding the worth of last week’s orders a snap.
| | orders = Order.last_n_days(7) |
Simpler scopes may have no parameters at all:
| | class Order < ApplicationRecord |
| | scope :checks, -> { where(pay_type: :check) } |
| | end |
Scopes can also be combined. Finding the last week’s worth of orders that were paid by check is just as straightforward:
| | orders = Order.checks.last_n_days(7) |
In addition to making your application code easier to write and easier to read, scopes can make your code more efficient. The previous statement, for example, is implemented as a single SQL query.
ActiveRecord::Relation objects are equivalent to an anonymous scope:
| | in_house = Order.where('email LIKE "%@pragprog.com"') |
Of course, relations can also be combined:
| | in_house.checks.last_n_days(7) |
Scopes aren’t limited to where conditions; we can do pretty much anything we can do in a method call: limit, order, join, and so on. Just be aware that Rails doesn’t know how to handle multiple order or limit clauses, so be sure to use these only once per call chain.
In nearly every case, the methods we have been describing are sufficient. But Rails is not satisfied with only being able to handle nearly every case, so for cases that require a human-crafted query, there’s an API for that too.
Writing Our Own SQL
Each of the methods we’ve been looking at contributes to the construction of a full SQL query string. The method find_by_sql lets our application take full control. It accepts a single parameter containing a SQL select statement (or an array containing SQL and placeholder values, as for find) and returns an array of model objects (that is potentially empty) from the result set. The attributes in these models will be set from the columns returned by the query. We’d normally use the select * form to return all columns for a table, but this isn’t required:
| | orders = LineItem.find_by_sql("select line_items.* from line_items, orders " + |
| | " where order_id = orders.id " + |
| | " and orders.name = 'Dave Thomas' ") |
Only those attributes returned by a query will be available in the resulting model objects. We can determine the attributes available in a model object using the attributes, attribute_names, and attribute_present? methods. The first returns a hash of attribute name-value pairs, the second returns an array of names, and the third returns true if a named attribute is available in this model object:
| | orders = Order.find_by_sql("select name, pay_type from orders") |
| | first = orders[0] |
| | p first.attributes |
| | p first.attribute_names |
| | p first.attribute_present?("address") |
This code produces the following:
| | {"name"=>"Dave Thomas", "pay_type"=>"check"} |
| | ["name", "pay_type"] |
| | false |
find_by_sql can also be used to create model objects containing derived column data. If we use the as xxx SQL syntax to give derived columns a name in the result set, this name will be used as the name of the attribute:
| | items = LineItem.find_by_sql("select *, " + |
| | " products.price as unit_price, " + |
| | " quantity*products.price as total_price, " + |
| | " products.title as title " + |
| | " from line_items, products " + |
| | " where line_items.product_id = products.id ") |
| | li = items[0] |
| | puts "#{li.title}: #{li.quantity}x#{li.unit_price} => #{li.total_price}" |
As with conditions, we can also pass an array to find_by_sql, where the first element is a string containing placeholders. The rest of the array can be either a hash or a list of values to be substituted.
| | Order.find_by_sql(["select * from orders where amount > ?", |
| | params[:amount]]) |
Ever since developers first wrapped relational databases with an object-oriented layer, they’ve debated the question of how deep to run the abstraction. Some object-relational mappers seek to eliminate the use of SQL entirely, hoping for object-oriented purity by forcing all queries through an OO layer.
Active Record does not. It was built on the notion that SQL is neither dirty nor bad, just verbose in the trivial cases. The focus is on removing the need to deal with the verbosity in those trivial cases (writing a ten-attribute insert by hand will leave any programmer tired) but keeping the expressiveness around for the hard queries—the type SQL was created to deal with elegantly.
Therefore, you shouldn’t feel guilty when you use find_by_sql to handle either performance bottlenecks or hard queries. Start out using the object-oriented interface for productivity and pleasure and then dip beneath the surface for a close-to-the-metal experience when you need to do so.
In the old days of Rails, people frequently resorted to using find_by_sql. Since then, all the options added to the basic find method mean you can avoid resorting to this low-level method.
Reloading Data
In an application where the database is potentially being accessed by multiple processes (or by multiple applications), there’s always the possibility that a fetched model object has become stale—someone may have written a more recent copy to the database.
To some extent, this issue is addressed by transactional support (which we describe in Transactions). However, there’ll still be times where you need to refresh a model object manually. Active Record makes this one line of code—call its reload method, and the object’s attributes will be refreshed from the database:
| | stock = Market.find_by(ticker: "RUBY") |
| | loop do |
| | puts "Price = #{stock.price}" |
| | sleep 60 |
| | stock.reload |
| | end |
In practice, reload is rarely used outside the context of unit tests.
Updating Existing Rows
After such a long discussion of finder methods, you’ll be pleased to know that there’s not much to say about updating records with Active Record.
If you have an Active Record object (perhaps representing a row from our orders table), you can write it to the database by calling its save method. If this object had previously been read from the database, this save will update the existing row; otherwise, the save will insert a new row.
If an existing row is updated, Active Record will use its primary key column to match it with the in-memory object. The attributes contained in the Active Record object determine the columns that will be updated—a column will be updated in the database only if its value has been changed. In the following example, all the values in the row for order 123 can be updated in the database table:
| | order = Order.find(123) |
| | order.name = "Fred" |
| | order.save |
However, in the following example, the Active Record object contains just the attributes id, name, and paytype—only these columns can be updated when the object is saved. (Note that you have to include the id column if you intend to save a row fetched using find_by_sql.)
| | orders = Order.find_by_sql("select id, name, pay_type from orders where id=123") |
| | first = orders[0] |
| | first.name = "Wilma" |
| | first.save |
In addition to the save method, Active Record lets us change the values of attributes and save a model object in a single call to update:
| | order = Order.find(321) |
| | order.update(name: "Barney", email: "[email protected]") |
The update method is most commonly used in controller actions where it merges data from a form into an existing database row:
| | def save_after_edit |
| | order = Order.find(params[:id]) |
| | if order.update(order_params) |
| | redirect_to action: :index |
| | else |
| | render action: :edit |
| | end |
| | end |
We can combine the functions of reading a row and updating it using the class methods update and update_all. The update method takes an id parameter and a set of attributes. It fetches the corresponding row, updates the given attributes, saves the result to the database, and returns the model object.
| | order = Order.update(12, name: "Barney", email: "[email protected]") |
We can pass update an array of IDs and an array of attribute value hashes, and it will update all the corresponding rows in the database, returning an array of model objects.
Finally, the update_all class method allows us to specify the set and where clauses of the SQL update statement. For example, the following increases the prices of all products with Java in their title by 10 percent:
| | result = Product.update_all("price = 1.1*price", "title like '%Java%'") |
The return value of update_all depends on the database adapter; most (but not Oracle) return the number of rows that were changed in the database.
save, save!, create, and create!
It turns out that there are two versions of the save and create methods. The variants differ in the way they report errors.
-
save returns true if the record was saved; it returns nil otherwise.
-
save! returns true if the save succeeded; it raises an exception otherwise.
-
create returns the Active Record object regardless of whether it was successfully saved. You’ll need to check the object for validation errors if you want to determine whether the data was written.
-
create! returns the Active Record object on success; it raises an exception otherwise.
Let’s look at this in a bit more detail.
Plain old save returns true if the model object is valid and can be saved:
| | if order.save |
| | # all OK |
| | else |
| | # validation failed |
| | end |
It’s up to us to check on each call to save to see that it did what we expected. The reason Active Record is so lenient is that it assumes save is called in the context of a controller’s action method and the view code will be presenting any errors back to the end user. And for many applications, that’s the case.
However, if we need to save a model object in a context where we want to make sure to handle all errors programmatically, we should use save!. This method raises a RecordInvalid exception if the object could not be saved:
| | begin |
| | order.save! |
| | rescue RecordInvalid => error |
| | # validation failed |
| | end |
Deleting Rows
Active Record supports two styles of row deletion. First, it has two class-level methods, delete and delete_all, that operate at the database level. The delete method takes a single ID or an array of IDs and deletes the corresponding row(s) in the underlying table. delete_all deletes rows matching a given condition (or all rows if no condition is specified). The return values from both calls depend on the adapter but are typically the number of rows affected. An exception is not thrown if the row doesn’t exist prior to the call.
| | Order.delete(123) |
| | User.delete([2,3,4,5]) |
| | Product.delete_all(["price > ?", @expensive_price]) |
The various destroy methods are the second form of row deletion provided by Active Record. These methods all work via Active Record model objects.
The destroy instance method deletes from the database the row corresponding to a particular model object. It then freezes the contents of that object, preventing future changes to the attributes.
| | order = Order.find_by(name: "Dave") |
| | order.destroy |
| | # ... order is now frozen |
There are two class-level destruction methods, destroy (which takes an ID or an array of IDs) and destroy_all (which takes a condition). Both methods read the corresponding rows in the database table into model objects and call the instance-level destroy method of those objects. Neither method returns anything meaningful.
| | Order.destroy_all(["shipped_at < ?", 30.days.ago]) |
Why do we need both the delete and destroy class methods? The delete methods bypass the various Active Record callback and validation functions, while the destroy methods ensure that they are all invoked. In general, it’s better to use the destroy methods if you want to ensure that your database is consistent according to the business rules defined in your model classes.
We covered validation in Chapter 7, Task B: Validation and Unit Testing. We cover callbacks next.