Creating a multi-tenant application using PostgreSQL's schemas and Rails

Posted by ramon.tayag on Stack Overflow See other posts from Stack Overflow or by ramon.tayag
Published on 2010-05-06T16:37:51Z Indexed on 2010/05/07 3:58 UTC
Read the original article Hit count: 1514

Stuff I've already figured out

I'm learning how to create a multi-tenant application in Rails that serves data from different schemas based on what domain or subdomain is used to view the application.

I already have a few concerns answered:

  1. How can you get subdomain-fu to work with domains as well? Here's someone that asked the same question which leads you to this blog.
  2. What database, and how will it be structured? Here's an excellent talk by Guy Naor, and good question about PostgreSQL and schemas.
  3. I already know my schemas will all have the same structure. They will differ in the data they hold. So, how can you run migrations for all schemas? Here's an answer.

Those three points cover a lot of the general stuff I need to know. However, in the next steps I seem to have many ways of implementing things. I'm hoping that there's a better, easier way.

Finally, to my question

When a new user signs up, I can easily create the schema. However, what would be the best and easiest way to load the structure that the rest of the schemas already have? Here are some questions/scenarios that might give you a better idea.

  1. Should I pass it on to a shell script that dumps the public schema into a temporary one, and imports it back to my main database (pretty much like what Guy Naor says in his video)? Here's a quick summary/script I got from the helpful #postgres on freenode. While this will probably work, I'm gonna have to do a lot of stuff outside of Rails, which makes me a bit uncomfortable.. which also brings me to the next question.
  2. Is there a way to do this straight from Ruby on Rails? Like create a PostgreSQL schema, then just load the Rails database schema (schema.rb - I know, it's confusing) into that PostgreSQL schema.
  3. Is there a gem/plugin that has these things already? Methods like "create_pg_schema_and_load_rails_schema(the_new_schema_name)". If there's none, I'll probably work at making one, but I'm doubtful about how well tested it'll be with all the moving parts (especially if I end up using a shell script to create and manage new PostgreSQL schemas).

Thanks, and I hope that wasn't too long!

UPDATE May 11, 2010 11:26 GMT+8

Since last night I've been able to get a method to work that creates a new schema and loads schema.rb into it. Not sure if what I'm doing is correct (seems to work fine, so far) but it's a step closer at least. If there's a better way please let me know.

    module SchemaUtils
        def self.add_schema_to_path(schema)
            conn = ActiveRecord::Base.connection
            conn.execute "SET search_path TO #{schema}, #{conn.schema_search_path}"

        def self.reset_search_path
            conn = ActiveRecord::Base.connection
            conn.execute "SET search_path TO #{conn.schema_search_path}"

        def self.create_and_migrate_schema(schema_name)
            conn = ActiveRecord::Base.connection

            schemas = conn.select_values("select * from pg_namespace where nspname != 'information_schema' AND nspname NOT LIKE 'pg%'")

            if schemas.include?(schema_name)
                tables = conn.tables
       "#{schema_name} exists already with these tables #{tables.inspect}"
       "About to create #{schema_name}"
                conn.execute "create schema #{schema_name}"

            # Save the old search path so we can set it back at the end of this method
            old_search_path = conn.schema_search_path

            # Tried to set the search path like in the methods above (from Guy Naor)
            # conn.execute "SET search_path TO #{schema_name}"
            # But the connection itself seems to remember the old search path.
            # If set this way, it works.
            conn.schema_search_path = schema_name

            # Directly from databases.rake. 
            # In Rails 2.3.5 databases.rake can be found in railties/lib/tasks/databases.rake
            file = "#{Rails.root}/db/schema.rb"
            if File.exists?(file)
       "About to load the schema #{file}"
                abort %{#{file} doesn't exist yet. It's possible that you just ran a migration!}

   "About to set search path back to #{old_search_path}."
            conn.schema_search_path = old_search_path

© Stack Overflow or respective owner

Related posts about ruby-on-rails

Related posts about postgresql