Review Apps Database Migration Issues with Ruby and heroku_ext schema

Issue

Some customers have seen difficulties with database migrations for Review Apps in Ruby due to compatibility issues between how Rails renders schemas and how new Heroku databases are provisioned using the heroku_ext schema.

Resolution

Note on Heroku Postgres extension functionality: The heroku_ext schema limitations for PostgreSQL extensions in Heroku Postgres referenced in this article result from the security vulnerability mitigation changes released on August 1, 2022 (see Changelog). On March 27, 2023, Heroku announced improvements to the Heroku Postgres extension experience. We've implemented alternative security measures so users can install extensions outside of the heroku_ext schema, effectively lifting the extension installation limitations introduced back on August 2022.

As this issue specifically relates to how Rails expects database schemas to be created, it can be resolved by creating a monkey patch similar to the following which will ensure schema's get rendered to be compatible with the heroku_ext schema. In short, you need to extend core Rails classes to enable you to specify schemas other than what Rails expects by default.

The following is an example of a possible way to do this (we tested with Rails 7). You will need to make modifications for other versions of Rails.

Notes:
1. THIS CODE IS FOR EXAMPLE PURPOSES ONLY. Any changes to your application code based off this pattern should be modified and tested to suit your individual applications use case.
2. This example uses structure.sql which we recommend using, but the same pattern will work for schema.rb if you make changes as applicable.

Setting config to use structure.sql

You can set your apps config to use structure.sql in application.rb:

module YourApp
  class Application < Rails::Application
    config.load_defaults 6.0
 
    # Add this line:
    config.active_record.schema_format = :sql
  end
end

Create the patch files

• Note: we have created 2 files in this example, but you could combine these files into one if you preferred

config/initializers/monkey_patches.rb

# frozen_string_literal: true

Dir[Rails.root.join("lib", "core_extensions", "*.rb")].sort.each { |f| require f }

ActiveSupport.on_load(:active_record) do
  raise "Review code_extensions and monkey_patches.rb" unless Rails.version == "INSERT_YOUR_RAILS_VERSION_HERE"

  ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.prepend(
    CoreExtensions::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
  )
  ActiveRecord::Tasks::PostgreSQLDatabaseTasks.prepend(
    CoreExtensions::ActiveRecord::Tasks::PostgreSQLDatabaseTasks
  )
  ActiveRecord::SchemaDumper.prepend(
    CoreExtensions::ActiveRecord::SchemaDumper
  )
  ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaDumper.prepend(
    CoreExtensions::ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaDumper
  )
end

lib/core_extensions/heroku_ext.rb

# frozen_string_literal: true

module CoreExtensions
  module ActiveRecord
    module SchemaDumper
      def dump(stream)
        header(stream)
        schemas(stream)
        extensions(stream)
        types(stream)
        tables(stream)
        trailer(stream)
        stream
      end
    end

    module ConnectionAdapters
      module PostgreSQL
        module SchemaDumper
          def schemas(stream)
            schema_names = @connection.schema_names.reject { |n| n.in?(%w[public]) }
            schema_names.each do |schema|
              stream.puts "  create_schema '#{schema}', if_not_exists: true"
            end
            stream.puts
          end

          def extensions(stream)
            extensions = @connection.extensions_with_schemas
            if extensions.any?
              stream.puts "  # These are extensions that must be enabled in order to support this database"
              extensions.keys.sort.each do |extname|
                schema = extensions[extname]
                stream.puts "  enable_extension #{extname}, schema: '#{schema}'"
              end
              stream.puts
            end
          end
        end
      end

      module PostgreSQLAdapter
        def create_schema(schema_name, **options)
          execute "CREATE SCHEMA#{" IF NOT EXISTS" if options[:if_not_exists]} #{quote_schema_name(schema_name)}"
        end

        def enable_extension(name, **options)
          exec_query("CREATE EXTENSION IF NOT EXISTS \"#{name}\"#{" WITH SCHEMA #{options[:schema]}" if options[:schema]}").tap {
            reload_type_map
          }
        end

        def extensions_with_schemas
          exec_query(
            [
              "SELECT e.extname as extname, n.nspname as nspname",
              "FROM pg_extension e",
              "LEFT JOIN pg_catalog.pg_namespace n",
              "ON n.oid = e.extnamespace",
            ].join(" "),
            "SCHEMA"
          ).cast_values.to_h
        end
      end
    end

    module Tasks
      module PostgreSQLDatabaseTasks
        def structure_dump(filename, extra_flags)
          super
          structure = File.read(filename)
          new_structure = heroku_ext_if_not_exists(structure)
          File.open(filename, "w") { |file| file.puts new_structure }
        end

        def heroku_ext_if_not_exists(structure)
          structure.gsub(/CREATE SCHEMA heroku_ext;/, "CREATE SCHEMA IF NOT EXISTS heroku_ext;")
        end

        def dequalify_gen_random_uuid(structure)
          structure.gsub(/public\.gen_random_uuid\(/, "gen_random_uuid(")
        end
      end
    end
  end
end

Update any pre-existing migrations files similar to below.

• Note: you only need to update the migrations files and structure.sql once. Once the patch has deployed you do not need to manually update files again:

structure.sql needs to follow the CREATE EXTENSION IF NOT EXISTS xxx; format so, e.g:

CREATE SCHEMA IF NOT EXISTS heroku_ext;

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA heroku_ext

db/migrate/20210722173124_create_resources.rb

class CreateResources < ActiveRecord::Migration[6.0]
  def change
    create_schema 'heroku_ext', if_not_exists: true
    enable_extension 'pgcrypto', schema: 'heroku_ext'