I’m facing my second Rails project that has to manage multiple tenants. In both projects each customer gets its own subdomain and it’s own data . There’s three common approaches to solve this:

• Share nothing (each tenant gets its own database)
• Share everything (a single database holds the data of all the tenants; table must include a tenant_id column)
• PostgreSQL schemas or namespaces (single database but a new schema is created per tenant with all the tables)

I strongly recommend to check out this video “Writing Multi-Tenant Applications in Rails”. It explains all these three options in detail.

For my first project I was a little paranoid about depending on just one column to select the correct data for each customer. If just one query misses to filter by that field you’ll be exposing private data to other customers. And what about backups? First option ruled out.

Even though the third option sounded appealing, I was just starting with Rails, Linux, etc. So I choose the “share nothing” approach using MySQL. It’s not trivial to make it work, and you have to add code to switch between databases per request. Migrations requires you to write some custom tasks too (future post?). That said, it’s working just fine. Backups are easy too as they are just different DBs.

For my new project I’m going to try PostgreSQL and the multiple schemas solution. One of the showstoppers for me to use this solution was that I didn’t know how to handle the hypothetical case that a customer needs to restore its data from a backup. In my mind there was no solution, but to restore the full database. I was wrong.

It’s as simple as dumping the database “per schema” using the PostgreSQL tool pg_dump which allows you to specify the schema.

Time to play with PostgreSQL and Rails 3. I’m using this post as starting point.