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:
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.
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.
151 Ted Turner Drive, Atlanta, GA
Let's talk: info@standardco.de
