Heroku Rails db migration file order and relationships

Question

I have a User model and a Role model. When I started building the app, I start by creating the User model and the migration file that was generated contain a reference to Roles:

class CreateUsers < ActiveRecord::Migration[5.0]
  def change
    create_table :users do |t|
      t.string :first_name
      t.string :last_name
      t.string :username
      t.string :email
      t.string :password
      t.string :password_digest
      t.boolean :banned
      t.references :role, foreign_key: true

      t.timestamps
    end
  end
end

Then I created the Role model which generated this migration file:

class CreateRoles < ActiveRecord::Migration[5.0]
  def change
    create_table :roles do |t|
      t.string :title
      t.integer :access_level

      t.timestamps
    end
  end
end

I am trying to deploy to Heroku and migrating my database per the documentation using the following command heroku run rails db:migrate (using Rails 5).

I am getting an error from Heroku saying:

heroku run rake db:migrate
Running rake db:migrate on ⬢ gentle-headland-79177... up, run.9293 (Free)
D, [2016-12-31T08:15:33.131367 #4] DEBUG -- :    (90.7ms)  CREATE TABLE "schema_migrations" ("version" character varying PRIMARY KEY)
D, [2016-12-31T08:15:33.152682 #4] DEBUG -- :    (11.5ms)  CREATE TABLE "ar_internal_metadata" ("key" character varying PRIMARY KEY, "value" character varying, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL)
D, [2016-12-31T08:15:33.155373 #4] DEBUG -- :    (1.1ms)  SELECT pg_try_advisory_lock(6845940114126317925);
D, [2016-12-31T08:15:33.172106 #4] DEBUG -- :   ActiveRecord::SchemaMigration Load (1.2ms)  SELECT "schema_migrations".* FROM "schema_migrations"
I, [2016-12-31T08:15:33.178453 #4]  INFO -- : Migrating to CreateUsers (20161117083901)
D, [2016-12-31T08:15:33.181903 #4] DEBUG -- :    (0.9ms)  BEGIN
== 20161117083901 CreateUsers: migrating ======================================
-- create_table(:users)
D, [2016-12-31T08:15:33.199351 #4] DEBUG -- :    (13.4ms)  CREATE TABLE "users" ("id" serial primary key, "first_name" character varying, "last_name" character varying, "username" character varying, "email" character varying, "password" character varying, "password_digest" character varying, "banned" boolean, "role_id" integer, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, CONSTRAINT "fk_rails_642f17018b"
FOREIGN KEY ("role_id")
  REFERENCES "roles" ("id")
)
D, [2016-12-31T08:15:33.200707 #4] DEBUG -- :    (1.0ms)  ROLLBACK
D, [2016-12-31T08:15:33.202190 #4] DEBUG -- :    (1.2ms)  SELECT pg_advisory_unlock(6845940114126317925)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::UndefinedTable: ERROR:  relation "roles" does not exist
: CREATE TABLE "users" ("id" serial primary key, "first_name" character varying, "last_name" character varying, "username" character varying, "email" character varying, "password" character varying, "password_digest" character varying, "banned" boolean, "role_id" integer, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, CONSTRAINT "fk_rails_642f17018b"
FOREIGN KEY ("role_id")
  REFERENCES "roles" ("id")
)

From my understanding, it appears Heroku is expecting Role to be defined first then User.

Why is it on my local machine, I can do db:migrate fine but on Heroku it fails?

Difference between Sqlite3 and Postgresql perhaps?

How do I go about solving this deployment problem?

Do I just rename my create_role migration file to have an earlier timestamp than the create_user migration file? Is that even recommended practice ? :D

Update

I did a git clone of my repository to a Desktop folder on my iMac.

Then I ran rails db:migrate on that new local copy.

No error whatsoever. All db migrations ran, all the tables are in place along with all the relationships. Something's really messed up on the Heroku end.

Update 2

Did another checkout of my repository into a new Desktop folder, ran bundle install and then tried this version of the db:migrate command:

rails db:migrate RAILS_ENV=production

and I see the same error output about roles does not exists

HOWEVER

I then created a brand spanking new rails project honey:

rails new honey

Did bundle install

Then went:

rails generate model User name:string role:references

Finally, I went:

rails db:migrate RAILS_ENV=production

and no errors...

I've clearly have not generated any Role model, so why does it not fail?

Here's the console log:

Warlocks-iMac:bad clementwu$ cd honey/
Warlocks-iMac:honey clementwu$ ls
Gemfile      Rakefile     config       lib          test
Gemfile.lock app          config.ru    log          tmp
README.md    bin          db           public       vendor
Warlocks-iMac:honey clementwu$ rails generate model User name:string role:references
Running via Spring preloader in process 27200
Expected string default value for '--jbuilder'; got true (boolean)
      invoke  active_record
      create    db/migrate/20170101100613_create_users.rb
      create    app/models/user.rb
      invoke    test_unit
      create      test/models/user_test.rb
      create      test/fixtures/users.yml
Warlocks-iMac:honey clementwu$ rails db:migrate RAILS_ENV=production
== 20170101100613 CreateUsers: migrating ======================================
-- create_table(:users)
   -> 0.0018s
== 20170101100613 CreateUsers: migrated (0.0018s) =============================

Warlocks-iMac:honey clementwu$ 

The database is created and even shows the role_id foreign key despite no table called role existing in my production database:

Baffling :D

Update 3

Perhaps it's the difference between a sqlite3 database and a postgresql database.

By default, a rails app config/database.yml specifies that production database is called db/production.sqlite3, i.e. it isn't using a PostgreSQL database, hence why it doesn't give the error about roles not existing.

And according to this Stackoverflow post: Does SQLite support referential integrity?

It appears SQLite3 doesn't guarantee referential integrity :(

Major pain in the butt.

It's a good thing this is only a personal learning project not a work project.

It's also not really feasible to start off with PostgreSQL, you can't just drop and recreate database as easily as you can with SQLite3 and Rails CLI.

I guess the lesson to learn here is to think hard and create the dependant tables first.

Answer 1

I fixed all my migrations and got the API running on Heroku server now.

The real answer is: deploy to Heroku early, don't wait till after finishing development on local machine then deploy.

Deploy early will identify problems early like my migration discrepancies between SQlite and PostgreSQL.

Additionally, when doing references in migration file, if not using matching model and table names e.g. author vs user, modify the migration file to use add_foreign_key before running the migration.

For example:

class CreateBooks < ActiveRecord::Migration[5.0]
  def change
    create_table :books do |t|
      t.string :title
      t.boolean :adult_content
      t.references :author, foreign_key: true

      t.timestamps
    end
  end
end

Needs to become:

class CreateBooks < ActiveRecord::Migration[5.0]
  def change
    create_table :books do |t|
      t.string :title
      t.boolean :adult_content
      t.references :author, index: true # this line changed

      t.timestamps
    end

    # new foreign key specifying correct table name and column
    add_foreign_key :books, :users, column: :author_id 

  end
end

Found the new knowledge from this link:

http://sevenseacat.net/2015/02/24/add_foreign_key_gotchas.html

Answer 2

Do I just rename my create_role migration file to have an earlier timestamp than the create_user migration file? Is that even recommended practice ? :D

Yes it's ok for your case.

BUT keep in mind doing so is ok on steps when you are scaffolding your system on very start. Once you have a production database you should be sure that your migrations are run with success before deploy.

So just a tip for future, here the my own best practice of production deploy

1. Dump your production database. You can do it with

   pg_dump <db_name> > <dump_file>; scp <server>:<path_to_dump_file> ./

   or https://github.com/sgruhier/capistrano-db-tasks

   in case of heroku here is nice article

2. Apply it in local environment with

   psql <db_name> < <dump_file>

3. Try to run migration

4. If everything go wrong go to step 5 else to step 6

5. Fix migration issues go to step 4

6. Check if migration do not corrupt data, and if it's try to fix migration and start from step 2, otherwise just deploy your code ;)

Answer 3

Migrating from SQLite to PostgreSQl to deply on heroku.

I have a situation like this but using tables: attendances, events, and users.

PG:undifiedTable ERROR: relation users does not exist

I solved this:

1. Locally I run rails db:drop - to delete databases locally

   • I had 3 migrations file example
   • 2019_11_07_205648_create_attendace
   • 2019_11_07_215648_create_events
   • 2019_11_07_225648_create_users

2. I change the day in the date in the first 2 files to:

   • 2019_11_07_225648_create_users
   • 2019_11_07_235648_create_events
   • 2019_11_07_245648_create_attendace

3. So they change order and users table is created first, I created the databases and the migration again $rails db:create rails db:migrate - works locally

4. push to heroku after committing - git push heroku master

5. heroku run rails db:migrate

6. Heroku open and it's done - Voila - Finito