Patching Rails database schema dumps to support multiple PostgreSQL schemas.

schema_dumper.rb

# Overrides Rails file activerecord/lib/active_record/schema_dumper.rb to
# include all schema information in the db/schema.rb file, for example, in the
# create_table statements. This allows for a working development database
# to be built from a schema.rb file generated by rake db:schema:dump.
#
# This is essentially a rebuild of the "schema_plus_multischema" gem (which is
# unfortunately only compatible with Rails ~> 4.2).
#
# Tested with Rails 6.0.

module ActiveRecord
  class SchemaDumper
    # Overridden in order to call new method "schemas".
    def dump(stream)
      header(stream)
      extensions(stream)
      schemas(stream)
      tables(stream)
      trailer(stream)
      clean(stream)

      stream
    end

    private

    # Adds following lines just after the extensions:
    # * connection.execute "CREATE SCHEMA ..."
    # * connection.schema_search_path = ...
    def schemas(stream)
      @connection.schema_search_path.split(',').each do |name|
        stream.puts %Q{  connection.execute "CREATE SCHEMA IF NOT EXISTS #{name}"}
      end
      stream.puts ""
      stream.puts %Q{  connection.schema_search_path = #{@connection.schema_search_path.inspect}}
      stream.puts ""
    end

    # Overridden in order to build a list of tables with their schema prefix
    # (rest of the method is the same).
    def tables(stream)
      table_query = <<-SQL
        SELECT schemaname, tablename
        FROM pg_tables
        WHERE schemaname = ANY(current_schemas(false))
      SQL

      sorted_tables = @connection.exec_query(table_query, 'SCHEMA').map do |table|
        "#{table['schemaname']}.#{table['tablename']}"
      end.sort

      sorted_tables.each do |table_name|
        table(table_name, stream) unless ignored?(table_name)
      end

      if @connection.supports_foreign_keys?
        sorted_tables.each do |tbl|
          foreign_keys(tbl, stream) unless ignored?(tbl)
        end
      end
    end
  end
end

Hey the clean stream line seems to have been removed in 6.0.4.3

https://github.com/rails/rails/blob/v6.0.3.4/activerecord/lib/active_record/schema_dumper.rb#L41

To anyone getting error undefined method clean for ... just removed that line it will still work!

Hey Glen - this is great, thanks for sharing.
It'd be nice to get this merged into Rails! 🙂

Thanks for this starting point.

We ended up with the following solution for our rails 7 app (if relevant) that builds off this gist https://gist.github.com/drnic/9d6e63802f1a7517434c25bb80f2ec09

My thanks as well, and I was able to build off drnic's example for my own Rails 7 solution, including an initializer

https://gist.github.com/yesthesoup/7331b20c1ef025f38d6dea50dcd52395

In looking options to handle this currently, I can see Rails 7.1 seems to have some logic for handling create_schema invocations: https://github.com/rails/rails/blob/7-1-stable/activerecord/lib/active_record/connection_adapters/postgresql/schema_dumper.rb#L31 unlike 7.0's branch.