Slim 4 - Doctrine DBAL Setup

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 Selective\Config\Configuration;
use Slim\App;
use Slim\Factory\AppFactory;

return [

    // ...
    
    // Database connection
    Connection::class => function (ContainerInterface $container) {
        $config = new DoctrineConfiguration();
        $connectionParams = $container->get(Configuration::class)->getArray('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:

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 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 a record

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

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

Delete a record

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

Read more