Multiple DB Laravel

Automatic database read/write splitting untuk aplikasi Laravel. Install package via Composer, set environment variables, dan semua query otomatis di-route — tanpa perlu ubah code di aplikasi yang sudah ada.

Mendukung MySQL, MariaDB, dan proxy seperti MaxScale/ProxySQL (same host, different port).

Features

• Zero-config splitting — SELECT → read replica, INSERT/UPDATE/DELETE → master
• Separate credentials — Username/password bisa berbeda antara read dan write
• Same host, different port — Cocok untuk setup ProxySQL/MaxScale
• Multiple replicas — Comma-separated read hosts dengan load distribution otomatis
• Sticky connections — Setelah write, read selanjutnya di request yang sama pakai master
• Health checking — Deteksi replica yang down, auto-remove dari rotation
• Fallback — Fallback ke master jika semua replica down
• Force write — Middleware, trait, dan helper untuk force master pada critical reads
• Query logging — Monitor distribusi read/write dan slow queries
• Artisan command — php artisan db:cluster-status

Requirements

• PHP 8.1+
• Laravel 10, 11, atau 12
• MySQL / MariaDB

Installation

Via Composer

Package sudah tersedia di Packagist, cukup install langsung:

composer require ranitachi/multiple-db-laravel

Laravel auto-discover akan register ServiceProvider secara otomatis.

Publish Config (Optional)

php artisan vendor:publish --tag=db-cluster-config

Set Environment Variables

DB_WRITE_HOST=10.255.0.91
DB_WRITE_PORT=3306
DB_READ_HOST=10.255.0.91
DB_READ_PORT=3307

Selesai. Tidak perlu ubah code apapun di aplikasi.

Contoh Setup

Setup 1: Same Host, Different Port (ProxySQL/MaxScale)

DB_HOST=10.255.0.91
DB_PORT=3306
DB_USERNAME=dbadmin
DB_PASSWORD=password

# Cluster config
DB_WRITE_HOST=10.255.0.91
DB_WRITE_PORT=3306
DB_READ_HOST=10.255.0.91
DB_READ_PORT=3307

Setup 2: Different Hosts, Same Credentials

DB_USERNAME=dbadmin
DB_PASSWORD=password

DB_WRITE_HOST=10.255.0.91
DB_WRITE_PORT=3306
DB_READ_HOST=10.255.0.92,10.255.0.93
DB_READ_PORT=3306

Setup 3: Different Hosts, Different Credentials

DB_WRITE_HOST=10.255.0.91
DB_WRITE_PORT=3306
DB_WRITE_USERNAME=dbadmin_master
DB_WRITE_PASSWORD=master_secret

DB_READ_HOST=10.255.0.92,10.255.0.93
DB_READ_PORT=3306
DB_READ_USERNAME=dbadmin_readonly
DB_READ_PASSWORD=readonly_secret

Setup 4: Mixed — Same User Write, Different User Read

DB_USERNAME=dbadmin
DB_PASSWORD=password

DB_WRITE_HOST=10.255.0.91
DB_WRITE_PORT=3306

DB_READ_HOST=10.255.0.91
DB_READ_PORT=3307
DB_READ_USERNAME=dbadmin_read
DB_READ_PASSWORD=read_password

Credential Resolution

Jika credentials sama, cukup set DB_USERNAME dan DB_PASSWORD seperti biasa.

Full Environment Variables

# Core
DB_CLUSTER_ENABLED=true              # default: true
DB_CLUSTER_CONNECTIONS=mysql          # default: mysql (comma-separated)

# Write (Master)
DB_WRITE_HOST=10.255.0.91            # default: DB_HOST
DB_WRITE_PORT=3306                   # default: DB_PORT
DB_WRITE_USERNAME=                    # default: DB_USERNAME
DB_WRITE_PASSWORD=                    # default: DB_PASSWORD

# Read (Replica)
DB_READ_HOST=10.255.0.91             # default: DB_HOST
DB_READ_PORT=3307                    # default: DB_PORT
DB_READ_USERNAME=                     # default: DB_USERNAME
DB_READ_PASSWORD=                     # default: DB_PASSWORD

# Behavior
DB_CLUSTER_STICKY=true               # default: true

# Health Check
DB_CLUSTER_HEALTH_CHECK=true         # default: true
DB_CLUSTER_HEALTH_INTERVAL=30        # default: 30 (seconds)
DB_CLUSTER_HEALTH_TIMEOUT=3          # default: 3 (seconds)
DB_CLUSTER_HEALTH_CACHE=redis        # default: file
DB_CLUSTER_HEALTH_FALLBACK=true      # default: true

# Logging
DB_CLUSTER_LOG=false                 # default: false
DB_CLUSTER_LOG_CHANNEL=stack         # default: stack
DB_CLUSTER_LOG_LEVEL=debug           # default: debug
DB_CLUSTER_SLOW_THRESHOLD=1000       # default: 0 (disabled), milliseconds

# Force Write Tables
DB_CLUSTER_FORCE_WRITE_TABLES=payments,sessions

Advanced Usage

Fitur-fitur di bawah ini opsional. Package bekerja tanpa perubahan code.

Force Write pada Route Tertentu

Middleware db.write sudah auto-register oleh package:

Route::middleware('db.write')->group(function () {
    Route::get('/admin/dashboard', [DashboardController::class, 'index']);
    Route::get('/payment/status/{id}', [PaymentController::class, 'status']);
});

Force Write pada Model Tertentu

use Ranitachi\MultipleDb\Traits\UseWriteConnection;

class Payment extends Model
{
    use UseWriteConnection;
    // Semua query pada model ini selalu ke master
}

Force Write dalam Code

use Ranitachi\MultipleDb\DbClusterHelper;

// Paksa satu query ke master
$user = DbClusterHelper::onWrite(fn () => User::find($id));

// Write lalu stick ke master untuk sisa request
DbClusterHelper::writeAndStick(fn () => $user->update($data));

Inline useWritePdo (Laravel built-in)

$user = User::query()->useWritePdo()->find($id);

Cek Status Cluster

php artisan db:cluster-status

=== Connection: mysql ===
  Write : 10.255.0.91:3306 (user: dbadmin)
  Read  : user=dbadmin, port=3307

+---------------+------+-----------+---------------------+
| Host          | Port | Status    | Checked At          |
+---------------+------+-----------+---------------------+
| 10.255.0.91   | 3307 | ● HEALTHY | 2025-01-15 10:30:00 |
+---------------+------+-----------+---------------------+
  ✓ All replicas healthy (1/1)
  Sticky           : Yes
  Fallback to write : Yes
  Health interval   : 30s

How It Works

1. ServiceProvider boot — Baca DB_WRITE_* dan DB_READ_* dari env
2. Config injection — Inject Laravel native read/write array ke database config
3. Laravel handles routing — DatabaseManager bawaan Laravel route SELECT → read, DML → write
4. Health checker — Ping replica periodik, cache hasilnya, remove yang down
5. Sticky — Flag recordsHaveBeenModified() setelah write agar read pakai master

Karena menggunakan mekanisme native Laravel, tidak ada overhead tambahan dan kompatibel penuh dengan Eloquent, Query Builder, raw queries, dan semua package Laravel.

Quick Start (Per Aplikasi)

# 1. Install dari Packagist
composer require ranitachi/multiple-db-laravel

# 2. Set env (minimum)
# DB_WRITE_HOST=10.255.0.91
# DB_WRITE_PORT=3306
# DB_READ_HOST=10.255.0.91
# DB_READ_PORT=3307

# 3. Verify
php artisan db:cluster-status

# 4. Done

License

MIT