Various tips and tricks 

• Use Find usages of PhpStorm for examples! The source code of the Core is a great way to learn how specific methods of the API are used. In PhpStorm it is extremely helpful to right-click on a single method and list all method usages with Find usages. This is especially handy to quickly see usage examples for complex methods like join() from the query builder.
• INSERT , UPDATE and DELETE statements are often better to read and write using the Connection object instead of the query builder.
• SELECT DISTINCT aField is not supported but can be substituted with a ->groupBy('aField') .

• getSql() and executeQuery() / executeStatement() can be used after each other during development to simplify debugging:

  EXT:my_extension/Classes/Domain/Repository/MyDbalRepository.php

  <?php
  
  declare(strict_types=1);
  
  namespace MyVendor\MyExtension\Domain\Repository;
  
  use Doctrine\DBAL\Result;
  use TYPO3\CMS\Core\Database\ConnectionPool;
  
  final readonly class MyDbalRepository
  {
    public function __construct(
      private ConnectionPool $connectionPool,
    ) {}
  
    public function findByBodytext(): Result
    {
      $queryBuilder = $this->connectionPool
          ->getQueryBuilderForTable('tt_content');
      $queryBuilder
          ->select('uid')
          ->from('tt_content')
          ->where(
            $queryBuilder->expr()->eq(
              'bodytext',
              $queryBuilder->createNamedParameter('lorem'),
            ),
          );
  
      debug($queryBuilder->getSQL());
  
      return $queryBuilder->executeQuery();
    }
  }
  

  Copied!
• Doctrine DBAL throws exceptions if something goes wrong when calling API methods. The exception type is \Doctrine\DBAL\Exception . Typical extensions should usually not catch such exceptions but let it bubble up to be handled by the global TYPO3 core error and exception handling: They most often indicate a broken connection, database schema or programming error and extensions should usually not try to hide away or escalate them on their own.
• count() <database-query-builder-count> query types using the query builder normally call ->fetchOne() to receive the count value. The count() method of the Connection object does this automatically and returns the result of the count value directly.