Slim 4 - Laminas Query Builder

Daniel Opitz
Daniel Opitz
01 Dec 2019

Table of contents

Requirements

Introduction

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

Installation

To install the laminas/laminas-db package, run:

composer require laminas/laminas-db

Configuration

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

// Database settings
$settings['db'] = [
    'driver' => 'Pdo_Mysql',
    'hostname' => 'localhost',
    'username' => 'root',
    'database' => 'test',
    'password' => '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'driver_options' => [
        // 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'
    ],
];

Add the following container definitions, e.g. in config/container.php:

<?php

use Psr\Container\ContainerInterface;
use Slim\App;
use Slim\Factory\AppFactory;
use Laminas\Db\Adapter\Adapter;
use Laminas\Db\Adapter\AdapterInterface;

return [

    // ...
    
    AdapterInterface::class => function (ContainerInterface $container) {
        return new Adapter($container->get('settings')['db']);
    },

    PDO::class => function (ContainerInterface $container) {
        $connection = $container->get(AdapterInterface::class)
            ->getDriver()
            ->getConnection();

        $connection->connect();

        return $connection->getResource();
    },
];

Add the QueryFactory class into: src/Factory/QueryFactory.php and copy / paste this content:

<?php

namespace App\Factory;

use Laminas\Db\Adapter\AdapterInterface;
use Laminas\Db\ResultSet\ResultSet;
use Laminas\Db\TableGateway\TableGateway;

final class QueryFactory
{
    /**
     * @var AdapterInterface The database connection
     */
    private $adapter;

    public function __construct(AdapterInterface $adapter)
    {
        $this->adapter = $adapter;
    }

    public function table(string $table): TableGateway
    {
        return new TableGateway(
            $table, 
            $this->adapter,
            null, 
            new ResultSet(ResultSet::TYPE_ARRAY)
        );
    }
}

Repository

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

<?php

namespace App\Domain\User\Repository;

use App\Factory\QueryFactory;

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

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

    // ...

}

Usage

Select

Fetch all rows:

$rows = $this->queryFactory->table('users')->select( /* where */ );

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

A complex query:

$table = $this->queryFactory->table('users');

$select = $table->getSql()->select();
$select->columns(['id']);
$select->where(['id' => 1]);

$rows = $table->selectWith($select);

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

Note that the result of a query is an instance of ResultSet that will expose each row as either an ArrayObject-like object or an array of row data.

Select only the first row:

$row = $this->queryFactory->table('users')
    ->select(['id' => 1])->current();

Read more: Select

Insert

Insert a record:

$table = $this->queryFactory->table('users');
$table->insert($values);

$newId = (int)$table->getLastInsertValue();

Read more: Insert

Update

Update a record

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

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

Read more: Update

Delete

Delete a record:

$this->queryFactory->table('users')->delete(['id' => 1]);

Read more: Delete

Handling relationships

You can define relationships directly with a join clause.

use Laminas\Db\Sql\Join;
// ...

$table = $this->queryFactory->table('users');

$select = $table->getSql()->select();

$select->columns(['id']);
$select->where(['id' => 1]);

$select->join('contacts', 'users.id = contacts.user_id');
$select->join('orders', 'users.id = orders.user_id', Join::JOIN_LEFT);

$rows = $table->selectWith($select);

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;
}