Federated Table with a Dynamic Connection

Question

I have B2B like application, facing a challenge is each of my clients needs to store their data in different RDS. I am planning to achieve this using MySQL federated engine, but while creating the local table, we need to specify connection string but is this possible to change those connection string based on the user logged in? I mean will store each users connection data's in the local database, then we accessing those users data need to add that connection string to a federated table, is this possible?. I am developing my application using Laravel and MySQL.

Answer 1

This doesn't solve your question exactly, but unless there's a legal reason to keep things in separate databases I would really try not to. You can use global scopes and tenant_ids to limit what users see.

On your user model you would add a tenant_id column, then on all your models use a global scope:

class TenantScope implements Scope
{
    public function apply(Builder $builder, Model $model)
    {
        $builder->where('tenant_id', \Auth::user()->tenant_id);
    }
}

class Widget extends Model {

    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope(new TenantScope);
    }
}

This way whenever a user accessed a model they would only receive the ones assigned to them. There are a lot of headaches maintaining separate databases (migrations, no ability to query globally, etc) so if you can avoid it you should.

Here is another question about changing connections on the fly which might help if you must: Laravel Change Connection Dynamically