Rails 6.1 supports `if_not_exists` option for adding index


Rails 6.0

Rails 6.0.0.beta1 provided support for :if_not_exists option to create_table.

Default value for if_not_exists option is false. An exception is raised when table already exists and the table creation is attempted with this option set to false.

class CreateOrders < ActiveRecord::Migration[6.0]
  def change
    create_table :orders, if_not_exists: false do |t|
      t.string :order_number
      t.timestamps
    end
  end
end

> CreateOrders.new.change
-- create_table(:orders, {:if_not_exists=>false})
(3.0ms)  CREATE TABLE "orders" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "order_number" varchar, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL)
Traceback (most recent call last):
2: from (irb):20
1: from (irb):14:in `change'
ActiveRecord::StatementInvalid (SQLite3::SQLException: table "orders" already exists)

Whereas if_not_exists: true does not raise any exception when table already exists.

class CreateOrders < ActiveRecord::Migration[6.0]
  def change
    create_table :orders, if_not_exists: true do |t|
      t.string :order_number
      t.timestamps
    end
  end
end

> CreateOrders.new.change
-- create_table(:orders, {:if_not_exists=>true})
   (2.0ms)  SELECT sqlite_version(*)
   (0.2ms)  CREATE TABLE IF NOT EXISTS "orders" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "order_number" varchar, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL)
   -> 0.0204s
 => []

However, if_not_exists option passed to create_table is not extended to indexes if create_table also adds index along with table creation.

class CreateOrders < ActiveRecord::Migration[6.0]
  def change
    create_table :orders, if_not_exists: true do |t|
      t.string :order_number
      t.references :user, null: false, foreign_key: true
      t.timestamps
    end
  end
end

> CreateOrders.new.change
-- create_table(:orders, {:if_not_exists=>true})
   (1.0ms)  CREATE TABLE IF NOT EXISTS "orders" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "order_number" varchar, "user_id" integer NOT NULL, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL, CONSTRAINT "fk_rails_f868b47f6a"
FOREIGN KEY ("user_id")
  REFERENCES "users" ("id")
)
Traceback (most recent call last):
        2: from (irb):35
        1: from (irb):28:in `change'
ArgumentError (Index name 'index_orders_on_user_id' on table 'orders' already exists)

As shown in the above example, even with if_not_exists set to true the migration fails.

Rails 6.1

Rails 6.1 provides support for if_not_exists to indexes.

class CreateOrders < ActiveRecord::Migration[6.1]
  def change
    create_table :orders, if_not_exists: true do |t|
      t.string :order_number
      t.references :user, null: false, foreign_key: true
      t.timestamps
    end
  end
end
> CreateOrders.new.change
-- create_table(:orders, {:if_not_exists=>true})
   (2.9ms)  SELECT sqlite_version(*)
   (0.2ms)  CREATE TABLE IF NOT EXISTS "orders" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "order_number" varchar, "user_id" integer NOT NULL, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL, CONSTRAINT "fk_rails_f868b47f6a"
FOREIGN KEY ("user_id")
  REFERENCES "users" ("id")
)
   (0.2ms)  CREATE  INDEX IF NOT EXISTS "index_orders_on_user_id" ON "orders" ("user_id")
   -> 0.0255s
 => []

As shown above, the option if_not_exists: true passed to create_table gets propagated to the index created within the migration. So index creation is not attempted when the index already exists.

With these changes, we can now pass if_not_exists option to add_index as well

Example:

add_index :orders, :order_number, unique: true, if_not_exists: true