Interacting with multiple schemas in Postgres using Rails and Heroku

While trying to deploy a project to Heroku, I discovered a simple solution for accessing multiple schemas on Postgres. Originally, the plan was to use two separate databases, but we moved away from this idea when I was not able to find a solution that worked. Instead we settled for using one Postgres database with multiple schemas.

In Postgres it can be common to use one or more named schemas in a single database. Each schema contains its own set of tables, and each can contain a table with the same name without any conflict. The main benefits to having multiple schemas are:

  • To allow many users to use one database without interfering with each other
  • To organize database objects into groups for more manageability
  • To allow third-party applications to have their own schemas

When using a Postgres database, by default Rails is setup to only use one schema. To allow your application to interact with other schemas only requires a few steps. For the purpose of our project we only configured the project to read from a second schema, but following this post you can see the other steps necessary to write to another schema.

Set Search Path

To be able to read from multiple schemas one simply needs to define schema search paths in the database.yml file. It should be noted that when creating tables, if a schema is not specified the default is to put it into the public schema, which every database contains.

development:

adapter: postgresql
encoding: utf-8
database: example_database
host: example_host.com
port: 5432
username: user
password: password
schema_search_path: "schema1,public"
Schema search paths should be separated only by a comma and no spacing. With the search paths defined the project will know to check both schemas for a table name. If pushing to Heroku make sure to include the schema search paths in theDATABASE_URL config variable like so:

postgres://user:password@example_host.com:5432/example_database?schema_search_path=schema1,public

Now your project is setup to search two schemas.

References



Scott
May. 04 2015