Patching Rails Database Schema Dumps to Support Multiple PostgreSQL Schemas

If you have a Ruby on Rails application running with a PostgreSQL database, then you may be aware that both tools support the usage of ActiveRecord models being backed by database tables stored in different schemas. This isn't the most common thing to do, but it can sometimes be useful to namespace your database tables within different schemas to keep them separate.

For example, at reinteractive on storeConnect®, we have tables that are synced from Salesforce via Heroku Connect. These tables are stored in the salesforce schema, and other tables that are specific to the local Rails application, such as shopping carts, are stored in the store_connect schema.

This means that the StoreConnect::Order model is stored in the salesforce database schema with a full name of salesforce.order, and that the StoreConnect::Cart model is stored in the store_connect schema with a full name of store_connect.carts.

In addition, there is the standard public schema and a postgis schema. It's not that unusual to have multiple schemas in a Rails application, but it is a little unusual to want them included in schema dumps. This is likely why Rails does not support it.

The Problem

If you have a Rails app with a multiple-schema database and you run rake db:schema:dump, the resulting db/schema.rb file will not prefix the tables with their schema, such as in the create_table statements. You will end up with, for example, create_table "order" ... for the salesforce.order table, and your models will not be able to locate their tables after loading the database schema from the file. Obviously, this is bad enough by itself, but it's even worse if you have two tables with the same name in different schemas.

The Options

To solve this, we have three options:

1) Update your Rails application's configuration to dump the schema as a SQL file (db/structure.sql) via rake db:structure:dump. This is far more verbose, is not database (or often database version) agnostic, and leads to additional churn in the file. There are valid reasons to use a SQL structure dump, such as for including functions and triggers, but in this case we can get away without doing so. It is far nicer to stick with the default Ruby format of the schema dump.

2) Patch ActiveRecord so that the schema dump file includes the schema creation and specifies the appropriate schemas when creating each table. I know that patching Rails internals is not nice, but it's better than SQL structure dumps!

3) There is a gem to do exactly this. Unfortunately it only supports Rails ~> 4.2, so that's out if you are on a more modern version of Rails.

The Solution

It's actually not that much work; see this gist for the full code. Note that this has been tested with Rails 6.0, the latest major version of Rails.

First we will tap into the ActiveRecord::SchemaDumper class. Create this file in your application and require it in an appropriate place in the application boot process (I'll leave that location up to you):

    module ActiveRecord
      class SchemaDumper
        # The below code will go here. Read on!
      end
    end

Next we'll override the dump method to insert a new schemas method into the process:

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

      stream
    end

Next add the new schemas method. This adds the following lines near the top of the file, just after the extensions:

* `connection.execute "CREATE SCHEMA ..."`
* `connection.schema_search_path = ...`

    private

    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  

Lastly, we need to override the tables method to build the list of tables including their schema prefixes (the rest of the method is the same as the original):

    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

And that's it! Include the above in your Rails configuration, and run rake db:schema:dump (or rake db:migrate or a similar task). You should see that the generated db/schema.rb file now includes all necessary schema information, and your models will be able to locate your namespaced tables after loading it. Refer to the gist for the full code.

And again: I know it isn't nice to patch ActiveRecord internals, so it's up to you to weigh doing so against the hassle of needlessly dealing with SQL structure dumps. For our team, this has been far more preferable. Thanks for reading!