Skip to content
PLAY VIDEO PLAY VIDEO PLAY VIDEO
By Glen Crawford

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!

Latest Articles by Our Team

Our expert team of designers and developers love what the do and enjoy sharing their knowledge with the world.

We Hire Only the Best

reinteractive is Australia’s largest dedicated Ruby on Rails development company. We don’t cut corners and we know what we are doing.

We are an organisation made up of amazing individuals and we take pride in our team. We are 100% remote work enabling us to choose the best talent no matter which part of the country they live in. reinteractive is dedicated to making it a great place for any developer to work.

Free Community Workshops

We created the Ruby on Rails InstallFest and Ruby on Rails Development Hub to help introduce new people to software development and to help existing developers hone their skills. These workshops provide invaluable mentorship to train developers, addressing key skills shortages in the industry. Software development is a great career choice for all ages and these events help you get started and skilled up.

  • Webinars

    Webinars

    Webinars are our online portal for tips, tricks and lessons learned in everything we do. Make the most of this free resource to help you become a better developer.

    Learn more about webinars

  • Installfest

    Installfest

    The Ruby on Rails Installfest includes a full setup of your development environment and step-by-step instructions on how to build your first app hosted on Heroku. Over 1,800 attendees to date and counting.

    Learn more about Installfest

  • Development Hub

    Development Hub

    The Ruby on Rails Development Hub is a monthly event where you will get the chance to spend time with our team and others in the community to improve and hone your Ruby on Rails skills.

    Learn more about Development Hub

Get the “reinteractive Review” Monthly Email