Slim 4 - Doctrine DBAL
Daniel Opitz
05 Dec 2019
Table of contents
- Requirements
- Introduction
- Installation
- Repository
- Usage
- Handling relationships
- Transactions
- Read more
Requirements
- PHP 7.1+
- MySQL 5.7+
- Composer
- A Slim 4 application
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;
}