Slim 4 - CakePHP Query Builder Setup

03 Dec 2019

Table of contents

Requirements

Introduction

You can use the CakePHP Query Builder to connect your Slim 4 application to a database.

Installation

To add the cakephp/database package to your application, run:

composer require cakephp/database

Configuration

Add the database settings into your configuration file, e.g config/settings.php:

// Database settings
$settings['db'] = [
    'driver' => \Cake\Database\Driver\Mysql::class,
    'host' => 'localhost',
    'database' => 'database',
    'username' => 'root',
    'password' => '',
    'encoding' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    // Enable identifier quoting
    'quoteIdentifiers' => true,
    // Set to null to use MySQL servers timezone
    'timezone' => null,
    // PDO options
    'flags' => [
        // 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 Cake\Database\Connection;
use Psr\Container\ContainerInterface;
use Selective\Config\Configuration;
use Slim\App;
use Slim\Factory\AppFactory;

return [

    // ...
    
    // Database connection
    Connection::class => function (ContainerInterface $container) {
        return new Connection($container->get(Configuration::class)->getArray('db'));
    },

    PDO::class => function (ContainerInterface $container) {
        $db = $container->get(Connection::class);
        $driver = $db->getDriver();
        $driver->connect();

        return $driver->getConnection();
    },
];

Create a new PHP file: src/Repository/QueryFactory.php and copy / paste this content:

<?php

namespace App\Repository;

use Cake\Database\Connection;
use Cake\Database\Query;
use UnexpectedValueException;

/**
 * Factory.
 */
final class QueryFactory
{
    /**
     * @var Connection
     */
    private $connection;

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

    /**
     * Create a new query.
     *
     * @return Query The query
     */
    public function newQuery(): Query
    {
        return $this->connection->newQuery();
    }

    /**
     * Create a new 'select' query for the given table.
     *
     * @param string $table The table name
     *
     * @throws UnexpectedValueException
     *
     * @return Query A new select query
     */
    public function newSelect(string $table): Query
    {
        $query = $this->newQuery()->from($table);

        if (!$query instanceof Query) {
            throw new UnexpectedValueException('Failed to create query');
        }

        return $query;
    }

    /**
     * Create an 'update' statement for the given table.
     *
     * @param string $table The table to update rows from
     *
     * @return Query The new update query
     */
    public function newUpdate(string $table): Query
    {
        return $this->newQuery()->update($table);
    }

    /**
     * Create an 'update' statement for the given table.
     *
     * @param string $table The table to update rows from
     * @param array $data The values to be updated
     *
     * @return Query The new insert query
     */
    public function newInsert(string $table, array $data): Query
    {
        return $this->newQuery()->insert(array_keys($data))->into($table)->values($data);
    }

    /**
     * Create a 'delete' query for the given table.
     *
     * @param string $table The table to delete from
     *
     * @return Query A new delete query
     */
    public function newDelete(string $table): Query
    {
        return $this->newQuery()->delete($table);
    }
}

Repository

You can inject the query factory instance into your repository like this:

<?php

namespace App\Domain\User\Repository;

use App\Domain\User\Data\UserData;
use App\Repository\QueryFactory;
use App\Repository\TableName;
use Cake\Database\StatementInterface;

/**
 * Repository.
 */
class UserRepository
{
    /**
     * @var QueryFactory The query factory
     */
    private $queryFactory;

    /**
     * The constructor.
     *
     * @param QueryFactory $queryFactory The query factory
     */
    public function __construct(QueryFactory $queryFactory)
    {
        $this->queryFactory = $queryFactory;
    }

    // ...
}

Usage

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

Query all rows

$query = $this->queryFactory->newSelect('users')->select('*');
$rows = $query->execute()->fetchAll('assoc');

foreach ($rows as $row) {
    print_r($row);
}

Query the table with where

$query = $this->queryFactory->newSelect('users');

$query->select(['id', 'username']);
$select->andWhere(['id' => 1]);

$rows = $query->execute()->fetchAll('assoc');

foreach ($rows as $row) {
    print_r($row);
}

Query the table by id

$query = $this->queryFactory->newSelect('users')->andWhere(['id' => 1]);

$row = $query->execute()->fetch('assoc');

Insert a record

$values = [
    'first_name' => 'john',
    'last_name' => 'doe',
    'email' => 'john.doe@example.com',
];

$this->queryFactory->newInsert('users', $values)->execute();

Insert a record and get the last inserted id:

$newId = (int)$this->queryFactory->newInsert('users', $values)
    ->execute()
    ->lastInsertId();

Update a record

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

$this->queryFactory->newUpdate('users')
    ->set($values)
    ->andWhere(['id' => 1])
    ->execute();

Delete a record

$this->queryFactory->newDelete('users')
    ->andWhere(['id' => 1])
    ->execute();