Getting Started

Minimal Steps

Installation:
```
composer require spatie/db-dumper
```
Ensure system dependencies (mysqldump, pg_dump, sqlite3, mongodump, gzip, or bzip2) are installed based on your database type.

First Use Case: Dump a MySQL database to a file:

use Spatie\DbDumper\Databases\MySql;

MySql::create()
    ->setDbName('your_db_name')
    ->setUserName('your_username')
    ->setPassword('your_password')
    ->dumpToFile('path/to/dump.sql');

Where to Look First:
- README.md: For quick examples and usage patterns.
- src/Databases/: For database-specific dumper classes (e.g., MySql.php, PostgreSql.php).
- src/Compressors/: For custom compression logic.

Implementation Patterns

Common Workflows

Environment-Specific Dumps: Use .env variables for credentials and dynamically configure the dumper:

$dbName = config('database.connections.mysql.database');
$user = config('database.connections.mysql.username');
$password = config('database.connections.mysql.password');

MySql::create()
    ->setDbName($dbName)
    ->setUserName($user)
    ->setPassword($password)
    ->dumpToFile(storage_path("dumps/{$dbName}_" . now()->format('Y-m-d') . '.sql'));

Scheduled Backups: Integrate with Laravel's task scheduling (app/Console/Kernel.php):

protected function schedule(Schedule $schedule)
{
    $schedule->command('db:dump')->dailyAt('2:00');
}

protected $commands = [
    \App\Console\Commands\DumpDatabase::class,
];

Partial Dumps: Dump specific tables or exclude data:

MySql::create()
    ->setDbName('db_name')
    ->includeTables(['users', 'products'])
    ->excludeTablesData(['logs'])
    ->dumpToFile('partial_dump.sql');

Compressed Dumps: Use built-in compressors for storage efficiency:

use Spatie\DbDumper\Compressors\GzipCompressor;

MySql::create()
    ->setDbName('db_name')
    ->useCompressor(new GzipCompressor())
    ->dumpToFile('dump.sql.gz');

Database URL Configuration: Parse connection URLs for flexibility:

MySql::create()
    ->setDatabaseUrl('mysql://user:pass@host:3306/db_name')
    ->dumpToFile('dump.sql');

Integration Tips

Laravel Artisan Commands: Create a custom command for reusable dumps:

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Spatie\DbDumper\Databases\MySql;

class DumpDatabase extends Command
{
    protected $signature = 'db:dump {--path= : Path to save the dump}';
    protected $description = 'Dump the database to a file';

    public function handle()
    {
        MySql::create()
            ->setDbName(config('database.connections.mysql.database'))
            ->setUserName(config('database.connections.mysql.username'))
            ->setPassword(config('database.connections.mysql.password'))
            ->dumpToFile($this->option('path') ?? storage_path('dumps/database.sql'));
    }
}

Event-Based Dumps: Trigger dumps on model events (e.g., ModelObserver):

use Spatie\DbDumper\Databases\MySql;

class UserObserver
{
    public function saved($user)
    {
        if ($user->isCritical()) {
            MySql::create()
                ->setDbName('db_name')
                ->includeTables(['users'])
                ->dumpToFile('critical_user_dump.sql');
        }
    }
}

Testing: Use in-memory SQLite for tests:

use Spatie\DbDumper\Databases\Sqlite;

Sqlite::create()
    ->setDbName(':memory:')
    ->dumpToFile('test_dump.sql');

Gotchas and Tips

Pitfalls

Binary Path Issues: If mysqldump, pg_dump, etc., are not in the system PATH, specify the custom path:

MySql::create()
    ->setDumpBinaryPath('/custom/path/to/mysqldump')
    ->setDbName('db_name')
    ->dumpToFile('dump.sql');

Permissions: Ensure the PHP process has read/write permissions for the dump file location and database access.

Large Dumps: Avoid memory issues by streaming output directly to a file or using compression:

MySql::create()
    ->setDbName('large_db')
    ->useCompressor(new GzipCompressor())
    ->dumpToFile('large_dump.sql.gz');

Auto-Increment Conflicts: Skip AUTO_INCREMENT values when migrating dumps to avoid primary key collisions:
```
MySql::create()
    ->setDbName('db_name')
    ->skipAutoIncrement()
    ->dumpToFile('dump.sql');
```

Column Statistics: Disable column statistics for older MySQL versions (pre-8.0):

MySql::create()
    ->setDbName('db_name')
    ->doNotUseColumnStatistics()
    ->dumpToFile('dump.sql');

Debugging

Command Inspection: Use getDumpCommand() to inspect the generated command before execution:

$command = MySql::create()
    ->setDbName('db_name')
    ->getDumpCommand('dump.sql', 'credentials.txt');
// Log or print $command for debugging.

Error Handling: Wrap dumper calls in try-catch blocks to handle exceptions gracefully:

try {
    MySql::create()
        ->setDbName('db_name')
        ->dumpToFile('dump.sql');
} catch (\Exception $e) {
    Log::error('Database dump failed: ' . $e->getMessage());
}

Logging: Enable verbose logging for troubleshooting:

MySql::create()
    ->setDbName('db_name')
    ->setLogger(new \Monolog\Logger('db_dumper', [new \Monolog\Handler\StreamHandler(storage_path('logs/db_dumper.log'))]))
    ->dumpToFile('dump.sql');

Tips

Custom Compressors: Extend the Compressor interface for custom compression logic (e.g., ZIP):

namespace App\Compressors;

use Spatie\DbDumper\Compressors\Compressor;

class ZipCompressor implements Compressor
{
    public function useCommand(): string
    {
        return 'zip';
    }

    public function useExtension(): string
    {
        return 'zip';
    }
}

Environment-Specific Config: Use Laravel's config to manage dumper settings:

// config/db_dumper.php
return [
    'default' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST'),
        'database' => env('DB_DATABASE'),
        'username' => env('DB_USERNAME'),
        'password' => env('DB_PASSWORD'),
        'path' => storage_path('dumps'),
    ],
];

Append Mode: Use useAppendMode() for incremental dumps (e.g., schema + data in separate steps):

MySql::create()
    ->setDbName('db_name')
    ->doNotDumpData()
    ->useAppendMode()
    ->dumpToFile('schema_dump.sql');

MySql::create()
    ->setDbName('db_name')
    ->includeTables(['recent_data'])
    ->useAppendMode()
    ->dumpToFile('schema_dump.sql');

MongoDB Quirks: MongoDB dumps are compressed by default (.gz extension). Ensure mongodump is configured to handle large collections:

MongoDb::create()
    ->setDbName('db_name')
    ->setUserName('user')
    ->setPassword('pass')
    ->dumpToFile('mongo_dump.gz');

Performance: For large databases, limit concurrent operations or use --single-transaction (PostgreSQL) or --quick (MySQL) flags:
```
PostgreSql::create()
    ->setDbName('db_name')
    ->addExtraOption('--single-transaction')
    ->dumpTo
```

Db Dumper Laravel Package