Add schema support for SQL Server driver

[lchevalot]

Greetings everyone,

I've been working with laravel for 3 months now (i love it) and eloquent has made my life so much easier with many SQL Server queries. But i've noticed that you can't do dynamic crossdatabase requests because SQL Server uses schemas and eloquent does not support it natively, here's an example of an insert into select between 2 databases :

CurrentItem::insertUsing(
    'id',
    Item::select('id')
)

the thrown error will be : Object name 'DATABASE2.Item' invalid. (Connexion: sqlsrv_database, SQL: insert into [current_item] ([id]) select [Id] from [DATABASE2].[Item]) because SQL Server uses schemas (in most cases 'dbo' by default) and needs to specify the schema before the table, the query would work if instead of DATABASE2.Item the target table from the other database was DATABASE2.schema.Item.

You can actually set the schema for a database connexion in database.php but it doesn't do anything for sql server driver based connexions.

The only workarounds i found are to either set a from with static database name and schema : ->from("DATABASE2.dbo.Item")
or set the table attribute for the model with the database and the schema : $table = "DATABASE2.dbo.Item"

This is my first contribution here, i hope this is a relevant topic

[shaedrich]

Warning

Eloquent does not currently support querying for relationship existence across databases. The relationships must exist within the same database.

Model::qualifyColumn() would have to be extended:

framework/src/Illuminate/Database/Eloquent/Model.php

Lines 561 to 574 in 9448e01

	/**
	* Qualify the given column name by the model's table.
	*
	* @param string $column
	* @return string
	*/
	public function qualifyColumn($column)
	{
	if (str_contains($column, '.')) {
	return $column;
	}
	return $this->getTable().'.'.$column;
	}

And there would need to be some kind of flag to determine of cross database joins are possible/wanted.

    const FQN_SEGMENT_DELIMITER = '.';

    protected function allowCrossDatabaseJoins()
    {
        return $this->getConnection()->allowsCrossDatabaseJoins() && config('database.use_fqn_for_qualifying');
    }

    protected function countFqnSegments()
    {
        return substr_count(config('database.fqn_pattern'), self::FQN_SEGMENT_DELIMITER);
    }

    /**
     * Qualify the given column name by the model's table.
     *
     * @param  string  $column
     * @return string
     */
    public function qualifyColumn($column)
    {
        $fqnQualifyingEnabled = $this->allowCrossDatabaseJoins();

        if ((!$fqnQualifyingEnabled && str_contains($column, self::FQN_SEGMENT_DELIMITER)) && $this->countFqnSegments() === str_count($column, ['.'](self::FQN_SEGMENT_DELIMITER))) {
            return $column;
        }

        $segments = [ $this->getTable(), $colum ];

        if ($fqnQualifyingEnabled) {
            array_unshift($segments, $this->getInstance(), $this->getSchema());
        }

        return sprintf(config('database.fqn_pattern'), $segments);
    }

[lchevalot]

Could you tell me what does 'FQN' stands for ?

Also this seems to works just fine on the basis that we could know if a connection allows cross database joins (which would come in a future update) right ?

[shaedrich]

FQN stands for "Fully qualified name", meaning an identifier of an entity including not only its local name but also its namespace (or schema in our case).

My provided code snippets is of course just a quick example and might be implemented differently, but I considered the following (correct me if I'm wrong):

• Via config/database.php or .env, a user can explicitly express whether they want to allow cross-database joins or not (bool|null)
• Via the driver defined in config/database.php, we can determine if the database engine supports cross-database joins in general
• If I know right, when the two database connections have separate users who hold the connection's permissions, cross-database joins are not possible

Because of this, there are some things to take care of beyond just implode('.', [ $instance, $database, $schema, $table ]); (not to mention that, because I wasn't sure, I left it open to have a different format and number of segments in the FQN for each driver)

[lchevalot]

Thank you for clarifying.

I don't think that a env/config var to allow or not global cross-database joins would be relevant since you could chose it for every driver, plus you can have apps with different drivers at the same time.

I am not such an experienced developer as you are since i'm new in the coding environment but i think that your main idea is reliable.

[shaedrich]

You're welcome.

The driver config will no longer be part of laravel/framework due to the 11.x slim skeleton directory structure and instead reside in laravel/laravel and therefore in the vendor/ directory, from where they can be merged with the user's custom config files.

[hamzaelmaghari]

Hello! Thank you for sharing your experience with Laravel and SQL Server. It's great to hear that Laravel has been helpful for you. Regarding your observation about dynamic cross-database requests, it sounds like you've encountered a limitation due to SQL Server's schema handling. Your suggested workarounds seem reasonable, and it's valuable to highlight this topic for discussion. Have you considered submitting a feature request or opening an issue on the Laravel GitHub repository to address this limitation? It could potentially benefit other users facing similar challenges.

[lchevalot]

Hello, isn't this post already a feature request ?

[hafezdivandari]

We have better support for schema on sqlsrv and pgsql on Laravel 11, check #50019

[lchevalot]

Thank you, i'll check this out.