58

Slim 4 - Doctrine DBAL

 3 years ago
source link: https://odan.github.io/2019/12/05/slim4-doctrine-dbal.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Daniel’s Dev Blog

Daniel's Dev Blog

Developer, Trainer, Open Source Contributor

Blog About me Donate

Slim 4 - Doctrine DBAL

Daniel Opitz

Daniel Opitz

05 Dec 2019

Table of contents

Requirements

Introduction

You can use a query builder such as Doctrine DBAL to connect your Slim 4 application to a database.

Installation

To add Doctrine DBAL to your application, run:

composer require doctrine/dbal

Configuration

Add the database settings to Slim’s settings array, e.g config/settings.php:

// Database settings
$settings['db'] = [
    'driver' => 'pdo_mysql',
    'host' => 'localhost',
    'dbname' => 'test',
    'user' => 'root',
    'password' => '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'driverOptions' => [
        // Turn off persistent connections
        PDO::ATTR_PERSISTENT => false,
        // Enable exceptions
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        // Emulate prepared statements
        PDO::ATTR_EMULATE_PREPARES => true,
        // Set default fetch mode to array
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        // Set character set
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
    ],
];

In your config/container.php or wherever you add your container definitions:

<?php

use Doctrine\DBAL\Configuration as DoctrineConfiguration;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DriverManager;
use Psr\Container\ContainerInterface;
use Slim\App;
use Slim\Factory\AppFactory;

return [

    // ...
    
    // Database connection
    Connection::class => function (ContainerInterface $container) {
        $config = new DoctrineConfiguration();
        $connectionParams = $container->get('settings')['db'];

        return DriverManager::getConnection($connectionParams, $config);
    },

    PDO::class => function (ContainerInterface $container) {
        return $container->get(Connection::class)->getWrappedConnection();
    },
];

Repository

You can inject the connection instance into your repository like this:

<?php

namespace App\Domain\User\Repository;

use Doctrine\DBAL\Connection;

class UserRepository
{
    /**
     * @var Connection The database connection
     */
    private $connection;

    /**
     * The constructor.
     *
     * @param Connection $connection The database connection
     */
    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

    // ...
}

Usage

Once the connection instance has been injected, you may use it like so:

Select

Query all rows:

$query = $this->connection->createQueryBuilder();

$rows = $query
    ->select('id', 'username')
    ->from('users')
    ->execute()
    ->fetchAll();

Query the table with where:

$userInputEmail = '[email protected]';

$query = $this->connection->createQueryBuilder();

$rows = $query
    ->select('id', 'username')
    ->from('users')
    ->where('email = :email')
    ->setParameter(':email', $userInputEmail)
    ->execute()
    ->fetchAll();

Query the table by id:

$query = $this->connection->createQueryBuilder();

$row = $query->select('id', 'username')
    ->from('users')
    ->where('id = :id')
    ->setParameter(':id', 1)
    ->execute()
    ->fetch();

Insert

Insert a record:

$values = [
    'first_name' => 'john',
    'last_name' => 'doe',
    'email' => '[email protected]',
];

$this->connection->insert('users', $values);

Retrieve the last inserted id:

$newId = $this->connection->lastInsertId();

Update

Update a record:

$values = ['email' => '[email protected]'];

$this->connection->update('users', $values, ['id' => 1]);

Delete

Delete a record

$this->connection->delete('users', ['id' => 1]);

Handling relationships

You can define relationships directly with a join clause.

$query = $this->connection->createQueryBuilder();

$rows = $query
    ->select('id', 'username')
    ->from('users')
    ->innerJoin('users', 'contacts', 'contacts', 'users.id = contacts.user_id')
    ->leftJoin('users', 'orders', 'orders', 'users.id = orders.user_id')
    ->execute()
    ->fetchAll();

Transactions

You should orchestrate all transactions in a service class. Please don’t use the transaction handler directly within a repository.

The transaction handling can be abstracted away with this interface:

<?php

namespace App\Database;

interface TransactionInterface
{
    public function begin(): void;
    public function commit(): void;
    public function rollback(): void;
}

Read more

© 2020 Daniel Opitz | Twitter


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK