Laravel Database Tools Laravel Package

Getting Started

Minimal Steps

1. Installation

   composer require --dev jpswade/laravel-database-tools
   

   Install as a dev dependency to avoid production bloat.

2. Publish Config

   php artisan vendor:publish --provider="Jpswade\LaravelDatabaseTools\ServiceProvider" --tag="config"
   

   Edit config/dbtools.php to define:

   • database (source DB for dumps)
   • filesystem (backup storage)
   • filesystem.path (backup directory)

3. First Use Case Dump a database to a file (e.g., for local dev):

   php artisan db:dump --database=production --file=backup.sql
   

   (Replace production with your config key.)

Implementation Patterns

Common Workflows

1. Local Dev Setup

   • Create a fresh DB (if missing):

     php artisan db:create --database=local
     

   • Restore from backup (Spatie-compatible):

     php artisan db:getFromBackup --backup=2025-01-01.sql.gz
     

2. Migration Debugging

   • Import a production dump into local SQLite for testing:

     php artisan db:import --file=prod_dump.sql --database=sqlite_local
     

   • Fix SQLite "no such function" errors (e.g., DATE_FORMAT):

     // Auto-resolved via package; no manual PDO tweaks needed.
     

3. Performance Maintenance

   • Optimize tables (MySQL-only):

     php artisan db:optimize --tables=users,posts
     

   • Update charset/collation (e.g., for legacy DBs):

     php artisan db:charset --database=legacy_db --charset=utf8mb4 --collation=utf8mb4_unicode_ci
     

Integration Tips

• Artisan Command Scheduling Add to app/Console/Kernel.php for automated backups:

  $schedule->command('db:dump --database=production --file=backups/prod_'.date('Y-m-d').'.sql')
           ->daily();
  

• Custom Filesystems Use config['dbtools.filesystem'] to point to S3, FTP, etc.:

  'filesystem' => 's3',
  'filesystem.path' => 'backups/',
  

• Event Hooks Trigger post-import tasks via registered events in AppServiceProvider:

  public function boot()
  {
      \Jpswade\LaravelDatabaseTools\Events\Imported::listen(function ($event) {
          // Run migrations or seeders after import.
      });
  }
  

Gotchas and Tips

Pitfalls

1. SQLite Limitations

   • db:optimize fails silently on SQLite. Add a guard:

     if (DB::connection($database)->getDriverName() === 'sqlite') {
         throw new \Exception("Optimize not supported for SQLite.");
     }
     

   • MySQL compatibility functions (e.g., DATE_FORMAT) require PHP’s date() functions. Test edge cases like:

     -- Works:
     SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
     -- May fail:
     SELECT DATE_FORMAT(NULL, '%Y-%m-%d'); -- Use NULL checks in queries.
     

2. Backup Compatibility

   • db:getFromBackup only supports Spatie Backup’s .sql.gz format. For other formats, pre-process backups:

     gunzip backup.sql.gz && php artisan db:import --file=backup.sql
     

3. Filesystem Permissions

   • Ensure storage/app/backups (or custom path) is writable:

     chmod -R 775 storage/app/backups
     

   • S3/FTP: Verify credentials in config/filesystems.php before using dbtools.filesystem.

4. Large Database Dumps

   • Memory issues: Use --chunk for db:dump (if supported in future updates):

     php artisan db:dump --chunk=100000
     

   • Timeouts: Increase max_execution_time in php.ini or use queues for large imports.

Debugging Tips

• Verbose Output Add -v flag for detailed logs:

  php artisan db:dump -v
  

• Dry Runs Test db:import with --dry-run (if implemented; otherwise, use tail -n 100 backup.sql):

  php artisan db:import --file=test.sql --dry-run
  

• Logging Enable Laravel’s debug mode and check storage/logs/laravel.log for errors like:

  [2025-01-01 12:00:00] local.ERROR: PDOException: SQLSTATE[HY000]: General error
  

Extension Points

1. Custom Commands Extend the package by creating a new command (e.g., db:compare):

   use Jpswade\LaravelDatabaseTools\Traits\HandlesDatabases;
   
   class CompareDatabases extends Command {
       use HandlesDatabases;
   
       public function handle() {
           $this->compareSchema('local', 'production');
       }
   }
   

   (Note: Requires implementing compareSchema() logic.)

2. Hook into Events Listen for Dumped, Imported, or BackupRestored events to add post-processing:

   \Jpswade\LaravelDatabaseTools\Events\Dumped::listen(function ($event) {
       $this->notifyTeam($event->filePath);
   });
   

3. Override Default Config Merge custom logic in config/dbtools.php:

   'pre_import' => function ($connection, $file) {
       // Run custom SQL before import.
       DB::connection($connection)->statement("SET FOREIGN_KEY_CHECKS=0;");
   },
   

4. SQLite MySQL Functions Extend Jpswade\LaravelDatabaseTools\Functions\MySqlCompat to add custom functions:

   class CustomMySqlCompat extends MySqlCompat {
       public function DATE_ADD($date, $interval) {
           return date('Y-m-d H:i:s', strtotime($date) + $this->parseInterval($interval));
       }
   }
   

   (Requires binding the new class in the service provider.)