Attention

TYPO3 v9 has reached its end-of-life September 30th, 2021 and is not maintained by the community anymore. Looking for a stable version? Use the version switch on the top left.

You can order Extended Long Term Support (ELTS) here: TYPO3 ELTS.

Migrating from TYPO3_DB

This chapter is for those poor souls who want to migrate old and busted $GLOBALS['TYPO3_DB'] calls to new hotness doctrine-dbal based API.

It tries to give some hints on typical pitfalls and areas a special eye should be kept on.

Migration of a single extension is finished if a search for $GLOBALS['TYPO3_DB'] does not return hits anymore. This search is the most simple entry point to see which areas need work.

Compare Raw Queries

The main goal during migration is usually to fire a logically identical query. One recommended and simple approach to verify this is to note down and compare the queries at the lowest possible layer. In $GLOBALS['TYPO3_DB'], the final query statement is usually retrieved by removing the exec_ part from the method name, in doctrine method QueryBuilder->getSQL() can be used:

// Initial code:
$res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('*', 'index_fulltext', 'phash=' . (int)$phash);

// Remove 'exec_' and debug SQL:
debug($GLOBALS['TYPO3_DB']->SELECTquery('*', 'index_fulltext', 'phash=' . (int)$phash));
// Returns:
'SELECT * FROM index_fulltext WHERE phash=42'

// Migrate to doctrine and debug SQL:
// 'SELECT * FROM index_fulltext WHERE phash=42'
$queryBuilder->select('*')
->from('index_fulltext')
->where(
   $queryBuilder->expr()->eq('phash', $queryBuilder->createNamedParameter($phash, \PDO::PARAM_INT))
);
debug($queryBuilder->getSQL());

The above example returns the exact same query as before. This is not always as trivial to see since WHERE clauses are often in a different order. This especially happens if the RestrictionBuilder is involved. Since the restrictions are crucial and can easily go wrong it is advised to keep an eye on those where parts during transition.

enableFields() and deleteClause()

BackendUtility::deleteClause() adds deleted=0 if ['ctrl']['deleted'] is specified in the table's TCA. The method call should be removed during migration. If there is no other restriction method involved in the old call like enableFields(), the migrated code typically removes all doctrine default restrictions and just adds the DeletedRestriction again:

// Before:
$res = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
   'uid, TSconfig',
   'pages',
   'TSconfig != \'\''
      . BackendUtility::deleteClause('pages'),
   'pages.uid'
);

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction
// After:
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
$queryBuilder
   ->getRestrictions()
   ->removeAll()
   ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
$res = $queryBuilder->select('uid', 'TSconfig')
   ->from('pages')
   ->where($queryBuilder->expr()->neq('TSconfig', $queryBuilder->createNamedParameter('')))
   ->groupBy('uid')
   ->execute();

BackendUtility::versioningPlaceholderClause('pages') is typically substituted with the BackendWorkspaceRestriction. Example very similar to the above one:

// Before:
$res = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
   'uid, TSconfig',
   'pages',
   'TSconfig != \'\''
      . BackendUtility::deleteClause('pages')
      . BackendUtility::versioningPlaceholderClause('pages'),
   'pages.uid'
);

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction
// use TYPO3\CMS\Core\Database\Query\Restriction\BackendWorkspaceRestriction
// After:
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
$queryBuilder
   ->getRestrictions()
   ->removeAll()
   ->add(GeneralUtility::makeInstance(DeletedRestriction::class))
   ->add(GeneralUtility::makeInstance(BackendWorkspaceRestriction::class));
$res = $queryBuilder->select('uid', 'TSconfig')
   ->from('pages')
   ->where($queryBuilder->expr()->neq('TSconfig', $queryBuilder->createNamedParameter('')))
   ->groupBy('uid')
   ->execute();

BackendUtility::BEenableFields() in combination with BackendUtility::deleteClause() adds the same calls as the DefaultRestrictionContainer. No further configuration needed:

// Before:
$GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
   'title, content, crdate',
   'sys_news',
   '1=1'
      . BackendUtility::BEenableFields($systemNewsTable)
      . BackendUtility::deleteClause($systemNewsTable)
);

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// After:
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
   ->getQueryBuilderForTable('sys_news');
$queryBuilder
   ->select('title', 'content', 'crdate')
   ->from('sys_news')
   ->execute();

cObj->enableFields() in frontend context is typically directly substituted with FrontendRestrictionContainer:

// Before:
$GLOBALS['TYPO3_DB']->exec_SELECTquery(
   '*', $table,
   'pid=' . (int)$pid
      . $this->cObj->enableFields($table)
);

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Database\Query\Restriction\FrontendRestrictionContainer
// After:
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
$queryBuilder->setRestrictions(GeneralUtility::makeInstance(FrontendRestrictionContainer::class));
$queryBuilder->select('*')
   ->from($table)
   ->where(
      $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($pid, \PDO::PARAM_INT))
   )
);

From ->exec_UDATEquery() to ->update()

Most often, the easiest way to migrate a $GLOBALS['TYPO3_DB']->exec_UDATEquery() is to use $connection->update():

// Before:
$database->exec_UPDATEquery(
    'aTable', // table
    'uid = 42', // where
    [ 'aField' => 'newValue' ] // value array
);

// After:
$connection->update(
    'aTable', // table
    [ 'aField' => 'newValue' ], // value array
    [ 'uid' => 42 ] // where
);

Warning

If switching from exec_UPDATEquery() to update, the order of arguments change, where and values are swapped!

Result Set Iteration

The exec_* calls return a resource object that is typically iterated over using sql_fetch_assoc(). This is typically changed to ->fetch() on the Statement object:

// Before:
$res = $GLOBALS['TYPO3_DB']->exec_SELECTquery(...);
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
   // Do something
}

// After:
$statement = $queryBuilder->execute();
while ($row = $statement->fetch()) {
   // Do something
}

sql_insert_id()

It is sometimes needed to fetch the new uid of a just added record to further work with that row. In TYPO3_DB this was done with a call to ->sql_insert_id() after a ->exec_INSERTquery() call on the same resource. ->lastInsertId() can be used instead:

// Before:
$GLOBALS['TYPO3_DB']->exec_INSERTquery(
   'pages',
   [
      'pid' => 0,
      'title' => 'Home',
   ]
);
$pageUid = $GLOBALS['TYPO3_DB']->sql_insert_id();

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// After:
$connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
$databaseConnectionForPages = $connectionPool->getConnectionForTable('pages');
$databaseConnectionForPages->insert(
   'pages',
   [
      'pid' => 0,
      'title' => 'Home',
    ]
);
$pageUid = (int)$databaseConnectionForPages->lastInsertId('pages');

fullQuoteStr()

->fullQuoteStr() is rather straight changed to a ->createNamedParameter(), typical case:

// Before:
$res = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
   'uid, title',
   'tt_content',
   'bodytext = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('horst')
);

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// After:
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$statement = $queryBuilder
   ->select('uid', 'title')
   ->from('tt_content')
   ->where(
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('horst'))
   )
   ->execute();

escapeStrForLike()

$GLOBALS['TYPO3_DB']->escapeStrForLike() is replaced by $queryBuilder->escapeLikeWildcards().

ext_tables.sql

The schema migrator that compiles ext_tables.sql files from all loaded extensions and compares them with current schema definitions in the database has been fully rewritten. It mostly should work as before, some specific fields however tend to grow a little larger on mysql platforms than before. This usually shouldn't have negative side effects, typically no ext_tables.sql changes are needed when migrating an extension to the new query API.

TCA and TypoScript

TCA and TypoScript needs to be adapted at places where SQL fragments are specified. Table and field names are quoted differently on different platforms and extension developers should never hard code quoting for specific target platforms, but let the core quote the field according to the currently used platform. This leads to a new syntax in various places, for instance in TCA property foreign_table_where. In general it applies to all places where SQL fragments are specified:

// Before:
'foreign_table_where' => 'AND tx_some_foreign_table_name.pid = 42',

// After:
'foreign_table_where' => 'AND {#tx_some_foreign_table_name}.{#pid} = 42',

If using MySQL, this fragment will be parsed to AND `tx_some_foreign_table_name`.`pid` = 42 (note the backticks) with the help of QueryHelper::quoteDatabaseIdentifiers().

Extbase QueryBuilder

The extbase internal QueryBuilder used in Repositories still exists and works a before. There is usually no manual migration needed. It is theoretically possible to use the doctrine based query builder object in Extbase which can become handy since the new one is much more feature rich, but that topic didn't yet fully settle in the core and no general recommendation can be given yet.