BigBinary Blog

Rails 6 adds if_not_exists option to create_table

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.

1>> class CreateUsers < ActiveRecord::Migration[6.0]
2>>   def change
3>>     create_table :users do |t|
4>>       t.string :name, index: { unique: true }
5>>
6>>       t.timestamps
7>>     end
8>>   end
9>> end
10
11>> CreateUsers.new.change
12-- create_table(:users)
13CREATE TABLE "users" ("id" bigserial primary key, "name" character varying, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL)
14
15=> #<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.

1>> class CreateUsers < ActiveRecord::Migration[6.0]
2>>   def change
3>>     create_table :users, if_not_exists: true do |t|
4>>       t.string :name, index: { unique: true }
5>>
6>>       t.timestamps
7>>     end
8>>   end
9>> end
10
11>> CreateUsers.new.change
12-- create_table(:users, {:if_not_exists=>true})
13CREATE TABLE "users" ("id" bigserial primary key, "name" character varying, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL)
14
15=> Traceback (most recent call last):
16        2: from (irb):121
17        1: from (irb):114:in 'change'
18ActiveRecord::StatementInvalid (PG::DuplicateTable: ERROR:  relation "users" already exists)
19: 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.

1>> class CreateUsers < ActiveRecord::Migration[5.2]
2>>   def change
3>>     unless ActiveRecord::Base.connection.table_exists?('users')
4>>       create_table :users do |t|
5>>         t.string :name
6>>
7>>         t.timestamps
8>>       end
9>>     end
10>>   end
11>> end
12
13>> CreateUsers.new.change
14
15=> 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.

1>> class CreateUsers < ActiveRecord::Migration[6.0]
2>>   def change
3>>     create_table :users, if_not_exists: true do |t|
4>>       t.string :name, index: { unique: true }
5>>
6>>       t.timestamps
7>>     end
8>>   end
9>> end
10
11>> CreateUsers.new.change
12-- create_table(:users, {:if_not_exists=>true})
13CREATE TABLE IF NOT EXISTS "users" ("id" bigserial primary key, "name" character varying, "created_at" timestamp(6) NOT NULL, "updated_at" timestamp(6) NOT NULL)
14
15=> #<PG::Result:0x00007fc4614fef48 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>
16
17>> CreateUsers.new.change
18-- create_table(:users, {:if_not_exists=>true})
19CREATE TABLE IF NOT EXISTS "users" ("id" bigserial primary key, "name" character varying, "created_at" timestamp(6) NOT NULL, "updated_at" timestamp(6) NOT NULL)
20
21=> #<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.

1>> class CreateUsers < ActiveRecord::Migration[6.0]
2>>   def change
3>>     create_table :users, if_not_exists: false do |t|
4>>       t.string :name, index: { unique: true }
5>>
6>>       t.timestamps
7>>     end
8>>   end
9>> end
10
11>> CreateUsers.new.change
12-- create_table(:users, {:if_not_exists=>false})
13CREATE TABLE "users" ("id" bigserial primary key, "name" character varying, "created_at" timestamp(6) NOT NULL, "updated_at" timestamp(6) NOT NULL)
14
15=> Traceback (most recent call last):
16        2: from (irb):23
17        1: from (irb):15:in `change'
18ActiveRecord::StatementInvalid (PG::DuplicateTable: ERROR:  relation "users" already exists
19)

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

Here is the relevant pull request.