Getting Started

To begin using vizrex/laravel-db-utils, follow these minimal steps:

Install the Package

composer require vizrex/laravel-db-utils

Publish Configuration

php artisan vendor:publish --provider="Vizrex\LaravelDbUtils\LaravelDbUtilsProvider"

This creates config/dbutils.php with default settings.

Configure Cloud Storage (Optional) Add Dropbox/Google Drive credentials to config/filesystems.php and .env:

DROPBOX_TOKEN=your_token_here
GOOGLE_DRIVE_CLIENT_ID=your_client_id
GOOGLE_DRIVE_CLIENT_SECRET=your_secret
GOOGLE_DRIVE_REFRESH_TOKEN=your_refresh_token
GOOGLE_DRIVE_FOLDER_ID=your_folder_id

First Use Case: Backup Database Run the default backup command:
```
php artisan db:backup
```
This saves the backup to the default path (configured in config/dbutils.php).

Implementation Patterns

Backup Workflow

Basic Backup
```
php artisan db:backup
```
- Uses default path, compression, and upload settings from config/dbutils.php.
Customized Backup
```
php artisan db:backup \
  --path=/custom/backups/ \
  --ignore_tables=logs,temp_data \
  --compress=true \
  --upload=true
```
- Flags:
  - --path: Override default backup location.
  - --ignore_tables: Exclude specific tables (comma-separated).
  - --compress: Enable/disable compression (default: false).
  - --upload: Upload to Dropbox/Google Drive (default: false).

Automated Backups in CI/CD Add to deploy.php (Deployer) or GitHub Actions:

- name: Backup Database
  run: php artisan db:backup --path=/backups/production --upload=true

Restore Workflow

Restore from Local Backup
```
php artisan db:restore /backups/2023_10_01_db.sql
```
- Restores the database from the specified SQL file.
Restore from Cloud
- Manually download the backup from Dropbox/Google Drive, then restore:
```
php artisan db:restore /path/to/downloaded/backup.sql
```

Database Creation Workflow

Create Database with Existing User
```
php artisan db:create new_db_name --username=root --password=root
```
- Creates new_db_name and updates .env with connection details.
Create Database with New User
```
php artisan db:create new_db_name \
  --username=root --password=root \
  --new_user=app_user --new_password=secure123 \
  --set_env=true
```
- Flags:
  - --new_user: Create a new database user.
  - --new_password: Password for the new user.
  - --set_env: Update .env (default: true).

Skip .env Update

php artisan db:create new_db_name --username=root --password=root --set_env=false

Useful for manual .env management.

Integration Tips

Environment-Specific Configs Override defaults in config/dbutils.php per environment (e.g., config/dbutils.local.php):

'backup' => [
    'path' => storage_path('app/backups'),
    'compress' => env('DB_BACKUP_COMPRESS', false),
    'upload' => env('DB_BACKUP_UPLOAD', false),
],

Scheduled Backups Use Laravel Scheduler (app/Console/Kernel.php):

$schedule->command('db:backup --path=/backups/daily --upload=true')->daily();

Exclude Sensitive Tables Always ignore tables containing PII (e.g., users, payments):
```
php artisan db:backup --ignore_tables=users,payments
```

Cloud Storage Fallbacks Configure multiple disks in config/filesystems.php for redundancy:

'disks' => [
    'local' => ['driver' => 'local', 'path' => storage_path('app/backups')],
    'dropbox' => ['driver' => 'dropbox', 'token' => env('DROPBOX_TOKEN')],
],

Gotchas and Tips

Pitfalls

Cloud Storage Permissions
- Issue: Uploads fail silently if Dropbox/Google Drive tokens are invalid.
- Fix: Test credentials manually:
```
php artisan storage:link --force
```
- Verify tokens using the Google Drive API Checker or Dropbox API console.
.env Backup Corruption
- Issue: If .env is corrupted during db:create --set_env=true, the command may fail without restoring the backup.
- Fix: Manually verify storage/dotenv-editor/backups/ after running db:create.
MySQL Host Misconfiguration
- Issue: The db:backup command ignores the host in database.php by default (fixed in v1.0.1).
- Fix: Ensure database.connections.mysql.host is set correctly in .env:
```
DB_HOST=127.0.0.1
```
Large Database Timeouts
- Issue: Backups for databases >5GB may time out or exceed PHP memory limits.
- Fix: Increase PHP memory and timeout:
```
php -d memory_limit=4G artisan db:backup
```
- For very large databases, consider splitting backups or using mysqldump --quick.
Table Exclusion Quirks
- Issue: --ignore_tables may not work as expected if table names contain spaces or special characters.
- Fix: Use exact table names (avoid wildcards):
```
php artisan db:backup --ignore_tables="temp_data,session_store"
```

Debugging Tips

Enable Verbose Output Add -v or -vvv to commands for debugging:
```
php artisan db:backup -vvv
```
- Look for errors in mysqldump execution or filesystem operations.
Check Filesystem Permissions
- Ensure the backup path is writable:
```
chmod -R 775 /path/to/backups
```
- For cloud storage, verify disk configurations in config/filesystems.php.

Validate SQL Dumps After backup, test the SQL file:

mysql -u [user] -p [database] < /path/to/backup.sql

Restore to a Staging Database Test restores on a non-production database first:
```
php artisan db:restore /backups/test.sql --database=staging_db
```

Extension Points

Custom Backup Paths Override the default path in config/dbutils.php:

'backup' => [
    'path' => storage_path('app/backups/production'),
],

Add New Cloud Providers Extend the package by adding a new filesystem driver (e.g., AWS S3):

Create a custom command or service to handle S3 uploads.

Example:

// app/Console/Commands/BackupToS3.php
use Illuminate\Support\Facades\Storage;

public function handle() {
    $backupPath = storage_path('app/backups/latest.sql');
    Storage::disk('s3')->put('backups/latest.sql', file_get_contents($backupPath));
}

Pre/Post-Backup Hooks Use Laravel events to extend backup logic:

// app/Providers/EventServiceProvider.php
protected $listen = [
    'db.backup.starting' => ['App\Listeners\BackupLogger'],
    'db.backup.finished' => ['App\Listeners\SlackNotifier'],
];

Encryption for Backups Encrypt backups before upload using Laravel Encryption:

use Illuminate\Support\Facades\Crypt;

$encrypted = Crypt::encrypt(file_get_contents($backupPath));
file_put_contents($backupPath . '.enc', $encrypted);

Configuration Quirks

Default Values Override Command-line flags always override config/dbutils.php settings. Example:
```
php artisan db:backup --compress=true
```

Laravel Db Utils Laravel Package