#389 Multitenancy with PostgreSQL pro

Oct 27, 2012 | 11 minutes | Active Record, Authorization

PostgreSQL Schemas can help create a multi-tenant app by keeping data separate at the database layer. Integrating with migrations can be tricky though, so watch this episode for the full details.

Click to Play Video ▶

Download:
source code Project Files in Zip (71.6 KB)
mp4 Full Size H.264 Video (28.7 MB)
m4v Smaller H.264 Video (14.5 MB)
webm Full Size VP8 Video (11.1 MB)
ogv Full Size Theora Video (33 MB)

Browse_code Browse Source Code

Resources

terminal

rails db
rails g model tenant subdomain
rake db:migrate
rails c
rails g migration add_sticky_to_topics sticky:boolean
rails g migration add_name_to_tenants name

rails console

t = Tenant.create! subdomain: "cheese"
c = t.connection
c.execute("create schema tenant1")
c.schema_search_path = "tenant1"
load 'db/schema.rb'
Post.all
Tenant.all
c.execute("drop table tenants")
c.schema_search_path = "tenant1, public"
Tenant.all
Post.all
Tenant.create! subdomain: "chunkybacon"

rails db

\dt
\dn
create schema foo;
create table foo.items ();
delete from items
delete from foo.items
show search_path;
set search_path to foo, public;
delete from items
drop schema foo cascade

models/tenant.rb

after_create :create_schema
def create_schema
 connection.execute("create schema tenant#{id}")
 scope_schema do
 load Rails.root.join("db/schema.rb")
 connection.execute("drop table #{self.class.table_name}")
 end
end
def scope_schema(*paths)
 original_search_path = connection.schema_search_path
 connection.schema_search_path = ["tenant#{id}", *paths].join(",")
 yield
ensure
 connection.schema_search_path = original_search_path
end

application_controller.rb

around_filter :scope_current_tenant
private
def current_tenant
 @current_tenant ||= Tenant.find_by_subdomain!(request.subdomain)
end
helper_method :current_tenant
def scope_current_tenant(&block)
 current_tenant.scope_schema("public", &block)
end

topics/index.html.erb

<h1><%= current_tenant.name %> Forum</h1>

lib/multitenant.rake

db_tasks = %w[db:migrate db:migrate:up db:migrate:down db:rollback db:forward]
namespace :multitenant do
 db_tasks.each do |task_name|
 desc "Run #{task_name} for each tenant"
 task task_name => %w[environment db:load_config] do
 Tenant.find_each do |tenant|
 puts "Running #{task_name} for tenant#{tenant.id} (#{tenant.subdomain})"
 tenant.scope_schema { Rake::Task[task_name].execute }
 end
 end
 end
end
db_tasks.each do |task_name|
 Rake::Task[task_name].enhance(["multitenant:#{task_name}"])
end