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
