I’m working on a Laravel project where I need to dynamically switch the database connection for different clients. I have a Query model and I’m trying to update records in the queries table. The store method works fine, but the update method does not update the database, even though there are no errors thrown.

In the update method, after calling setConnectionByClient($connection), the query object appears empty and the database update does not happen. Here are the details:
Model:

<?php

namespace AppModels;

use IlluminateDatabaseEloquentFactoriesHasFactory;
use IlluminateDatabaseEloquentModel;

class Query extends Model
{
    use HasFactory;

    protected $fillable = ['name', 'query_sql', 'created_by', 'updated_by'];

    public function setConnectionByClient($client)
    {
        Log::info('Setting connection for client:', ['client' => $client]);
        $this->setConnection($client);
        Log::info('Connection set to:', ['connection' => $this->getConnectionName()]);
        return $this;
    }
}

Controller:

<?php

namespace AppHttpControllers;

use AppHttpRequestsUpdateQueryRequest;
use AppModelsQuery;
use IlluminateSupportFacadesAuth;
use IlluminateSupportFacadesDB;

class QueryController extends Controller
{
    public function update(UpdateQueryRequest $request, Query $query)
    {
        $connection = $this->getConnection();

        Log::info('Connection used:', ['connection' => $connection]);

        $data = $request->validated();
        $data['updated_by'] = Auth::id();

        Log::info('Validated data:', $data);

        try {
            $query->setConnectionByClient($connection);
            
            Log::info('Query object after setting connection:', $query->toArray());

            $query->update($data);

            Log::info('Query updated successfully.');
        } catch (Exception $e) {
            Log::error('Error updating query:', [
                'message' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            return to_route('query.index')->with('fail', 'Error updating the query.');
        }

        return to_route('query.index')->with('success', 'Query was updated');
    }

    private function getConnection()
    {
        return session('db_connection', config('database.default'));
    }
}

UpdateQueryRequest

<?php

namespace AppHttpRequests;

use IlluminateFoundationHttpFormRequest;

class UpdateQueryRequest extends FormRequest
{
    public function authorize(): bool
    {
        return true;
    }

    public function rules(): array
    {
        return [
            'name' => 'required|string|max:255',
            'query_sql' => 'required|string',
        ];
    }

}

Observations:

The store method works correctly and inserts data into the queries table.
The update method logs show the connection and validated data, but the query object appears empty after setting the connection as below.
local.INFO: Query object after setting connection:

Questions:

Why does the query object appear empty after setting the connection in the update method?
Is there a better way to dynamically set the connection and update the model in Laravel?

Additional Information:

Laravel Version: 11.10.0
PHP Version: 8.3.7
Database: MySQL (using multiple databases for different clients)

I also tried:
Using the DB Facade

<?php
use IlluminateSupportFacadesDB;

public function update(UpdateQueryRequest $request, Query $query)
{
    $connection = $this->getConnection();

    Log::info('Connection used:', ['connection' => $connection]);

    $data = $request->validated();
    $data['updated_by'] = Auth::id();

    Log::info('Submitting data:', $data);

    try {
        DB::connection($connection)->table('queries')
            ->where('id', $query->id)
            ->update($data);
    } catch (Exception $e) {
        Log::error('Error updating query:', ['message' => $e->getMessage(), 'trace' => $e->getTraceAsString()]);
        return to_route('query.index')->with('fail', 'Error updating the query.');
    }

    Log::info('Query updated successfully.');

    return to_route('query.index')->with('success', 'Query was updated');
}

Using a New Query Instance

<?php
public function update(UpdateQueryRequest $request, Query $query)
{
    $connection = $this->getConnection();

    Log::info('Connection used:', ['connection' => $connection]);

    $data = $request->validated();
    $data['updated_by'] = Auth::id();

    Log::info('Submitting data:', $data);

    try {
        $newQuery = new Query();
        $newQuery->setConnectionByClient($connection);
        $newQuery->where('id', $query->id)->update($data);
    } catch (Exception $e) {
        Log::error('Error updating query:', ['message' => $e->getMessage(), 'trace' => $e->getTraceAsString()]);
        return to_route('query.index')->with('fail', 'Error updating the query.');
    }

    Log::info('Query updated successfully.');

    return to_route('query.index')->with('success', 'Query was updated');
}

I expected the update method to modify the existing record in the specified database. However, the record does not get updated, and there are no error messages in the log.