Question

How to use Multiple Databases (MySQL and Postgres) in Laravel 11

Answer 1

inchirags@gmail.com    Chirag's Laravel Tutorial https://www.chirags.in

*********************************************************************************************

How to use Multiple Databases (MySQL and Postgres) in Laravel 11

*********************************************************************************************

Here is the complete Laravel 11 project code for managing two databases (MySQL and PostgreSQL). It provides standard Laravel web routes and views for CRUD operations.

1. Install Laravel Project

composer create-project --prefer-dist laravel/laravel laravel-11-multi-db

cd laravel-11-multi-db

2. Configure Databases

Update .env file:

# MySQL Configuration
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_11_multidb_mysql
DB_USERNAME=root
DB_PASSWORD=admin@123

# PostgreSQL Configuration
PG_CONNECTION=pgsql
PG_HOST=127.0.0.1
PG_PORT=5432
PG_DATABASE=laravel_11_multidb_postgresql
PG_USERNAME=postgres
PG_PASSWORD=admin@123

Create Databases:

MySQL:

CREATE DATABASE laravel_11_multidb_mysql;

PostgreSQL:

CREATE DATABASE laravel_11_multidb_postgresql;

3. Configure Multiple Databases in Laravel

Edit config/database.php: Add a PostgreSQL connection:

'pgsql_secondary' => [
    'driver' => 'pgsql',
    'host' => env('PG_HOST', '127.0.0.1'),
    'port' => env('PG_PORT', '5432'),
    'database' => env('PG_DATABASE', 'forge'),
    'username' => env('PG_USERNAME', 'forge'),
    'password' => env('PG_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'schema' => 'public',
    'sslmode' => 'prefer',
],

4. Create Models and Migrations

Generate Models and Migrations:

php artisan make:model Product -m

php artisan make:model Order -m

Edit Migrations:

products table (MySQL): In database/migrations/xxxx_xx_xx_xxxxxx_create_products_table.php:

public function up()
{
    Schema::create('products', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->decimal('price', 10, 2);
        $table->timestamps();
    });
}

orders table (PostgreSQL): In database/migrations/xxxx_xx_xx_xxxxxx_create_orders_table.php:

public function up()
{
    Schema::connection('pgsql_secondary')->create('orders', function (Blueprint $table) {
        $table->id();
        $table->string('product_name');
        $table->integer('quantity');
        $table->timestamps();
    });
}

Run Migrations:

php artisan migrate

Define Models:

Product.php:

namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Product extends Model
{
    protected $fillable = ['name', 'price'];
}

Order.php:

namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Order extends Model
{
    protected $connection = 'pgsql_secondary';
    protected $fillable = ['product_name', 'quantity'];
}

5. Create Controllers

Generate Controllers:

php artisan make:controller ProductController

php artisan make:controller OrderController

Implement ProductController:

namespace App\Http\Controllers;

use App\Models\Product;
use Illuminate\Http\Request;

class ProductController extends Controller
{
    public function index()
    {
        $products = Product::all();
        return view('products.index', compact('products'));
    }

    public function create()
    {
        return view('products.create');
    }

    public function store(Request $request)
    {
        Product::create($request->all());
        return redirect()->route('products.index');
    }

    public function edit(Product $product)
    {
        return view('products.edit', compact('product'));
    }

    public function update(Request $request, Product $product)
    {
        $product->update($request->all());
        return redirect()->route('products.index');
    }

    public function destroy(Product $product)
    {
        $product->delete();
        return redirect()->route('products.index');
    }
}

Implement OrderController:

namespace App\Http\Controllers;

use App\Models\Order;
use Illuminate\Http\Request;

class OrderController extends Controller
{
    public function index()
    {
        $orders = Order::all();
        return view('orders.index', compact('orders'));
    }

    public function create()
    {
        return view('orders.create');
    }

    public function store(Request $request)
    {
        Order::create($request->all());
        return redirect()->route('orders.index');
    }

    public function edit(Order $order)
    {
        return view('orders.edit', compact('order'));
    }

    public function update(Request $request, Order $order)
    {
        $order->update($request->all());
        return redirect()->route('orders.index');
    }

    public function destroy(Order $order)
    {
        $order->delete();
        return redirect()->route('orders.index');
    }
}

6. Define Routes

Edit routes/web.php:

use App\Http\Controllers\ProductController;
use App\Http\Controllers\OrderController;

Route::resource('products', ProductController::class);
Route::resource('orders', OrderController::class);

7. Create Views

Create Blade templates:

resources/views/products/index.blade.php:

<h1>Products</h1>
<a href="{{ route('products.create') }}">Add Product</a>
<ul>
    @foreach($products as $product)
        <li>
            {{ $product->name }} - ${{ $product->price }}
            <a href="{{ route('products.edit', $product) }}">Edit</a>
            <form action="{{ route('products.destroy', $product) }}" method="POST">
                @csrf
                @method('DELETE')
                <button type="submit">Delete</button>
            </form>
        </li>
    @endforeach
</ul>

resources/views/orders/index.blade.php:

<h1>Orders</h1>
<a href="{{ route('orders.create') }}">Add Order</a>
<ul>
    @foreach($orders as $order)
        <li>
            {{ $order->product_name }} - Quantity: {{ $order->quantity }}
            <a href="{{ route('orders.edit', $order) }}">Edit</a>
            <form action="{{ route('orders.destroy', $order) }}" method="POST">
                @csrf
                @method('DELETE')
                <button type="submit">Delete</button>
            </form>
        </li>
    @endforeach
</ul>

8. Test the Application

Run the development server:

php artisan serve

Access CRUD pages:

Products: 

http://127.0.0.1:8000/products

Orders: 

http://127.0.0.1:8000/orders

This setup allows you to manage CRUD operations for both MySQL (products) and PostgreSQL (orders) tables in a single Laravel project with web views. Let me know if you'd like more details on any step!

For any doubts and query, please write on YouTube video comments section.

Note: Flow the Process shown in video.

Subscribe and like for more videos:

https://www.youtube.com/@chiragstutorial

Don't forget to, Follow, Like, Share &, Comment

Thanks & Regards,

Chitt Ranjan Mahto "Chirag"

_____________________________________________________________________

Note: All scripts used in this demo will be available in our website.

Link will be available in description.

#chirags 

#chiragstutorial 

#chiragslaraveltutorial

#chiragslaraveltutorials

#laraveltutorial 

#laravel11 

#laravelcourse 

#installlaravel

#laravel_tutorial 

#laravelphp

#laravelmultidb

#laravelmultipledatabase

chirags, chiragstutorial, chiragslaraveltutoria, chiragslaraveltutorial, laraveltutorial, laravel11, laravelcourse, installlarave, laravel_tutorial, laravelphp