SQLite and ActiveRecord
- Record Creation
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).
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.
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).
There are a couple of things we need to do:
- Install the SQLite libraries
- Install the SQLite Ruby module
- 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).
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
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:'
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:
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
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:
tracks_id (notice the naming convention of
tableName_id - singular, not plural).
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 (
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
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
: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.
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.
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.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
 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.
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…
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.
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:
This is an alias for
Album.all would return all records found within the 'Album` table.
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).
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).
first but returns the last item found.
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.
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
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.