README

The purposes of this package are:

• provide generic agnostic database tools for DDD and Clean architecture
• provide database migration with Phinx migration
• facilitate the integration of Laravel Database in non-laravel project

Installation

 composer require peak/database

Laravel Database Usage

use Peak\Database\Laravel\DatabaseService;

$config = [
    'driver' => 'mysql',
    'host' => 'localhost',
    'port' => '3306',
    'database' => 'database',
    'username' => 'root',
    'password' => 'root',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
];
    
$db = (new DatabaseService())->createConnection($config, 'connectionName');

That's it! Check out Laravel Query Builder for more info on how to make queries.

Database Migration Usage

For database migrations, create a file at the root of your project name phinx.php. This file should return an array of Phinx configuration.

<?php

namespace {

    use Peak\Database\Common\LaravelPhinxMigration;
    use Peak\Database\Laravel\LaravelDatabaseService;
    use Peak\Database\Laravel\LaravelConnectionManager;
    use Peak\Database\Phinx\PhinxConfigService;
    use Peak\Database\Phinx\PhinxEnvConfig;

    require __DIR__.'/vendor/autoload.php';

    try {
        $env = getenv();
        $db = (new LaravelDatabaseService())->createConnection([
           'driver' => $env['DB_DRIVER'],
           'host' => $env['DB_HOST'],
           'port' => $env['DB_PORT'],
           'database' => $env['DB_DATABASE'],
           'username' => $env['DB_USERNAME'],
           'password' => $env['DB_PASSWORD'],
           'charset' => $env['DB_CHARSET'],
           'collation' => $env['DB_COLLATION'],
           'prefix' => $env['DB_PREFIX'],
       ], 'connectionName');
        LaravelConnectionManager::setConnection($db, 'prod');

        return (new PhinxConfigService())
            ->create(
                'migrations',
                LaravelPhinxMigration::class,
                'migrations',
                'prod',
                [
                    new PhinxEnvConfig('prod', [
                        'name' => $db->getDatabaseName(),
                        'connection' => $db->getPdo(),
                    ])
                ]
            );

    } catch(\Exception $e) {
        die($e->getMessage());
    }
}

This phinx.php above will allow the usage of Laravel Database directly in your migrations with the help of LaravelPhinxMigration:

<?php

use Peak\Database\Laravel\LaravelPhinxMigration;
use Illuminate\Database\Schema\Blueprint;

class Users extends LaravelPhinxMigration
{
    public function up()
    {
        $this->db->getSchemaBuilder()->create('users', function(Blueprint $table){
            $table->increments('id');
            $table->string('username')->unique();
            $table->string('email')->unique();
            $table->string('password');
            $this->tsColumns($table);
            $table->timestamp('lastSeen')->nullable()->default(null);
        });
    }

    public function down()
    {
        $this->db->getSchemaBuilder()->drop('users');
    }
}

Generic tools

The purpose of generic tools is to help you express a query without being attached to a particular database framework or any database at all.

• Use QueryFilters to express "where" statements.
• Use QueryPagination to express pagination like order by and limit/offset statement.

It is important to note that those generic tools doesn't to do anything by themselves. They serve mainly as "boundary" interfaces between a domain or use case and your actual real database or storage implementation. In your final implementation, you will need builder/helper to translate generic expression to your actual database framework/orm.

Example of creating generic query "where" filters and generic query "pagination"

$queryFilters = new QueryFilters();
$queryFilters
    ->setColumns(['id', 'title'])
    ->where('level', '6', '>')
    ->orWhere('level', '2', '<')
    ->orWhereArray((new QueryFilters())
        ->where('status', 'online')
        ->where('type', '2')
        ->whereNull('ban')
        ->whereNotNull('deletedAt')
    );

$queryPagination = new QueryPagination(
    $column, 
    $direction, 
    $pageNumber, 
    $itemsPerPages
);

Pass the $queryFilters and $queryPagination to a use case. This will help to create a boundary between use cases and repositories because the use case doesn't have to know the details of your implementation (database framework/orm, etc)

<?php

namespace Domain\UseCase;

use Peak\Database\Generic\QueryFiltersInterface;
use Peak\Database\Generic\QueryPaginationInterface;

class MyUseCase 
{
    // ...
    public function execute(
        QueryFiltersInterface $queryFilters,
        QueryPaginationInterface $queryPagination
    ) {
        // do things
        // ...
       
        return $this->repository->getMany($queryFilters, $queryPagination);
    }
}

And finally, we use LaravelGenericHelper in our repository implementation to transform generic QueryFiltersInterface to actual laravel query builder "where" expressions;

<?php

use Domain\Repository\MyRepositoryInterface;
use Peak\Database\Generic\QueryFiltersInterface;
use Peak\Database\Generic\QueryPaginationInterface;
use Peak\Database\Common\LaravelGenericHelper;

class MyRepository implements MyRepositoryInterface
{
    // ...
    
   public function getMany(
       QueryFiltersInterface $queryFilters,
       QueryPaginationInterface $queryPagination
   ) {
       $qb = $this->table('tusers');
       $qb = LaravelGenericHelper::filterQuery($qb, $queryFilters);
       $qb = LaravelGenericHelper::paginateQuery($qb, $queryPagination);
       return $qb->get();
   }
}

We could simply use laravel query builder directly in our use case but this could also tie the code to much to specific database library (here laravel database). By using generic query filters and pagination, it becomes really easy to tests repository and use cases without a real database connection.

Important security information on pagination and filters with Laravel Database

From Laravel Database docs:

The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings. But:

"PDO does not support binding column names. Therefore, you should never allow user input to dictate the column names referenced by your queries, including "order by" columns, etc. If you must allow the user to select certain columns to query against, always validate the column names against a white-list of allowed columns."

If you let your user choose the column names, you should create a class that extends AbstractRestrictedQueryPagination to protected from unwanted column names.

class UserPagination extends AbstractRestrictedQueryPagination
{
    protected $allowedColumns = [
        'username', 'email', 'createdAt', 'updatedAt', 'deletedAt'
    ];
    
    protected $allowedDirections = [
        'asc', 'desc'
    ];
}


// and use it like this:

$queryPagination = new UserPagination(
    $column, 
    $direction, 
    $pageNumber, 
    $itemsPerPages
);

The same can be applied to query filters columns and operators with AbstractRestrictedQueryFilters:

class UserFilters extends AbstractRestrictedQueryFilters
{
    protected $allowedColumns = [
        'username', 'email', 'createdAt', 'updatedAt', 'deletedAt'
    ];
    
    protected $allowedOperators = [
        '=', '>', '<', 'like'
    ];
}


// and use it like this:

$queryFilters = new UserFilters();
$queryFilters
    ->where('username', 'bob', '=')
    //...