Store or where to store database queries. (PHP)

And so, sooner or later, all developers, I want to believe it, have a question on the agenda: "But where can I still store queries to the database?". The simplest answer to this question, given that we live in the world of MVC — in models or in controllers, but this answer is as simple as it is short-sighted.

Queries in models. In general, if we consider models that implement the ActiveRecord template, and these are models in the overwhelming number of frameworks, then the main disadvantage of such models will be their hyper-responsiveness. That is, looking into the model, we can see that it is an object of business data, rules and logic, which by definition violates a bunch of principles of writing code.

Requests in controllers. I think this option is the worst, because by inflating your controllers, you make your system more difficult to maintain, not to mention that your code will violate a bunch of principles (greetings from the principle of unified responsibility). In addition, by writing a request directly in the controller method, you deprive yourself of the opportunity to re-use the request in other places.
So, in order to avoid such a mess in the code and to spread the areas of responsibility, it is just worth using store.

Example in pure PHP

<?php

class ArticleStore {

    private string $tableName = 'articles';
    private $connection;

    public function __construct($connection)
    {
        $this->connection = $connection;
    }

    public function findAll()
    {
        $query = "SELECT * FROM {$this->tableName}";
        $stmt = $this->connection->prepare($query);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function findById(int $id)
    {
        $query = "SELECT * FROM {$this->tableName} WHERE id=:id";
        $stmt = $this->connection->prepare($query);
        $stmt = $stmt->bindParam(':id', $id);
        $stmt->execute();

        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
}

The ArticleStore class contains methods for making queries to the articles table. Note that we use prepared queries, which makes the code more secure and prevents SQL injection.

Let's analyze the advantages of comparing with writing database queries in models or controllers that you will get when using store in your project:

Re-use of queries. You can create queries in the store and then use them in different parts of your app, without having to re-write the code. This not only saves time, but also helps to maintain uniformity in your code.

Improved scalability. Using the store also allows you to make your app more scalable. You can easily replace a database or an implementation without rewriting a lot of code, in the first case you will need to overturn the necessary connection, and in the second store corresponding to the necessary interface.

Improving code readability. store allows you to make the code more readable and maintainable, since database queries are placed in a separate abstraction layer, which simplifies understanding the application logic.

Simplified search by project. Since all your application's database accesses occur through the store, we can always track where a particular request is used in the application, since all requests are presented in the form of store methods, and modern IDE have a wonderful search in the code of places where class methods are used.

Improved modularity because data access logic is separated from business logic.

Simplified testing thanks to the ability to replace real data with mock data in the store.

As a result, using store for database queries allows you to improve the architecture of your application, simplify its maintenance and make the code cleaner and more supported.