Repositories
Repositories form the data access layer in MinistryHub, providing a clean abstraction between controllers and the database. They encapsulate all SQL queries and use PDO prepared statements for security.Architecture Pattern
The Repository Pattern separates business logic from data access: Benefits:- Controllers focus on routing and permissions
- Repositories handle SQL complexity
- Easy to mock for testing
- Database queries are centralized and reusable
Repository Structure
All repositories follow this pattern:Key Characteristics
- Static Methods: All repository methods are static (no need to instantiate)
- Type Safety: Use strict types where possible
- Error Handling: Wrap queries in try-catch blocks
- Logging: Log errors for debugging without breaking the app
- PDO Prepared Statements: ALWAYS use parameterized queries
Multi-Database Access
MinistryHub uses two separate databases:Main Database (User Data)
user_accounts- Login credentialsmember- User profileschurch- Church organizationsareas- Ministry areasteams- Service teamsroles- Permission rolesservices- Hub services (worship, social media, etc.)
Music Database (Content Data)
songs- Song libraryplaylists- Setlists for servicesinstruments- Musical instrumentssong_edits- Pending song change proposals
- Performance: Independent scaling and optimization
- Security: Isolate sensitive user data from public content
- Flexibility: Different backup/replication strategies
UserRepo: Complex Example
TheUserRepo demonstrates advanced patterns:
Advanced Patterns Demonstrated
- Eager Loading: Load related data in one method call
- Transactions: Ensure atomicity for multi-table operations
- Dynamic SQL: Build INSERT statements for variable-length arrays
- Cascading Deletes: Handle foreign key relationships manually
- Password Hashing: Use PHP’s
password_hash()for security
Song Edit Moderation Flow
TheSongRepo implements a moderation workflow where collaborators propose edits that leaders must approve:
- Collaborator proposes edit → Stored as JSON in
song_editstable - Leader reviews pending edits → Fetched via
listPendingEdits() - Leader approves → Data applied to
songstable - Leader rejects → Edit marked as rejected, no changes applied
Query Optimization Techniques
1. Use Indexes
Ensure frequently queried columns have indexes:2. Avoid N+1 Queries
❌ Bad: Query in a loop3. Use LIMIT for Large Tables
4. Cache Expensive Queries
Security Best Practices
1. ALWAYS Use Prepared Statements
❌ NEVER DO THIS (SQL Injection vulnerability):2. Validate Data Types
3. Use Whitelists for Dynamic SQL
4. Hash Passwords Properly
Transaction Management
Use transactions for operations that modify multiple tables:- Use transactions when multiple operations must succeed/fail together
- Always wrap in try-catch
- Always rollback on exception
- Keep transactions short (lock tables briefly)
Repository Testing
Test repositories using a test database:Common Repository Methods
Most repositories implement these standard methods:| Method | Purpose | Returns |
|---|---|---|
getAll($filter) | Fetch all records | array |
findById($id) | Fetch single record | array | null |
add($data) | Insert new record | int (new ID) |
update($id, $data) | Update record | bool |
delete($id) | Remove record | bool |
count($filter) | Count records | int |
exists($id) | Check if exists | bool |
Next Steps
Controllers
Learn how controllers use repositories
PHP Architecture
Understand the complete backend structure