Multi-tenancy with Postgres schemas and Ecto

On a recent project, I learned and used a feature of Postgres that was new to me. Exposure to new tech is one of the best parts of this job! I want to share a little bit about Postgres schemas, how to use them with Elixir’s Ecto library, and some challenges we faced while integrating them into an existing application.

What is a “schema”? #

A schema is a kind of Postgres object that sits in between the database and the tables. The official docs analogize them to directories on your filesystem. By default, all of your tables live inside the public schema, but you can create other schemas. When you write a query like SELECT * FROM users; Postgres will actually execute SELECT * FROM db_name.public.users;. Neat! Schemas can be used as a way to set up different permissions, to separate different customers, or to logically organize your tables. For this project we used Elixir, Phoenix and Ecto, but these have nothing to do with Ecto’s schemas (which did lead to some confusion).

What was our problem? #

Our client, lets call them Acme Corp for the rest of this post, had an existing web application that we helped build a little while ago. It was working so well for them that they wanted to license their platform to other companies that provide the same service they do. The goal was to fully whitelabel the application, so that when Other Corp used Acme’s platform, their users would only see Other Corp branding and data. This problem calls for a multi-tenant architecture. After we finished, Acme Corp and all their existing data would be just one tenant amongst others.

Row Level versus Postgres Schemas #

One solution for this would be to add a tenant_id column to every table in the database. All queries would then include this id. An alternative solution is to store each tenant’s data in a separate Postgres schema, and query against that. Based on the title of this post I bet you can figure out which one we chose!

This wasn’t an easy decision. The appeal of using schemas is the very clear lines of separation between customer data. By default every query will already be scoped to a tenant and getting data across tenant schemas is an exceptional case that requires extra work. This should reduce accidental data leakage between tenants, which is a huge concern. On the other hand, some devs have had difficulty scaling with schemas. In our case we aren’t expecting a huge number of tenants, so the data isolation seemed worth any scaling difficulties.

How it works in Ecto #

I came across the schema feature in a podcast interview with Lars Wikman who had just written a fantastic series of blog posts covering a few different approaches to multi-tenancy with Ecto. His third post was the basis for our implementation. We also referred to this blog post by Iván González and the sample code he shared. Ecto has a feature called "query prefixes" that lets you specify which schema a given query should be run against. There are a few different syntaxes for this, but the one we used most commonly looked like:

This tells Ecto to execute a query like SELECT * FROM acme_development.my_prefix.users.

Wrapped Repo #

So with this in place we now needed a way to tell every database call in our application which prefix to target. Whew! Every function needed to have prefix appended as an option (i.e get_users(prefix: prefix)).

Our first prototype nested every route in the app under a tenant identifier. We pulled it out of the URL and passed it down from the controller, through the relevant context and to the actual DB call site. This basic pattern seemed okay, but we came up with a slightly different approach that we liked better. Instead of the prefix string, we dynamically generated a repo module and passed that down. Now, instead of a global Acme.Repo, every function that called out to the database had the repo appended as an option (i.e get_users(repo: repo)) and made calls against that module. We still pulled out the proper prefix from the URL in a plug but now we used it to call Acme.Repo.with_prefix(prefix), which returned the generated repo module. This module simply redefined every function in the Ecto.Repo behavior, but passed through the specific prefix. We assigned this to current_repo giving access to every controller action. Here’s a gist that is pretty close to our final implementation.

This felt better. It isolated the multi-tenant aware code. A new developer to the project just needs to know that we don’t use the global Acme.Repo module, they don’t need to know the specific details about how prefixes or schemas work. It also is a bit of dependency injection, which was helpful for some of our test code. Finally, only the plug and the code to generate the module needed to know about our prefixing scheme. The rest of the application could ignore those details.

There are downsides to this approach. Most importantly, it's weird. Using the global AppName.Repo module is a more typical pattern for Phoenix apps. Meta programming is also tricky to understand. It works now, but future developers may have difficulty maintaining it. Second, if the Ecto.Repo behavior interface changes, we now have to update our generated module. Finally, generating the module introduces some overhead to every request in the app. This is much less than alternatives like establishing a new DB connection on each request. However, in testing we didn’t see a significant slow down, and there are lower hanging optimizations to do first.

“Migrations” are part of your app code now #

We wanted users to be able to create new tenants within the app. This meant that we needed code to dynamically create a new schema and populate it with our applications tables. Creating a schema is easy enough:

But filling it with the proper tables was a bit tricker. It required a wholesale rethinking and rewriting of our migrations. We split out a “tenant_migrations” directory which contained one canonical “migration” for each table in the application. These would be run just once, at tenant creation time. To make sure they matched our current database, we compared diffs from running mix ecto.dump. It took a few iterations to make sure all our various foreign keys, constraints and types lined up properly.

Finally, when we do want to make a migration of existing data, we need to wrap the code in a loop that runs over each provider. Then we need to go update the associated “tenant_migration” file for that table so that future providers get the change.

Conclusion #

I hope this article helped you understand how to use postgres schemas with Ecto. I really enjoyed digging deeper into this feature of postgres and appreciated the ease with which Ecto allowed us to use it. It made implementing multi-tenancy a much faster task than I was expecting it to be!