SQLite and ActiveRecord

Introduction

This was intended to be a quick post about how to use the embedded relational database "SQLite" with a Ruby/Sinatra application but will also include information on incorporating the ActiveRecord pattern as well.

We'll look at installing and setting up SQLite and incorporating the ActiveRecord pattern using the open-source library of the same name (which is included as part of the Ruby on Rails Framework).

SQLite

Typically if you're developing an application in Ruby using the Ruby on Rails framework then you'll use SQLite in development and then switch over to another database for production (maybe MySQL or PostgreSQL).

What most people don't realise is that if you have a low traffic website then SQLite is fine to use.

The only thing to be aware of when using SQLite in production is that it inherently isn't designed to scale. To help clarify, the author of SQLite had this to say on the subject…

SQLite usually will work great as the database engine for low to medium traffic websites. The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

SQLite will normally work fine as the database backend to a website. But if your website is so busy that you are thinking of splitting the database component off onto a separate machine, then you should definitely consider using an enterprise-class client/server database engine instead of SQLite.

ActiveRecord

So most Ruby developers will know of ActiveRecord but may not realise that it's actually an architectural pattern and the term was originally coined by Martin Fowler (a legend in the field of software engineering and data modeling).

The pattern describes how to provide an Interface that lets the user interact with a relational database in a more user friendly fashion rather than constructing their own SQL queries.

We'll be using Ruby on Rails' implementation of the pattern which they've extracted into an external library/module (so it's not specifically reliant on the Ruby on Rails framework if you're using something more lightweight like Sinatra).

Installation

There are a couple of things we need to do:

  1. Install the SQLite libraries
  2. Install the SQLite Ruby module
  3. Install the ActiveRecord Ruby module

So if you have Homebrew installed then you can simply run brew install sqlite.

Note: you may need to run brew --force link afterwards, as a much older version of SQLite is already installed on the Mac OS and so the OS will attempt to load that first.

To install the ruby module just run gem install sqlite3

To install the ActiveRecord ruby module just run gem install activerecord

Command Line Tool

Once the SQLite libraries are installed you will have access to a command line tool that lets you directly inspect the contents of a SQLite database file.

For example, if you have a database file called test.db then you can run sqlite3 test.db to open the database.

You can then run SQL commands against the database such as select * from tableName; (notice the semicolon at the end ; you'll need this otherwise the command won't know when it is considered completed, the plus side is that you can write complex SQL queries across multiple lines).

Set-up

Imagine we have a single file sqlite-example.rb with the following content…

require 'active_record'

ActiveRecord::Base.logger = Logger.new(File.open('database.log', 'w'))

ActiveRecord::Base.establish_connection(
  :adapter  => 'sqlite3',
  :database => 'example.db'
)

…so let's analyse what this script is doing.

First thing we're doing is loading the SQLite module.

Second, ActiveRecord allows us to specify a recipient for any log information. In this instance we store any log information into a file called database.log. We could also send the log information directly to the terminal screen using Logger.new(STDERR) instead.

Thirdly, we actually connect to the SQLite database. ActiveRecord has adapters for multiple databases and so here we specify we're using SQLite and that we want to connect to the example.db database. We could store data all in memory if we wanted (e.g. it wouldn't persist after the application has finished running), we do this by changing to…

:database => ':memory:'

Schema

As you should probably already know, we need to define a Schema for our database. Because although we're using an ActiveRecord interface we're still interacting with a relational database underneath that layer.

A Schema helps dictate in code what the structure of the database should be and what the different columns/rows and data types should be used.

To do this we'll add…

ActiveRecord::Schema.define do
  unless ActiveRecord::Base.connection.tables.include? 'albums'
    create_table :albums do |table|
      table.column :title,     :string
      table.column :performer, :string
    end
  end

  unless ActiveRecord::Base.connection.tables.include? 'tracks'
    create_table :tracks do |table|
      table.column :album_id,     :integer # foreign key <table-name-singular>_id (i.e. this is the primary key from the 'albums' table)
      table.column :track_number, :integer
      table.column :title,        :string
    end
  end
end

…as you might be able to tell, we're doing a couple of things.

First we're defining a new Schema.

Second, we're creating two tables: albums and tracks (we wrap the creation of the tables in a condition so we make sure we don't cause any errors by trying to create a table that already exists). Also, the convention is to name the tables as plurals (notice we don't call it album or track as they'll be holding multiples of that data).

Thirdly, inside of the table creation we're specifying specific columns and what their data types should be.

When we create a table a "primary key" is automatically created for us and is named after the table. So in this instance we have two primary keys created for us: albums_id and tracks_id (notice the naming convention of tableName_id - singular, not plural).

Inside the tracks table you'll see the first column we create is actually a "foreign key" because we're creating a column which is named after the albums table's primary key (album_id).

Creating

The ActiveRecord pattern is based on conventions, so in this instance we'll create two new classes that inherit from ActiveRecord's base class and we'll use these two classes for creating new records for each table…

class Album < ActiveRecord::Base
  has_many :tracks
end

class Track < ActiveRecord::Base
  belongs_to :album
end

unless Album.find_by_title('In Utero')
  album = Album.create(
    :title     => 'In Utero',
    :performer => 'Nirvana'
  )

  track_listing = [
    nil,
    'Serve the Servants',
    'Scentless Apprentice',
    'Heart-Shaped Box',
    'Rape Me',
    'Frances Farmer',
    'Dumb',
    'Very Ape',
    'Milk It',
    'Pennyroyal Tea',
    'Radio Friendly Unit Shifter',
    'Tourettes',
    'All Apologies'
  ]

  track_listing.each_with_index do |value, index|
    album.tracks.create(:track_number => index, :title => value) unless index === 0 # skip zero index
  end
end

unless Album.find_by_title('La-te-ra-lus')
  album = Album.create(
    :title     => 'La-te-ra-lus',
    :performer => 'Tool'
  )

  track_listing = [
    nil,
    'The Grudge',
    'Eon Blue Apocalypse',
    'The Patient',
    'Mantra',
    'Schism',
    'Parabol',
    'Parabola',
    'Ticks & Leeches',
    'Lateralus',
    'Disposition',
    'Reflection',
    'Triad',
    'Faaip de Oiad'
  ]

  track_listing.each_with_index do |value, index|
    album.tracks.create(:track_number => index, :title => value) unless index === 0
  end
end

…OK, so there are a couple of things worth noting here:

When we inherit from ActiveRecord::Base our class is mapped to a table of the same name. So for example our class Album is mapped to the albums table.

Also, as we're inheriting from the ActiveRecord Base class, we don't need to specify attributes (for example, inside our Tracks class) such as :title or :track_number within our class, as they will be indirectly inferred from the Schema we defined earlier and will come from that table which the class is mapped to.

Association

You'll notice within the Album class we call a has_many method (provided through the inheritance chain via ActiveRecord::Base) which sets up the association between 'albums' and 'tracks'.

Similarly, within the Track class we call belongs_to and tell it that our tracks belong to the 'albums' table. We should only do this when the class/table holds the foreign key for the table we're saying it belongs to. In this case our 'Track' class holds the foreign key that points to the 'albums' table.

Record Creation

Finally we actually create some records to insert into each table.

We call a create method like so…

album = Album.create(...)

…which is equivalent to…

album = Album.new(...);
album.save

…so it saves us a little extra typing (which is nice).

You'll also notice that when we create a new Album instance we can access and create new Tracks as well (via the album instance: album.tracks.create(…)). This is because we've made an association within the top level classes between Albums and Tracks.

In my example I'm trying to be a bit smarter by not repeating the same chunk of code over and over. So rather than doing…

album.tracks.create(:track_number => 1, :title => 'a')
album.tracks.create(:track_number => 2, :title => 'b')
album.tracks.create(:track_number => 3, :title => 'c')
#…rinse repeat…

…instead, I create the track listing as an Array and then loop the Array creating new records.

The only additional logic I need is to avoid including the [0] item in the Array which I do using album.tracks.create(…) unless index === 0.

The reason I avoid it is simply because track listings on albums always start at 1 where as our Array is zero-indexed.

Querying

Now in my example code you'll find me wrapping certain chunks in begin/rescue as my example isn't made to work when accessed multiple times (it's just a silly example to demonstrate how to connect, populate and query data using ActiveRecords). So don't worry about those aspects and instead just focus on the APIs I'm using. Specifically…

find

This allows you to find a record by ID. So Album.find(1) would return the first album in the table. Album.find(1).tracks.length would then tell you the number of tracks that particular album held.

find_by

This is actually a very intelligent method. It is based on conventions again, so find_by_title maps to a standard find_by method but passes through title as the attribute we're interested in searching within: Album.find_by_title('La-te-ra-lus')

all

This is an alias for find(:all). So Album.all would return all records found within the 'Album` table.

where

This is a conditional that maps to SQL's where clause. So Track.where(title: 'Triad') would return the record(s) within the Track database whose title attribute contained the value Triad. You can do more complicated filters such as: Table.where(user_name: user_name, password: password).first which safely sanitises your input and checks two different attributes and then returns the first record found (in case there are multiples).

first

We've seen this used already and works in a similar way to how Ruby's first property works (it returns the first item found).

last

Same as first but returns the last item found.

delete

Once you've found a record then you can delete it using this method. So Album.first.delete would find the first record in the Album table and then delete that record.

save

You can modify records by first locating them and simply assigning a new value to them, but you would then use the save method to store that update back to the database: track = Track.where(title: 'yolo').first.title = 'Blah'; track.save

Conclusion

So we've not delved too deeply (as there is far too much to cover in one post) but hopefully this has been a good starting point for you.

The complete code I was using looks like this…

require 'active_record'

ActiveRecord::Base.logger = Logger.new(File.open('database.log', 'w'))

ActiveRecord::Base.establish_connection(
  :adapter  => 'sqlite3',
  :database => 'example.db'
)

ActiveRecord::Schema.define do
  unless ActiveRecord::Base.connection.tables.include? 'albums'
    create_table :albums do |table|
      table.column :title,     :string
      table.column :performer, :string
    end
  end

  unless ActiveRecord::Base.connection.tables.include? 'tracks'
    create_table :tracks do |table|
      table.column :album_id,     :integer
      table.column :track_number, :integer
      table.column :title,        :string
    end
  end
end

class Album < ActiveRecord::Base
  has_many :tracks
end

class Track < ActiveRecord::Base
  belongs_to :album
end

unless Album.find_by_title('In Utero')
  album = Album.create(
    :title     => 'In Utero',
    :performer => 'Nirvana'
  )

  track_listing = [
    nil,
    'Serve the Servants',
    'Scentless Apprentice',
    'Heart-Shaped Box',
    'Rape Me',
    'Frances Farmer',
    'Dumb',
    'Very Ape',
    'Milk It',
    'Pennyroyal Tea',
    'Radio Friendly Unit Shifter',
    'Tourettes',
    'All Apologies'
  ]

  track_listing.each_with_index do |value, index|
    album.tracks.create(:track_number => index, :title => value) unless index === 0
  end
end

unless Album.find_by_title('La-te-ra-lus')
  album = Album.create(
    :title     => 'La-te-ra-lus',
    :performer => 'Tool'
  )

  track_listing = [
    nil,
    'The Grudge',
    'Eon Blue Apocalypse',
    'The Patient',
    'Mantra',
    'Schism',
    'Parabol',
    'Parabola',
    'Ticks & Leeches',
    'Lateralus',
    'Disposition',
    'Reflection',
    'Triad',
    'Faaip de Oiad'
  ]

  track_listing.each_with_index do |value, index|
    album.tracks.create(:track_number => index, :title => value) unless index === 0
  end
end

begin
  p Album.find(1).tracks.length # 13 (on first run of this script it's fine, but next run we've deleted the record)
rescue ActiveRecord::RecordNotFound
  p 'We just rescued a "RecordNotFound" error'
end

p Album.find(2).tracks.length # 14
p Album.find_by_title('La-te-ra-lus').title # "La-te-ra-lus"
p Track.find_by_title('Very Ape').album_id # 1
p Album.all # ActiveRecord::Relation => complete set of database records
p Album.all.length # 2
p Track.all # ActiveRecord::Relation => complete set of database records
p Track.where(title: 'Triad') # ActiveRecord::Relation => single record
p Track.where(track_number: 6..8) # returns tracks 6 to 8 from all albums
p Album.first # returns first record (calling `Album.find(:first|:last) is deprecated`)
p Album.last # returns last record

if Album.all.length > 1
  p Album.first.delete # delete the first record
end

p Album.all # now we'll see there is only one record remaining

track_to_be_modified = Track.where(title: 'The Grudge').first
track_to_be_modified.title = 'Grudgeola'
track_to_be_modified.save
p track_to_be_modified # displays modified record
p Track.where(title: 'The Grudge') # empty Array (not found as we've overridden the original record)
p Track.where(title: 'Grudgeola') # displays Array of records found (only one, the modified record)

But remember this was just a quick introduction to the concepts of the ActiveRecord pattern (and using it with a SQLite database). There are many different querying methods available via ActiveRecord so do get stuck into the API documentation to see what other goodies are at your disposal.

If you don't use/like Ruby then as I said previously: there are many different adaptations of the ActiveRecord pattern so look out for implementations in your language of choice.


Links