Rails 6 adds if_not_exists option to create_table

Amit Choudhary

By Amit Choudhary

on May 22, 2019

This blog is part of our  Rails 6 series.

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.

Stay up to date with our blogs. Sign up for our newsletter.

We write about Ruby on Rails, ReactJS, React Native, remote work,open source, engineering & design.