Db Dumper Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require spatie/db-dumper
   

   Ensure system dependencies (mysqldump, pg_dump, sqlite3, mongodump, gzip, or bzip2) are installed based on your database type.

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

   use Spatie\DbDumper\Databases\MySql;
   
   MySql::create()
       ->setDbName(env('DB_DATABASE'))
       ->setUserName(env('DB_USERNAME'))
       ->setPassword(env('DB_PASSWORD'))
       ->dumpToFile(storage_path('app/dump.sql'));
   

Where to Look First

• README.md: Focus on the "Usage" section for quick examples.
• Database-Specific Classes: Check Spatie\DbDumper\Databases\* for your database type (e.g., MySql, PostgreSql).
• Compressor Classes: Review Spatie\DbDumper\Compressors\* for compression options.

Implementation Patterns

Core Workflows

1. Basic Dump:

   $dumper = MySql::create()
       ->setDbName($dbName)
       ->setUserName($user)
       ->setPassword($password)
       ->dumpToFile($outputPath);
   

2. Selective Dumping:

   • Include Specific Tables:

     ->includeTables(['users', 'products'])
     

   • Exclude Tables/Data:

     ->excludeTables(['logs'])
     ->excludeTablesData(['sessions'])
     

3. Advanced Options:

   • Skip Auto-Increment:

     ->skipAutoIncrement()
     

   • Add Custom CLI Options:

     ->addExtraOption('--xml')
     

4. Compression:

   use Spatie\DbDumper\Compressors\GzipCompressor;
   
   $dumper->useCompressor(new GzipCompressor())
       ->dumpToFile('dump.sql.gz');
   

Integration Tips

• Environment Variables: Use Laravel's .env for credentials:

  ->setDatabaseUrl(env('DATABASE_URL'))
  

• Artisan Commands: Create a custom command for CLI access:

  // app/Console/Commands/DumpDatabase.php
  public function handle() {
      MySql::create()
          ->setDbName(config('database.connections.mysql.database'))
          ->setUserName(config('database.connections.mysql.username'))
          ->setPassword(config('database.connections.mysql.password'))
          ->dumpToFile(storage_path('dumps/database_' . now()->format('Y-m-d') . '.sql'));
  }
  

• Scheduled Dumps: Use Laravel's scheduler (app/Console/Kernel.php):

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

Gotchas and Tips

Pitfalls

1. Missing System Dependencies:

   • Ensure mysqldump, pg_dump, etc., are installed and in PATH. Custom paths can be set via:

     ->setDumpBinaryPath('/custom/path/to/mysqldump')
     

   • Compression requires gzip/bzip2:

     sudo apt-get install gzip bzip2  # Ubuntu/Debian
     

2. Auto-Increment Conflicts:

   • Skipping AUTO_INCREMENT (skipAutoIncrement()) may cause ID gaps on import. Use doNotSkipAutoIncrement() to retain values.

3. Column Statistics Errors (MySQL < 8):

   • Older MySQL versions lack column_statistics in information_schema. Use:

     ->doNotUseColumnStatistics()
     

4. File Permissions:

   • Ensure the Laravel storage directory (storage/app/) is writable by the web server user.

5. Large Dumps:

   • Memory limits may be hit for large databases. Use streaming or chunked dumps if needed.

Debugging

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

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

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

  ->dumpToFile('dump.sql', ['log' => 'dump.log']);
  

Extension Points

1. Custom Compressors: Implement the Compressor interface for new formats:

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

2. Pre/Post-Dump Hooks: Extend the dumper class to add logic before/after dumping:

   class CustomDumper extends MySql {
       public function dumpToFile($path) {
           // Pre-dump logic
           parent::dumpToFile($path);
           // Post-dump logic
       }
   }
   

3. Database URL Parsing: Override parseDatabaseUrl() to support custom URL formats.

Tips

• Partial Dumps: Combine includeTables() and excludeTablesData() for schema-only or data-only dumps:

  ->includeTables(['users'])
  ->excludeTablesData(['users']) // Schema only
  ->doNotCreateTables()          // Data only
  

• Remote Hosts: Specify non-local hosts:

  ->setHost('db.example.com')
  

• MongoDB Quirks: MongoDB dumps use .gz by default. Ensure your MongoDB user has backup privileges.

• Testing: Use SQLite for lightweight tests:

  Sqlite::create()
      ->setDbName(storage_path('test.db'))
      ->dumpToFile('test_dump.sql');