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

[This](https://stackoverflow.com/a/5905958/4160532) could help in understanding — Kedarnag Mukanahallipatna, Aug 16 '18 at 05:30
From my understanding, it adds `using: :btree`, means it is default index type for postgresql. It helps in querying results based on range, ordering etc. There could be many more reasons, this is by far the few ones I'm aware of. — Kedarnag Mukanahallipatna, Aug 16 '18 at 05:56
Hi @Kedarnag Mukanahallipatna, are you saying that this schema was created when the application author was using postgresql? How did you know that by simply looking at the line containing btree alone? Is it because postgresql doesn't support btree while mariadb does support it, so since the btree line is added, that makes you think the database must be switching from postgresql to mariadb? — Henry Yang, Aug 16 '18 at 06:39
@HenryYang The `using: :btree` must come from MariaDB like the rest of the additions. The `name: "fk_..."` would suggest that it is related to how MariaDB handles foreign key constraints. — mu is too short, Aug 16 '18 at 06:42
@Henry Yang It says so in this [documentation](https://www.postgresql.org/docs/9.2/static/indexes-types.html) — Kedarnag Mukanahallipatna, Aug 16 '18 at 06:43
@muistooshort Thank you for that clarification, I have not worked with other DB's. So `btree` has nothing to do with a particular DB, it is supported by Relational and NoSQL's ? — Kedarnag Mukanahallipatna, Aug 16 '18 at 06:45
I am a bit confused now. the link Kedarnag Mukanahallipatna posted says PostgreSQL provides the B-tree index types, but from our discussion, we agree that we are moving from postgresql to mariadb, but the schema before migration (generated when author is using postgresql) doesn't show any b-tree, while the schema after migration (generated when I'm using mariadb and did a db:migrate) did show lines with b-tree. Why is this happenning. I would expect postgresql to provide no B-tree index types because the schema before migration doesn't have any line with btree. @muistooshort — Henry Yang, Aug 16 '18 at 06:49
@muistooshort Thank you for that clarification. You helped me learn something new today :) — Kedarnag Mukanahallipatna, Aug 17 '18 at 02:18

score 2 · Accepted Answer · edited Aug 17 '18 at 00:55

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.

Let's assume the schema indeed came from using postgresql. If postgresql indeed have b-tree feature (which it does, according to this link https://www.postgresql.org/docs/9.2/static/indexes-types.html), why doesn't it display the b-tree in the schema in the first place? Why does it only show up later when I do the migrate? — Henry Yang, Aug 16 '18 at 23:46
@HenryYang Because there is no btree index called `fk_rails_584be190c2 ` in PostgreSQL, that index is only in the MariaDB version and is, presumably, a side effect of calling `add_foreign_key 'answers', 'users'` with MariaDB. And thanks for the edit. — mu is too short, Aug 17 '18 at 00:55

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

1 Answers1