Why exactly does schema.rb get modified if I run `rake db:migrate` immediately after `rake db:schema:load`?

Question

Question summary:

I cloned an open source project called DMP Roadmap and follow its installation guide until rake db:schema:load.

Then I do rake db:migrate which produce no output in terminal, but I notice the schema.rb file got modified. I don't think this should happen because rake db:migrate doesn't produce any output, which should imply that nothing will be changed, but the schema file is changed.

There must be something going on underneath, can anyone who knows the ins and outs of DB migration please explain what happened underneath?

Step to reproduce:

On Mac OS, brew install mariadb (brew info mariadb will say: stable 10.3.9 (bottled))

Follow the installation guide:

git clone https://github.com/DMPRoadmap/roadmap.git
cd roadmap
cp config/database_example.yml config/database.yml
cp config/secrets_example.yml config/secrets.yml
cp config/branding_example.yml config/branding.yml
cp config/initializers/devise.rb.example config/initializers/devise.rb
cp config/initializers/recaptcha.rb.example config/initializers/recaptcha.rb
cp config/initializers/wicked_pdf.rb.example config/initializers/wicked_pdf.rb
bundle install
rake secret
vi config/secrets.yml # put the secret generated above into `config/secret.yml`
rake db:create
rake db:schema:load
rake db:migrate

Part of the git diff after doing rake db:migrate:

 ActiveRecord::Schema.define(version: 20180508151824) do

   create_table "annotations", force: :cascade do |t|
-    t.integer  "question_id"
-    t.integer  "org_id"
-    t.text     "text"
-    t.integer  "type",        default: 0, null: false
+    t.integer  "question_id", limit: 4
+    t.integer  "org_id",      limit: 4
+    t.text     "text",        limit: 65535
+    t.integer  "type",        limit: 4,     default: 0, null: false
     t.datetime "created_at"
     t.datetime "updated_at"
   end

-  add_index "annotations", ["question_id"], name: "index_annotations_on_question_id"
+  add_index "annotations", ["org_id"], name: "fk_rails_aca7521f72", using: :btree
+  add_index "annotations", ["question_id"], name: "index_annotations_on_question_id", using: :btree

   create_table "answers", force: :cascade do |t|
-    t.text     "text"
-    t.integer  "plan_id"
-    t.integer  "user_id"
-    t.integer  "question_id"
+    t.text     "text",         limit: 65535
+    t.integer  "plan_id",      limit: 4
+    t.integer  "user_id",      limit: 4
+    t.integer  "question_id",  limit: 4
     t.datetime "created_at"
     t.datetime "updated_at"
-    t.integer  "lock_version", default: 0
+    t.integer  "lock_version", limit: 4,     default: 0
   end

-  add_index "answers", ["plan_id"], name: "index_answers_on_plan_id"
-  add_index "answers", ["question_id"], name: "index_answers_on_question_id"
+  add_index "answers", ["plan_id"], name: "index_answers_on_plan_id", using: :btree
+  add_index "answers", ["question_id"], name: "index_answers_on_question_id", using: :btree
+  add_index "answers", ["user_id"], name: "fk_rails_584be190c2", using: :btree

   create_table "answers_question_options", id: false, force: :cascade do |t|
-    t.integer "answer_id",          null: false

Extra questions:

• Why did it add a bunch of limit and btree?
• Why did it add this line: + add_index "annotations", ["org_id"], name: "fk_rails_aca7521f72", using: :btree? It wasn't here before

Answer 1

Your db/schema.rb file represents that database's structure as Rails sees it. Any time you do anything that could change the database, Rails will query the database for its current structure and write that structure to db/schema.rb.

Rails has no way of knowing if a migration will change the structure of the database. If you only stick to the usual migration helpers then it could, in theory, know if the schema changed but there's always connection.execute for sending SQL directly to the database. Building schema.rb is relatively quick so the easiest (and sanest) solution is to rebuild it every time you rake db:migrate.

The change from:

t.integer  "question_id"

to

t.integer  "question_id", limit: 4

suggests that the original schema.rb came from PostgreSQL (which doesn't support a :limit option on integer columns) but yours came from MariaDB (which does have :limits on integer columns). The other changes have the same source: PostgreSQL does things one way, MariaDB does them another way.

---

I need more than a comment to clarify a few things for some commenters so here goes.

Looks like btree indexes are supported by MariaDB (a fork of MySQL to keep Oracle away) and in this specific case, the fk_rails_584be190c2 name suggests that it is tied up with how MariaDB (or ActiveRecord with MariaDB) handles foreign keys. If you look at the bottom of the original schema.rb:

add_foreign_key "annotations", "orgs"
...
add_foreign_key "answers", "users"
...

The FK on answers.users matches the btree index named fk_rails_584be190c2. FKs often have indexes associated with them to make checking referential integrity efficient. Perhaps ActiveRecord/MariaDB creates them automatically, perhaps MariaDB does it on its own. I'm not a MySQL or MariaDB expert so I don't know.