Creating a multi-tenant application using PostgreSQL's schemas and Rails
- by ramon.tayag
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:
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.
What database, and how will it be structured? Here's an excellent talk by Guy Naor, and good question about PostgreSQL and schemas.
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.
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.
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.
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}"
end
def self.reset_search_path
conn = ActiveRecord::Base.connection
conn.execute "SET search_path TO #{conn.schema_search_path}"
end
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
Rails.logger.info "#{schema_name} exists already with these tables #{tables.inspect}"
else
Rails.logger.info "About to create #{schema_name}"
conn.execute "create schema #{schema_name}"
end
# 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)
Rails.logger.info "About to load the schema #{file}"
load(file)
else
abort %{#{file} doesn't exist yet. It's possible that you just ran a migration!}
end
Rails.logger.info "About to set search path back to #{old_search_path}."
conn.schema_search_path = old_search_path
end
end
