How to create ruby test to ensure that created postgresql index for specific column is used/covered

I'm working with a Ruby on Rails framework, and I have an ActiveRecord model called "Post" with the following columns:

• id: bigint
• title: string
  I'm utilizing GitLab CI/CD for my project. Let's assume that the index test has passed successfully.

Now, I need to create a migration in a separate Merge Request to add an additional reference column to another table. Here's the migration code:

add_reference :posts, :person, foreign_key: { to_table: :persons }, index: true

In my Rails project, I have configured the schema format as follows:

config.active_record.schema_format = :sql

After this migration is executed, a new index will be created. As first step I want to create a Ruby script that can compare the "structure.sql" file in the upstream branch with the "structure.sql" file in the current branch version. The script should be able to detect the newly created indexes and return an array of those indexes.

I'm seeking guidance on how to implement this Ruby test and retrieve the information about the created indexes.

Comparing the SQL DB structure from various git versions is possible, but it's not a great way to test this. Would you lock in the commit SHA "pre-index" and compare to the current checked-out commit? There are complications there such as if you re-base before merging.

Rather how about just testing that the index exists. It's usually up to the DB as to when the index gets utilized or not so let's ignore that. But its simple to assert the index exists.

shoulda-matchers is a very common Rails & Rspec testing gem. It provides a have_db_index matcher that you can use in an RSpec test to assert the index is present. If you're not using it yet, I urge you to give it a shot. It's one of the first gems I install after rails new

Once you've installed the gem (see their github page) you can use the matcher in a test similar to:

  # in your spec/models/post_spec.rb
  it 'has a DB index for the person reference' do
    is_expected.to have_db_index(:person_id)
  end

  # or as a readable one-liner
  it { is_expected.to have_db_index(:person_id) }

Even this is maybe a little more than many devs would test, but it's a fast and easy test so little harm if you want to be thorough here.

You can also use the gem to add a straightforward test for this relationship:

  it { is_expected.to belong_to(:person) }