How do I enable an extension only if it doesn't already exist?

Question

I’m using Rails 4.2.7. I want to create a migration that enables an extension, but only if that extension doesn’t exist in the host environment in which I’m running. I have created

class EnableUuidOsspExtension < ActiveRecord::Migration
  def change
    enable_extension 'uuid-ossp'
  end
end

but I would like to suppress enabling of the extension if this is already enabled. How do I adjust the above migration to achieve this? The motivation for this is because on my local machine I have to run this to add it to PostGres, but if I migrate to Heroku, this extension may already be in place but I don’t want things to crash when I run my db migration scripts.

Vespid

Answer 1

There is an extensions method that returns an array of extension names so you could do something like this:

def up
  enable_extension('uuid-ossp') unless extensions.include?('uuid-ossp')
end

def down
  disable_extension('uuid-ossp') if extensions.include?('uuid-ossp')
end

You could also do it by hand in SQL where you'd have access to create extension if not exists:

def up
  connection.execute('create extension if not exists "uuid-ossp"')
end

Stealage

Answer 2

In new rails you don't need specify it explicitly

Just use such syntax

def change
  enable_extension :pgcrypto
end

It generates SQL query

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

And when rollback

DROP EXTENSION IF EXISTS "pgcrypto" CASCADE;

Wilderness

Answer 3

By Postgres documentation, you can explicitly flag IF NOT EXISTS (https://www.postgresql.org/docs/current/static/sql-createextension.html)

This is also used at enable_extension in PostgreSQLAdapter (https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L332)

  def enable_extension(name)
    exec_query("CREATE EXTENSION IF NOT EXISTS \"#{name}\"").tap {
      reload_type_map
    }
  end

Also, in case it would not be used, Postgres does not re-create or do some magic if the extension is already installed. It will simply throw error – for which case normally your migration should not anyway crash and burn :)

Frankfurter