TYPO3's interface to execute queries via Doctrine DBAL typically starts by asking
ConnectionPool for a
QueryBuilder or a
Connection object, handing over the table name to be queried:
// use TYPO3\CMS\Core\Utility\GeneralUtility; // use TYPO3\CMS\Core\Database\ConnectionPool; // Get a query builder for a table $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_myext_comments'); // or // Get a connection for a table $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('tx_myext_comments');
QueryBuilder is the default workhorse object used by extension authors to express complex queries,
Connection instance can be used as shortcut to deal with some simple query cases and little written down code.
TYPO3 can handle multiple connections to different database endpoints at the same time. This
can be configured on a per-table basis in
$GLOBALS['TYPO3_CONF_VARS']. It allows running tables
on different databases, without an extension developer taking care of that.
ConnectionPool implements this feature: It looks up a configured table-to-database
mapping and can return a
Connection or a
QueryBuilder instance for that specific connection.
Those objects internally know which target connection they are dealing with and will
for instance quote field names accordingly.
The transparency of tables to different database endpoints is limited, though:
Executing a table
JOIN between two tables that point to different connections will throw an exception.
This restriction may in practice create implicit "groups" of tables that need to point to one connection
at once if an extension or the TYPO3 Core joins those tables.
This can turn out as a headache if multiple different extensions use for instance the Core category or collection API with their mm table joins between Core internal tables and their extension's counterparts.
That situation is not easy to deal with. At the time of this writing the Core development will eventually implement some non-join fallbacks for typical cases that would be good to decouple, though.
In case joins cannot be decoupled but still affected tables must run on different databases,
and if the code can not be easily adapted, some DBMS like
PostgreSQL allow executing those
queries by having own connection handlers to different other endpoints on its own.