Laravel Database Per User - On The Fly Database Connections - Multi Tenant Laravel App - User Own DB


Let's Read How to setup on the fly database connection in Laravel Per User


Laravel Own Database connection Per User is the legend of Laravel SaaS Application, You must provide your users instant setup while at the same time separating their data away from other users.

Databases per user vs Single Shared Database

The key decision you have to make when you are developing a Laravel SaaS application, is whether you need to share single database  between your users or keep it separated across multiple databases.

If you want to share single database for all your users, you might want to add a user_id column, to each and every table to segment who owns which data. so with the help of one of important Laravel feature, Query Scope you can globally filter out the data per user login. Query Scope segment the data by automatically adding the WHERE clauses to your Eloquent queries so you don't accidentally mix up users data.

However, if you do not need to share single database between your users (which is what I think the majority of SaaS application like to avoid) there is no reason to keep everything in one single database. In fact it's in your code's best interest not to do things that way.

I know this because I developed an ERP Software which is originally started with the shared database approach and I realized this wouldn't scale in the long run. The amount of data coming in & the complexity to shared that data later on too great to stay on a single shared database, when a customer want a dedicated server or hardware etc,.

So about 2 years ago while I was upgrading to a new version of my codebase I took the steps to separate every customer into their own database with a single shared database as a master database which to act as the "router" of sorts.

When a request comes in, I check the domain name against the master shared database to lookup the customer. If found, I then bootstrap an new database connection (named "customer_db" to the correct customer database named "customer_1234") through a Laravel service provider.


Following are list of pros and cons having a separate database per user:

Pros:

  • Simplified architecture -- Most of my code doesn't need to worry about the user database, as it is set in a service provider at the start of the request.
  • Enhanced Data security -- Similar to above, if there is only 1 place that has to worry about choosing the right user database, you can ensure your client's information isn't being exposed to other clients, also assume if there is security breach in one of users database, it will not affect other users.
  • Don't worry about Third party Laravel Modules -- Most of third party modules I may want to use in my application do not take the "Shared Database" approach into consideration. To use these Laravel modules I would have to rewrite their functionality to include the user_id parameter I mentioned above. By using this separate database architecture I can use modules without modification.
  • Information Portability -- Later on, If my customer decides they want to terminate my service and switch to another company, I can export the data easily without any hassle which can be imported in another server.
  • Enterprise Clients -- If my customers database gets too big and wants their own dedicated hardware its extremely simple to export their data and import in a dedicated server.
  • User Clusters -- Much like the enterprise users, I could also separate half of my users to one data center and the other half of users to another data center depending on where they were located. This would allow me to run multiple "clusters" of my application so if one went down my other users would still be available. (Obviously I would want to have other fail-over measures here as well).
  • Beta Versions -- If I want to deploy beta features to a segment of users its not difficult to only migrate some of my users data and keep the others running on the more stable branches.

Cons:

  • Complicated Database migrations -- When doing Laravel database migrations I must migrate all users database one by one which may take longer.
  • Must pass user_id to background jobs -- If I am using a background job queue like beanstalkd I am going to need to pass my user_id in each job to make sure the worker inits the user database properly before processing the job.
  • Must separate other services -- If I use third party services I must separate my users' data on those as well. For instance if I use Mailgun for outbound emails I would want to make sure that I include a namespace in my identifier to correctly route responses & stats back to the correct customer.

As you can see, there are many pretty good reasons to use the separate database architecture over the shared database architecture but only you will be able to make the decisions for your own app. Event the most Cons listed above, which are just making sure you're always using the correct users when using third-party services.

if you have any questions regarding "Laravel database Per User/ClientsLaravel on the fly Database connections , Laravel User Own Database, Multi Tenant Laravel Application", please feel free to leave your comment bellow.

Written by Akram Wahid 6 years ago

are you looking for a chief cook who can well craft laravel and vuejs, to make some awsome butterscotch,
yes then it is right time for you to look at my profile.

Do you want to write Response or Comment?

You must be a member of techalyst to proceed!

Continue with your Email ? Sign up / log in

Responses

Be the first one to write a response :(

{{ item.member.name }} - {{ item.created_at_human_readable }}

{{ reply.member.name }} - {{ reply.created_at_human_readable }}