vincent15000's avatar

Multitenancy with only one database

Hello,

I'm not sure what to do for a multitenancy application with one database.

I know it exists some packages to do that, but I can also add my owner scope to do the same.

Say the datas belong to a compay. Is it really useful to add the company_id field on all tables ?

Here is an example.

I have a users and a rules tables and a user_rule pivot table. Is it useful to add the company_id field on the pivot table ?

I ask this because with some database queries, I get some errors related to conditions on company_id (the query builder doesn't know if it's users.company_id or rules.company_id or user_rule.company_id).

What would be the advantage to use a package instead of adding my own scope on the needed tables ?

Thanks for your help.

V

0 likes
11 replies
tykus's avatar

You can alias any column with in the query using table_name.column_name so that issue should be easy to overcome.

I suppose the choice of roll-your-own or package is the amount of work you want to take on. If you choose to use a package; especially if you can find one that has decent usage numbers, is well tested, and is still in active development, you can save yourself all of the work necessary to put single-database tenancy together. You can concentrate on the functionality that makes your application unique rather than re-inventing something that already exists.

1 like
Ben Taylor's avatar

You don't necessarily need a company_id field on every table. But just remember for data security sake, you may find it beneficial. You don't want to serve up one tenant's data to another by mistake.

Users can potentially belong to many tenants and could presumably have different roles for each tenant. So yes it would be useful to add the company_id to the user rule table.

And as @tykus has already said, you can solve the ambiguous column issue easily enough

1 like
Snapey's avatar
Snapey
1 year ago
Best Answer
Level 122

If you add company_id to each table, you can add a global scope to each model to apply a filter to the table on every query. The majority of your code does not then need to know that the data is being filtered.

A model observer can set the company_id on every database write, so again you don't need to worry about it in the code.

You can set the company ID in session, when the user logs in, and then check it in your global scope.

Things that are not session based need additional handling, eg jobs that write to the database won't have access to the company ID so this will probably have to be passed into the job when it is dispatched.

Its possible to do all this yourself, but packages exist to make this simple.

eg https://tenancyforlaravel.com/ supports both single and multi-database designs.

1 like
vincent15000's avatar

@Snapey Thank you for your answer.

I have already an observer to add the company_id to each model.

And effectively I will have to run cron jobs that are not session based, for which there won't be any authenticated user, so effectively I need to pass the company_id as a parameter.

I have also other situations like the creation of a product.

A product can be created via a synchronization button with an external service (by the authenticated user) or automatically via a cron task (no authenticated user). In this case I can't use an observer which will fail (auth()->user()->company_id) with the cron task.

Does tenancy for laravel manage this kind of situation ?

Snapey's avatar

@vincent15000 fir the cron, I doubt it. It can't possibly know unless you tell it? Surely you know which users need the cron running for?

1 like
vincent15000's avatar

@Snapey The cron will run for all companies, the command will have a loop on companies.

But each command can take some time to be executed, it depends on the amount of datas to handle.

vincent15000's avatar

@tykus I have already asked for precisions about Context and the real difference with sessions. Even if I start understanding something, it's not so clear for me.

Sessions are shared between requests too. I mean, if I store a value in the session, I can send several requests and retrieve this value any time in each request.

For cron commands executed during the night, is it justified to use queues ? In my mind, queues are useful when a user clicks on a button and if the task is long, the queue avoid him to wait before doing something else.

tykus's avatar

@vincent15000 the main differences are

  1. speed. The Context is stored in memory, not in a file or database
  2. flexibility. You can store a wider variety of types in Context than Cache/Session
  3. scope. The Context is automatically available to the shared Jobs dispatched by the Request whereas the Session is not
  4. logging. The Context is automatically included* with any logs written during the Request

* unless explicitly hidden

1 like
Snapey's avatar

@vincent15000 in cases where the cron task can take a long time or an unknown amount of time, it is better use the cron to dispatch a job, in this case per company, passing the job the company id.

make sure the jobs are forced unique to avoid queuing a new job when the last has not finished

1 like

Please or to participate in this conversation.