ConnectionPool

TYPO3's interface for executing queries via Doctrine DBAL starts with a request to the ConnectionPool for a QueryBuilder or a Connection object and passing the table name to be queried:

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php
<?php

declare(strict_types=1);

namespace Vendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyTableRepository
{
    private const TABLE_NAME = 'tx_myextension_domain_model_mytable';

    public function __construct(
        private readonly ConnectionPool $connectionPool
    ) {
    }

    public function findSomething()
    {
        // Get a query builder for a table
        $queryBuilder = $this->connectionPool
            ->getQueryBuilderForTable(self::TABLE_NAME);

        // Or get a connection for a table
        $connection = $this->connectionPool
            ->getConnectionForTable(self::TABLE_NAME);
    }
}

The QueryBuilder is the default object used by extension authors to express complex queries, while a Connection instance can be used as a shortcut to handle some simple query cases.

Pooling

TYPO3 can handle multiple connections to different database endpoints at the same time. This can be configured for each individual table in $GLOBALS['TYPO3_CONF_VARS'] (see database configuration for details). This makes it possible to run tables on different databases without an extension developer having to worry about it.

The ConnectionPool implements this feature: It looks for configured table-to-database mapping and can return a Connection or a QueryBuilder instance for that specific connection. These objects know internally which target connection they are dealing with and will quote field names accordingly, for instance.

Beware

However, the transparency of tables for different database endpoints is limited.

Executing a table JOIN between two tables that reference different connections will result in an exception. This restriction may in practice lead to implicit "groups" of tables that must to point to a single connection when an extension or the TYPO3 Core joins these tables.

This can be problematic when several different extensions use, for instance, the Core category or collection API with their mm table joins between Core internal tables and their extension counterparts.

That situation is not easy to deal with. At the time of writing the Core development will implement eventually some non-join fallbacks for typical cases that would be good to decouple, though.

Tip

In the case joins cannot be decoupled, but still need to run affected tables on different databases, and when the code can not be easily adapted, some DBMS like PostgreSQL allow these queries to be executed by having their own connection handlers to various other endpoints.