This article starts a series of posts regarding a case-study about Symfony and multi-tenancy.
Subject of the case-study is our flagship software Nuvola, a multi-tenancy product for schools, used since 2013 by more than 1 thousands Italian schools. Nuvola let them fulfill their administrative tasks, teachers completely accomplish their work online (grade students, take notes, share educational contents and much more), parents and students interact with teachers and with the school system as a whole.
It all started in the late 2012 as a side-project to let some schools migrate a few daily administrative tasks online. Nobody could have imagined such exponential growth and so we started with a single Symfony app and a single MySQL database underneath.
Nuvola is a SaaS Symfony app running on PHP 7 and the latest version of Symfony. Here is the symfony.com showcase.
We embrace the Symfony framework as a whole and many well-known community bundles. We like a lot the BDD approach and so we use phpspec and Behat to deliver high-quality software. Doctrine is the ORM we chose to abstract the database away.
In only about a year we got 500 schools using our software and their data was in one database of many GB of data.
As (almost) every app we started with a single db for every relational data.
Single (big) MySQL db problems
- Disaster recovery. If your db is not so small recovering it could take too much time. You have to set up a system like a master-slave MySQL to reduce risks and be sure to have always a working replica synchronized with the master one.
- Slow migration. Apply a migration (eg.: update or alter table) to a table of million rows is slow. During that users can experience troubles.
- Bug fixing. Sometimes, as developers, we need to replicate some bugs or scenarios using the real production data. Downloading and restoring a big db on your local environment takes hours and sometimes is impossible.
- Data security. In a single db every tenant uses the same database. This could led to security issues: an attacker who exploit a single tenant can access data of other tenants.
How to avoid them?
First of all we need to put on the table some constraints we have in our project:
- Relational data. Many NoSQL databases offer sharding and scaling solutions but our model data is best suited to a relational data model. So switching to NoSQL is not an option.
- Low cost. We did want to find an affordable solution, without locking ourselves to an enterprise expensive ready-made solution provided by well-known db vendors.
- Elastic. We were in the middle of an exponential growth and so the chosen solution had to be very elastic to continue sustaining that growth.
Given these constraints we asked ourselves: what kind of app Nuvola is?
The quite obvious answer was: Nuvola is a multi-tenant app.
“Every app which has a shared software instance among multiple organizations using it is multi-tenant”.
So, why not splitting the monolith db by tenants data? Our tenant is the single school. Each school, in turn, has a bunch of users: administrative people, teachers, students, parents.
In the part 2 we’re going to explore pro and cons of a multi-tenancy solution applied to our software.