CODEWITHSUNDEEP
mysqlruby-on-railsruby-on-rails-3
LewlSauce
LewlSauce

Reputation: 5872

Rails edit migration file to include index?

I've just been trying to figure out some ways to optimize my MySQL database, but I'm having a bit of an issue with indexing here.

As opposed to stacking up a bunch of migration files (since I'm not really managing much data yet), I'm simply "going back" and editing migration files periodically to include columns, etc. Not sure if this is best practice, but I figured it'd be easier to avoid ending up with 100 migration files adding small things.

Anyways, I'm trying to add an index to one of the columns in a migration file, but I can't seem to get it to work (it's not showing up in the schema file after rolling back and migrating again).

class CreateVuln < ActiveRecord::Migration
  def change
    create_table :vuln do |t|
      t.integer :node_id
      t.string :node_identifier
      t.string :vuln_finding_identifier

      t.timestamps null: false
    end

    add_index :vuln, :node_identifier
  end
end

But when I go to schema.rb, here's what it looks like:

  create_table "vuln", force: :cascade do |t|
    t.integer  "node_id",                 limit: 4
    t.string   "node_identifier",         limit: 255
    t.string   "vuln_finding_identifier", limit: 255
    t.datetime "created_at",                               null: false
    t.datetime "updated_at",                               null: false
  end

Can someone tell me what I'm missing here? If I add a new migration by running rails g migration AddIndexToVuln node_identifier:string:index then I can see that the schema.rb file gets updated properly. schema.rb then adds this line: add_index "vuln", ["node_identifier"], name: "index_vuln_on_node_identifier", using: :btree

So as opposed to creating a new migration file, I'm just wondering if I can just roll back my database and make the changes in the existing migration file for that table.

Upvotes: 0

Views: 385

Answers (2)

oreoluwa
oreoluwa

Reputation: 5633

It's important to first understand what's going on with your migration.

So, Rails uses timestamps to track the changes in your migration, so when you run rake db:migrate, Rails would first look at the schema.rb to check the current state of the your db. You would have seen this kind of line in your schema file: ActiveRecord::Schema.define(version: 20160625234411) do, all migrations are versioned by a timestamp in this fashion.

Therefore, it would only run any new migration, i.e whose timestamp is greater than that on the schema file.

So, that's reason you should actually generate a new migration if you want to change the state of your db, otherwise, you would have to keep doing rake db:[drop|reset] etc everytime to get things working.

Upvotes: 1

Bart Jedrocha
Bart Jedrocha

Reputation: 11570

I don't see anything wrong with your migration so the first thing to check is whether rake db:rollback actually succeeds. If you ran the rollback after adding the add_index line then the rollback should fail since Rails will attempt a remove_index on a non-existent index. If the rollback fails, the fix is simple. Comment out the add_index line and run rake db:rollback, uncomment the line and then run rake db:migrate.

If you're in the early stages of development and truly don't care about the data, you can run rake db:reset which will drop the db and rebuild starting with the first migration.

All that being said, there is absolutely nothing wrong with creating new migrations (no matter how small) to modify the schema - this is their intended purpose. Sooner or later you will get to a point where rolling back and modifying migrations just won't scale so just embrace the approach from the get-go.

Upvotes: 1

Related Questions