Rails 4 SQLite3::SQLException error

Question

I have a devise User model which I want to add a admin boolean field so I ran

rails generate migration add_admin_to_users admin:boolean which created the following migration

class AddAdminToUsers < ActiveRecord::Migration
 def change
  add_column :users, :admin, :boolean
 end
end

However when I run rake db:migrate I keep getting the following error

SQLite3::SQLException: no such table: users: ALTER TABLE "users" ADD "admin" boolean/home/notebook/.rvm/gems/ruby-2.0.0-p353/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:91:in `initialize'

I have tried to rake db:migrate VERSION=0 to rollback to the beginning and redone rake db:migrate again but I keep getting the same error

Here is my user model migration file

class DeviseCreateUsers < ActiveRecord::Migration
  def change
   create_table(:users) do |t|
  ## Database authenticatable
  t.string :email,              :null => false, :default => ""
  t.string :encrypted_password, :null => false, :default => ""

  ## Recoverable
  t.string   :reset_password_token
  t.datetime :reset_password_sent_at

  ## Rememberable
  t.datetime :remember_created_at

  ## Trackable
  t.integer  :sign_in_count, :default => 0, :null => false
  t.datetime :current_sign_in_at
  t.datetime :last_sign_in_at
  t.string   :current_sign_in_ip
  t.string   :last_sign_in_ip

  ## Confirmable
  t.string   :confirmation_token
  t.datetime :confirmed_at
  t.datetime :confirmation_sent_at
  t.string   :unconfirmed_email # Only if using reconfirmable

  ## Lockable
  # t.integer  :failed_attempts, :default => 0, :null => false # Only if lock strategy is :failed_attempts
  # t.string   :unlock_token # Only if unlock strategy is :email or :both
  # t.datetime :locked_at
  t.timestamps
end

add_index :users, :email,                :unique => true
add_index :users, :reset_password_token, :unique => true
add_index :users, :confirmation_token,   :unique => true
# add_index :users, :unlock_token,         :unique => true
end
end

My db/schema.rb file is as follows:

ActiveRecord::Schema.define(version: 20140217093954) do
  create_table "entities", force: true do |t|
   t.string   "entity"
   t.string   "genre"
   t.string   "url"
   t.datetime "created_at"
   t.datetime "updated_at"
end

   add_index "entities", ["entity"], name: "index_entities_on_entity", unique: true

end

score 2 · Accepted Answer · edited May 23 '17 at 10:24

2

It sounds like your development database is not in sync with the schema (or what you think the schema is). Running database migrations is intended for incremental changes and not intended to be be a way to rollback the database and run from the first migration. For this you want to use rake db:reset, which will drop the database and load the schema from db/schema. This post has a good overview of the different database rake commands.

edited May 23 '17 at 10:24

Community

1
1

answered Feb 23 '14 at 15:41

Peter Brown

50,956
18
113
146

I've tried that too and then run rake db:migrate but I keep getting the same error – Mutuma Feb 23 '14 at 15:51
@Mutuma does your db/schema file have a users table in it? – Peter Brown Feb 23 '14 at 15:55
I have like 13 models and my schema file indicates only one model which I had scaffolded but when I deleted the scaffold nothing in the schema changed. Let me post the schema.rb file – Mutuma Feb 23 '14 at 15:58
looks like you didn't run some of the migrations. What happens if you do "rake db:drop && rake db:migrate"? – Peter Brown Feb 23 '14 at 16:06
I've just dropped the db and migrated the migrations and it worked – Mutuma Feb 23 '14 at 16:10

sites · Answer 2 · 2014-02-23T15:45:26.567

1

Your database has not users table, you need to create users table first:

rails g migration create_users

And put that migration before the one you are trying to run now, i.e. changing its timestamp.

An easier option is to add in your current file:

class AddAdminToUsers < ActiveRecord::Migration
 def change
  create_table :users
  add_column :users, :admin, :boolean
 end
end

Whatever of the solutions you apply, you have a problem with the order of your migrations, search inside migrate folder for a migration that actually creates users table.

edited Feb 23 '14 at 15:45

answered Feb 23 '14 at 15:39

sites

21,417
17
87
146

I created a Users table using the command rails g model Users which created a users table – Mutuma Feb 23 '14 at 15:42
please post migration file creating that table, that way we can be sure migration exists. – sites Feb 23 '14 at 15:44
I have a had added a username field to the same table using a similar command only that I used username instead of admin and string as a field type and it worked perfectly – Mutuma Feb 23 '14 at 15:45
problem could be the timestamp of migrations, check that number in file `xxx_create_users` is greater than `xxx_add_admin_to_users`, `xxx` are the numbers – sites Feb 23 '14 at 15:47
The file name of the admin migration is 20140223152952_add_admin_to_users.rb and that of the user model is 20140211193312_devise_create_users.rb – Mutuma Feb 23 '14 at 15:53

Rails 4 SQLite3::SQLException error

2 Answers2