Multiple DBs: archtechx / tenancy

Link to the repository

[Only for premium members]

Before switching to the setup of multiple databases, let's talk about WHY you would want to do that.

I got this question from a few people on Twitter:

A multi-database setup means one database per customer/company/organization. Yes, it means managing 100s of databases if you have 100s of companies using your application.

Sounds overwhelming, doesn't it? But not necessarily.

The most common use case for this is the BUSINESS needs by those companies to store data separately from other companies using your application. Usually for privacy and legal reasons.

For example, if you are Microsoft, you would probably want to minimize the risks that some of your internal data would become visible to Apple or Google?

Also, there may be technical architectural reasons to separate the database, such as potential individual scaling or managing resources for each tenant based on their own requirements.

However, it also comes with additional complexity and more work to set up and manage multiple databases.

Let me try to summarize the pros and cons of this multi-database approach.

Advantages of multiple databases:

1. More security and privacy, sometimes required legally. More extensive protection from data leaks.
2. Ability to export data and make backups separately.
3. Potential performance gains: queries work faster because the database wouldn't need to touch other databases.
4. Ability to separate virtual/physical servers and scale individual tenant resources when needed.
5. If something wrong happens with data, it's easier to roll back or recover the data for one database without touching others.

Disadvantages:

1. Complexity to set up and maintain, especially the DB migrations. Connection names are used everywhere, still with a risk of accidentally connecting to the wrong database.
2. Harder to add new features and deploy changes because you need to execute migrations to multiple databases.
3. Harder/impossible to query data from multiple tenants if grouped reports are needed.

In short, you need a multi-database setup only if it's actually the requirement either from the clients or from yourself as an architectural decision for scalability.

In most cases, for smaller projects, one database is totally fine.

That said, if you still want to dive into that more complex setup, let's get familiar with the approach and the tools.

Installation and Configuration

Let's first create the multi-database and multi-tenancy setup with the package stancl/tenancy. Some of the code from earlier for this package from a single database, like installation, will be repeated, and then we'll move deeper.

The initial setup is the same Laravel Breeze with two CRUDs: Projects and Tasks.

So, let's install the package.

composer require stancl/tenancy
php artisan tenancy:install

bootstrap/providers.php:

return [
    App\Providers\AppServiceProvider::class,
    App\Providers\TenancyServiceProvider::class, 
];

IMPORTANT NOTICE: Version 3 doesn't support the database session driver. The easiest change would be to file as a session driver.

And now, here is the first difference between single and multiple databases. The DB_CONNECTION value should be...