Error when querying ActiveRecord sqlite database in Ruby

Question

I'm using some basic Ruby code to create a database table and to set some values, but I'm getting an error about "albums" table already existing when trying to run the code (I'm using Ruby 1.9.3 and Active Record 4.0.0).

The code and error is below, but my question is this: firstly, how can I prevent against this error (because if the table does indeed already exist then the error would make sense) and secondly how can I view what tables the database has and the contents of those tables on the command line (using Mac OSX Terminal - I've tried the sqlite3 command and looked at the help files but I can't seem to get it to do what I want?).

The code is...

# gem install sqlite3
# gem install activerecord

require 'active_record'

ActiveRecord::Base.logger = Logger.new(STDERR)
ActiveRecord::Base.establish_connection(
  :adapter  => 'sqlite3',
  :database => 'dbfile_example' # :memory:
)

ActiveRecord::Schema.define do
  create_table :albums do |table|
    table.column :title,     :string
    table.column :performer, :string
  end

  create_table :tracks do |table|
    table.column :album_id,     :integer
    table.column :track_number, :integer
    table.column :title,        :string
  end
end

# Active Records don't specify their attributes directly
# but rather infer them from the table definition with which they're linked

class Album < ActiveRecord::Base
  has_many :tracks # adds additional collection methods to the class
end

class Track < ActiveRecord::Base
  belongs_to :album # specifies a one-to-one association with another Class (only use if this table holds the foreign key)
end

album = Album.create(
  :title     => 'In Utero',
  :performer => 'Nirvana'
) # notice you don't have to include the brackets to indicate it's a Hash

# notice the Album object instance can reference the 'tracks' collection
# via the `has_many` method and the `create` method is one of the additional
# methods added as part of the call to `has_many`

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) if index # zero is falsey so we skip it
end

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) if index
end

p Album.find(1).tracks.length
p Album.find(2).tracks.length
p Album.find_by_title('La-te-ra-lus').title
p Track.find_by_title('Very Ape').album_id

...and the error is...

-- create_table(:albums)
D, [2013-10-05T14:07:55.478413 #46822] DEBUG -- :    (0.2ms)  CREATE TABLE "albums" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "title" varchar(255), "performer" varchar(255)) 
E, [2013-10-05T14:07:55.478496 #46822] ERROR -- : SQLite3::SQLException: table "albums" already exists: CREATE TABLE "albums" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "title" varchar(255), "performer" varchar(255)) 
/Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:91:in `initialize': SQLite3::SQLException: table "albums" already exists: CREATE TABLE "albums" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "title" varchar(255), "performer" varchar(255))  (ActiveRecord::StatementInvalid)
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:91:in `new'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:91:in `prepare'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:134:in `execute'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/connection_adapters/sqlite3_adapter.rb:328:in `block in execute'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:425:in `block in log'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activesupport-4.0.0/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:420:in `log'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/connection_adapters/sqlite3_adapter.rb:328:in `execute'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/schema_statements.rb:190:in `create_table'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/migration.rb:625:in `block in method_missing'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/migration.rb:597:in `block in say_with_time'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/1.9.1/benchmark.rb:280:in `measure'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/migration.rb:597:in `say_with_time'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/migration.rb:617:in `method_missing'
  from sqlite-example.rb:13:in `block in <main>'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/schema.rb:42:in `instance_eval'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/schema.rb:42:in `define'
  from /Users/markmcdonnell/.rbfu/rubies/1.9.3-p385/lib/ruby/gems/gems/activerecord-4.0.0/lib/active_record/schema.rb:62:in `define'
  from sqlite-example.rb:12:in `<main>'

Is this code in a single file script(e.g one .rb containing all the code)? If so then you're indeed trying to create the database every single time. If that's the case you could separate that specific part (table creation) onto another file or start the script by dropping the tables before creating them: drop_table :albums — fmendez
– fmendez, Commented Oct 5, 2013 at 14:40

Integralist · Accepted Answer · 2013-10-05 16:12:19Z

4

Wrap your schema creation code with a condition checking table's existence (explicit style):

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

Can write in a more concise way but left explicit style for an easier understanding.

edited Oct 5, 2013 at 16:12

Integralist

2,2211 gold badge34 silver badges50 bronze badges

answered Oct 5, 2013 at 14:42

David Unric

7,7791 gold badge41 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Integralist Over a year ago

That didn't work for me, still displays the table "albums" already existing. It looks like the problem is later on in the code where it actually calls Album.create?

Integralist Over a year ago

Also, would it be possible to show a more concise version so I can see how someone would approach taking the full version and refactoring it to be more ruby'ish

ChrisPhoenix · Accepted Answer · 2013-11-02 17:37:24Z

2

The code was copied from this gist: https://gist.github.com/unnitallman/944011

The original gist had

ActiveRecord::Base.establish_connection(
    :adapter => "sqlite3",
    :dbfile => ":memory:"
)

So it would initialize a new database every time. It broke when you created a database file, which persists across runs of the script.

answered Nov 2, 2013 at 17:37

ChrisPhoenix

1,1081 gold badge12 silver badges16 bronze badges

Comments

Benjamin Tan Wei Hao · Accepted Answer · 2013-10-05 14:24:10Z

0

I guess one simple way is to wrap the database creation code within a begin ... rescue ... end block.

How did you run the command? If I recall, you need to run sqlite3 your_db_name.db.

answered Oct 5, 2013 at 14:24

Benjamin Tan Wei Hao

9,6723 gold badges34 silver badges57 bronze badges

1 Comment

Integralist Over a year ago

re: the sqlite command line - I ran sqlite3 and then .help to see the commands, and then I started running commands that it was showing me but none were working as I expected so didn't know if I was just doing something blatantly wrong?

Collectives™ on Stack Overflow

Error when querying ActiveRecord sqlite database in Ruby

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related