php-database_skill

Overview

This skill teaches practical PHP database mastery across PDO, Eloquent, Doctrine, query optimization, schema design, migrations, and transaction safety. It provides concrete patterns, code examples, retry strategies, and performance guidance for MySQL, PostgreSQL, and SQLite. The content is organized by beginner to advanced modules and emphasizes safe, efficient production-ready techniques.

How this skill works

The skill inspects the selected topic and level to deliver targeted guidance: PDO connection patterns, prepared statements and transactions; ORM usage for Eloquent or Doctrine; optimization tactics like indexing and EXPLAIN; and migration/versioning workflows. It includes retry and error-handling strategies, sample code for common operations, and troubleshooting steps for N+1 queries, deadlocks, and memory pressure.

When to use it

• Building new PHP database access layers with PDO or an ORM
• Improving performance of slow queries and reducing N+1 issues
• Designing and applying safe migrations and schema changes
• Implementing transactional workflows with retry and deadlock handling
• Refactoring memory-heavy batch jobs into chunked or cursor-based processing

Best practices

• Always use prepared statements and set PDO error mode to exceptions
• Prevent N+1 by eager loading and selecting minimal columns
• Add and analyze indexes with EXPLAIN before and after changes
• Wrap critical work in transactions and implement exponential backoff for retryable deadlocks
• Process large datasets with chunk() or cursor() to avoid memory exhaustion

Example use cases

• Create a PDO-based repository with secure prepared statements and lastInsertId handling
• Optimize a report query by adding composite indexes and verifying the plan with EXPLAIN
• Refactor Eloquent code to eliminate N+1 by using with() and specific column selection
• Implement migrations for schema changes and a rollback plan for production deploys
• Add transaction retry logic that detects deadlocks and retries with exponential backoff