Three Database Architectures for a Multi-Tenant Rails-Based SaaS App

Multi-tenant Software as a Service (SaaS) applications are extremely popular products in 2019. Why is that?

Let’s imagine you want to create a web app and provide it to several organizations as a white label product. How might you implement such a project? You could simply copy the entire codebase for each organization. But this means you’ll have to support multiple application instances separately. That’s not the best solution, as your development team will have to manage server infrastructure and update features independently for each app instance.

Let’s now imagine that you provide your app to a dozen tenants and deploy a new app version for each of them. To update all applications, your web team will have to do the same thing a dozen times! There is a much better solution – you can develop a multi-tenant Software as a Service application.

Multi-tenancy means that multiple organizations – otherwise called tenants or groups of users – can employ the very same application. With a multi-tenant SaaS app, your web development team will need to deploy and support only one codebase – not multiple applications. You’ll be able to update your SaaS app simultaneously for all tenants, and it’ll be easier to support the server infrastructure. Overall, multi-tenancy greatly simplifies development of a Software as a Service app.

But before you build a multi-tenant SaaS application, you’ll need to address one major problem – how to securely isolate each client’s data. Thus, the database layer will require special attention. We’ll introduce three approaches to designing the database layer.

We’ll be talking about how to build a multi-tenant SaaS app with Rails, and to design the database layer for a multi-tenant Rails-based SaaS application, you’ll need the Apartment Ruby library. The other Ruby libraries we should mention are Detectify and Houser. Your development team can use those libraries to build database requests. You’ll also need a relational database, such as PostgreSQL or MySQL, to save tenant data in the Rails app. These Ruby libraries and databases that we’ve just mentioned will help you complete specific tasks, and we’ll review them in detail.

Multi-Tenant SaaS Application and Database Design

As we’ve suggested, the major architectural concern with multi-tenant SaaS apps is the database layer, which is also called the persistence layer. There are three aspects of database design that we’ll address:

• The level of tenant data isolation;
• Difficulties with restoring data;
• Difficulties with data encryption.

A multi-tenant application architecture can adopt one of three database architectures. The first option is to use a separate database for each tenant. The second option is to use the same database for all tenants, but to give each tenant their own schema with individual tables. With either of these two approaches, we recommend using Apartment, the Ruby gem we mentioned previously.

But there’s also the third option: tenants share a schema within the same database. Note that Apartment won’t work in this case. Your development team will need to implement this design manually.

We’ll provide more details about the workings of the Ruby libraries we’ve mentioned, but first let’s dive into how to organize the database layer.

Single Database for Single Tenant

The main advantage of “one database–one tenant” design is that it ensures the highest level of data safety. Each database instance is located on a separate server, so instances are physically separated. This means that one tenant simply can’t access another tenant’s data.

Another advantage of this approach is that it’s flexible. Imagine a situation when one tenant wants to encrypt data but others don’t. Do you have to encrypt the data of all tenants? Actually, you don’t! Since tenants have fully isolated databases, we are free to encrypt the data of any tenant (or not).

The complete isolation of databases also helps us to easily restore data. Whenever a tenant’s data gets corrupted, we can retrieve it from a backup database without implementing complex logic.

Given these advantages, using individual databases for tenants seems like a slick solution. But there’s always a catch. As we mentioned before, multi-tenant Software as a Service applications are designed to reduce the cost of server infrastructure. However, when you provide a dedicated database for each tenant, you have to use a separate server instance to store each database. Hence, you’ll have to pay for additional servers, driving up the cost of using the application. Besides, the ‘separate database’ approach complicates the server infrastructure, making it more difficult to support.

Tenants may not want to save their data alongside that of other tenants. If tenants need the highest data safety and flexibility, and are ready to pay for it, then the “single database” design is a perfect choice. You can implement this design for a full white-label web application (it's understood that a full white-label app has a unique domain name; a partial white-label app would have only a unique subdomain name).

But there is a database design that is cheaper and still provides partial isolation: the “separate schemas” design.

Separate Schema for Each Tenant

To lower the operating costs of the database layer, you can opt for a ‘separate schema’ design.

With this design, the app connects to a single database instance. Each tenant has their own schema (a set of tables) within the database, but not an entire database. Using separate schemas lets you reduce the complexity of server infrastructure, and thereby the cost. The separate schema design provides another benefit as well: it’s very flexible. Although initially each new schema has standard tables, tenants are able to customize their schema however they want.

There are some disadvantages of the separate schema design, however; for one, we have to pay attention to how we back up and restore data. Let’s say that one tenant’s data gets corrupted, while the data of all other tenants is intact. To avoid overwriting the entire database, developers will have to implement special techniques.

One approach would be to copy tenant data from a backup server to a separate server. After that, we could retrieve the data from the necessary schema. Finally, we could use this data to replace the corrupted information in the main database. As you can imagine, this process is time consuming.

Separate schemas provide only partial data isolation, but are a cheaper alternative to fully isolated databases. The separate schema design is equally good for both partial and full white-label SaaS applications.

So far, we’ve reviewed two approaches to designing the database layer that provide a high enough level of data isolation. There’s one more option left – a shared schema for all tenants.

Shared Schema for Tenants

Using a shared schema for all tenants is an easy-to-implement approach, at least at the initial stages of development. Let’s sort through how a shared schema works. In the simplest sense, the data of all tenants will be stored in the same tables. To find and retrieve data from each row, which is assigned to a specific tenant, your SaaS application will use a tenant ID.

The shared schema design provides a few benefits. There’s no need to create and adjust schemas for tenants, unlike with the separate schema approach. There’s also no need to run additional servers for databases.

But what about the disadvantages of using a shared schema? Hopefully, your app will gradually add more and more tenants over time. Assuming it does, then eventually it will become increasingly difficult to query, index, and update data. Besides, it’s also difficult to retrieve data for a given tenant. The multi-tenant SaaS app first has to verify the tenant access level; then the app must also verify the user access level, and only after that can we build a request to the database. With this design, each request will be fairly complex.

Given the advantages and disadvantages of each approach, we recommend using separate schemas, as they provide the best balance between difficulty of development and cost of running the database layer. But you should take into consideration the level of data safety your tenants will require. All three approaches guarantee enough security level for certain applications, and your Service Level Agreements must stipulate the level of security you provide: either full or partial isolation.

So that’s how to implement the database layer in a Software as a Service application. Now we need to talk about what tools you can use to develop this layer for a Ruby on Rails-based SaaS app.

Implementing Database Design in a Rails-based SaaS App

Apartment is a top Ruby library that separates tenant data. Basically, you can create tenants using Apartment, and the library will automatically assign a new isolated schema to each tenant. This is the second approach that we discussed. But Apartment can create isolated database instances as well.

As we can see, Apartment allows us to build Rails-based SaaS apps with full or partial white-labeling right away. And to get the most from Apartment, you should use a PostgreSQL database.

How does Apartment work? Apartment fetches data from the schema that corresponds to the elevator – domain, subdomain, or full host name. The library simply analyzes each request to the database using elevators. By default, Apartment splits tenants by subdomains, but you can quickly set it up to use domains.

Keep in mind that for the third approach – a shared schema – Apartment won’t work.

To implement a shared schema approach, your development team only needs to assign IDs to tenants and send database requests with those IDs. And if you’re going to provide custom domains or subdomains for your clients, you’ll need either Detectify or Houser.

Houser is a multi-tenancy gem that can send database requests using subdomain names. But Houser is a relatively old Ruby library. For modern Rails-based SaaS apps, we wanted more flexibility. That's why we've developed Detectify to replace Houser.

Detectify can create database requests using both domains and subdomains. It also lets the app ignore routes when sending a URL-based request to the database. Overall, Detectify is a more advanced multi-tenancy gem than Houser. For more information, you can read our Detectify review.

Developing a multi-tenant Software as a Service application with Rails goes well beyond designing the database layer. But if you choose one of these three database architectures at the start, it will be easier to develop, support, and scale your web application in the long run.