DBAL Tools

This package provides a set of tools to work with the Doctrine DBAL.

Installation

composer require phpro/dbal-tools

The package can be used standalone or with Symfony. If you are not using symfony/flex, you'll have to manually add the bundle to your bundles file:

// config/bundles.php

return [
    // ...
    Phpro\DbalTools\DbalToolsBundle::class => ['all' => true],
];

Schema

This package contains a set of schema tools to configure your database schema from within PHP. You can configure following schema types:

Configuring a table

A table consists out of 2 parts:

The class that declared the table
An enum that declares the available table columns:

use Phpro\DbalTools\Column\TableColumnsInterface;
use Phpro\DbalTools\Column\TableColumnsTrait;

enum UsersTableColumns: string implements TableColumnsInterface
{
    case Id = 'user_id';
    case Username = 'username';

    use TableColumnsTrait;

    public function linkedTableClass(): string
    {
        return UsersTable::class;
    }
}

use Doctrine\DBAL\Schema\Table as DoctrineTable;
use Doctrine\DBAL\Types\Types;
use Phpro\DbalTools\Column\Columns;
use Phpro\DbalTools\Schema\Table;

final class UsersTable extends Table
{
    public static function name(): string
    {
        return 'users';
    }

    public static function columns(): Columns
    {
        return Columns::for(UsersTableColumns::class);
    }

    public static function createTable(): DoctrineTable
    {
        return DoctrineTable::editor()
            ->setUnquotedName(self::name())
            ->addColumn(
                Column::editor()
                    ->setUnquotedName(UsersTableColumns::Id->value)
                    ->setTypeName(Types::GUID)
                    ->setNotnull(true)
                    ->create()
            )
            ->addPrimaryKeyConstraint(
                PrimaryKeyConstraint::editor()
                    ->setUnquotedColumnNames(UsersTableColumns::Id->value)
                    ->create()
            )
            ->addColumn(
                Column::editor()
                    ->setUnquotedName(UsersTableColumns::Username->value)
                    ->setTypeName(Types::STRING)
                    ->setLength(255)
                    ->setNotnull(true)
                    ->create()
            )
            ->create();
    }
}

You can register this table configuration in your Symfony service configuration:

services:
    App\Dbal\Schema\UsersTable:
        tags:
            - 'phpro.dbal_tools.schema.table'

This way, it is available inside the Doctrine\DBAL\Schema\Schema service and can be used to create the table through migrations.

If you want to make sure that all columns are being used inside the table, you can add a PHPUnit TableColumnEnumTestCase for the implementation:

<?php

use Phpro\DbalTools\Test\Column\TableColumnEnumTestCase;

final class UsersTableColumnsTest extends TableColumnEnumTestCase
{
    public function className(): string
    {
        return UsersTableColumns::class;
    }
}

Configuring sequences

From time to time, you might need a sequence. This can be configured in the same way as a table:

use Phpro\DbalTools\Schema\Sequence;
use Doctrine\DBAL\Schema\Sequence as DoctrineSequence;

final class UserNumberSequence extends Sequence
{
    public static function name(): string
    {
        return 'user_number_seq';
    }

    public static function createSequence(): DoctrineSequence
    {
        return DoctrineSequence::editor()
            ->setUnquotedName(self::name())
            ->create();
    }
}

Next, add the sequence to your service configuration:

services:
    App\Dbal\Schema\UserNumberSequence:
        tags:
            - 'phpro.dbal_tools.schema.sequence'

Migrations

This package ships with doctrine/DoctrineMigrationsBundle. It links the schema configuration to the migration commands. This way, you can use doctrine:migrations, just like you would in a regular ORM based system.

./bin/console doctrine:migrations:diff
./bin/console doctrine:migrations:migrate

Building models and repositories

Since no ORM is being used, the main structure for building models and repositories would follow following pattern:

You can build your entities in raw PHP without any mapping limitations: (just using public props here for simplicity)

namespace App\Entity;

class User
{
    public function __construct(
        public string $id,
        public string $userName
    ) {
    }
}

A repository could look like this:

namespace App\Repository;

use App\Doctrine\Schema\UsersTable;
use App\Doctrine\Schema\UsersTableColumns;
use App\Entity\User;
use Doctrine\DBAL\Connection;
use Phpro\DbalTools\Expression\Comparison;
use Phpro\DbalTools\Expression\Factory\NamedParameter;

class UsersRepository
{
    public function __construct(private Connection $connection)
    {
    }

    public function findById(string $id): ?User
    {
        $qb = $this->connection->createQueryBuilder();
        $qb->select(...UsersTable::columns()->select())
            ->from(UsersTable::name())
            ->where(Comparison::equal(
                UsersTableColumns::Id,
                NamedParameter::createForTableColumn($qb, UsersTableColumns::Id, $id)
            )->toSQL());

        if (!$row = $qb->fetchAssociative()) {
            return null;
        }

        return new User(
            $row[UsersTableColumns::Id->value],
            $row[UsersTableColumns::Username->value],
        );
    }

    public function create(User $user): void
    {
        $this->connection->insert(
            UsersTable::name(),
            [
                UsersTableColumns::Id->value => $user->id,
                UsersTableColumns::Username->value => $user->userName,
            ],
            UsersTable::columnTypes()
        );
    }
}

As you can see, you can use the schema configuration to build your queries. If any of the table names or column names change, you will only have to change it in one place. In a real application, you can split up a data mapper that maps the entity to the table and back. That way, you have full control on how the data is made available in the entity.

Persisting relations

Inside your model, you might have relations to other models. For example, a User can be linked to multiple companies. You can use the Phpro\DbalTools\CollectionPatchCollection to detect changes in a collection of related entities and deal with the change accordingly:

use Phpro\DbalTools\CollectionPatchCollection;

$patchCollection = new CollectionPatchCollection();
$this->patchCollection->patch(
    newCollection: $user->companies(),
    previousCollection: $previous?->companies() ?? new Companies(),
    idProvider: static fn (Company $company) => $company->id(),
    insert: fn (Company $company) => $this->connection->insert(
        UserCompaniesTableColumn::name(),
        [
            UserCompaniesTableColumn::UserId->value => $user->id(),
            UserCompaniesTableColumn::CompanyId->value => $company->id(),
        ],
        UserCompaniesTableColumn::columnTypes()
    ),
    update: fn (Company $company) => null,
    delete: fn (Company $company) => $this->connection->delete(
        UserCompaniesTableColumn::name(),
        [
            UserCompaniesTableColumn::UserId->value => $user->id(),
            UserCompaniesTableColumn::CompanyId->value => $company->id(),
        ],
        UserCompaniesTableColumn::columnTypes()
    )
);

Fixtures

This package contains a command to load fixtures. In order to create a fixture, you need to create a new PHP class per entity:

use App\Doctrine\Schema\UsersTable;
use App\Entity\User;
use App\Repository\UsersRepository;
use Phpro\DbalTools\Fixtures\Fixture;

/**
 * @template-implements Fixture<User>
 */
final readonly class UserFixtures implements Fixture
{
    public function __construct(
        private UsersRepository $userRepository,
    ) {
    }

    public function type(): string
    {
        return User::class;
    }

    public function tables(): array
    {
        return [UsersTable::name()];
    }

    /**
     * @return \Generator<string, User>
     */
    public function execute(): \Generator
    {
        foreach ($this->provideFixtures() as $fixture) {
            if ($this->exists($fixture)) {
                continue;
            }

            yield $fixture->id => $fixture;
            $this->userRepository->create($fixture);
        }
    }

    public function exists(object $x): bool
    {
        return (bool) $this->userRepository->findById($x->id);
    }


    /**
     * @return \Generator<string, User>
     */
    private function provideFixtures(): \Generator
    {
        yield 'admin' => new User(
            '9080f592-3a1e-433d-8b27-0e109fd1d32c',
            'admin',
        );
    }
}

Next, you can register the fixture in your service configuration:

services:
    App\Doctrine\Fixtures\UserFixtures:
        arguments:
            - '@App\Repository\UsersRepository'
        tags:
            - 'phpro.dbal_tools.fixture'

You can now load the fixtures using the command:

./bin/console doctrine:fixtures

Following options are available:

--type=TYPE           Only create / truncate specific type (FQCN) "App\Domain\Model\User".
-t, --truncate        Truncate all fixture tables and relations.
-r, --reload          Truncate and Import all fixture.

Testing

This package contains a set of tools that can be used to test your code against a database. If you are using paratest, the system will automatically create a new database for each process. This way, tests can independently run in parallel resulting in a super fast test-suite.

The DbalTestCase class

If you want to test a class that executes database queries, you can use the DbalTestCase class. This class provides tools to build up the database schema, load testing fixtures and assert if database records exist. An example on how to test the repository we created above:

use App\Doctrine\Schema\UsersTable;
use App\Doctrine\Schema\UsersTableColumns;
use App\Entity\User;
use App\Repository\UsersRepository;
use Phpro\DbalTools\Expression\Comparison;
use Phpro\DbalTools\Expression\Composite;
use Phpro\DbalTools\Expression\LiteralString;
use Phpro\DbalTools\Test\DbalTestCase;
use PHPUnit\Framework\Attributes\Test;

final class UsersRepositoryTest extends DbalTestCase
{
    protected function createFixtures(): void
    {
        self::insert(
            UsersTable::name(),
            UsersTable::columnTypes(),
            $this->fixtures['user1'] = [
                UsersTableColumns::Id->value => '07220bfb-00ff-4f69-9543-bb7a959ad452',
                UsersTableColumns::Username->value => 'user1',
            ],
        );
    }

    protected static function schemaTables(): array
    {
        return [UsersTable::class];
    }

    #[Test]
    public function it_can_fetch_user_by_id(): void
    {
        $repository = $this->createRepository();
        $user1 = $repository->findById($this->fixtures['user1'][UsersTableColumns::Id->value]);

        self::assertSame($this->fixtures['user1'][UsersTableColumns::Id->value], $user1->id);
        self::assertSame($this->fixtures['user1'][UsersTableColumns::Username->value], $user1->userName);
    }

    #[Test]
    public function it_can_create_user(): void
    {
        $repository = $this->createRepository();
        $repository->create(new User('dea2303c-0224-4765-9712-7847a49d8eb7', 'user2'));

        self::assertRecordExists(UsersTable::name(), Composite::and(
            Comparison::equal(UsersTableColumns::Id, new LiteralString('dea2303c-0224-4765-9712-7847a49d8eb7')),
            Comparison::equal(UsersTableColumns::Username, new LiteralString('user2')),
        ));
    }

    private function createRepository(): UsersRepository
    {
        return new UsersRepository($this->connection());
    }
}

The DbalReaderTestCase class

If you have a class that performs a very specific database lookup, you can use the DbalReaderTestCase class. This class will only load the fixtures that are needed for the test once instead of before each test, resulting in a faster test suite.

Example:

use App\Doctrine\Schema\UsersTable;
use App\Doctrine\Schema\UsersTableColumns;
use Phpro\DbalTools\Test\DbalReaderTestCase;
use PHPUnit\Framework\Attributes\Test;

final class UsersLookupTest extends DbalReaderTestCase
{
    protected function createFixtures(): void
    {
        self::insert(
            UsersTable::name(),
            UsersTable::columnTypes(),
            $this->fixtures['user1'] = [
                UsersTableColumns::Id->value => '07220bfb-00ff-4f69-9543-bb7a959ad452',
                UsersTableColumns::Username->value => 'user1',
                UsersTableColumns::Active->value => true,
            ],
            $this->fixtures['user2'] = [
                UsersTableColumns::Id->value => '503beea0-1b37-4303-b8d7-e23f16db0ed6',
                UsersTableColumns::Username->value => 'user1',
                UsersTableColumns::Active->value => false,
            ],
        );
    }

    protected static function schemaTables(): array
    {
        return [UsersTable::class];
    }

    #[Test]
    public function it_can_find_active_users(): void
    {
        $queryHandler = new FindActiveUsersHandler($this->connection());
        $users = $queryHandler->handle(new ActiveUsersQuery(isActive: true));

        self::assertCount(1, $users);
        self::assertSame($this->fixtures['user1'][UsersTableColumns::Id->value], $users[0]->id);
        
    }

    #[Test]
    public function it_can_find_inactive_users(): void
    {
        $queryHandler = new FindActiveUsersHandler($this->connection());
        $users = $queryHandler->handle(new ActiveUsersQuery(isActive: false));

        self::assertCount(1, $users);
        self::assertSame($this->fixtures['user2'][UsersTableColumns::Id->value], $users[0]->id);
    }
}

The DoctrineValidatorTestCase class

If you are using symfony/validator, you can use the DoctrineValidatorTestCase class to test your validation rules. This class will use symfony's ConstraintValidatorTestCase as a base and will automatically load the database schema and fixtures. This way, you can test your validation rules against database records.

use Phpro\DbalTools\Test\Validator\DoctrineValidatorTestCase;
use PHPUnit\Framework\Attributes\Test;
use Symfony\Component\Validator\ConstraintValidatorInterface;

final class UniqueUsernameValidatorTest extends DoctrineValidatorTestCase
{
    protected static function schemaTables(): array
    {
        return [UsersTable::class];
    }

    protected function createFixtures(): void
    {
        self::connection()->insert(
            UsersTable::name(),
            [
                UsersTableColumns::Id->value => '2c8c206c-0031-4c85-b36d-a7d5be16c138',
                UsersTableColumns::Username->value => 'noobslayer23',
            ],
            UsersTable::columnTypes(),
        );
    }

    protected function createValidator(): ConstraintValidatorInterface
    {
        return new UniqueUsernameValidator(
            self::connection()
        );
    }

    #[Test]
    public function it_must_have_a_unique_username(): void
    {
        $constraint = new UniqueUsernameConstraint();
        $this->validator->validate(new User('f7e2c2df-1786-497b-b71e-43b66073ecc6', 'noobslayer23'), $constraint);

        $violationList = $this->context->getViolations();
        self::assertSame(1, $violationList->count());
        self::assertSame(
            'There is already a user with the username {{ username }}. Please choose another username.',
            $violationList->get(0)->getMessage()
        );
    }
}

Validators

This package contains a set of common validators that can be used to validate your data against the database.

SchemaFieldValidator

This validator can be used to validate if a field exists in the database schema. It will perform checks like: Does the provided input length exceed the length of the column in the database schema.

Example configuration:

App\Domain\Model\User:
  properties:
    userName:
      - Phpro\DbalTools\Validator\SchemaFieldConstraint:
          table: App\Infrastructure\Doctrine\Schema\User\UsersTable
          column: !php/enum App\Infrastructure\Doctrine\Schema\User\UsersTableColumns::Username

TableKeyExistsValidator

This validator can be used to validate if a record identified by a key exists in the database. It can be used to verify if a relation ID exists before storing it in the database.

Example configuration:

App\Domain\Model\User:
  properties:
    companyId:
      - Phpro\DbalTools\Validator\TableKeyExistsConstraint:
          table: App\Infrastructure\Doctrine\Schema\Company\CompaniesTable
          column: !php/enum App\Infrastructure\Doctrine\Schema\Company\CompaniesTableColumns::Id

UniqueValidator

This validator can be used to validate if a record with the same value exists in the database already to ensure uniqueness.

Example configuration:

App\Domain\Model\User:
  constraints:
    - Phpro\DbalTools\Validator\UniqueConstraint:
        table: App\Infrastructure\Doctrine\Schema\User\UsersTable
        columns:
          "username": !php/enum App\Infrastructure\Doctrine\Schema\User\UsersTableColumns::Username
        
        # You can specify an alternate message and path name.
        message: "A user already exists with this username."
        path: "data.username"
        
        # Can be used for updates to check if you are updating the existing record:
        identifiers:
          "id": !php/enum App\Infrastructure\Doctrine\Schema\User\UsersTableColumns::Id
        
        # Enable case-insensitive comparison:
        caseInsensitive: false

Pagination

Dealing with pagination is a task that is often needed. This package contains some tools to help you with that:

WindowCountPager - the default: one query, the total counted with a window function.
DeferredProjectionPager - for rows that carry aggregated or joined objects.

Both are honest implementations of Pager and both stay: pick the one that matches the width of your rows.

WindowCountPager

use Phpro\DbalTools\Pager\MappingPager;
use Phpro\DbalTools\Pager\Pager;
use Phpro\DbalTools\Pager\Pagination;
use Phpro\DbalTools\Pager\WindowCountPager;

$query = $connection->createQueryBuilder()->select('*')->from('users');
$userMapper = new UsersMapper()->convertFromDb(...);

$usersPager = new MappingPager(
    WindowCountPager::create(
        new Pagination(page: $page limit: $limit),
        $query,
    ),
    $userMapper,
);

// Iterating over the results:
$totalResults = $usersPager->totalResults();
$totalPages = $usersPager->totalPages();
$users = [...$usersPager];

DeferredProjectionPager

WindowCountPager adds COUNT(1) OVER() to the query it pages. PostgreSQL evaluates window functions before ORDER BY/LIMIT at the same query level, so every matching row is buffered before the page is cut. That is free for a narrow row, and expensive as soon as the row carries a jsonb_build_object or a jsonb_agg_strict projection over joined tables: those buffered rows are kilobytes wide and spill to temp files.

DeferredProjectionPager pages narrow rows - the key column plus the count window - inside a CTE, and joins the fat projection onto that CTE. The total is still computed over the whole matching set, but out of (key, total) rows, and the expensive projection is only evaluated for the page that is returned. It remains a single statement, so there is one snapshot and no second round trip.

Which one to reach for:

WindowCountPager for a narrow row. It is correct, cheaper, and pays nothing for a CTE it does not need.
DeferredProjectionPager when a row carries aggregated or joined objects.

use Doctrine\DBAL\Query\QueryBuilder;
use Phpro\DbalTools\Expression\Alias;
use Phpro\DbalTools\Expression\Factory\NamedParameter;
use Phpro\DbalTools\Expression\ILike;
use Phpro\DbalTools\Expression\JsonbAggStrict;
use Phpro\DbalTools\Expression\JsonbBuildObject;
use Phpro\DbalTools\Expression\OrderBy;
use Phpro\DbalTools\Pager\DeferredProjectionPager;
use Phpro\DbalTools\Pager\MappingPager;
use Phpro\DbalTools\Pager\Pagination;
use Phpro\DbalTools\Query\CompositeQuery;

// Which rows: the key column and every filter. No display columns, no join that only adds data.
$keys = $connection->createQueryBuilder();
$matchingKeys = new CompositeQuery(
    $connection,
    $keys
        ->select(UsersTableColumns::Id->select())
        ->from(UsersTable::name())
        ->where(new ILike(
            UsersTableColumns::Username->column(),
            NamedParameter::createForTableColumn($keys, UsersTableColumns::Username, 'jo%', ':username'),
        )->toSQL()),
    [],
);

$usersPager = new MappingPager(
    DeferredProjectionPager::create(
        pagination: new Pagination(page: $page, limit: $limit),
        matchingKeys: $matchingKeys,
        key: UsersTableColumns::Id->column(),
        order: new OrderBy(OrderBy::field(UsersTableColumns::Username->column(), OrderBy::ASC)),
        // What a row looks like: a new query from the table of the key.
        // This one needs no CTE, so it ignores both arguments.
        projection: static fn (CompositeQuery $folded, string $pageAlias): QueryBuilder => $connection->createQueryBuilder()
            ->select(
                ...UsersTable::columns()->select(),
                ...[new Alias(
                    JsonbAggStrict::onManyLeftJoinedJsonObjects(
                        new JsonbBuildObject([
                            'id' => PostsTableColumns::Id->column(),
                            'post' => PostsTableColumns::Post->column(),
                        ]),
                        PostsTableColumns::Id->column(),
                    ),
                    'posts',
                )->toSQL()],
            )
            ->from(UsersTable::name())
            ->leftJoin(...UsersTable::joinOntoPosts())
            ->groupBy(UsersTableColumns::Id->use()),
    ),
    $userMapper,
);

For page 2 with a limit of 10, that runs as one statement. The main query of $matchingKeys became the deferred_page CTE, with the count window, the order and the page added to it. The projection became the new main query, joined onto that CTE:

WITH deferred_page AS (
    SELECT users.user_id, COUNT(1) OVER() AS total_results
    FROM users
    WHERE users.username ILIKE :username
    ORDER BY users.username ASC
    LIMIT 10 OFFSET 10
)
SELECT
    users.user_id, users.username, users.first_name, users.last_name,
    jsonb_agg_strict(
        CASE WHEN posts.post_id IS NULL THEN NULL
        ELSE jsonb_build_object('id', posts.post_id, 'post', posts.post) END
    ) AS posts,
    (SELECT deferred_page.total_results FROM deferred_page LIMIT 1) AS total_results
FROM users
LEFT JOIN posts posts ON users.user_id = posts.user_id
INNER JOIN deferred_page deferred_page ON users.user_id = deferred_page.user_id
GROUP BY users.user_id
ORDER BY users.username ASC

You describe the list twice:

matchingKeys decides which rows are in the list: a composite whose main query selects the key column and nothing else, with every filter and scope join. No display columns, order or limit, the pager adds those.
key is that column, table qualified. The page is joined back to the projection on it.
projection decides what a row looks like: a new query builder selecting everything a row shows from the table of the key, with its joins, aggregates and group by, and no filters, order or limit. The closure receives the composite that will run and the name of the page CTE. Use the composite to join a CTE registered on matchingKeys or to register one of your own; a projection that needs no CTE ignores both arguments.

Things worth knowing:

The filters and the scope joins belong on $matchingKeys. A filter applied by the projection would drop rows after the total was computed, so the page would come back short and the total would over-report.
To filter on a related table, for example users with a matching post, use EXISTS on $matchingKeys rather than a join: a join yields the key once per matching post. To limit which related rows the projection aggregates, put the condition in its LEFT JOIN, not in a WHERE, so rows without a match stay on the page.
Every CTE you registered on $matchingKeys survives the fold, and so does a parameter bound on its main query: that same query builder is moved into the WITH list, and CompositeQuery::execute() merges the parameters of every registered builder.
The key must be table qualified (the join is derived from it) and unique among the matching keys. A duplicate multiplies the projected rows and makes the total disagree with the page.
Aggregate freely. The pager reads its count as a scalar sub-query rather than as a column of the joined CTE, so a projection that groups needs no group-by for it.
The pager owns the join onto the page CTE, the count column, and the order - which it applies to both levels, since the narrow sort decides which rows the page holds and the outer one the order they come back in. The closure only supplies its projection.
The projection closure returns its query rather than writing onto the folded main query, because QueryBuilder keeps its select, from and join parts private with no setters.
Rows are yielded verbatim, the count field included, exactly like WindowCountPager.

Building queries

This package contains a set of tools that helps you build queries based on partial SQL Expressions. The base of this is the Phpro\DbalTools\Expression interface.

A few notable places:

Every column enum can be used directly as part of an SQL expression.
There is a Phpro\DbalTools\Query\CompositeQuery that can be used to build composite queries (CTEs)
Inside the Phpro\DbalTools\Expression namespace, you can find a lot of classes that can be used to build partial SQL expressions.

About

Submitting bugs and feature requests

Bugs and feature request are tracked on GitHub. Please take a look at our rules before contributing your code.

License

dbal-tools is licensed under the MIT License.

Name		Name	Last commit message	Last commit date
.github		.github
config		config
src		src
stubs		stubs
tests		tests
.docker.env		.docker.env
.gitignore		.gitignore
.php-cs-fixer.php		.php-cs-fixer.php
CONTRIBUTING		CONTRIBUTING
LICENSE		LICENSE
README.md		README.md
composer.json		composer.json
docker-compose.yml		docker-compose.yml
grumphp.yml		grumphp.yml
phpunit.xml.dist		phpunit.xml.dist
psalm.xml		psalm.xml

Navigation Menu

Repository files navigation

DBAL Tools

Installation

Schema

Configuring a table

Configuring sequences

Migrations

Building models and repositories

Persisting relations

Fixtures

Testing

The DbalTestCase class

The DbalReaderTestCase class

The DoctrineValidatorTestCase class

Validators

SchemaFieldValidator

TableKeyExistsValidator

UniqueValidator

Pagination

WindowCountPager

DeferredProjectionPager

Building queries

About

Submitting bugs and feature requests

License

About

Releases

Packages

Used by

Contributors

Languages

Latest commit

History

Folders and files

Repository files navigation

DBAL Tools

Installation

Schema

Configuring a table

Configuring sequences

Migrations

Building models and repositories

Persisting relations

Fixtures

Testing

The DbalTestCase class

The DbalReaderTestCase class

The DoctrineValidatorTestCase class

Validators

SchemaFieldValidator

TableKeyExistsValidator

UniqueValidator

Pagination

WindowCountPager

DeferredProjectionPager

Building queries

About

Submitting bugs and feature requests

License

About

Resources

Contributing

Stars

Watchers

Forks

Releases

Packages

Used by

Contributors

Languages