Migration not executed from rspec

Question

I created the following migration script (using bundle exec rails generate migration CreateSequenceStudentId):

class CreateSequenceStudentId < ActiveRecord::Migration[6.0]
  def change
    # Based on: https://schmijos.medium.com/execute-sql-in-rails-migrations-cdb26f51c683
    execute 'CREATE SEQUENCE IF NOT EXISTS student_id;'
  end
end

Then executed bundle exec rails db:migrate RAILS_ENV=test and it completed. I can access the sequence using psql student_test -c"select nextval('student_id')". Running psql student_test -c"select * from schema_migrations sm" lists 20201122013457 at the very last. No problems so far.

I then wrote the following spec to access the sequence:

sql = "select nextval('student_id')"
p Student.connection.select_all(sql)

And it failed:

# --- Caused by: ---
# PG::UndefinedTable:
#   ERROR:  relation "student_id" does not exist
#   LINE 1: select nextval('student_id')

And I executed psql student_test -c"select nextval('student_id')" one more time and got:

ERROR:  relation "student_id" does not exist
LINE 1: select nextval('student_id')

bundle exec rails db:migrate:status RAILS_ENV=test gives:

 Status   Migration ID    Migration Name
--------------------------------------------------
...
   up     20201122013457  Create sequence student

So it indicates the migration executed successfully.

psql student_test -c"select * from schema_migrations sm" gives:

    version
----------------
 20201122013457
 20191209013052
 20191220005953
 20191225001051
 20191225001255
 20191225001458
 ...

The new script was executed first. This smells fishy.

Sounds like the migration ran successfully when using db:migrate but not when running rspec. Appreciate any pointers on what I could be doing wrong.

PS: Based on this solution, I have checked if the script name is unique and it is.

user_5841310 · Answer

I was able to fix your issue by running exact below commands. I found the answer here.

RAILS_ENV=test rake db:drop
RAILS_ENV=test rake db:create
RAILS_ENV=test rake db:migrate

UPDATE:

I found also the way to fix rails db:reset command, you just need to add line config.active_record.schema_format = :sql in your application.rb so info about sequence will be stored in structure.sql. Please take a look that with default schema dump :ruby info about seqeunce is not stored in schema.rb file.

Important: make sure to firstly create structure.sql with rails db:migrate and then run rails db:reset. After just rails db:migrate I'm still getting the error you want to fix.

Migration not executed from rspec

1 Answers