Ruby Scripting with ActiveRecord

I'm writing a small Ruby script to pull data from multiple CSV files and I want to store the data in a database using ActiveRecord. I knew projects such as Camping use ActiveRecord directly and after a quick search I came across this DevDaily post.

The example is close but not exactly what I want to do. Instead of MySQL I like to use SQLite3 as a development database. The database is stored in a single file and there is no server that you have to configure and maintain. This allows me to delete, copy, or rename the database and proves useful for having a particular snapshot for testing.

I also want to avoid keeping a separate script or firing up a SQL Client to create a new database. ActiveRecord::Migration provides the methods needed to manage your schema. Using the example from the DevDaily site, I added a new Migration class and changed the database adapter to sqlite3. You'll also notice that you don't need a :username parameter and that the :database parameter points to a file.

require "rubygems"
require "activerecord"

ActiveRecord::Base.establish_connection(
  :adapter => "sqlite3",
  :host => "localhost",
  :database => "/home/chuck/.ar.db")

class AddEmployee < ActiveRecord::Migration
  def self.up
    create_table :employees do |t|
      t.column :first_name, :string
      t.column :last_name,  :string
    end
  end

  def self.down
    drop_table :employees
  end
end

class Employee < ActiveRecord::Base
end

AddEmployee.up unless Employee.table_exists?

employee = Employee.new
employee.first_name = "Fred"
employee.last_name = "Flinstone"
employee.save

arr = Employee.find(:all)
puts arr.inspect

The new AddEmployee class inherits from Migration and creates the table when AddEmployee.up is called. I don't want to call AddEmployee.up each time since an SQLException is thrown if the table already exists. The ActiveRecord::Base#table_exists? method allows you to check this.

Running the script the first time shows that the table is created:

chuck$ ruby lib/ar.rb 
==  AddEmployee: migrating ====================================================
-- create_table(:employees)
   -> 0.0117s
==  AddEmployee: migrated (0.0196s) ===========================================

[#<Employee id: 1, first_name: "Fred", last_name: "Flinstone">]

Running the script a second time shows that a duplicate record is created in the existing table.

chuck$ ruby lib/ar.rb 
[#<Employee id: 1, first_name: "Fred", last_name: "Flinstone">, #<Employee id: 2, first_name: "Fred", last_name: "Flinstone">]

Now everything I need to do with the database can be accomplished using Ruby.