Multi-schema Rails support via monkeypatching ActiveRecord's PostgreSQL::SchemaDumper

multi_schema_dumper.rb

##
# Originally forked from https://gist.github.com/drnic/9d6e63802f1a7517434c25bb80f2ec09
# Works with Rails 7.0.8 and 7.1.2
# Intended to support a multi-schema, single DB Rails app, with separate files for the public schema (schema.rb)
# and the new schema (second_schema.rb)

# my starting point was an existing database for the Rails app,
# and then adding this new schema, new schema file, and new migrations directory.

# INTEGRATION STEPS:
# 0.
# file changes 1-3 below in FILE CHANGES REQUIRED FOR INTEGRATION, then INTEGRATION STEPS here

# 1.
rails generate migration AddSecondSchema
# edit the file:
def up
  create_schema('second') unless schema_exists?('second')
end

def down
  drop_schema('second') if schema_exists?('second')
end

# 2.

# NOTE: the following doesn’t actually create the schema in the db,
# just gets the migration in the primary up to date + creates the second_schema.db file.
rails db:migrate

# 3. commit all of the above

# 4. in a separate PR:
rails g migration MyNewSecondSchemaTable --database second # creates this file in db/second_migrations/
rails db:migrate:second
# see below for more usage details.
# once the above is migrated, there should be the new schema `second` in the db,
# your new table in it, and the rails ar_internal_metadata + schema_migrations tables

# MULTI-SCHEMA USAGE after all of the above has been pushed and shipped:

# DB CREATION

# if your dbs don't exist yet, i.e. cloning repo
rails db:setup

# if your dbs do exist
# this will create or migrate both the dev db and the test db and both schemas for each db.
rails db:prepare

# MIGRATIONS

# For the `primary` public schema, continue to use
rails generate migration MyNewMigration # as normal. Then
rails db:migrate # or
rails db:prepare # (see below NOTE).

# For the `second` schema
rails g migration MyNewMigration --database second # then
rails db:migrate:second # or :
rails db:prepare

# Important: in migrations for the second schema only, when referencing a new or existing table, you will need to prepend `second.` to the table name. e.g.
create_table('second.my_table') # or:
add_index('second.my_existing_table')

# NOTE: only `db:prepare` migrates all schemas and all environments (dev + test).
# To migrate the test database, you will have to prepend your `migrate` command with the `RAILS_ENV` var, e.g.
RAILS_ENV=test rails db:migrate:second

# ROLLING BACK/DOWNGRADES

# For the `primary` public schema
rails db:rollback:primary [options] # or:
rails db:downgrade:primary [options]

# For the `second` schema
rails db:rollback:revenue [options] # or
rails db:downgrade:revenue [options]

# NOTE: the above rollback/downgrade tasks will not affect the test database
# You will have to prepend your task with the `RAILS_ENV` var, e.g.
RAILS_ENV=test rails db:rollback:primary

# FILE CHANGES REQUIRED FOR INTEGRATION

# (1) config/database.yml 
# modify to include the key configs schema_search_path and migrations_paths
# NOTE: this snippet only shows the development config now, but can be extended to test and prod

default: &default
  <<: *timeout
  adapter: postgresql
  pool: 5
  encoding: unicode
  username: postgres
  host: localhost
  password:

second: &second
  schema_search_path: "second"
  migrations_paths: db/second_migrate

development:
  primary:
    <<: *default
    database: my_service_development
    schema_search_path: "public"
  second:
    <<: *default
    database: my_service_development
    <<: *second

# (2) app/config/initializers/multi_schema_dumper.rb

ActiveSupport.on_load(:active_record_postgresqladapter) do
  def create_schema_dumper(options)
    # override and replace with subclass
    # https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb#create_schema_dumper
    ActiveRecordExtensions::MultiSchemaDumper.create(self, options)
  end
end

# (3) app/lib/active_record_extensions/multi_schema_dumper.rb

require 'active_record/connection_adapters/postgresql/schema_dumper'

module ActiveRecordExtensions
  class MultiSchemaDumper < ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaDumper
    def dump(stream)
      # taken from:
      # https://github.com/rails/rails/blob/main/activerecord/lib/active_record/schema_dumper.rb
      # reordered schemas() to be after extensions()
      header(stream)
      extensions(stream)
      schemas(stream)
      types(stream)
      tables(stream)
      trailer(stream)

      stream
    end

    private

    def schemas(stream)
      # modified from:
      # https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/postgresql/schema_dumper.rb#schemas
      # to initially read from schema_search_path which is set in database.yml.
      # this handles when the non-public schema is first being created via db:migrate, db:setup, or similar
      # after that, it will be present in `@connection.schema_names`
      excluded_schemas = ["public"]
      schema_names = (@connection.schema_names - excluded_schemas).presence || @connection.schema_search_path.split(',')

      if schema_names.any?
        schema_names.sort.each do |name|
          next if excluded_schemas.include?(name) # 'public' doesn't need a create_schema statement in any schema.db file
          next if @connection.current_schema != name # only the current_schema's schema.db file needs its own create statement

          stream.puts "  create_schema #{name.inspect} unless schema_exists? #{name.inspect}"
          stream.puts
        end
      end
    end
  end
end

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.

Yes, but it still needs the method override as I've done here and mentioned in my comment, to read from schema_search_path. The Rails method only reads from @connection.schema_names which doesn't have any additional schemas when the db is first created, so it will not write the required create_schema line to the schema file.