Db Command Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require wp-cli/db-command
   

   Ensure wp-cli/wp-cli is also installed (composer require wp-cli/wp-cli).

2. First Use Case: Navigate to your WordPress project root and verify database connectivity:

   wp db check
   

   This checks table integrity using mysqlcheck with credentials from wp-config.php.

Key Initial Commands

Implementation Patterns

Workflows

1. Database Maintenance

• Optimize/Repair:

  wp db optimize --tables=wp_posts,wp_postmeta  # Target specific tables
  wp db repair --all-tables-with-prefix         # Repair all tables
  

• Backup:

  wp db export backup-$(date +%Y-%m-%d).sql --tables=$(wp db tables --format=csv)
  

2. Development Environments

• Reset State:

  wp db drop --yes && wp db create && wp core install --url=localhost:8000
  

• Multisite Management:

  wp db query "SELECT * FROM wp_2_options WHERE option_name='home'"  # Query site #2
  

3. CI/CD Integration

• Pre-Deploy Checks:

  wp db check --all-tables-with-prefix || exit 1
  

• Automated Backups:

  wp db export --porcelain --tables=$(wp db tables --format=csv) > /backups/db-$(date +%s).sql
  

Laravel Integration Tips

1. Artisan Command Wrapper: Create a custom Artisan command to delegate to wp-cli/db-command:

   // app/Console/Commands/WpDbReset.php
   namespace App\Console\Commands;
   use Illuminate\Support\Facades\Process;
   
   class WpDbReset extends Command {
       protected $signature = 'wp:db-reset';
       public function handle() {
           Process::run('wp db reset --yes');
           $this->info('WordPress database reset complete.');
       }
   }
   

   Register in app/Console/Kernel.php:

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

2. Environment-Specific Config: Use Laravel’s .env to override wp-config.php credentials:

   WP_DB_HOST=127.0.0.1
   WP_DB_USER=laravel_user
   WP_DB_PASSWORD=secure_password
   

   Then reference these in your wp-config.php:

   define('DB_HOST', env('WP_DB_HOST'));
   

3. Seeding with WP-CLI: Combine Laravel’s seeder with WP-CLI for bulk operations:

   // database/seeders/WpSeed.php
   public function run() {
       Process::run('wp db query < seed.sql');
   }
   

Gotchas and Tips

Pitfalls

1. Multisite Table Prefixes:

   • Issue: wp db tables only lists tables for the primary site. For multisite, manually specify prefixes (e.g., wp_2_posts).
   • Fix: Use wp site list to map site IDs to prefixes:

     wp db query "SELECT * FROM wp_$(wp site list --fields=blog_id --format=csv | tail -n 1)_options"
     

2. Character Encoding:

   • Issue: wp db export may corrupt UTF-8 data if --default-character-set isn’t set.
   • Fix: Explicitly specify:

     wp db export --default-character-set=utf8mb4 backup.sql
     

3. Permissions:

   • Issue: Commands fail silently if MySQL credentials in wp-config.php are invalid.
   • Fix: Test connectivity first:

     wp db cli --execute="SHOW DATABASES;"
     

4. Interactive Prompts:

   • Issue: --yes flag is required for destructive actions (drop, reset), but may bypass safety checks.
   • Tip: Use in CI/CD only; avoid in production scripts.

Debugging

1. Verbose Output: Enable debug mode for wp db query:

   wp db query --verbose "SELECT * FROM wp_posts LIMIT 1"
   

2. MySQL Error Logs: Redirect stderr to a file:

   wp db query "DROP TABLE wp_test" 2> mysql_errors.log
   

3. Dry Runs: Use --dry-run with mysqldump (if supported):

   wp db export --dry-run backup.sql
   

Extension Points

1. Custom Commands: Extend the package by creating a subclass of WP_CLI\DB_Command:

   // src/CustomDbCommand.php
   namespace App\WPCLI;
   use WP_CLI\DB_Command;
   
   class CustomDbCommand extends DB_Command {
       public function customQuery($sql) {
           return $this->run_sql($sql);
       }
   }
   

   Register in composer.json:

   "extra": {
       "wp-cli": {
           "commands": ["App\\WPCLI\\CustomDbCommand"]
       }
   }
   

2. Pre/Post Hooks: Use Laravel’s service providers to wrap wp-cli commands:

   // app/Providers/WpCliServiceProvider.php
   public function boot() {
       Process::macro('wp', function ($command) {
           return Process::run("wp {$command}");
       });
   }
   

3. Testing: Mock wp-cli commands in Laravel tests:

   Process::shouldReceive('run')
           ->with('wp db check')
           ->andReturn(new ProcessResult(0, '', ''));
   

Configuration Quirks

1. --defaults Flag:

   • Use Case: Required if MySQL config files (e.g., /etc/my.cnf) are misconfigured.
   • Risk: May load unintended settings. Prefer explicit credentials.

2. Table Wildcards:

   • Issue: wp db tables 'wp_user*' may not work as expected with custom prefixes.
   • Fix: Use --all-tables-with-prefix for consistency.

3. Large Exports:

   • Tip: Stream exports to avoid memory issues:

     wp db export --single-transaction --quick backup.sql