Getting Started

Minimal Steps

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

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 basic usage examples.
- Database-specific classes for advanced configurations (e.g., PostgreSql, MongoDb).
- Compressor classes for compression options.

Implementation Patterns

Common Workflows

Environment-Specific Dumps: Use .env variables for credentials and dynamically set them in your dump logic:

$dbConfig = [
    'dbname' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'host' => env('DB_HOST', '127.0.0.1'),
];

MySql::create()
    ->setDbName($dbConfig['dbname'])
    ->setUserName($dbConfig['username'])
    ->setPassword($dbConfig['password'])
    ->setHost($dbConfig['host'])
    ->dumpToFile(storage_path('dumps/' . now()->format('Y-m-d_His') . '.sql'));

Scheduled Dumps: 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 sensitive data:

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

Compressed Dumps: Automatically compress dumps for storage/transfer:

use Spatie\DbDumper\Compressors\GzipCompressor;

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

Database URL Support: Use Laravel's .env database URL:

MySql::create()
    ->setDatabaseUrl(env('DATABASE_URL'))
    ->dumpToFile('dump.sql');

Integration Tips

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(env('DB_DATABASE'))
            ->setUserName(env('DB_USERNAME'))
            ->setPassword(env('DB_PASSWORD'))
            ->dumpToFile($this->option('path') ?? storage_path('dumps/' . now()->format('Y-m-d_His') . '.sql'));
        $this->info('Database dumped successfully!');
    }
}

Events/Listeners: Trigger dumps on model events (e.g., after deployment):

namespace App\Listeners;

use Spatie\DbDumper\Databases\MySql;

class DumpAfterDeployment
{
    public function handle()
    {
        MySql::create()
            ->setDbName(env('DB_DATABASE'))
            ->dumpToFile(storage_path('dumps/post-deployment_' . now()->format('Y-m-d_His') . '.sql'));
    }
}

Testing: Use in phpunit.xml for snapshot testing or seed data:

<env name="DB_DUMP_PATH" value="tests/dumps/production.sql"/>

Gotchas and Tips

Pitfalls

Binary Path Issues:
- If mysqldump/pg_dump isn't in the system PATH, specify the custom path:
```
MySql::create()
    ->setDumpBinaryPath('/usr/local/mysql/bin/mysqldump')
    ->setDbName('app_db')
    ->dumpToFile('dump.sql');
```
- Debug Tip: Run which mysqldump in your terminal to locate the binary.
AUTO_INCREMENT Conflicts:
- Skipping AUTO_INCREMENT values can cause ID collisions in staging/production:
```
MySql::create()
    ->setDbName('app_db')
    ->skipAutoIncrement()
    ->dumpToFile('dump.sql');
```
- Fix: Reset auto-increment after import:
```
ALTER TABLE users AUTO_INCREMENT = 1;
```

Column Statistics Errors (MySQL 5.7):

Older MySQL versions lack the column_statistics table:

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

File Permissions:
- Ensure the web server user (e.g., www-data) has write permissions to the dump directory:
```
chmod -R 775 storage/dumps
chown -R www-data:www-data storage/dumps
```
Large Dumps:
- Memory limits may cause PHP to crash. Use ignoreUserAbort() or increase memory_limit in php.ini:
```
ini_set('memory_limit', '2G');
MySql::create()->ignoreUserAbort()->dumpToFile('large_dump.sql');
```
MongoDB Dumps:
- MongoDB dumps are binary (dump.gz) and not SQL. Use mongorestore to import:
```
mongorestore --gzip --db target_db dump.gz
```

Debugging

Check Dump Command: Use getDumpCommand() to inspect the generated CLI command:

$command = MySql::create()
    ->setDbName('app_db')
    ->getDumpCommand('dump.sql');
// Outputs: mysqldump --user=... --password=... app_db > dump.sql

Log Output: Redirect dump output to a log file for debugging:

MySql::create()
    ->setDbName('app_db')
    ->dumpToFile('dump.sql', true); // Append to file

Dry Run: Test with a small subset of tables first:

MySql::create()
    ->setDbName('app_db')
    ->includeTables(['users'])
    ->dumpToFile('test_dump.sql');

Extension Points

Custom Compressors: Extend the Compressor interface for custom compression (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'; }
}

Pre/Post-Dump Hooks: Use Laravel events to run logic before/after dumps:

// In EventServiceProvider
protected $listen = [
    'db.dumped' => [
       \App\Listeners\NotifyDumpComplete::class,
   ],

];


3. **Dynamic Table Selection**:
Fetch tables dynamically from the database:
```php
$tables = DB::select('SHOW TABLES');
$tableNames = collect($tables)->pluck('Tables_in_app_db')->all();

MySql::create()
    ->setDbName('app_db')
    ->includeTables($tableNames)
    ->dumpToFile('dynamic_dump.sql');

Conditional Dumps: Skip dumps in CI or local environments:

if (!app()->environment('production')) {
    return;
}
MySql::create()->setDbName('app_db')->dumpToFile('prod_dump.sql');

Db Dumper Laravel Package