Create table in Rails only if it does not exist already

In Rails, we create the tables using the migrations. A typical create table migration looks like this.

create_table :repo_subscriptions do |t|
  t.string      :user_name
  t.string      :repo_name
  t.timestamps
end

If our database already contains repo_subscriptions table and we try to run above migration then we get an error.

You may be wondering when will you run into this situation 🤔? We have multiple Rails applications which share same database in staging and production environment. But in development they do not share the database. So we need the migrations in all the applications for development but not in staging and production. This situation may also happen when working with legacy applications where tables already exist in production environment but not locally or in staging.

Rails provides a handy option if_not_exists that we can pass to create_table.

create_table :repo_subscriptions, if_not_exists: true do |t|
  t.string      :user_name
  t.string      :repo_name
  t.timestamps
end

This will ensure that Rails will attempt to create the table only if it does not exist already.

This option is supported from Rails 6 onwards so you need to be using Rails 6 to use it 😄

One more cool thing about this option is that adds the check for whether the table exists in database or not in SQL instead of checking it via Ruby code.

When we run above migration, it runs following SQL.

(3.3ms)  CREATE TABLE IF NOT EXISTS "repo_subscriptions" ("id" bigserial primary key, "user_name" character varying, "repo_name" character varying, "created_at" timestamp(6) NOT NULL, "updated_at" timestamp(6) NOT NULL)

---

This small trick helped us making sure that the migration did not fail and we didn't have to add any environment specific hackery!

If you want to know more about #Ruby and #Rails tips, follow me on Twitter.

Comments

[Andrew Brown 🇨🇦]

You can also force a table to be dropped and then created.
A bit more destructive but works well.

create_table :repo_subscriptions, force: true do |t|

[Prathamesh Sonpatki]

Yup it’s useful for tests but a bit destructive for production 😄

[Arnab Sen]

I was actually looking for this. Thanks

[Francisco Quintero 🇨🇴]

This is so awesome. Thanks for sharing. Rails full of things like this we cannot find in the docs regularly or easily.