Don't add database index if it already exists in Rails
Tags: rails
Published: 2020-04-19 17:49:08

---
title: Don't add database index if it already exists in Rails
published: true
date: 2020-04-16 04:46:05 UTC
tags: rails
canonical_url: https://prathamesh.tech/2020/04/16/dont-add-database-index-if-it-already-exists/
---

Rails 6 added an option `if_not_exists` to the `create_table` method which will not create the table if it already exists.  This option is handy when we have added a table directly in production but now want to add it to the code so that it is available in other environments as well.

Let's look at a migration to understand this feature.

<!--kg-card-begin: code-->
```rb
class CreateIssues < ActiveRecord::Migration[6.0]
  def change
    create_table :issues, if_not_exists: true do |t|
      t.integer :comment_count
      t.string :url, :repo_name, :user_name
      t.datetime :last_touched_at
      t.integer :number

      t.timestamps
    end
  end
end
```
<!--kg-card-end: code-->

This migration will not create the `issues` table if it exists already, as we have passed `if_not_exists: true` to the `create_table` method. Let's change the migration a little bit. We will now add a reference to `users` table in `issues` table because we want to create a reference to the `users` table as well.

<!--kg-card-begin: code-->
```rb
class AddIssuesAgainAgainAgain < ActiveRecord::Migration[6.0]
  def change
    create_table :issues, if_not_exists: true do |t|
      t.integer :comment_count
      t.string :url, :repo_name, :user_name
      t.datetime :last_touched_at
      t.integer :number

      t.references :user
      t.timestamps
    end
  end
end

```
<!--kg-card-end: code-->

If this migration is run twice, it results into following error.

<!--kg-card-begin: code-->
```sh
➜ codetriage git:(master) ✗ be rake db:migrate
== 20200401140530 AddIssuesAgainAgainAgain: migrating =========================
-- create_table(:issues, {:if_not_exists=>true})
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

Index name 'index_issues_on_user_id' on table 'issues' already exists
/Users/prathamesh/Projects/sources/codetriage/db/migrate/20200401140530_add_issues_again_again_again.rb:3:in `change'
/Users/prathamesh/.rbenv/versions/2.6.5/bin/bundle:23:in `load'
/Users/prathamesh/.rbenv/versions/2.6.5/bin/bundle:23:in `<main>'

Caused by:
ArgumentError: Index name 'index_issues_on_user_id' on table 'issues' already exists
/Users/prathamesh/Projects/sources/codetriage/db/migrate/20200401140530_add_issues_again_again_again.rb:3:in `change'
/Users/prathamesh/.rbenv/versions/2.6.5/bin/bundle:23:in `load'
/Users/prathamesh/.rbenv/versions/2.6.5/bin/bundle:23:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
```
<!--kg-card-end: code-->

We can see that it did not cause an error related to creating the table. The error was raised for existing index.

_But we do know that the index may exist. That's why we added the `if_not_exists: true` flag._

Then why is Rails trying to create the index when we clearly told it check if the index exists or not.

Well, turns out the `if_not_exists` flag was not getting passed to the query that Rails was generating for adding index. To understand this properly, let's take a look at queries generated by above migration.

<!--kg-card-begin: code-->
```rb
CREATE TABLE IF NOT EXISTS `issues`
CREATE INDEX `index_issues_on_user_id` on `issues`
```
<!--kg-card-end: code-->

The `INDEX` query that was generated by the above migration was not having the `IF NOT EXISTS` clause so Rails was trying to create the index again even if the `if_not_exists: true` was passed.

Let's look at the code of `create_table` method in Rails to understand why this is happening. We will only see the relevant code related to adding indexes as the method is very big.

<!--kg-card-begin: code-->
```rb
def create_table
  td = create_table_definition(table_name, **options.extract!(:temporary, :if_not_exists, :options, :as, :comment))
  ...
  
  result = execute schema_creation.accept td
  ...
  
  unless supports_indexes_in_create?
    td.indexes.each do |column_name, index_options|
      add_index(table_name, column_name, index_options)
    end
  end
  ...
  
  result    
end
```
<!--kg-card-end: code-->

> The `create_table` also adds comments to the table. For brevity, we have only considered the relevant code above.

This code if translated to plain English looks like this.

<!--kg-card-begin: code-->
```rb
def create_table
  Generate SQL for CREATE TABLE
  result = Execute SQL for CREATE TABLE
  
  For each of the indexes -
   1) Generate SQL for ADD INDEX
   2) Execute SQL for ADD INDEX
   
  return result
end
```
<!--kg-card-end: code-->

Looking at the error that we got related to existing index, it is pretty evident that `if_not_exists` option was not used while generating the SQL for index whereas it was used while generating the SQL for creating table.

Now the solution is straightforward. We need to make sure that Rails uses the `if_not_exists` option while generating the SQL for index.

The generation of SQL for indexes and its execution is handled by the `add_index` method. It looks like this.

<!--kg-card-begin: code-->
```rb
def add_index(table_name, column_name, options = {})
  index_name, index_type, index_columns, index_options = add_index_options(table_name, column_name, **options)
  execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{index_columns})#{index_options}"
end
```
<!--kg-card-end: code-->

So if we pass the `if_not_exists` option from `create_table` to `add_index` which in turn passes it to

`add_index_options` and which generates the proper SQL that we want, then our problem is solved!

That's what I did in this pull request [https://github.com/rails/rails/pull/38555](https://github.com/rails/rails/pull/38555) and this issue is now fixed on Rails master.

### One last thing

While working on this fix, I realized that MySQL does not support `IF NOT EXISTS` clause for indexes. Bummer! To support this feature for MySQL adapter, we have to actually check if the index exists or not before trying to add it.

<!--kg-card-begin: code-->
```rb
# activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb 

def add_index(table_name, column_name, options = {}) #:nodoc:
  return if options[:if_not_exists] && index_exists?(table_name, column_name, options)
  ..
  execute ..
end
```
<!--kg-card-end: code-->
### One more thing!

The support for `if_not_exists` is also available to `add_index` method now. So you can just pass it as follows when trying to create standalone indexes.

<!--kg-card-begin: code-->
```rb
add_index :issues, :user_id, if_not_exists: true
```
<!--kg-card-end: code-->

If you are interested in more such articles about Ruby on Rails, then [subscribe to my newsletter.](https://prathamesh.tech/mailing-list)