Multi-tenant Laravel for 40 koperasi: one database with tenant_id, or one database per tenant?
Bayu Setiawan@bayu_laravel
Hello all. I maintain a savings and loan system (koperasi simpan pinjam) used by 40 cooperatives. Same features for all, each has 2,000 to 20,000 members. Laravel 10, PHP 8.2, MySQL 8, one VPS.
Now it is single database, every table has koperasi_id, and I use a global scope:
class KoperasiScope implements Scope
{
public function apply(Builder $builder, Model $model): void
{
$builder->where(
$model->getTable().'.koperasi_id',
auth()->user()->koperasi_id
);
}
}My worry: one day a developer forgets the scope (or uses DB::table()) and koperasi A see the members of koperasi B. For savings data this is very bad. So I am thinking to move to database per tenant (maybe with stancl/tenancy).
But I am not sure about:
- migrations on 40 databases every deploy
- backups, some koperasi ask "give us a copy of only our data"
- the government office (dinas) wants one report across all koperasi every month
- cost, all on one MySQL server
For the case of 40 tenants with same schema, which one you will choose? Thank you 🙏
Accepted answer
Did this for 26 schools (a school ERP, same shape as your problem). My vote: one database, tenant_id, but do it properly.
1. Don't read the tenant from auth() inside the scope. Make a small TenantContext class, bind it as a singleton, set it in a middleware for web requests and inside the job's handle() for queued work (pass koperasi_id into the job constructor). The scope reads from TenantContext and throws if it is empty. Fail loud, never fail open.
2. Put the scope and a creating hook in one trait, BelongsToKoperasi, so a new model gets both by adding one line. The creating hook fills koperasi_id automatically.
3. Every unique index includes koperasi_id. member_number is unique per koperasi, not globally. Composite indexes start with koperasi_id.
4. One feature test per model: create data for two koperasi, act as one, assert you never see the other. Ours caught 3 leaks in the first month.
5. "We want our own copy": mysqldump with --where="koperasi_id=7" per table, zip it, done. A 2 minute script.
Database per tenant is worth it when tenants need custom schema, when the regulator demands physical separation, or when one tenant is 100x bigger than the rest. For 40 koperasi with max 20k members you will spend more time running 40 migrations and 40 backups than you save. And the dinas report becomes 40 queries and a merge in PHP. yaar, not worth it.
Ask the regulator question first though. If they say separate databases, the decision is made for you.
Get the best of #php_still_pays weekly
One email each Monday with the most useful posts. No spam; unsubscribe in one click.
Comments
Sign in to join the conversation.
Sign in to commentOluwatobi Adeyemi@tobi_freelance
Not an answer but: that auth()->user() inside the scope will bite you the first time a queued job or an artisan command touches the model. auth() is null there. Ask me how I know 😅
Bayu Setiawan@bayu_laravel
Ah. We have nightly job for interest calculation (bunga). It use withoutGlobalScopes now, maybe that is why I did not see it yet 😅
Accepted answer
Ayesha Siddiqui@laravel_ayesha
Did this for 26 schools (a school ERP, same shape as your problem). My vote: one database, tenant_id, but do it properly.
1. Don't read the tenant from auth() inside the scope. Make a small TenantContext class, bind it as a singleton, set it in a middleware for web requests and inside the job's handle() for queued work (pass koperasi_id into the job constructor). The scope reads from TenantContext and throws if it is empty. Fail loud, never fail open.
2. Put the scope and a creating hook in one trait, BelongsToKoperasi, so a new model gets both by adding one line. The creating hook fills koperasi_id automatically.
3. Every unique index includes koperasi_id. member_number is unique per koperasi, not globally. Composite indexes start with koperasi_id.
4. One feature test per model: create data for two koperasi, act as one, assert you never see the other. Ours caught 3 leaks in the first month.
5. "We want our own copy": mysqldump with --where="koperasi_id=7" per table, zip it, done. A 2 minute script.
Database per tenant is worth it when tenants need custom schema, when the regulator demands physical separation, or when one tenant is 100x bigger than the rest. For 40 koperasi with max 20k members you will spend more time running 40 migrations and 40 backups than you save. And the dinas report becomes 40 queries and a merge in PHP. yaar, not worth it.
Ask the regulator question first though. If they say separate databases, the decision is made for you.
Bayu Setiawan@bayu_laravel
This is very clear, thank you mbak 🙏 The TenantContext idea fix exactly the problem tobi said. Regulator only need audit trail, not separate DB, I asked them last year. So single DB it is, with the tests.
Caio Nascimento@caio_backend
Schema-per-tenant on Postgres here, 300 schemas, Flyway running the migration 300 times every deploy. Deploy take 25 minutes. Do what Ayesha said kkkk
Nguyễn Minh Hiếu@homelab_hieu
For backup: with single DB you can still do per tenant export like she said, plus full mysqldump --single-transaction every night. and test the restore once a month!! nobody do this
Rodel Bautista@rodel_java
Bank side view: auditors care about access logs and who can query what. Separate databases do not save you if everyone has the root password.
Bram Verhoeven@bram_on_a_bike
Late, but I agree with Ayesha. Single database. tenant_id in every unique index and every query. If you were on Postgres I would add row level security as a second lock. On MySQL the tests are your second lock.