90

I was doing this kind of thing in my migrations:

add_column :statuses, :hold_reason, :string rescue puts "column already added"

but it turns out that, while this works for SQLite, it does not work for PostgreSQL. It seems like if the add_column blows up, even if the Exception is caught, the transaction is dead and so the Migration can't do any additional work.

Is there any non-DB sepecific ways to check if a column or table already exist? Failing that, is there any way to get my rescue block to really work?

Dan Rosenstark
  • 68,471
  • 58
  • 283
  • 421
  • It need to be mentioned, that conditional migration leads to problems with rollback due to the fact that at the rollback stage it is not known what conditions were during the forward migration – oklas Feb 13 '20 at 08:48
  • Only do the non optional part in rollback – Dan Rosenstark Feb 13 '20 at 23:36

5 Answers5

193

As of Rails 3.0 and later, you can use column_exists? to check for the existance of a column.

unless column_exists? :statuses, :hold_reason
  add_column :statuses, :hold_reason, :string
end

There's also a table_exists? function, which goes as far back as Rails 2.1.

Tobias Cohen
  • 19,893
  • 7
  • 54
  • 51
  • Is it considered best practice to check if a column/table exists before add/create it? (I know of course it depends on the problem in hands) – Aldo 'xoen' Giambelluca Apr 25 '14 at 11:11
  • 4
    Does this work with rollbacks if I define it in change method? – dardub Sep 18 '15 at 17:57
  • 2
    Yeah rollback would be a issue... we're not sure whether we should remove the column or not.. since we're not recording the previous state. – songyy Jul 12 '16 at 09:26
13

Rails 6.1+:

add_column :statuses, :hold_reason, :string, if_not_exists: true

https://github.com/rails/rails/pull/38352/files

Rails < 6.1:

add_column :statuses, :hold_reason, :string unless column_exists?(:statuses, :hold_reason)
Radin Reth
  • 847
  • 8
  • 7
10

Or even shorter

add_column :statuses, :hold_reason, :string unless column_exists? :statuses, :hold_reason
SG 86
  • 6,974
  • 3
  • 25
  • 34
4

For Rails 2.X, you can check the existence of columns with the following:

columns("[table-name]").index {|col| col.name == "[column-name]"}

If it returns nil, no such column exists. If it returns a Fixnum, then the column does exist. Naturally, you can put more selective parameters between the {...} if you want to identify a column by more than just its name, for example:

{ |col| col.name == "foo" and col.sql_type == "tinyint(1)" and col.primary == nil }

(this answer first posted on How to write conditional migrations in rails?)

Community
  • 1
  • 1
JellicleCat
  • 28,480
  • 24
  • 109
  • 162
0

add_column :statuses, :hold_reason, :string unless Status.column_names.include?("hold_reason")

aabiro
  • 3,842
  • 2
  • 23
  • 36
Denis Neverov
  • 1
  • 2