Table columns not discovered in multi-tenant databases

[vpratfr]

Versions:

• ide-helper Version: 2.9.0
• Laravel Version: 8.x
• PHP Version: 8.0

Description:

We have a multi-tenant application where models are split in 2 DB connections : system (app DB) and tenant (each customer has one)

Models on the tenant database do not have their attributes discovered from the database.

Package used to manage tenancy is hyn-multitenant which basically dynamically builds the Laravel DB connection information depending on the active tenant (which could be determined by the subdomain for instance or programmatically)

Issue

The code linked below does not get the proper database which should match a tenant database

laravel-ide-helper/src/Console/ModelsCommand.php

Lines 418 to 436 in f0959c1

	protected function getPropertiesFromTable($model)
	{
	$table = $model->getConnection()->getTablePrefix() . $model->getTable();
	$schema = $model->getConnection()->getDoctrineSchemaManager();
	$databasePlatform = $schema->getDatabasePlatform();
	$databasePlatform->registerDoctrineTypeMapping('enum', 'string');
	$platformName = $databasePlatform->getName();
	$customTypes = $this->laravel['config']->get("ide-helper.custom_db_types.{$platformName}", []);
	foreach ($customTypes as $yourTypeName => $doctrineTypeName) {
	$databasePlatform->registerDoctrineTypeMapping($yourTypeName, $doctrineTypeName);
	}
	$database = null;
	if (strpos($table, '.')) {
	[$database, $table] = explode('.', $table);
	}
	$columns = $schema->listTableColumns($table, $database);

$database stays null

Request

We should have a mecanism to allow discovery of those attributes.

One possible way to allow that could be to have a configuration entry where we could override the database according to the connection name.

Something like

   'db_mapping'=> [
      // connection => database name
      'system' => 'homestead',
      'tenant' => 'my_tenant',
   ],

Then code to get the database could be fixed liked that:

       $connectionName = $model->getConnection()->getName();
       $database = config("ide-helper.db_mapping.$connectionName");
        if (strpos($table, '.')) {
            [$database, $table] = explode('.', $table);
        }

I have tested that on our project. There should be no difference in previous behaviour if the configuration is not specified.

Any thoughts?

[mfn]

If the tenant is dynamically built, how do you decide which one to use for ide-helper (and: I assume we're still talking about a development system and not running this in production…)?

[vpratfr]

Yes, this is run on a local VM (Homestead).

We have at least 2 DB active on the VM: the system DB and a dummy client DB (we actually have several client DB, but they are all the same in schema, so we can use any of them).

the tenant is dynamically built

the whole tenant is not dynamically built, it is just the connection details which are dynamically set according to the tenant properties.

e.g. : when accessing the subdomain acme.example.com, we would setup the tenant connection to access the tenant_acme database with user X and password Y.

This really does not matter in ide-helper context : all those tenant DB have the same schema, so we do not need any dynamic logic, we simply need to point IDE Helper to one valid tenant DB it can explore for those models which use the tenant connection. And still point it to the system database for those models which use the system connection.

[alejandrotrevi]

Just run the ide-helper:models command for a specific tenant:

https://tenancy.dev/docs/hyn/5.6/commands

I'm using spatie/multitenancy and i had to run the command like this since the ide-helper:models command needs a working connection:

php artisan tenants:artisan "ide-helper:models" --tenant=1

[dennisameling]

This worked for us using Tenancy for Laravel:

php artisan tenants:run ide-helper:models 🚀

[bretto36]

@alejandrotrevi 's suggestion works well for the new tenancy package by the same guys - tenancy/tenancy#238

[vpratfr]

I decided to give it a fresh new look and see why it is working for others and not for us.

We had hyn/multitenant and recently switched to spatie/multitenant. In both cases we see the same issue, this is not a matter of library used to handle tenancy.

This works well for others because probably they do not have a combination of landlord and tenant models. Or they have not noticed that their landlord models where not generated properly.

As I said above

We have at least 2 DB active at the same time on the VM: the system/landlord DB and a tenant DB

That means that IDE Helper needs to inspect the DB Schema in two different connections when run. And it does not, as it is only capable of handling a single connection at a time.

[vpratfr]

I have opened a PR to allow handling such cases (which are more and more common, judging by the increasing number of packages to handle multi-tenancy)

[sbalex27]

Solution for Tenancy for Laravel:

1. Create a custom artisan command: php artisan make:command IdeHelperTenantModels
2. Create a signature for the ide helper:

    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'ide-helper:tenant-models';

3. Write in the handle method:

Important: You will need to write attributes to create a temporary tenant or use a factory

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        tenancy()->initialize($tenant = Tenant::create(
            // your tenant model attributes
        ));
        $this->call('ide-helper:models');
        $tenant->delete();
        return Command::SUCCESS;
    }

4. (Bonus) Write in the description attribute the details:

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Generate autocompletion for tenant models';

5 Done! ✅, now for generate the tenant models for phpdocs just run php artisan ide-helper:tenant-models

This command will create a new tenant database, then calls the command while connected to it, generates the documents with the extracted tenant templates attributes, and then deletes the temporary database.

[sbalex27]

@dennisameling it works too, the problem is if i have 8 tenants the command will be executed 8 times

[vpratfr]

@dennisameling sorry, but that does not work either, please read the original issue again: that will properly generate helpers for tenant side models. Landlord side models will not be generated properly.