Creating Rails methods with SQL: Part II

So in my last blog, I built the basics of creating a database, dropping a database, creating an instance of your class, and saving that instance to the database. Now I want to move on to some more methods. I ended the last blog talking about how the data we get back from a database query is an array so now I want to show you how to build methods such as:

• Building an instance from the data you receive from the database.
• Update an instance method
• Tweak the save method to include the ability to update an instance.
• Find an instance in a database or create a new one if it doesn’t exist.
• Then a simple one such as find_by_name.

So let’s start with building our instance from the database query.

Our database table is comprised of rows and when we query the database we will get an array/lines of arrays dependent on the query you choose. This method is fairly straightforward but easy to get wrong.

When I query the array I will get something like this:

So, our method is simply going to be re-organising our array. We want a method that takes a row of data and then assigns each element.

Here we are using a class method. Then I am assigning the attributes in the same order as the initialization and then calling self.new or User.new with the attributes. Perfect! This is a method that is likely to be used regularly in some of our other methods as we query the database for more and more data.

Okay, first one down, now on to update. An update is exactly what it says on the tin, you are looking to update an instance. 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. We need to write our SQL in our heredoc and then connect to our database to query it.

What we have here is an instance method, we write our SQL, which uses the UPDATE keyword and SETS the name and age of our instance, using the id to make sure it chooses the correct line in the table! The question marks are called bound parameters. Instead of interpolating variables into a string of SQL, we are using the ? characters as placeholders.

Then we call our connection to the database, passing in the sql variable, then (because it is an instance method) we can call, self.name, self.age, self.id, to pass the required arguments.

Okay, so now we have our update method, we can tweak our save method. We need to check that the object being called on has already been persisted (belongs in the database). If it does, don’t insert a new row, just update the existing one. So, we can simply add update to our save method.

So, if the current instance has an id, update the instance, else create our new line in the database and add the id!

Okay, we’ve managed to create a couple of methods that help us save or update existing methods. But how do we actually find these instances? Firstly we want to send a query to our database asking it to find the instance, with the attributes we supply. Then when it sends back the array, check to see if this array is empty. If this array isn’t empty, then we just make a new instance (which doesn’t save to the database as this instance already exists on the database), or we use our create method to create a new row in the database if the array is empty! Finally, we simply return the user.

This method is a bit longer, as we need to query the database first, then add on code underneath our database connection (which is usually at the bottom of our methods). Then we need to either make a new user or make a clone of the user if it already exists in the database.

Okay. Now for a more niche method, where we look through a class via an instance name.

We once again write our SQL code that queries the database. However, we need to turn that data we have found in the database back into an object we can use. Therefore we use an enumerable to iterate through the data we found. It will pass this row of data to the method we created previously. Which will recreate the instance we were after!

The chances of you having to use this in Ruby or Rails is minimal, but having that background knowledge is so key to checking out your development logs, knowing if a query maybe isn’t working correctly. Allow you to spot query issues in your log, such as N+1 problems or places where you may want to cache your data but aren’t. Also, you just understand the methods you will type a lot in personal and professional projects. The more you practice the better you become, but also, it’s breaking that initial barrier after a Bootcamp of going through a lot of in-depth learning and barely having time to stop. Take that time after a Bootcamp to re-learn everything you know, because if you’re asked about it in an interview, you’ll be more confident with your answer and it will show your eagerness to continue learning.