This blog is part of our Rails 6 series. Rails 6.0.0.rc1 was recently released.

Rails 6 added if_not_exists to create_table option to create a table if it doesn’t exist.

Before Rails 6, we could use ActiveRecord::Base.connection.table_exists?.

Default value of if_not_exists option is false.

Rails 5.2

Let’s create users table in Rails 5.2.

>> class CreateUsers < ActiveRecord::Migration[6.0]
>>   def change
>>     create_table :users do |t|
>>       t.string :name, index: { unique: true }
>>
>>       t.timestamps
>>     end
>>   end
>> end

>> CreateUsers.new.change
-- create_table(:users)
CREATE TABLE "users" ("id" bigserial primary key, "name" character varying, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL)

=> #<PG::Result:0x00007fd73e711cf0 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>

Now let’s try creating users table again with if_not_exists option.

>> class CreateUsers < ActiveRecord::Migration[6.0]
>>   def change
>>     create_table :users, if_not_exists: true do |t|
>>       t.string :name, index: { unique: true }
>>
>>       t.timestamps
>>     end
>>   end
>> end

>> CreateUsers.new.change
-- create_table(:users, {:if_not_exists=>true})
CREATE TABLE "users" ("id" bigserial primary key, "name" character varying, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL)

=> Traceback (most recent call last):
        2: from (irb):121
        1: from (irb):114:in 'change'
ActiveRecord::StatementInvalid (PG::DuplicateTable: ERROR:  relation "users" already exists)
: CREATE TABLE "users" ("id" bigserial primary key, "name" character varying, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL)

We can see that Rails 5.2 ignored if_not_exists option and tried creating the table again.

Now let’s try ActiveRecord::Base.connection.table_exists? with Rails 5.2.

>> class CreateUsers < ActiveRecord::Migration[5.2]
>>   def change
>>     unless ActiveRecord::Base.connection.table_exists?('users')
>>       create_table :users do |t|
>>         t.string :name
>>
>>         t.timestamps
>>       end
>>     end
>>   end
>> end

>> CreateUsers.new.change

=> nil

We can see that create_table :users never executed because ActiveRecord::Base.connection.table_exists?('users') returned true.

Rails 6.0.0.beta2

Let’s create users table in Rails 6 with if_not_exists option set as true.

>> class CreateUsers < ActiveRecord::Migration[6.0]
>>   def change
>>     create_table :users, if_not_exists: true do |t|
>>       t.string :name, index: { unique: true }
>>
>>       t.timestamps
>>     end
>>   end
>> end

>> CreateUsers.new.change
-- create_table(:users, {:if_not_exists=>true})
CREATE TABLE IF NOT EXISTS "users" ("id" bigserial primary key, "name" character varying, "created_at" timestamp(6) NOT NULL, "updated_at" timestamp(6) NOT NULL)

=> #<PG::Result:0x00007fc4614fef48 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>

>> CreateUsers.new.change
-- create_table(:users, {:if_not_exists=>true})
CREATE TABLE IF NOT EXISTS "users" ("id" bigserial primary key, "name" character varying, "created_at" timestamp(6) NOT NULL, "updated_at" timestamp(6) NOT NULL)

=> #<PG::Result:0x00007fc46513fde0 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>

We can see that no exception was raised when we tried creating users table the second time.

Now let’s see what happens if we set if_not_exists to false.

>> class CreateUsers < ActiveRecord::Migration[6.0]
>>   def change
>>     create_table :users, if_not_exists: false do |t|
>>       t.string :name, index: { unique: true }
>>
>>       t.timestamps
>>     end
>>   end
>> end

>> CreateUsers.new.change
-- create_table(:users, {:if_not_exists=>false})
CREATE TABLE "users" ("id" bigserial primary key, "name" character varying, "created_at" timestamp(6) NOT NULL, "updated_at" timestamp(6) NOT NULL)

=> Traceback (most recent call last):
        2: from (irb):23
        1: from (irb):15:in `change'
ActiveRecord::StatementInvalid (PG::DuplicateTable: ERROR:  relation "users" already exists
)

As we can see, Rails raised an exception here because if_not_exists was set to false.

Here is the relevant pull request.