Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 877
Ramon Tayag Avatar asked May 06 '10 16:05

Ramon Tayag


People also ask

How do I create a multi-tenant application?

There are three approaches on how to build a multi-tenant application: Database per tenant — each tenant has its database. Shared database, separate schema — all tenants are using the same database, but each tenant has his schema. Shared database, shared schema — all tenants are using the same schema.

What is multi-tenancy in rails?

Multitenancy means serving multiple independent customers from one app. Pretty typical for SaaS model. You can implement it on several different levels: Row level - you put a tenant_id column into every DB table and filter by tenant_id in every query.

How do I create a multi-tenant database in MongoDB?

Create a Node app initialize one MongoDB connection along with the app and export this connection object. create this connection using mongoose createConnection as the createConnection method does not create default connection but return a DB connection object.


1 Answers

Update Dec 5, 2011

Thanks to Brad Robertson and his team, there's the Apartment gem. It's very useful and does a lot of the heavy lifting.

However, if you'll be tinkering with schemas, I strongly suggest knowing how it actually works. Familiarize yourself with Jerod Santo's walkthrough , so you'll know what the Apartment gem is more or less doing.

Update Aug 20, 2011 11:23 GMT+8

Someone created a blog post and walks though this whole process pretty well.

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)     # [METHOD 1]: conn.execute "SET search_path TO #{schema_name}"     # But the connection itself seems to remember the old search path.     # When Rails executes a schema it first asks if the table it will load in already exists and if :force => true.      # If both true, it will drop the table and then load it.      # The problem is that in the METHOD 1 way of setting things, ActiveRecord::Base.connection.schema_search_path still returns $user,public.     # That means that when Rails tries to load the schema, and asks if the tables exist, it searches for these tables in the public schema.     # See line 655 in Rails 2.3.5 activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb     # That's why I kept running into this error of the table existing when it didn't (in the newly created schema).     # If used this way [METHOD 2], it works. ActiveRecord::Base.connection.schema_search_path returns the string we pass it.     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 
like image 58
Ramon Tayag Avatar answered Oct 15 '22 02:10

Ramon Tayag