Jonathan Altman

I wanted a nice convenient way to have multiple Rails apps share my single postgresql database but keep all the apps' tables/procs/views/other artifacts isolated from each other. I came up with the following solution, which I can't claim is necessarily original, but I did not find documentation that laid out the solution all in one place. I currently have the typo blog you are reading this article on up using this solution.

The trick is to use multiple Postgresql schemas (part of Postgresql since 7.4) to separate each Rails app's tables etc. into their own schema space Then, every time you want to load up a new Rails app, or really any postgresql-based application, you just create a new schema. You could in theory even put your development, testing, and production databases for a single Rails app into separate schemas in one database.

Documentation on Postgresql schemas, and more information about what the recipe below is doing with schema commands is here. Rails has had support for leveraging Postgresql schemas since version 0.11.0 as mentioned here.

Steps to create a schema in a postgresql database and configure a Rails app to use the schema

Step 1. Create the new schema in your database:

CREATE SCHEMA __myrailsapp__\g

Replace __myrailsapp__ with whatever name you want for your schema in the SQL above and everywhere else following.

Step 2. If you are installing an existing Rails app that has a SQL script to generate the tables etc., then insert the following as the first line of the schema file, otherwise skip to step 4:

SET search_path TO __myrailsapp__;

Postgresql uses the first schema in the schema path as the default location to look for tables/procs/triggers/etc if the name is unspecified (see this discussion in the Postgresql docs for more details).

Step 3. Execute the SQL script with the change from step 2. If you go into psql and issue the following commands, you should see the objects that your script created:

Step 4. Edit your database.yml file and add the following to the appropriate section(s):

schema_search_path: __myrailsapp__

So your database.yml section for production would look like this if you wanted to use the myrailsapp schema in the mydbname database:

That schema_search_path: statement in your database.yml file tells Rails to set the schema search path to look only in the __myrailsapp__ schema for unqualified database object names. That means that without changing anything about how you write your code, your Rails app now has all its artifacts in a schema inside your database.

Enjoy!

Jonathan