# Updating Records in an ORM ## Objectives * Write a method that will update an existing database record once changes have been made to that record's equivalent Ruby object. * Identify whether a Ruby object has already been persisted to the database. * Build a method that can *either* find and update *or* create a database record. ## Updating Records It's hard to imagine a database that would stay totally static and never change. For example, a customer who uses your online marketplace updates their billing information or makes a new purchase. A user of your social networking site "friends" another user, creating a new association between them. A hospital updates the medical history of one of its patients. In each of these example apps that uses a database, we need to be able to update, or change, the records that are stored in that database. What do we need to do in order to successfully update a record? First, we need to find the appropriate record. Then, we make some changes to it, and finally, save it once again. In our Ruby ORM, where attributes of given Ruby objects are stored as an individual row in a database table, we will need to retrieve these attributes, reconstitute them into a Ruby object, make changes to that object using Ruby methods, and *then* save those (newly updated) attributes back into the database. Let's walk through this process together. ## Updating a Record in a Ruby ORM For the purposes of this example, we'll be working with a fictitious music management app that allows the user to store their songs. Our app has a `Song` class that maps to a songs database table. Our `Song` class has all the methods it needs to create the songs table, insert records into that table and retrieve records from that table. ### The `Song` Class For this example, we'll assume that our database connection is stored in the `DB[:conn]` constant. ``` class Song attr_accessor :name, :album attr_reader :id def initialize(id=nil, name, album) @id = id @name = name @album = album end def self.create_table sql = <<-SQL CREATE TABLE IF NOT EXISTS songs ( id INTEGER PRIMARY KEY, name TEXT, album TEXT ) SQL DB[:conn].execute(sql) end def save sql = <<-SQL INSERT INTO songs (name, album) VALUES (?, ?) SQL DB[:conn].execute(sql, self.name, self.album) @id = DB[:conn].execute("SELECT last_insert_rowid() FROM songs")[0][0] end def self.create(name:, album:) song = Song.new(name, album) song.save song end def self.find_by_name(name) sql = "SELECT * FROM songs WHERE name = ?" result = DB[:conn].execute(sql, name)[0] Song.new(result[0], result[1], result[2]) end end ``` With the `Song` class as defined above, we can create new `Song` instances, save them to the database and retrieve them from the database: ``` ninety_nine_problems = Song.create(name: "99 Problems", album: "The Blueprint") Song.find_by_name("99 Problems") # => # ``` Now that we've seen how to create a `Song` instance, save its attributes to the database, retrieve those attributes and use them to re-create a `Song` instance, let's move on to updating records and objects. ### Updating Songs In order to update a record, we must first find it: ``` ninety_nine_problems = Song.find_by_name("99 Problems") ninety_nine_problems.album # => "The Blueprint" ``` Uh-oh, 99 Problems is off The Black Album, as we all know. Let's fix this. ``` ninety_nine_problems.album = "The Black Album" ninety_nine_problems.album # => "The Black Album" ``` Much better. Now we need to save this record back into the database: To do so, we'll need to use an `UPDATE` SQL statement. That statement would look something like this: ``` UPDATE songs SET album="The Black Album" WHERE name="99 Problems"; ``` Let's put it all together using our SQLite3-Ruby gem magic. Remember, in this example, we assume our database connection is stored in `DB[:conn]`. ``` sql = "UPDATE songs SET album = ? WHERE name = ?" DB[:conn].execute(sql, ninety_nine_problems.album, ninety_nine_problems.name) ``` Here we've updated the album of a given song. What happens when we want to update some other attribute of a song? Let's take a look: ``` Song.create(name: "Hella", album: "25") ``` Let's correct the name of the above song from `"Hella"` to `"Hello"`. ``` hello = Song.find_by_name("Hella") sql = "UPDATE songs SET name='Hello' WHERE name = ?" DB[:conn].execute(sql, hello.name) ``` This code is almost exactly the same as the code we used to update the album of the first song. The only difference is in the particular attribute we wanted to update. In the first case, we were updating the album. In this case, we updated the name. Repetitious code has a smell. Let's extract this functionality of updating a record into a method, `#update`. ### The `#update` Method How will we write a method that will allow us to update any attributes of any song? How will we know *which* attributes have been recently updated and which will remain the same? The best way for us to do this is to simply update *all* the attributes whenever we update a record. That way, we will catch any changed attributes, while the un-changed ones will simply remain the same. For example: ``` hello = Song.find_by_name("Hella") sql = "UPDATE songs SET name = 'Hello', album = ? WHERE name = ?" DB[:conn].execute(sql, hello.album, hello.name) ``` Here we update *both* the name and album attribute of the song, even though only the name attribute is actually different. Okay, now that we've solved this problem, let's build our method: ``` class Song ... def update sql = "UPDATE songs SET name = ?, album = ? WHERE name = ?" DB[:conn].execute(sql, self.name, self.album, self.name) end end ``` Now we can update a song with the following: ``` hello = Song.create(name: "Hella", album: "25") hello.name = "Hello" hello.update ``` Wait a second, you might be wondering, how can the `#update` method use `WHERE name = #{self.name}"` if we *just changed the name of the song?* **Well...it can't!** The above `#update` method *will not work* if we are trying to update the name of a song. Think about it: If we change the name of `hello` from `"Hella"` to `"Hello"` with the following: Then the database table doesn't yet know that we changed the name. We haven't saved that change yet so the database row that stores `hello`'s information still has a value of `"Hella"` in the "name" column. So, after the above line of code is executed, our SQL query: ``` sql = "UPDATE songs SET name = ?, album = ? WHERE name = ?" DB[:conn].execute(sql, self.name, self.album, self.name) ``` Would be interpreted like this: ``` DB[:conn].execute(sql, "Hello", "25", "Hello") ``` And, seeing as our database row still has a value of `"Hella"` in the "name" column, our query would fail to find the correct record and consequently fail to update it. So using something changeable, like name, to identify the record we want to update, won't work. If only each individual database record and its analogous Ruby object had some kind of unique, un-changing identifier... That's where the primary key ID of a database record and the `id` attribute of its analogous Ruby object come in. ## Identifying Objects and Records Using ID We need a way to select a Ruby object's analogous table row using some fixed and unique attribute. Song records in the database table have a unique `id`, and our `Song` instances have an `id` attribute. Recall that we have been setting the `id` attribute of individual songs directly after the data regarding that song gets inserted into the database table, right at the end of our `#save` method. Why? The unique `id` number of a `Song` instance should *come from the database*. When a song record gets inserted into the database, that row automatically gets assigned a unique ID number. We need to grab that ID number *from the database record* and assign it to the `Song` instance's `id` attribute. If that sounds confusing, check out this diagram: [![](https://camo.githubusercontent.com/2cd35ada62f647080c6537fb13aae9222ac14297/687474703a2f2f726561646d652d706963732e73332e616d617a6f6e6177732e636f6d2f556e7469746c656425323064726177696e672e706e67)](https://camo.githubusercontent.com/2cd35ada62f647080c6537fb13aae9222ac14297/687474703a2f2f726561646d652d706963732e73332e616d617a6f6e6177732e636f6d2f556e7469746c656425323064726177696e672e706e67) Let's break it down: * We create a new instance of the `Song` class. That instance has a `name` and `album` attribute. But its `id` attribute is `nil`. * The name and album of this song instance are used to create a new database record––a new row in the songs table. That record has an ID of `1` (this would appear to be the first song we've ever saved in our database). * The ID of the newly created database record is then taken and assigned to the `id` attribute of the original song object. What's so great about this? Well, with this pattern, every instance of the `Song` class that is ever saved into the database will be assigned a unique `id` attribute that we can use to differentiate it from the other `Song` objects we created and that we can use to find, retrieve and update unique songs. Now that we are all convinced that this is the behavior we want to implement, take a closer look at the code that implements it. ### Assigning Unique IDs on `#save` At what point in time should a `Song` instance get assigned a unique `id`? Right after we `INSERT` it into the database. At that point, its equivalent database record will have a unique ID in the ID column. We want to simply grab that ID and use it to assign the `Song` object its `id` value. When do we `INSERT` a new record into our database? In the `#save` method: ``` def save sql = <<-SQL INSERT INTO songs (name, album) VALUES (?, ?) SQL DB[:conn].execute(sql, self.name, self.album) end ``` Right after we `execute` the SQL `INSERT` statement is an appropriate place to assign our `Song` object its unique `id` from the database. How do we get the unique ID of the record we just created? We query the database table for the ID of the last inserted row: ``` SELECT last_insert_rowid() FROM songs ``` **Important:** When we execute the above SQL statement using our SQLite3-Ruby gem, we get back something that may feel unexpected: ``` DB[:conn].execute("SELECT last_insert_rowid() FROM songs") # => [[1]] ``` Recall that whenever we execute SQL statements against our database using the SQLite3-Ruby gem's `#execute` method, we will get back an array of arrays. Here, we used the `last_insert_rowid()` SQL query to request one thing: the last inserted row's ID. Our SQLite3-Ruby gem obliged and gave us an array that contains one array that contains one element––the last inserted row ID. Phew! So, let's put it all together with our new-and-improved `#save` method: ``` def save sql = <<-SQL INSERT INTO songs (name, album) VALUES (?, ?) SQL DB[:conn].execute(sql, self.name, self.album) @id = DB[:conn].execute("SELECT last_insert_rowid() FROM songs")[0][0] end ``` Now let's see what happens when we create a new song: ``` hello = Song.create(name: "Hello", album: "25") hello.name # => "Hello" hello.album # => "25" hello.id # => 1 ``` We did it! Now our individual `Song` objects will get assigned a unique `id` attribute, as soon as they are saved to the database. That means that we can refactor our `#update` method such that it will only update the correct, unique record. ## Using `id` to Update Records Our `#update` method should identify the correct record to update based on the unique ID that both the song Ruby object and the songs table row share: ``` class Song ... def update sql = "UPDATE songs SET name = ?, album = ? WHERE id = ?" DB[:conn].execute(sql, self.name, self.album, self.id) end end ``` Now we will never have to worry about accidentally updating the wrong record, or being unable to find a record once we change its name. ## Refactoring our `#save` Method to Avoid Duplication Our `#save` method currently looks like this: ``` def save sql = <<-SQL INSERT INTO songs (name, album) VALUES (?, ?) SQL DB[:conn].execute(sql, self.name, self.album) @id = DB[:conn].execute("SELECT last_insert_rowid() FROM songs")[0][0] end ``` This method will *always `INSERT` a new row into the database table*. But, what happens if we accidentally call `#save` on an object that has already been persisted and has an analogous database row? It would have the effect of creating a new database row with the same attributes as an existing row. The only difference would be the `id` number: ``` hello = Song.new("Hello", "25") hello.save DB[:conn].execute("SELECT * FROM songs WHERE name = 'Hello' AND album = '25'") # => [[1, "Hello", "25"]] # What happens if we save the same song again? hello.save DB[:conn].execute("SELECT * FROM songs WHERE name = 'Hello' AND album = '25'") # => [[1, "Hello", "25"], [2, "Hello", "25"]] ``` Oh no! We have two records in our songs table that contain the same information. It is clear that our `#save` method needs some fail-safes to protect against this kind of thing. We need our `#save` method to check to see if the object it is being called on has already been persisted. If so, *don't `INSERT` a new row into the database*, simply *update* an existing one. Now that we have our handy `#update` method ready to go, this should be easy. How do we know if an object has been persisted? If it has an `id` that is not `nil`. Remember that an object's `id` attribute gets set only once it has been `INSERT`ed into the database. Let's take a look at our new `#save` method: ``` def save if self.id self.update else sql = <<-SQL INSERT INTO songs (name, album) VALUES (?, ?) SQL DB[:conn].execute(sql, self.name, self.album) @id = DB[:conn].execute("SELECT last_insert_rowid() FROM songs")[0][0] end end ``` Great, now our `#save` method will never create duplicate records! View [Updating Records in an ORM](https://learn.co/lessons/orm-updating-records) on Learn.co and start learning to code for free. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://certil-remy.gitbook.io/learn/orm-and-active-record/untitled-5.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.