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 = 'mail@example.com';

$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' => 'john.doe@example.com',
];

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

Retrieve the last inserted id:

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

Update

Update a record:

$values = ['email' => 'new@example.com'];

$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