Creating Inbuilt Rails Methods With SQL

I decided that although I loved Rails and Ruby, you could build a perfectly simple API or Rails application without understanding too much of what is going on in the background. For instance, after doing my Bootcamp, I knew that the Create action from CRUD essentially bundled the new and create actions together. I knew that a method like User.create (if valid) would create a new instance of a user. Could I tell you the SQL that was used, probably not, I just knew that it was running the query for me in the background and could check it out in the development log if I wanted to. But, because I wanted to get better with Ruby, I wanted to dive deeper into the ‘stuff in the background’, even if I never had to use it myself.

“Know thy enemy and know yourself; in a hundred battles, you will never be defeated.” — Sun Tzu (whilst he was learning to code).

It is a valid thought. In one sense, you want to know the language as well as possible, but you also need to know your limits, the areas you maybe don’t know as well as others. For someone coming out of a Bootcamp, this was an extensive list, but one that you can slowly chip away at.

So. I decided to have a go at building out a Ruby class that could create a database with SQLite3, add instances, delete instances, drop the database, etc. It may not be overly useful in the long run as Rails handles all of this, but I understand what it is handling and how.

I set up a basic ruby folder, containing a place for some rspec testing (may as well have a go at testing my code as well), my class, and a run file.

My gemfile contained the rspec and sqlite3 gem.

The start of my User class. As I am creating the link to the database myself, I need to include an attribute for id so that when I save it to the database, it will be able to have the correct id attached to it, so it will always default to nil at the beginning of an instance initialization.

Why not include the id with the initialize. Firstly, that could be a pain, but also, what happens if the initialize doesn’t work correctly. We want to make sure that only on the method call to save the instance we have just created is the id added.

Perfect, now typically in rails. You run rails db:migrate, rails db:seed to set up your databases with dummy data. I didn’t know much apart from ActiveRecord being an ORM, where tables are considered classes and lines in the database an instance of that class. Now in Rails, the config folder is run first, with 3 files running, in particular, to set everything up for you. However, this time I won’t have the luxury of rails new creating everything for me.

So I needed, a config folder with an environment.rb file in it. This would be the file I would need to run first that would set up a very basic environment for me.

My environment file is below:

We will create a new database called users.db, which will be stored inside the db subdirectory. It will return a Ruby object that represents the connection between our Ruby program and SQL database. This is the object that connects the rest of our Ruby program, i.e. any code we write to create users to our SQL database.

So I require the necessary file that will have my User class in. I then create a global variable called DB. This DB variable is equal to a hash that contains our connection to the database. We can access our connection anywhere throughout our Ruby program by simply calling DB[:conn].

Moving on to our User class. Let’s set up a method to create a table and drop the table. Now, we don’t need these methods to be linked to every instance we create, it just needs to be linked to our class. Therefore these methods will look something like below:

These are two class methods. At the bottom of each method, you can see a call to our database connection, with an SQLite-Ruby method called execute, which will take an argument of the SQL query we write above it.

Now, I need to write an SQL query in each method that will create or drop the table. Depending on which one is called.

The way to write SQL over multiple lines is to use a heredoc. Which is simply ‘ <<- SQL ’ at the beginning, then at the very end ‘SQL’.

This is the SQL to add to our method. We set a variable called sql then use a heredoc to write an SQL query, this query will create a table with the necessary attributes for it. (Little more complex than rails, where rails g model name age:integer would do the same and so much more).

• * Side note — if you go to your run.rb file and put User.create_table and run the file. This error may appear — ‘`open_v2’: unable to open database file (SQLite3::CantOpenException)’. Make sure you have a db folder set up! Then try again. It should work. *

Now for our drop table method. It is a little smaller but follows the same pattern of an sql query being passed to the execute method.

Our test specs are below:

In the sql variable, at the end there is a ‘;’, this is how you finish an SQL query.

These are simply copying creating or dropping a table. We call the same global variable DB[:conn] to connect.

Excellent. That’s our set up and tear down methods sorted. Now for instance methods to save instances we create to the database.

So, when we create an instance we want to be able to save it. So let’s create a method called save.

There’s a little bit more going on in this method. Firstly, our SQL statement looks a little different. Why do we have a values query with two question marks? These are called bound parameters. They protect our method and program from SQL injections and special characters. The question marks are simply placeholders. When we pass the arguments into execute, we pass the instances name and age, which will take the place of the question marks.

The last line of our save method grabs the value of the ID column of the last inserted row (which is our new instance). It then sets that equal to the given user instance’s id attribute. Then our new instance will contain a name, age, and correct id.

Here, we create our database table, save our instance of user we created at the top of our RSpec file, then test that the id is 1. It should be as this is the only instance we are creating in this block! When we query our users database to see all the instances of users, our database will return an array of all the necessary data. So id:1, name: “Ben”, age: 20.

Now, we need to make an actual create method! This method will be another class method, as remember you typically create a new instance with User.create. aka self.create. This method can also invoke our save method!

So this method is fairly straight forward, we create a new user, call .save on it, and then return the user.

All done! In your run folder put something along the lines of:

You should get an output of testname and 1.

Remember though, this is a rudimentary example, every time you run the run file it will create a new instance with “testname” and 25 as its attributes. When we send data to our database, we are sending raw data to the database, if we were to write a method to query all the users in the database, we are making an instance (object) to represent each row of data we are given from the database. You do not send the object itself to the database when you save it, you are sending a representation of all its data, the database and Ruby do not speak the same language and we need to write code to reflect what we send/receive.