Multiple Database Connections in Laravel 10

Hawari Muflih Munte — Tue, 26 Nov 2024 08:06:34 +0000

Need to Connect to Multiple Databases in Laravel 10?
You're in the right place!

I'm assuming you already familiar with Laravel 10 basics. If not, no worries, this guide will still be easy to follow.

To start, let's break down the problem and the solution. First up, here's a flowchart to help you visualize the problem:

Laravel comes with a default .env file, pre-filled with default database credentials like these:

APP_NAME=Laravel
APP_ENV=local
APP_KEY=
APP_DEBUG=true
APP_URL=http://localhost

...

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=A1
DB_USERNAME=CoolUsernameHere
DB_PASSWORD=HotHashedPass

...

By default, Laravel provides the configuration in the .env.example file. You can remove the .example suffix to create your .env file, where you can then customize the settings to match your project's environment.

As shown above, this default configuration is set up for a single database connection.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=A1
DB_USERNAME=CoolUsernameHere
DB_PASSWORD=HotHashedPass

This default configuration connects your project to a single database. If you need to add more connections, then you'll have to write more configurations in your .env file. For example:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=A1
DB_USERNAME=CoolUsernameHere
DB_PASSWORD=HotHashedPass

DB_SECOND_CONNECTION=second_connection
DB_SECOND_HOST=127.0.0.1
DB_SECOND_PORT=5528
DB_SECOND_DATABASE=B2
DB_SECOND_USERNAME=HotUsernameHere
DB_SECOND_PASSWORD=CoolHashedPass

Next, define your new connection in config/database.php file:

'second_connection' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_SECOND_HOST', '127.0.0.1'),
    'port' => env('DB_SECOND_PORT', '8809'),
    'database' => env('DB_SECOND_DATABASE', 'B2_fallback'),
    'username' => env('DB_SECOND_USERNAME', 'ShadowAdmin'),
    'password' => env('DB_SECOND_PASSWORD', 'AdminShadow'),
    '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'),
    ]) : [],
],

Once you've added the code above, you can continue by registering it in your app/Http/Kernel.php.

/**
 * The application's route middleware.
 *
 * These middleware may be assigned to groups or used individually.
 *
 * @var array<string, class-string|string>
 */
protected $routeMiddleware = [
    'auth' => \App\Http\Middleware\Authenticate::class,
    'auth.basic' => \Illuminate\Auth\Middleware\AuthenticateWithBasicAuth::class,
    'cache.headers' => \Illuminate\Http\Middleware\SetCacheHeaders::class,
    'can' => \Illuminate\Auth\Middleware\Authorize::class,
    'guest' => \App\Http\Middleware\RedirectIfAuthenticated::class,
    'password.confirm' => \Illuminate\Auth\Middleware\RequirePassword::class,
    'signed' => \Illuminate\Routing\Middleware\ValidateSignature::class,
    'throttle' => \Illuminate\Routing\Middleware\ThrottleRequests::class,
    'verified' => \Illuminate\Auth\Middleware\EnsureEmailIsVerified::class,
    // Add this line below
    'checkUserLevel' => \App\Http\Middleware\CheckUserLevel::class,
];

Create a new middleware called CheckUserLevel.

<?php

namespace App\Http\Middleware;

use Closure;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;

class CheckUserLevel
{
    public function handle(Request $request, Closure $next, $level)
    {
        Log::info('CheckUserLevel middleware:', [
            'required_level' => $level,
            'user_level' => session('level')
        ]);

        if (session('level') < $level) {
            // If custom credential for user not met, which in this case
            // we use level as custom credentials for user
            return redirect('/')->with('error', 'You have no access to this resources');
        }

        return $next($request);
    }
}

In LoginController.php, you can write custom login logic below.

<?php

namespace App\Http\Controllers\Auth;

use App\Http\Controllers\Controller;
use App\Providers\RouteServiceProvider;
use Illuminate\Foundation\Auth\AuthenticatesUsers;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Log;

class LoginController extends Controller
{
    /*
    |--------------------------------------------------------------------------
    | Login Controller
    |--------------------------------------------------------------------------
    |
    | This controller handles authenticating users for the application and
    | redirecting them to your home screen. The controller uses a trait
    | to conveniently provide its functionality to your applications.
    |
    */

    use AuthenticatesUsers;

    /**
     * Override method attemptLogin to include custom login logic.
     */
    protected function attemptLogin(Request $request)
    {
        // Step 1: check login with first default database (auth Laravel)
        if (Auth::attempt($this->credentials($request), $request->filled('remember'))) {
            return true;
        }

        // Step 2: If step 1 fails, then check second database
        $credentials = $this->credentials($request);
        $user = DB::connection('second_connection')->table('users')
            ->where('email', $credentials['email'])
            ->first();

        if ($user && Hash::check($credentials['password'], $user->password)) {
            // Create custom session if user successfully logged in
            // with second database credentials
            $this->createCustomSession($user);

            // Log user in using taken ID from second database.
            Auth::loginUsingId($user->id, $request->filled('remember'));

            return true;
        }

        return false;
    }

    /**
     * Handle custom session creation for second database users.
     */
    protected function createCustomSession($user)
    {
        // Save user ID and level to session
        session(['custom_session' => $user->id]);

        if (isset($user->level)) {
            session(['level' => $user->level]);
        }
    }

    /**
     * Override method sendFailedLoginResponse to handle error messages.
     */
    protected function sendFailedLoginResponse(Request $request)
    {
        return redirect()->back()->withErrors([
            $this->username() => __('auth.failed'),
        ]);
    }
}

And that's it -- you're all set! 🎉
Now you can use multiple database connections in your project as needed. If you have any questions, don't hesitate to ask! 😊

DEV Community: Hawari Muflih Munte

Multiple Database Connections in Laravel 10