How to Use Multiple Database Connection in Laravel 6 Application?

How to Use Multiple Database Connection in Laravel 6 Application?

In this Laravel 6 Database tutorial, you'll learn how to use multiple Database connection in Laravel 6 Application. Laravel 6 Multiple Database Connection Tutorial. Learn to use multi database connection Laravel 6. Learn 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.

Today, I would like to show you how to use multiple db connection in Laravel 6 Application. We will learn to use multi database connection Laravel 6. You will find out way of implementing Laravel 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=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=mydatabase

DB_USERNAME=root

DB_PASSWORD=root

DB_CONNECTION_SECOND=mysql

DB_HOST_SECOND=127.0.0.1

DB_PORT_SECOND=3306

DB_DATABASE_SECOND=mydatabase2

DB_USERNAME_SECOND=root

DB_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...

How to setup automatic Databases backup in Laravel

How to setup automatic Databases backup in Laravel

As Laravel developers we need a way to back up our app’s databases somewhere for both save keeping the data and for analysis. In this post, you'll learn how to setup automatic Databases backup in Laravel

I want to share with you a simple solution that I have devised for some of my smaller projects. I have tested this method only on MySQL Databases and on a VPS, but I think it’s pretty much the same for any database.

TLDR;

The outline of my solution is as follows. There’s an artisan command which handles the backup, that runs periodically using a cron. This command makes use of the mysqldump tool that comes with all MySql installations. mysqldump dumps the given DB into a .sql and we can control where it is dumped. And that’s how my solution works. Now let’s see some code.

The Code

First off create an Artisan command like so php artisan make:command BackupDatabase. This should create a class with the name you mentioned in the command and should also have some default scaffolding for the command.

Let me explain what the above code does. Within the constructor we prepare the name of the file (line 20), then we check if there’s a folder named backups within the storage folder, if not we create it (line 21). Then we instantiate a process, with the command that we want to be executed, we also pass it other details (line 23-29).

Then in the handle method runs the process and logs the output to the application logs. This handle method is executed after the constructor by Laravel itself, so you don’t have to invoke it from anywhere.

And finally you need to setup cron, you have 2 options here, either you can call Laravel’s schedule:run command or call your backup command directly.

Conclusion

This is a pretty simple and neat solution for backing up your most valuable asset, that’s the data. You can extend this feature to do more, like attach the .sql file to an email or upload it to dropbox or where ever and so on.

Manage databases easily with Laravel Prequel

Manage databases easily with Laravel Prequel

Laravel Prequel is a Laravel package which can be used to manage databases without the need for tools like PHPmyadmin or Sequel Pro.

Laravel Prequel is a Laravel package which can be used to manage databases without the need for tools like PHPmyadmin or Sequel Pro.

What is Prequel exactly?

Prequel is meant to be a database management tool for Laravel to replace the need for separate standalone database tools like phpMyAdmin, Sequel Pro or MySQL Workbench. With its (hopefully) clear and concise UI, Prequel is to be a modern and lightweight database browser/tool ready for the web of the future. Prequel's design is purposefully based on that of Laravel Telescope because (web-)developers today have enough to learn and master already, so let's help eachother out and make sure to not add anything virtually useless to that huge pile of knowledge.

Clear and concise database management## Installation

To install follow the instructions below.
$ composer require protoqol/prequel  
$ php artisan prequel:install

When installation and publishing is done navigate to /prequel in your browser to see Prequel in action!
Updating
To update you can use the command specified below.
$ php artisan prequel:update

Issues, bugs and feature requests can be reported here!

Configuration

You might have noticed that, while publishing a config file appeared under config/prequel.php. That configuration file looks something like this.

Note that you can define PREQUEL_ENABLED in your .env file.

[  

    /*  
    |--------------------------------------------------------------------------  
    | Prequel Master Switch : boolean
    |--------------------------------------------------------------------------  
    |  
    | Manually disable/enable Prequel, if in production Prequel will always  
    | be disabled. Reason being that nobody should ever be able to directly look  
    | inside your database besides you or your dev team (obviously).  
    |  
    */
    'enabled'      => env('PREQUEL_ENABLED', true),  
      
    /*  
    |--------------------------------------------------------------------------  
    | Prequel Path : string
    |--------------------------------------------------------------------------  
    |  
    | The path where Prequel will be residing. Note that this does not affect 
    | Prequel API routes.  
    |  
    */
    'path'         => 'prequel',  
    
    /*  
    |--------------------------------------------------------------------------  
    | Prequel Database Configuration : array
    |--------------------------------------------------------------------------  
    |  
    | This enables you to fully configure your database-connection for Prequel.
    |  
    */
    'database' => [  
      'connection' => env('DB_CONNECTION', 'mysql'),  
      'host'       => env('DB_HOST', '127.0.0.1'),  
      'port'       => env('DB_PORT', '3306'),  
      'database'   => env('DB_DATABASE', 'homestead'),  
      'username'   => env('DB_USERNAME', 'homestead'),  
      'password'   => env('DB_PASSWORD', 'secret'),  
     ],  
     
    /*  
    |--------------------------------------------------------------------------  
    | Prequel ignored databases and tables : array
    |--------------------------------------------------------------------------  
    | Databases and tables that will be ignored during database discovery.
    |
    | Using 'mysql' => ['*'] ignores the entire mysql database.
    | Using 'mysql' => ['foo']  ignores only the mysql.foo table.
    */
    'ignored'      => [  
         // 'information_schema'  => ['*'],  
         // 'sys'                 => ['*'],
         // 'performance_schema'  => ['*'], 
         // 'mysql'               => ['*'],
         '#mysql50#lost+found'    => ['*'],  
     ],
     
    /*
    |--------------------------------------------------------------------------
    | Prequel pagination per page : integer
    |--------------------------------------------------------------------------
    |
    | When Prequel retrieves paginated information, this is the number of
    | records that will be in each page.
    |
    */
    'pagination' => 100,
    
        /*
        |--------------------------------------------------------------------------
        | Prequel middleware : array
        |--------------------------------------------------------------------------
        |
        | Define custom middleware for Prequel to use.
        |
        | Ex. 'web', Protoqol\Prequel\Http\Middleware\Authorised::class
        |
        */
    
        'middleware' => [
            Protoqol\Prequel\Http\Middleware\Authorised::class,
        ],
];

Clear and concise database management

The Complete Guide to Database Seeder in Laravel 6

The Complete Guide to Database Seeder in Laravel 6

You have lots of question what is seeder in Laravel 6? How to use seeder in Laravel 6? What is command to create seeder in Laravel 6? Why we have to use seeder in Laravel 6? That's all I will example in this Laravel 6 database seed tutorial.

Laravel introduce seeder for creating testing data and if you have small admin project then you can create admin user and also setting table default data.

Whenever you have admin project that don't have register page then what you will do?, I mean you need to create at least one admin user. So basically he can login and access whole admin panel. But you don't have register page on front end. you only have login page. So you can create one admin from database directly? If yes then you have to always create new admin user from directly database when you create new setup of your project. But i will suggest you to create admin seeder so you can create admin user using Laravel 6 seeder. You just fire on command to run seeder in Laravel 6.

Same things, if you have default setting configuration then you can create setting seeder and add default configuration to your database table.

In this tutorial, i will show you how to create database seeder in Laravel 6 and what is command to create seeder and how to run that seeder in Laravel 6. So you have to just follow few step get how it's done.

Laravel gives command to create seeder in laravel. so you can run following command to make seeder in laravel application.

Create Seeder Command:

php artisan make:seeder AdminUserSeeder

After run above command, it will create one file AdminUserSeeder.php on seeds folder. All seed classes are stored in the database/seeds directory.

Then you can write code of create admin user using model in laravel.

database/seeds/AdminUserSeeder.php

<?php

  

use Illuminate\Database\Seeder;

use App\User;

   

class AdminUserSeeder extends Seeder

{

    /**

     * Run the database seeds.

     *

     * @return void

     */

    public function run()

    {

        User::create([

            'name' => 'Hardik',

            'email' => '[email protected]',

            'password' => bcrypt('123456'),

        ]);

    }

}

As you can see above code, i simply write creating new user in my users table now. so you can add your code to write admin user creating. Now how you can run this seeder manually.

There is a two way to run this seeder. I will give you both way to run seeder in Laravel 6.

Way 1: Run Single Seeder

You need to run following command to run single seeder:

php artisan db:seed --class=AdminUserSeeder

Way 2: Run All Seeders

In this way, you have to declare your seeder in DatabaseSeeder class file. then you have to run single command to run all listed seeder class.

So can list as bellow:

database/seeds/DatabaseSeeder.php

<?php

  

use Illuminate\Database\Seeder;

   

class DatabaseSeeder extends Seeder

{

    /**

     * Seed the application's database.

     *

     * @return void

     */

    public function run()

    {

        $this->call(AdminUserSeeder::class);

    }

}

Now you need to run following command for run all listed seeder:

php artisan db:seed

Now i think you will understand how seeding is work and we have to use in our Laravel app.

I hope it can help you...