Database and Query Best Practices

# CHAPTER 14

Database and Query Best Practices #

1. Introduction #

2. Learning Objectives #

• Understand why mixing business logic with database queries is a violation of SRP.

• Implement the "Repository Pattern" to abstract data access.

• Differentiate between Active Record and Data Mapper ORM patterns.

• Avoid the "N+1 Query Problem" (the most common ORM performance killer).

• Write clean, parameterized SQL queries to prevent security vulnerabilities.

3. The Repository Pattern #

• The Problem: UserService calls DB::query("SELECT * FROM users"). The service is tightly coupled to SQL.

• The Repository: Create an interface IUserRepository. Create a class MySQLUserRepository that implements it. The Service Layer only asks the Interface for data: $user = $userRepository->findById(5).

• The Benefit: You can switch from MySQL to MongoDB tomorrow by writing one new class, and the UserService code doesn't change a single line. It also allows you to easily Mock the database during Unit Testing.

4. ORM Best Practices (Object-Relational Mapping) #

• The Good: They massively speed up development for basic CRUD operations and handle security (SQL Injection prevention) automatically.

• The Bad: Developers treat ORMs like magic and forget that real SQL is happening underneath. This leads to horrific performance issues.

5. The N+1 Query Problem #

• The Mistake: You query 100 users. Then, in a loop, you ask the ORM for each user's profile.

$users = User::all(); // 1 Query
foreach($users as $user) {
    echo $user->profile->bio; // 100 Queries executed in the loop!
}

*Total Queries: 101.* This will crash the server under heavy load.

• The Clean Solution (Eager Loading): Tell the ORM to fetch everything up front.

$users = User::with('profile')->get(); // 2 Queries total!
foreach($users as $user) { echo $user->profile->bio; }

6. Clean SQL Queries #

• Formatting: Do not write a massive query on one line. Capitalize SQL keywords, indent clauses, and use line breaks.

SELECT 
    users.id, 
    users.name, 
    users.email 
FROM users 
JOIN orders 
    ON users.id = orders.user_id 
WHERE 
    status = 'active';

7. Diagrams/Visual Suggestions #

[ Controller ]
      |
[ UserService ] (Business Logic)
      |
[ IUserRepository ] (Interface / Abstraction Layer)
      |
      +---- [ MySQLUserRepository ] ---> (MySQL Database)
      |
      +---- [ MockUserRepository ] ----> (Used in Unit Tests)

8. Best Practices #

• Never Trust Input (Parameterization): When writing raw SQL, NEVER concatenate user input directly into the query string ("SELECT * FROM users WHERE name = '" . $input . "'"). This causes catastrophic SQL Injection attacks. Always use Parameterized Queries (Prepared Statements).

9. Common Mistakes #

• Database Logic Leakage: A developer uses an ORM to query data, but adds ->where('status', 'active') directly inside the Frontend HTML View template. The View now contains business logic and database knowledge. All data retrieval logic MUST remain in the Repository or Service layer. The View should only be handed a finalized array of data to render.

10. Mini Project: Build a Repository #

class ProductService {
    public function getActiveProducts() {
        // Tied directly to the Eloquent ORM
        return Product::where('status', 'active')->get(); 
    }
}

*After (Repository Pattern):*

interface ProductRepositoryInterface {
    public function findActive(): array;
}

class SqlProductRepository implements ProductRepositoryInterface {
    public function findActive(): array {
        return Product::where('status', 'active')->get();
    }
}

class ProductService {
    private $repository;
    public function __construct(ProductRepositoryInterface $repo) {
        $this->repository = $repo; // Decoupled!
    }
    public function getActiveProducts() {
        return $this->repository->findActive();
    }
}

11. Practice Exercises #

1. 1. Define the "N+1 Query Problem." Explain how Eager Loading solves this severe performance issue.

1. 2. Why is the Repository Pattern crucial for adhering to the Dependency Inversion Principle (DIP)?

12. MCQs with Answers #

13. Interview Questions #

• Q: You are reviewing code and notice an ORM is being called inside a foreach loop to fetch relational data. Explain the performance implications of this to the junior developer and how to refactor it.

• Q: Explain the difference between the "Active Record" ORM pattern (e.g., Laravel Eloquent) and the "Data Mapper" ORM pattern (e.g., Doctrine). Which one enforces stricter separation of concerns?

• Q: Why does Robert C. Martin state that "The Database is a Detail"? How should high-level business policies treat the database?

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #