loading...

Create table in Rails only if it does not exist already

prathamesh profile image Prathamesh Sonpatki ・2 min read

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.

Discussion

pic
Editor guide
Collapse
andrewbrown profile image
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|
Collapse
prathamesh profile image
Prathamesh Sonpatki Author

Yup it’s useful for tests but a bit destructive for production πŸ˜„