Query Guidelines

Best Practices for Database Queries

SelectRaw Usage

• Prefer avoiding selectRaw when it doesn't impact performance
• Use selectRaw only when it provides significant performance benefits
• Always consider the performance implications before using selectRaw

Query Builder Usage

• Avoid using DB::table and similar direct query builder methods in the source code (except in migrations)
• Only use direct query builder methods when there's absolutely no alternative
• Discuss with the team before implementing any solution that requires direct query builder usage

General Guidelines

• Always use prepared statements to prevent SQL injection
• Keep queries as simple as possible while maintaining performance
• Consider using Eloquent ORM for most database operations
• Use query scopes for reusable query constraints
• Eager load relationships to prevent N+1 query problems
• Consider using database indexes for frequently queried columns
• Document complex queries with clear comments explaining their purpose

Performance Considerations

• Avoid using * in SELECT statements; specify only needed columns
• Use chunk() for processing large datasets
• Consider using database transactions for multiple related operations
• Be mindful of memory usage when working with large result sets
• Use cursor() for memory-efficient iteration through large result sets

Testing

• Write tests for complex queries to ensure they work as expected
• Consider performance testing for queries that will be executed frequently
• Test queries with realistic data volumes to identify potential performance issues