Default to a second database connection for migrations only

[charleskhenry]

Sorry, new to Laravel. I'm currently using version 8.0 as a best practice. However, I'm having difficulty setting the default MySQL connection for migration activities.

I would like to default all database transactions to limited permissions; while automatically elevating permissions for all migration activities.

I would think this feature exists already, but I can't find it in the documentation or after a few hours of googling.

1. First I created a database and some user accounts.

I created a 'laravel-basic' MySQL user, granting: create temporary tables, delete, execute, insert, lock tables, select, and update permissions.

I then created a 'laravel-elevated' MySQL user with all permissions except grant.

2. I then edited my .env and config/database.php files to have multiple connections
.env file:

DEFAULT_DB_CONNECTION=sentry-trn-rms-laravel

# Basic permissions
SENTRY_TRN_RMS_LARAVEL_DB_NAME=sentry-trn-rms-laravel
SENTRY_TRN_RMS_LARAVEL_DB_CONNECTION=mysql
SENTRY_TRN_RMS_LARAVEL_DB_HOST=127.0.0.1
SENTRY_TRN_RMS_LARAVEL_DB_PORT=3306
SENTRY_TRN_RMS_LARAVEL_DB_DATABASE=sentry-trn-rms-dev
SENTRY_TRN_RMS_LARAVEL_DB_USERNAME=sentry-trn-rms-laravel
SENTRY_TRN_RMS_LARAVEL_DB_PASSWORD="***"

# Elevated permissions
SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_NAME=sentry-trn-rms-laravel-elevated
SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_CONNECTION=mysql
SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_HOST=127.0.0.1
SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_PORT=3306
SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_DATABASE=sentry-trn-rms-dev
SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_USERNAME=sentry-trn-rms-laravel-elevated
SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_PASSWORD="***"

config/database.php file:

'default' => env('DEFAULT_DB_CONNECTION', 'mysql'),

'connections' => [
        env('SENTRY_TRN_RMS_LARAVEL_DB_NAME', 'mysql') => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('SENTRY_TRN_RMS_LARAVEL_DB_HOST', '127.0.0.1'),
            'port' => env('SENTRY_TRN_RMS_LARAVEL_DB_PORT', '3306'),
            'database' => env('SENTRY_TRN_RMS_LARAVEL_DB_DATABASE', 'forge'),
            'username' => env('SENTRY_TRN_RMS_LARAVEL_DB_USERNAME', 'forge'),
            'password' => env('SENTRY_TRN_RMS_LARAVEL_DB_PASSWORD', ''),
            'unix_socket' => env('SENTRY_TRN_RMS_LARAVEL_DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

        env('SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_NAME', 'mysql2') => [
            'driver' => 'mysql',
            'url' => env('SENTRY_TRN_RMS_LARAVEL_ELEVATED_DATABASE_URL'),
            'host' => env('SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_HOST', '127.0.0.1'),
            'port' => env('SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_PORT', '3306'),
            'database' => env('SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_DATABASE', 'forge'),
            'username' => env('SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_USERNAME', 'forge'),
            'password' => env('SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_PASSWORD', ''),
            'unix_socket' => env('SENTRY_TRN_RMS_LARAVEL_ELEVATED_DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
    ],

3. Finally, for all migrations, I'm sure to replace default MySQL connections to use the proper elevated connection.
database/migrations/2014_10_12_000000_create_users_table.php file:

// example of default 
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
                    $table->id();
                    $table->string('name');
                    $table->string('email')->unique();
                    $table->timestamp('email_verified_at')->nullable();
                    $table->string('password');
                    $table->rememberToken();
                    $table->timestamps();
        });
    }
// example of replacement code to indicate the proper database connection
    public function up()
    {
        Schema::connection("sentry-trn-rms-laravel-elevated")
            ->create('users', function (Blueprint $table) {
                    $table->id();
                    $table->string('name');
                    $table->string('email')->unique();
                    $table->timestamp('email_verified_at')->nullable();
                    $table->string('password');
                    $table->rememberToken();
                    $table->timestamps();
        });
    }

The Problem:
When I run php artisan migrate, for the first time, laravel fails to create the database tables because it trys to execute the database commands from the default account (which has basic permissions).

I verified this by changing the default connection in config/database.php to instead use the elevated account rather than the basic account... and it runs without any problems.

Additionally, if I add the create permission to the basic account, everything works okay.

So, how can I tell laravel to use the second database connection for Migrations only?

It appears to me that laravel\framework\src\Illuminate\Database\Connection.php:678 only uses the default MySQL connection. Is this correct? Maybe, I should submit a feature request?

Thanks for the help, loving the framework already.