Migration on Multi-Tenant Multi-Database App

Hey guys, this might be a more "complex" question.

What I have: A Single-Tenant Laravel Application that I host for customers, basically a SaaS. Every customer has their own subdomain (or bring their own FQDN). Once they choose their subdomain the site gets provisioned on Forge with a new VHost and new database and so on... Now my goal is to convert this to a Multi-Tenant app that just runs on a subdomain wildcard: *.myservice.com. A middleware then grabs the subdomain and assigns the correct database. I have solved most of the problems there, including protecting sessions so users can't just visit another subdomain and be logged in as another user.

There is ONE thing I couldn't figure out in 6 hours of coding yesterday: How to deal with migrations.

Currently I deploy updates like that: Change version in deploy script and migrate. Now how can I modify the migrations command or make my own that migrates ALL tenant databases for an update?

bobbybouwmann

8 years ago

Best Answer

Level 88

Well you probably need to create your own migration script for this. You can for example loop over all the tenants and call the migration command on that.

So I image you have some kind of way to determine what kind of database config needs to be use to actually fetch the data. I would probably put that in a middleware and assign it to the config. From there it's pretty easy to do it I guess.

use Illuminate\Support\Facades\Artisan;

foreach ($tenants as $tenant) {
    // Set the config for each $tenant 
    $config = config('database.connections.tenant');

        $config['database'] =$tenant->database_name;
        $config['username'] = $tenant->database_username;
        $config['password'] = $tenant->database_password;

        config()->set('database.connections.' . $tenant->identifier, $config);
        config()->set('database.default', $tenant->identifier);

    // Now that we have the correct database connection 
    // we can simply call the artisan command for each tenant
    Artisan::call('migrate', ['--force' => true', '--database' => $tenant->identifier]);
}

You can put this code in some command that you run after deployment for example. You only need some way to get all the tenants and you're done ;)

Let me know if this helps for you!

1 like

cat24max

8 years ago

Level 1

Thanks :)

I tried running the migrate command from inside another command already, that didn't work though. I think I messed something up with all the third party libraries I tried yesterday.

Now after I reversed my code back to the original state everything works again.

public function handle()
    {
        if($this->argument('id') == "all")
        {
            echo "Starting Migration for all tenants".PHP_EOL;
            $tenants = Instance::all();

            foreach ($tenants as $tenant)
            {
                $tenant->migrate();

            }
            echo "Migration finished for all tenants".PHP_EOL;
        } else {
            Instance::findOrFail($this->argument('id'))->migrate();
        }
    }

And the Tenant model migrate() function

public function migrate()
    {
        $config = config('database.connections.mysql');

        $config['database'] = $this->db;
        $config['username'] = env('DB_USERNAME');
        $config['password'] = env('DB_PASSWORD');

        config()->set('database.connections.' . $this->id, $config);
        config()->set('database.default', $this->id);

        echo "Migrating Database for ".$this->fqdn.PHP_EOL;
        Artisan::call('migrate', ['--force' => true, '--database' => $this->id]);
    }

cat24max

8 years ago

Level 1

After fixing the migration problem, the session is coming back to me... This is my middleware:

public function handle($request, Closure $next)
    {
        $instance = Instance::where('fqdn', $request->getHost())->first();
        if (is_null($instance)) die('Hostname not found');
        config(['database.connections.mysql.database' => $instance->db]);
        DB::reconnect();
        return $next($request);
    }

Session driver is DB and every tenant DB has their own sessions table. But the XSRF token doesn't seem to work multi-page and will always show as expired. How can I fix that?

Please or to participate in this conversation.