PHP/MVC/PDO - beginTransaction outside of Database class

✔ Recommended Answer

Some suggestions:

[a] You shouldn't create objects (with "new") inside class methods. Instead you should inject existent instances into constructors/setters. This is named dependency injection and can be applied with a dependency injection container.

• Dependency Injection and Dependency Inversion in PHP - James Mallison - PHPTour 2017 Nantes
• PHP-DI The dependency injection container for humans

[b] As @YourCommonSense noted, Database would greatly benefit from a single PDO instance, injected in the constructor. The injection task would be the job of the DI container. For example, if you'd use PHP-DI, there would be a definition entry for creating a database connection:

return [    'database-connection' => function (ContainerInterface $container) {        $parameters = $container->get('database.connection');        $dsn = $parameters['dsn'];        $username = $parameters['username'];        $password = $parameters['password'];        $connectionOptions = [            PDO::ATTR_EMULATE_PREPARES => false,            PDO::ATTR_PERSISTENT => false,            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        ];        $connection = new PDO($dsn, $username, $password, $connectionOptions);        return $connection;    },];

and another definition entry to inject it in Database:

return [    Database::class => autowire()        ->constructorParameter('connection', get('database-connection')),];

The Database contructor would look like:

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

[c] The model is not a class (like BookModel). It is a layer (model layer, or domain model), composed of multiple components: entities (or domain objects), value objects, data mappers, repositories, domain services. Your BookModel is a combination btw. an entity and a data mapper (at least). Note: inheriting it from Database is wrong, because a model can't be a database.

• How should a model be structured in MVC?
• The difference between domains, domain models, object models and domain objects

[d] You shouldn't inject models into controllers. Instead, controllers should use so-called application services (also named use cases, or actions, or interactors). These services contain the so-called application logic and are the proper way to decouple the presentation layer (or delivery mechanism) - which, among other components, includes the controllers and the views - from the domain model. The application services also assure the communication btw. the two layers. Note: there could also be domain services, specific to the domain and separated from the application services, which are specific to the application.

• Sandro Mancuso : Crafted Design
• Ruby Midwest 2011 - Keynote: Architecture the Lost Years by Robert Martin
• Robert "Uncle Bob" Martin - Architecture: The Lost Years
• How should a model be structured in MVC?

[e] Database class is not needed at all! You already have the very elegant & powerful PDO at disposal, to handle the database operations.

[f] Actually, it is not wrong to "call the method that calls the method that calls the PDO method". Each method in this chain encapsulates a certain behavior, specific to the current object. Though, the functionality of each method should add some plus value. Otherwise, it wouldn't make sense to have this chain, indeed. An example: In an application service, you can directly use a data mapper to fetch a book by id from the database:

class FindBooksService {    public function __construct(        private BookMapper $bookMapper    ) {    }    public function findBookById(?int $id = null): ?Book {        return $this->bookMapper->fetchBookById($id);    }}class BookMapper {    public function __construct(        private PDO $connection    ) {            }    public function fetchBookById(?int $id): ?Book {        $sql = 'SELECT * FROM books WHERE id = :id LIMIT 1';        // Fetch book data from database; convert the record to a Book object ($book).        //...        return $book;    }}

Now, you could use a repository instead, to hide even the fact that the queried data comes from a database. This makes sense, since a repository object is seen as a collection of objects of a certain type (here Book) by other components. Therefore, the other components think that the repository is a collection of books, not a bunch of data in some database, and they ask the repository for them correspondingly. The repository will, in turn, interogate the data mapper to query the database. So, the previous code becomes:

class FindBooksService {    /**     * @param BookCollection $bookCollection The repository: a collection of books, e.g. of Book instances.     */    public function __construct(        private BookCollection $bookCollection    ) {    }    public function findBookById(?int $id = null): ?Book {        return $this->bookCollection->findBookById($id);    }}class BookCollection {    private array $books = [];    public function __construct(        private BookMapper $bookMapper    ) {            }    /**     * This method adds a plus value to the omolog method in the data mapper (fetchBookById):     * - caches the Book instances in the $books list, therefore reducing the database querying operations;     * - hides the fact, that the data comes from a database, from the external world, e.g. other components.     * - provides an elegant collection-like interface.     */    public function findBookById(?int $id): ?Book {        if (!array_key_exists($id, $this->books)) {            $book = $this->bookMapper->fetchBookById($id);                        $this->books[id] = $book;        }        return $this->books[$id];    }}class BookMapper {    // the same...}

[g] A "real" mistake would be to pass an object through other objects, just to be used by the last object.

Alternative example code:

I wrote some code as an alternative to yours. I hope it will help you better understand, how the components of an MVC-based application could work together.

Important: Notice the namespace SampleMvc/Domain/Model/: that's the domain model. Note that the application services, e.g. all components from SampleMvc/App/Service/, should communicate ONLY with the domain model components, e.g. with the components from SampleMvc/Domain/Model/ (mostly interfaces), not from SampleMvc/Domain/Infrastructure/. In turn, the DI container of your choice will take care of injecting the proper class implementations from SampleMvc/Domain/Infrastructure/ for the interfaces of SampleMvc/Domain/Model/ used by the application services.

Notice the method updateBook() in SampleMvc/Domain/Infrastructure/Book/PdoBookMapper.php. I included a transaction code in it, along with two great links. Have fun.

Project structure:

Let's assume the definition of the following routes (maybe found in a "routes.php" file):

<?php// Adds/updates a book record in the database.$routeCollection->post('/books/add', [    'controller' => SampleMvc\App\Controller\Book\AddBook::class,    'view' => SampleMvc\App\View\Book\AddBook::class,]);// Finds books by author name.$routeCollection->get('/books/find/{authorName}', [SampleMvc\App\View\Book\FindBooks::class, 'findBooksByAuthorName']);

All objects of the application should be created by a dependency injection container.

Though, without it, the code for creating and calling the controller and/or the view could look like this:

File "index.php" in case a "POST" request for adding a book is performed:

<?phpuse SampleMvc\App\{    Controller\Book\AddBook as AddBookController,    View\Book\AddBook as AddBookView,};// [...]// Controller needed for updating the domain model.// View needed for querying the domain model and bulding the response.$addBookView = new AddBookView($responseFactory, $templateRenderer);$addBookController = new AddBookController($addBookView, $addBookService);$addBookController($request);$response = $addBookView();// Present the response to the user.$responseEmitter->emit($response);

File "index.php" in case a "GET" request for finding a list of books is performed:

<?phpuse SampleMvc\App\View\Book\FindBooks as FindBooksView;// [...]// No controller needed for querying the domain model !!!// Only view needed for querying the domain model and building the response.$findBooksView = new FindBooksView($findBooksService, $responseFactory, $templateRenderer);$response = $findBooksView->findBooksByAuthorName($authorName);// Present the response to the user.$responseEmitter->emit($response);

SampleMvc/App/Controller/Book/AddBook.php:

<?phpnamespace SampleMvc\App\Controller\Book;use SampleMvc\App\Service\Book\{    AddBook as AddBookService,    Exception\BookAlreadyExists,};use Psr\Http\Message\ServerRequestInterface;use SampleMvc\App\View\Book\AddBook as AddBookView;/** * A controller for adding a book. * Let's assume the existence of this route definition: *  * $routeCollection->post('/books/add', [ *  'controller' => SampleMvc\App\Controller\Book\AddBook::class, *  'view' => SampleMvc\App\View\Book\AddBook::class, * ]); */class AddBook {    /**     * @param AddBookView $view The view for presenting the response to the request back to the user.     * @param AddBookService $addBookService An application service for adding a book to the model layer.     */    public function __construct(        private AddBookView $view,        private AddBookService $addBookService    ) {            }    /**     * Add a book.     *      * The book details are submitted from a form, using the HTTP method "POST".     *      * @param ServerRequestInterface $request A server request.     * @return void     */    public function __invoke(ServerRequestInterface $request): void {        $authorName = $request->getParsedBody()['authorName'];        $title = $request->getParsedBody()['title'];        try {            $this->addBookService($authorName, $title);        } catch (BookAlreadyExists $exception) {            $this->view->setErrorMessage(                $exception->getMessage()            );        }    }}

SampleMvc/App/View/View.php:

<?phpnamespace SampleMvc\App\View;use Psr\Http\Message\ResponseFactoryInterface;use SampleLib\Template\Renderer\TemplateRendererInterface;/** * View. */abstract class View {    /** @var string The error message */    protected string $errorMessage = '';    /**     * @param ResponseFactoryInterface $responseFactory Response factory.     * @param TemplateRendererInterface $templateRenderer Template renderer.     */    public function __construct(        protected ResponseFactoryInterface $responseFactory,        protected TemplateRendererInterface $templateRenderer    ) {            }    /**     * Set the error message.     *      * @param string $errorMessage An error message.     * @return static     */    public function setErrorMessage(string $errorMessage): static {        $this->errorMessage = $errorMessage;        return $this;    }}

SampleMvc/App/View/Book/AddBook.php:

<?phpnamespace SampleMvc\App\View\Book;use SampleMvc\App\View\View;use Psr\Http\Message\ResponseInterface;/** * A view for adding a book. * Let's assume the existence of this route definition: *  * $routeCollection->post('/books/add', [ *  'controller' => SampleMvc\App\Controller\Book\AddBook::class, *  'view' => SampleMvc\App\View\Book\AddBook::class, * ]); */class AddBook extends View {    /**     * Add a book.     *      * @return ResponseInterface The response to the current request.     */    public function _invoke(): ResponseInterface {        $bodyContent = $this->templateRenderer->render('@Templates/Book/AddBook.html.twig', [            'error' => $this->errorMessage,        ]);        $response = $this->responseFactory->createResponse();        $response->getBody()->write($bodyContent);        return $response;    }}

SampleMvc/App/View/Book/FindBooks.php:

<?phpnamespace SampleMvc\App\View\Book;use SampleMvc\App\{    View\View,    Service\Book\FindBooks as FindBooksService,};use Psr\Http\Message\{    ResponseInterface,    ResponseFactoryInterface,};use SampleLib\Template\Renderer\TemplateRendererInterface;/** * A view for finding books. * Let's assume the existence of this route definition: *  * $routeCollection->get('/books/find/{authorName}', [SampleMvc\App\View\Book\FindBooks::class, 'findBooksByAuthorName']); */class FindBooks extends View {    /**     * @param FindBooksService $findBooksService An application service for finding books by querying the model layer.     */    public function __construct(        private FindBooksService $findBooksService,        ResponseFactoryInterface $responseFactory,        TemplateRendererInterface $templateRenderer,    ) {        parent::__construct($responseFactory, $templateRenderer);    }    /**     * Find books by author name.     *      * The author name is provided by clicking on a link of some author name      * in the browser. The author name is therefore sent using the HTTP method      * "GET" and passed as argument to this method by a route dispatcher.     *      * @param string|null $authorName (optional) An author name.     * @return ResponseInterface The response to the current request.     */    public function findBooksByAuthorName(?string $authorName = null): ResponseInterface {        $books = $this->findBooksService->findBooksByAuthorName($authorName);        $bodyContent = $this->templateRenderer->render('@Templates/Book/FindBooks.html.twig', [            'books' => $books,        ]);        $response = $this->responseFactory->createResponse();        $response->getBody()->write($bodyContent);        return $response;    }}

SampleMvc/App/Service/Book/AddBook.php:

<?phpnamespace SampleMvc\App\Service\Book;use SampleMvc\Domain\Model\Book\{    Book,    BookMapper,};use SampleMvc\App\Service\Book\Exception\BookAlreadyExists;/** * An application service for adding a book. */class AddBook {    /**     * @param BookMapper $bookMapper A data mapper for transfering books      * to and from a persistence system.     */    public function __construct(        private BookMapper $bookMapper    ) {            }    /**     * Add a book.     *      * @param string|null $authorName An author name.     * @param string|null $title A title.     * @return void     */    public function __invoke(?string $authorName, ?string $title): void {        $book = $this->createBook($authorName, $title);        $this->storeBook($book);    }    /**     * Create a book.     *      * @param string|null $authorName An author name.     * @param string|null $title A title.     * @return Book The newly created book.     */    private function createBook(?string $authorName, ?string $title): Book {        return new Book($authorName, $title);    }    /**     * Store a book.     *      * @param Book $book A book.     * @return void     * @throws BookAlreadyExists The book already exists.     */    private function storeBook(Book $book): void {        if ($this->bookMapper->bookExists($book)) {            throw new BookAlreadyExists(                    'A book with the author name "' . $book->getAuthorName() . '" '                    . 'and the title "' . $book->getTitle() . '" already exists'            );        }        $this->bookMapper->saveBook($book);    }}

SampleMvc/App/Service/Book/FindBooks.php:

<?phpnamespace SampleMvc\App\Service\Book;use SampleMvc\Domain\Model\Book\{    Book,    BookMapper,};/** * An application service for finding books. */class FindBooks {    /**     * @param BookMapper $bookMapper A data mapper for transfering books      * to and from a persistence system.     */    public function __construct(        private BookMapper $bookMapper    ) {            }    /**     * Find a book by id.     *      * @param int|null $id (optional) A book id.     * @return Book|null The found book, or null if no book was found.     */    public function findBookById(?int $id = null): ?Book {        return $this->bookMapper->fetchBookById($id);    }    /**     * Find books by author name.     *      * @param string|null $authorName (optional) An author name.     * @return Book[] The found books list.     */    public function findBooksByAuthorName(?string $authorName = null): array {        return $this->bookMapper->fetchBooksByAuthorName($authorName);    }}

SampleMvc/App/Service/Book/Exception/BookAlreadyExists.php:

<?phpnamespace SampleMvc\App\Service\Book\Exception;/** * An exception thrown if a book already exists. */class BookAlreadyExists extends \OverflowException {    }

SampleMvc/Domain/Infrastructure/Book/PdoBookMapper.php:

<?phpnamespace SampleMvc\Domain\Infrastructure\Book;use SampleMvc\Domain\Model\Book\{    Book,    BookMapper,};use PDO;/** * A data mapper for transfering Book entities to and from a database. *  * This class uses a PDO instance as database connection. */class PdoBookMapper implements BookMapper {    /**     * @param PDO $connection Database connection.     */    public function __construct(        private PDO $connection    ) {            }    /**     * @inheritDoc     */    public function bookExists(Book $book): bool {        $sql = 'SELECT COUNT(*) as cnt FROM books WHERE author_name = :author_name AND title = :title';        $statement = $this->connection->prepare($sql);        $statement->execute([            ':author_name' => $book->getAuthorName(),            ':title' => $book->getTitle(),        ]);        $data = $statement->fetch(PDO::FETCH_ASSOC);        return ($data['cnt'] > 0) ? true : false;    }    /**     * @inheritDoc     */    public function saveBook(Book $book): Book {        if (isset($book->getId())) {            return $this->updateBook($book);        }        return $this->insertBook($book);    }    /**     * @inheritDoc     */    public function fetchBookById(?int $id): ?Book {        $sql = 'SELECT * FROM books WHERE id = :id LIMIT 1';        $statement = $this->connection->prepare($sql);        $statement->execute([            'id' => $id,        ]);        $record = $statement->fetch(PDO::FETCH_ASSOC);        return ($record === false) ?            null :            $this->convertRecordToBook($record)        ;    }    /**     * @inheritDoc     */    public function fetchBooksByAuthorName(?string $authorName): array {        $sql = 'SELECT * FROM books WHERE author_name = :author_name';        $statement = $this->connection->prepare($sql);        $statement->execute([            'author_name' => $authorName,        ]);        $recordset = $statement->fetchAll(PDO::FETCH_ASSOC);        return $this->convertRecordsetToBooksList($recordset);    }    /**     * Update a book.     *      * This method uses transactions as example.     *      * Note: I never worked with transactions, but I      * think the code in this method is not wrong.     *      * @link https://phpdelusions.net/pdo#transactions (The only proper) PDO tutorial: Transactions     * @link https://phpdelusions.net/pdo (The only proper) PDO tutorial     * @link https://phpdelusions.net/articles/error_reporting PHP error reporting     *      * @param Book $book A book.     * @return Book The updated book.     * @throws \Exception Transaction failed.     */    private function updateBook(Book $book): Book {        $sql = 'UPDATE books SET author_name = :author_name, title = :title WHERE id = :id';        try {            $this->connection->beginTransaction();            $statement = $this->connection->prepare($sql);            $statement->execute([                ':author_name' => $book->getAuthorName(),                ':title' => $book->getTitle(),                ':id' => $book->getId(),            ]);            $this->connection->commit();        } catch (\Exception $exception) {            $this->connection->rollBack();            throw $exception;        }        return $book;    }    /**     * Insert a book.     *      * @param Book $book A book.     * @return Book The newly inserted book.     */    private function insertBook(Book $book): Book {        $sql = 'INSERT INTO books (author_name, title) VALUES (:author_name, :title)';        $statement = $this->connection->prepare($sql);        $statement->execute([            ':author_name' => $book->getAuthorName(),            ':title' => $book->getTitle(),        ]);        $book->setId(            $this->connection->lastInsertId()        );        return $book;    }    /**     * Convert the given record to a Book instance.     *      * @param array $record The record to be converted.     * @return Book A Book instance.     */    private function convertRecordToBook(array $record): Book {        $id = $record['id'];        $authorName = $record['author_name'];        $title = $record['title'];        $book = new Book($authorName, $title);        $book->setId($id);        return $book;    }    /**     * Convert the given recordset to a list of Book instances.     *      * @param array $recordset The recordset to be converted.     * @return Book[] A list of Book instances.     */    private function convertRecordsetToBooksList(array $recordset): array {        $books = [];        foreach ($recordset as $record) {            $books[] = $this->convertRecordToBook($record);        }        return $books;    }}

SampleMvc/Domain/Model/Book/Book.php:

<?phpnamespace SampleMvc\Domain\Model\Book;/** * Book entity. */class Book {    /**     * @param string|null $authorName (optional) The name of an author.     * @param string|null $title (optional) A title.     */    public function __construct(        private ?string $authorName = null,        private ?string $title = null    ) {            }    /**     * Get id.     *      * @return int|null     */    public function getId(): ?int {        return $this->id;    }    /**     * Set id.     *      * @param int|null $id An id.     * @return static     */    public function setId(?int $id): static {        $this->id = $id;        return $this;    }    /**     * Get the author name.     *      * @return string|null     */    public function getAuthorName(): ?string {        return $this->authorName;    }    /**     * Set the author name.     *      * @param string|null $authorName The name of an author.     * @return static     */    public function setAuthorName(?string $authorName): static {        $this->authorName = $authorName;        return $this;    }    /**     * Get the title.     *      * @return string|null     */    public function getTitle(): ?string {        return $this->title;    }    /**     * Set the title.     *      * @param string|null $title A title.     * @return static     */    public function setTitle(?string $title): static {        $this->title = $title;        return $this;    }}

SampleMvc/Domain/Model/Book/BookMapper.php:

<?phpnamespace SampleMvc\Domain\Model\Book;use SampleMvc\Domain\Model\Book\Book;/** * An interface for various data mappers used to  * transfer Book entities to and from a persistence system. */interface BookMapper {    /**     * Check if a book exists.     *      * @param Book $book A book.     * @return bool True if the book exists, false otherwise.     */    public function bookExists(Book $book): bool;    /**     * Save a book.     *      * @param Book $book A book.     * @return Book The saved book.     */    public function saveBook(Book $book): Book;    /**     * Fetch a book by id.     *      * @param int|null $id A book id.     * @return Book|null The found book, or null if no book was found.     */    public function fetchBookById(?int $id): ?Book;    /**     * Fetch books by author name.     *      * @param string|null $authorName An author name.     * @return Book[] The found books list.     */    public function fetchBooksByAuthorName(?string $authorName): array;}

Source: stackoverflow.com

Answered By: dakis

Method #2

In a typical MVC architecture, the database functionality is usually encapsulated within a Database class that provides methods for interacting with the database, including connecting to the database, executing queries, and handling transactions. It's generally considered good practice to keep the database functionality separate from the rest of your application logic, which makes it easier to maintain and test.

If you want to use transactions in your PHP/MVC/PDO application, you should create a method for beginning a transaction within your Database class. This method should create a PDO object and call the beginTransaction method on it. Here's an example of how this might look:

php
class Database {
    private $pdo;

    public function __construct($host, $db, $user, $pass) {
        $dsn = "mysql:host=$host;dbname=$db;charset=utf8mb4";
        $this->pdo = new PDO($dsn, $user, $pass);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    public function beginTransaction() {
        $this->pdo->beginTransaction();
    }

    // ... other database methods ...
}

Once you have a beginTransaction method in your Database class, you can call it from your application logic to start a transaction. For example:

php
$db = new Database('localhost', 'mydb', 'myuser', 'mypass');
$db->beginTransaction();

// Perform some database operations within the transaction
// ...

$db->commit(); // Commit the transaction

Note that you should always call commit or rollback after starting a transaction to ensure that the transaction is properly ended, even if there is an error.