Writing My Own Queries Vs Using Active Record

While trying to integrate a database to my application, I decided to initially write my own queries mainly just to see how difficult it would be as compared to using Active record.

 I’ll compare the experience I had with creating a table in an existing database using both methods.

1. Writing My Own Queries

   For this I was using TDD, so first I had to define the cases I was going to use to create the tables.
   The first case was to create a table with 1 column.

 #  spec
it 'creates a table with 1 column in the database' do
    database = PostgresDatabase.new
    table_name = 'game'
    column = { name: 'state', type: 'varchar(200)' } 
    result = database.create_table(table_name, @column_name, @column_type)
    exxpected = PG::Result
    expect(result).to be_instance_of(expected)
end

# PostgresDatabase
def create_table(table_name, column)
    query = "CREATE TABLE # (# #);”
    @conn.exec(query)
end

@conn is the instance of a connection to the database.
When you execute the query successfully, it returns an instance of PG::Result, otherwise it raises an error and therefore, the test is expected that. This is one way of testing and maybe the easiest in this case. Other things that can be tested could be checking in the database if there’s a table named game with the column specified.

The second case was to ensure that if a table is already created, it doesn’t create it again.
The spec for this was just executing the same query to create a table twice.

# spec
it "handles existing table" do
    query1 = @database.create_table(@table_name, @column)
    expect(query1).to be_instance_of(PG::Result)

    query2 = @database.create_table(@table_name, @column)
    expect(query2).to be_instance_of(PG::Result)
end

# PostgresDatabase
def create_table(table_name, column)
    query = "CREATE TABLE IF NOT EXISTS # (# #);”
    @conn.exec(query)
end

The spec has refactored out column and table name making them global.
The change here was to add the ‘IF NOT EXISTS’ clause to the query.

The third case was to add a case for a table with multiple columns.

 #  spec
it 'creates a table with multiple columns in the database' do
    columns = [
        { name: 'state', type: 'varchar(200)' },
        { name: 'turn', type: 'char(1)' }
    result = @database.create_table(@table_name, columns)
    exxpected = PG::Result
    expect(result).to be_instance_of(expected)
end

# PostgresDatabase
def create_table(table_name, columns)
    query = "CREATE TABLE IF NOT EXISTS # ("
    columns.each { |column| query += "# #, " }
    query += ");"
    @conn.exec(query)
end

I’ll stop with the cases here since I can pick out my points already, though the next case I did was to specify the primary key.

Next I’ll go over how I handled the same table creation with Active Record.

2. Using Active Record
I created 1 file for this in addition to the setup for active record in Sinatra like adding the necessary gems to the Gemfile as well as necessary imports in the rake file to run some rake commands.

# models/game.rb
require 'sinatra/activerecord'

class Game < ActiveRecord::Base
end

From here, you run the command
$ rake db:create_migration
This will create a migration indb/migrate/{date-time}_game.rb.
From there, I updated the migration to create the table that I wanted.

# db/migrate/_game.rb
class Game < ActiveRecord::Migration[6.0]
  def up
    create_table :games do |t|
      t.text :game_id, null: false
      t.text :state, array: true"
      t.text :turn
    end
  end
end

Next I run the command
$ rake db:migrate
This will create the schema in db/schema that has the block that creates this table for us.

# db/schema
create_table "games", force: :cascade do |t|
    t.text "game_id", null: false
    t.text "state" array: true
    t.text "turn"
end

Done, that’s all that is required for active record.

Comparison between the 2 methods
Time: Writing my own queries took a lot more time than using active record.
Simplicity: Using active record was a lot more simpler apart from the fact that I needed to constantly check the documentation for how to do everything as opposed to writing my own queries where I rarely checked.
Tests: I wasn’t able to write tests for the active record method, mainly because I followed steps on how to connect the 2 and I’m still not sure what and how I would test the schema, whereas for writing my own queries I was able to follow the TDD approach.
Code Reusability: With writing my own queries I was able to reuse the method I wrote for creating a table, whereas with active record, I had to write a different migration for each table I wanted to create.

Conclusion
Before writing this blog, I assumed that the comparison with writing my own queries and using Active record would result into active record being the outright favourite, but after writing down the points, I guess it comes down to preferences on what your goal is. In the end I went ahead with Active Record mainly because it saved me time.

References

https://samuelstern.wordpress.com/2012/11/28/making-a-simple-database-driven-website-with-sinatra-and-heroku/