Laravel 7/6 Multiple Database Connection Tutorial (ok)

https://www.itsolutionstuff.com/post/laravel-6-multiple-database-connection-tutorialexample.html

C:\xampp\htdocs\reset\.env

APP_NAME=Test
APP_ENV=local
APP_KEY=base64:Oki7sndHBwYxkCBwH0gtS44oKrtsL+xjN07/KKWtA+c=
APP_DEBUG=true
APP_URL=http://localhost/reset
LOG_CHANNEL=stack
LOG_DEPRECATIONS_CHANNEL=null
LOG_LEVEL=debug

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=reset
DB_USERNAME=root
DB_PASSWORD=

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=reset2
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=

BROADCAST_DRIVER=redis
CACHE_DRIVER=file
FILESYSTEM_DRIVER=local
QUEUE_CONNECTION=sync
SESSION_DRIVER=file
SESSION_LIFETIME=120
MEMCACHED_HOST=127.0.0.1
REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
LARAVEL_ECHO_PORT=6001
REDIS_PORT=6379
MAIL_MAILER=smtp
MAIL_DRIVER=smtp
MAIL_HOST=smtp.gmail.com
MAIL_PORT=587
MAIL_USERNAME=phamngoctuong1805@gmail.com
MAIL_PASSWORD=lufcvcbdnbjcrvta
MAIL_ENCRYPTION=tls
MAIL_FROM_ADDRESS=phamngoctuong1805@gmail.com
MAIL_FROM_NAME="${APP_NAME}"
AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
AWS_DEFAULT_REGION=us-east-1
AWS_BUCKET=
AWS_USE_PATH_STYLE_ENDPOINT=false
PUSHER_APP_ID=
PUSHER_APP_KEY=
PUSHER_APP_SECRET=
PUSHER_APP_CLUSTER=mt1
MIX_PUSHER_APP_KEY="${PUSHER_APP_KEY}"
MIX_PUSHER_APP_CLUSTER="${PUSHER_APP_CLUSTER}"

C:\xampp\htdocs\reset\config\database.php

<?php
use Illuminate\Support\Str;
return [
  /*
  |--------------------------------------------------------------------------
  | Default Database Connection Name
  |--------------------------------------------------------------------------
  |
  | Here you may specify which of the database connections below you wish
  | to use as your default connection for all database work. Of course
  | you may use many connections at once using the Database library.
  |
   */
  'default'     => env('DB_CONNECTION', 'mysql'),
  /*
  |--------------------------------------------------------------------------
  | Database Connections
  |--------------------------------------------------------------------------
  |
  | Here are each of the database connections setup for your application.
  | Of course, examples of configuring each database platform that is
  | supported by Laravel is shown below to make development simple.
  |
  |
  | All database work in Laravel is done through the PHP PDO facilities
  | so make sure you have the driver for your particular database of
  | choice installed on your machine before you begin development.
  |
   */
  'connections' => [
    'sqlite'       => [
      'driver'                  => 'sqlite',
      'url'                     => env('DATABASE_URL'),
      'database'                => env('DB_DATABASE', database_path('database.sqlite')),
      'prefix'                  => '',
      'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
    ],
    'mysql'        => [
      'driver'         => 'mysql',
      'url'            => env('DATABASE_URL'),
      'host'           => env('DB_HOST', '127.0.0.1'),
      'port'           => env('DB_PORT', '3306'),
      'database'       => env('DB_DATABASE', 'forge'),
      'username'       => env('DB_USERNAME', 'forge'),
      'password'       => env('DB_PASSWORD', ''),
      'unix_socket'    => env('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'),
      ]) : [],
    ],
    'mysql_second' => [
      'driver'         => 'mysql',
      'url'            => env('DATABASE_URL_SECOND'),
      'host'           => env('DB_HOST_SECOND', '127.0.0.1'),
      'port'           => env('DB_PORT_SECOND', '3306'),
      'database'       => env('DB_DATABASE_SECOND', 'forge'),
      'username'       => env('DB_USERNAME_SECOND', 'forge'),
      'password'       => env('DB_PASSWORD_SECOND', ''),
      'unix_socket'    => env('DB_SOCKET_SECOND', ''),
      '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'),
      ]) : [],
    ],
    'pgsql'        => [
      'driver'         => 'pgsql',
      'url'            => env('DATABASE_URL'),
      'host'           => env('DB_HOST', '127.0.0.1'),
      'port'           => env('DB_PORT', '5432'),
      'database'       => env('DB_DATABASE', 'forge'),
      'username'       => env('DB_USERNAME', 'forge'),
      'password'       => env('DB_PASSWORD', ''),
      'charset'        => 'utf8',
      'prefix'         => '',
      'prefix_indexes' => true,
      'schema'         => 'public',
      'sslmode'        => 'prefer',
    ],
    'sqlsrv'       => [
      'driver'         => 'sqlsrv',
      'url'            => env('DATABASE_URL'),
      'host'           => env('DB_HOST', 'localhost'),
      'port'           => env('DB_PORT', '1433'),
      'database'       => env('DB_DATABASE', 'forge'),
      'username'       => env('DB_USERNAME', 'forge'),
      'password'       => env('DB_PASSWORD', ''),
      'charset'        => 'utf8',
      'prefix'         => '',
      'prefix_indexes' => true,
    ],
  ],
  /*
  |--------------------------------------------------------------------------
  | Migration Repository Table
  |--------------------------------------------------------------------------
  |
  | This table keeps track of all the migrations that have already run for
  | your application. Using this information, we can determine which of
  | the migrations on disk haven't actually been run in the database.
  |
   */
  'migrations'  => 'migrations',
  /*
  |--------------------------------------------------------------------------
  | Redis Databases
  |--------------------------------------------------------------------------
  |
  | Redis is an open source, fast, and advanced key-value store that also
  | provides a richer body of commands than a typical key-value system
  | such as APC or Memcached. Laravel makes it easy to dig right in.
  |
   */
  'redis'       => [
    'client'  => env('REDIS_CLIENT', 'phpredis'),
    'options' => [
      'cluster' => env('REDIS_CLUSTER', 'redis'),
      'prefix'  => env('REDIS_PREFIX', Str::slug(env('APP_NAME', 'laravel'), '_') . '_database_'),
    ],
    'default' => [
      'url'      => env('REDIS_URL'),
      'host'     => env('REDIS_HOST', '127.0.0.1'),
      'password' => env('REDIS_PASSWORD', null),
      'port'     => env('REDIS_PORT', '6379'),
      'database' => env('REDIS_DB', '0'),
    ],
    'cache'   => [
      'url'      => env('REDIS_URL'),
      'host'     => env('REDIS_HOST', '127.0.0.1'),
      'password' => env('REDIS_PASSWORD', null),
      'port'     => env('REDIS_PORT', '6379'),
      'database' => env('REDIS_CACHE_DB', '1'),
    ],
  ],
];

C:\xampp\htdocs\reset\routes\web.php

<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\HomeController;
use App\Http\Controllers\ProductController;
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
Route::get('/', function () {
    return view('welcome');
});
Auth::routes();
Route::get('/home', [HomeController::class, 'index'])->name('home');
Route::get('get-mysql-products',  [ProductController::class, 'index']);
Route::delete('get-mysql-products/{id}',  [ProductController::class, 'destroy']);
Route::delete('myproductsDeleteAll',  [ProductController::class, 'deleteAll']);
/*------------------------------------------
--------------------------------------------
Getting Records of Mysql Database Connections
--------------------------------------------
--------------------------------------------*/
Route::get('/get-mysql-products', function () {
  $products = DB::table("products")->get();
  return view('products')->with(compact('products'));
});
/*------------------------------------------
--------------------------------------------
Getting Records of Mysql Second Database Connections
--------------------------------------------
--------------------------------------------*/
Route::get('/get-mysql-second-products', function () {
  $products = DB::connection('mysql_second')->table("products")->get();
  return view('products')->with(compact('products'));
});

C:\xampp\htdocs\reset\resources\views\products.blade.php

<!DOCTYPE html>
<html>
<head>
    <title>Laravel 5 - Multiple delete records with checkbox example</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-confirmation/1.0.5/bootstrap-confirmation.min.js"></script>
    <meta name="csrf-token" content="{{ csrf_token() }}">
</head>
<body>
<div class="container">
    <h3>Laravel 5 - Multiple delete records with checkbox example</h3>
    <button style="margin-bottom: 10px" class="btn btn-primary delete_all" data-url="{{ url('myproductsDeleteAll') }}">Delete All Selected</button>
    <table class="table table-bordered">
        <tr>
            <th width="50px"><input type="checkbox" id="master"></th>
            <th width="80px">No</th>
            <th>Product Name</th>
            <th>Product Details</th>
            <th width="100px">Action</th>
        </tr>
        @if($products->count())
            @foreach($products as $key => $product)
                <tr id="tr_{{$product->id}}">
                    <td><input type="checkbox" class="sub_chk" data-id="{{$product->id}}"></td>
                    <td>{{ ++$key }}</td>
                    <td>{{ $product->name }}</td>
                    <td>{{ $product->details }}</td>
                    <td>
                         <a href="{{ url('get-mysql-products',$product->id) }}" class="btn btn-danger btn-sm"
                           data-tr="tr_{{$product->id}}"
                           data-toggle="confirmation"
                           data-btn-ok-label="Delete" data-btn-ok-icon="fa fa-remove"
                           data-btn-ok-class="btn btn-sm btn-danger"
                           data-btn-cancel-label="Cancel"
                           data-btn-cancel-icon="fa fa-chevron-circle-left"
                           data-btn-cancel-class="btn btn-sm btn-default"
                           data-title="Are you sure you want to delete ?"
                           data-placement="left" data-singleton="true">
                            Delete
                        </a>
                    </td>
                </tr>
            @endforeach
        @endif
    </table>
</div> <!-- container / end -->
</body>
<script type="text/javascript">
    $(document).ready(function () {
        $('#master').on('click', function(e) {
         if($(this).is(':checked',true))  
         {
            $(".sub_chk").prop('checked', true);  
         } else {  
            $(".sub_chk").prop('checked',false);  
         }  
        });
        $('.delete_all').on('click', function(e) {
            var allVals = [];  
            $(".sub_chk:checked").each(function() {  
                allVals.push($(this).attr('data-id'));
            });  
            if(allVals.length <=0)  
            {  
                alert("Please select row.");  
            }  else {  
                var check = confirm("Are you sure you want to delete this row?");  
                if(check == true){  
                    var join_selected_values = allVals.join(","); 
                    $.ajax({
                        url: $(this).data('url'),
                        type: 'DELETE',
                        headers: {'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')},
                        data: 'ids='+join_selected_values,
                        success: function (data) {
                            if (data['success']) {
                                $(".sub_chk:checked").each(function() {  
                                    $(this).parents("tr").remove();
                                });
                                alert(data['success']);
                            } else if (data['error']) {
                                alert(data['error']);
                            } else {
                                alert('Whoops Something went wrong!!');
                            }
                        },
                        error: function (data) {
                            alert(data.responseText);
                        }
                    });
                  $.each(allVals, function( index, value ) {
                      $('table tr').filter("[data-row-id='" + value + "']").remove();
                  });
                }  
            }  
        });
        $('[data-toggle=confirmation]').confirmation({
            rootSelector: '[data-toggle=confirmation]',
            onConfirm: function (event, element) {
                element.trigger('confirm');
            }
        });
        $(document).on('confirm', function (e) {
            var ele = e.target;
            e.preventDefault();
            $.ajax({
                url: ele.href,
                type: 'DELETE',
                headers: {'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')},
                success: function (data) {
                    if (data['success']) {
                        $("#" + data['tr']).slideUp("slow");
                        alert(data['success']);
                    } else if (data['error']) {
                        alert(data['error']);
                    } else {
                        alert('Whoops Something went wrong!!');
                    }
                },
                error: function (data) {
                    alert(data.responseText);
                }
            });
            return false;
        });
    });
</script>
</html>

C:\xampp\htdocs\reset\app\Http\Controllers\ProductController.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
class ProductController extends Controller {
  /**
   * Show the application dashboard.
   *
   * @return \Illuminate\Http\Response
   */
  public function index() {
    $products = DB::table("products")->get();
    return view('products', compact('products'));
  }
  /**
   * Show the application dashboard.
   *
   * @return \Illuminate\Http\Response
   */
  public function destroy($id) {
    DB::table("products")->delete($id);
    return response()->json(['success' => "Product Deleted successfully.", 'tr' => 'tr_' . $id]);
  }
  /**
   * Show the application dashboard.
   *
   * @return \Illuminate\Http\Response
   */
  public function deleteAll(Request $request) {
    $ids = $request->ids;
    $idsa = explode(",", $ids);
    DB::table("products")->whereIn('id', $idsa)->delete();
    return response()->json(['success' => "Products Deleted successfully."]);
  }
}

INSERT INTO `products` (`id`, `name`, `details`, `created_at`, `updated_at`) VALUES

(1, 'Product A', 'Product A Details', NULL, NULL),

(3, 'Product C', 'Product C Details', NULL, NULL),

(4, 'Product D', 'Product D Details', NULL, NULL),

(5, 'Product E', 'Product E Details', NULL, NULL),

(6, 'Product F', 'Product F Details', NULL, NULL);

C:\xampp\htdocs\reset\database\migrations\2022_05_20_161330_create_products_table.php

<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateProductsTable extends Migration {
  /**
   * Run the migrations.
   *
   * @return void
   */
  public function up() {
    Schema::create('products', function (Blueprint $table) {
      $table->id();
      $table->string('name', '30');
      $table->string('details', '300');
      $table->timestamps();
    });
  }
  /**
   * Reverse the migrations.
   *
   * @return void
   */
  public function down() {
    Schema::dropIfExists('products');
  }
}m

Laravel 7/6 Multiple Database Connection Tutorial

By Hardik Savani December 4, 2019 Category : LaravelPlayUnmuteLoaded: 1.17%FullscreenToday, i would like to show you how to use multiple db connection in laravel 7/6 application. we will learn to use multi database connection laravel 7/6. you will find out way of implementing laravel 7/6 multiple database connection example.

I will give you step by step implementation of how to use laravel 6 multiple database connections using .env file. we will add configuration variable on .env file and use it to database configuration file. You can just follow me, i will also learn how to work with migration, model and database query for multiple database connection.

As we know sometime we need to use multiple database connection like mysql, mongodb etc. i can say when you work with large amount of project then you will need maybe. So let's follow bellow step.

Set ENV Variable:

Here, you need to set configuration variable on .env file. let's create as bellow:

.env

DB_CONNECTION=mysqlDB_HOST=127.0.0.1DB_PORT=3306DB_DATABASE=mydatabaseDB_USERNAME=rootDB_PASSWORD=rootDB_CONNECTION_SECOND=mysqlDB_HOST_SECOND=127.0.0.1DB_PORT_SECOND=3306DB_DATABASE_SECOND=mydatabase2DB_USERNAME_SECOND=rootDB_PASSWORD_SECOND=root

Use ENV Variable:

Now, as we created variable in env file, we need to use that variable on config file so let's open database.php file and add new connections key as like bellow:

config/database.php

<?php  use Illuminate\Support\Str;  return [       'default' => env('DB_CONNECTION', 'mysql'),       'connections' => [        .....           'mysql' => [            'driver' => 'mysql',            'url' => env('DATABASE_URL'),            'host' => env('DB_HOST', '127.0.0.1'),            'port' => env('DB_PORT', '3306'),            'database' => env('DB_DATABASE', 'forge'),            'username' => env('DB_USERNAME', 'forge'),            'password' => env('DB_PASSWORD', ''),            'unix_socket' => env('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'),            ]) : [],        ],        'mysql2' => [            'driver' => env('DB_CONNECTION_SECOND'),            'host' => env('DB_HOST_SECOND', '127.0.0.1'),            'port' => env('DB_PORT_SECOND', '3306'),            'database' => env('DB_DATABASE_SECOND', 'forge'),            'username' => env('DB_USERNAME_SECOND', 'forge'),            'password' => env('DB_PASSWORD_SECOND', ''),            'unix_socket' => '',            'charset' => 'utf8mb4',            'collation' => 'utf8mb4_unicode_ci',            'prefix' => '',            'prefix_indexes' => true,            'strict' => true,            'engine' => null,        ],.....

Use Database Multiple Connection:

Here, i will give you simple example of how you can use as multiple connection:

Use with migration

<?php.....public function up(){    Schema::connection('mysql2')->create('blog', function (Blueprint $table) {        $table->increments('id');        $table->string('title');        $table->string('body')->nullable();        $table->timestamps();    });}.....

Use with model

<?php   namespace App;  use Illuminate\Database\Eloquent\Model;   class Blog extends Model{    protected $connection = 'mysql2';}

Use with Controller

<?php  class BlogController extends BaseController{    public function getRecord()    {        $blogModel = new Blog;        $blogModel->setConnection('mysql2');        $find = $blogModel->find(1);        return $find;    }}

Use with Query Builder

$blogs = DB::table("blog")->get();print_r($blogs);   $blogs = DB::connection('mysql2')->table("blog")->get();print_r($blogs);

I hope it can help you...

PreviousHow to Create Reusable Components in Laravel? (ok)NextDefine Global Variable in Laravel 5.8 (ok)

Last updated 3 years ago

Was this helpful?