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):
ruby
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:
```ruby # 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:
ruby
* `connection.execute "CREATE SCHEMA ..."`
* `connection.schema_search_path = ...`
```ruby
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):
ruby
def tables(stream)
table_query = <<-SQL
ruby
SELECT schemaname, tablename
ruby
FROM pg_tables
```ruby
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!
