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!
Popular Articles by Our Team
Our expert team of designers and developers love what the do and enjoy sharing their knowledge with the world.
-
The Benefits of Ruby on Rails
-
Heroku Forcing Password Resets as GitHub Investigation Continues
-
How to Maximise your ROI with a Custom Application Built in Sa...
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.