Connection

Introduction

The TYPO3\CMS\Core\Database\Connection class extends the basic Doctrine DBAL Doctrine\DBAL\Connection class and is mainly used internally in TYPO3 to establish, maintain and terminate connections to single database endpoints. These internal methods are not the scope of this documentation, since an extension developer usually does not have to deal with them.

However, for an extension developer, the class provides a list of short-hand methods that allow you to deal with query cases without the complexity of the query builder. Using these methods usually ends up in rather short and easy-to-read code. The methods have in common that they only support "equal" comparisons in WHERE conditions, that all fields and values are automatically fully quoted, and that the created queries are executed right away.

Instantiation

Using the connection pool

An instance of the TYPO3\CMS\Core\Database\Connection class is retrieved from the ConnectionPool by calling ->getConnectionForTable() and passing the table name for which a query should be executed. The ConnectionPool can be injected via constructor:

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

declare(strict_types=1);

namespace MyVendor\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()
    {
        $connection = $this->connectionPool
            ->getConnectionForTable(self::TABLE_NAME);
    }
}
Copied!

Via dependency injection

Another way is to inject the Connection object directly via dependency injection if you only use one table.

  1. Configure the concrete connection as a service

    To make a concrete Connection object available as a service, use the factory option in the service configuration:

    EXT:my_extension/Configuration/Services.yaml
    services:
      _defaults:
        autowire: true
        autoconfigure: true
        public: false
    
      MyVendor\MyExtension\:
        resource: '../Classes/*'
    
      connection.tx_myextension_domain_model_mytable:
        class: 'TYPO3\CMS\Core\Database\Connection'
        factory: ['@TYPO3\CMS\Core\Database\ConnectionPool', 'getConnectionForTable']
        arguments:
          - 'tx_myextension_domain_model_mytable'
    
      MyVendor\MyExtension\Domain\Repository\MyTableRepository:
        arguments:
          - '@connection.tx_myextension_domain_model_mytable'
    
    Copied!
  2. Use constructor injection in your class

    Now the Connection object for a specific table can be injected via the constructor:

    EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php
    <?php
    
    declare(strict_types=1);
    
    namespace MyVendor\MyExtension\Domain\Repository;
    
    use TYPO3\CMS\Core\Database\Connection;
    
    final class MyTableRepository
    {
        public function __construct(
            private readonly Connection $connection,
        ) {}
    
        public function findSomething()
        {
            // Here you can use $this->connection directly
        }
    }
    
    Copied!

Parameter types

The parameter types are used in various places to bind values to types, for example, when using named parameters in the query builder:

// use TYPO3\CMS\Core\Database\Connection;

$queryBuilder->createNamedParameter(42, Connection::PARAM_INT);
Copied!

The following parameter types are available:

\TYPO3\CMS\Core\Database\Connection::PARAM_NULL
Represents an SQL NULL data type.
\TYPO3\CMS\Core\Database\Connection::PARAM_INT
Represents an SQL INTEGER data type.
\TYPO3\CMS\Core\Database\Connection::PARAM_STR
Represents an SQL CHAR or VARCHAR data type.
\TYPO3\CMS\Core\Database\Connection::PARAM_LOB
Represents an SQL large object data type.
\TYPO3\CMS\Core\Database\Connection::PARAM_BOOL
Represents a boolean data type.
\TYPO3\CMS\Core\Database\Connection::PARAM_INT_ARRAY
Represents an array of integer values.
\TYPO3\CMS\Core\Database\Connection::PARAM_STR_ARRAY
Represents an array of string values.

The default parameter type is Connection::PARAM_STR, if this argument is omitted.

Internally, these parameter types are mapped to the types Doctrine DBAL expects.

insert()

The insert() method creates and executes an INSERT INTO statement. Example:

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

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

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

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

    public function insertSomething(
        int $pid,
        string $someString,
        array $someData,
    ): void {
        $this->connectionPool
            ->getConnectionForTable(self::TABLE_NAME)
            ->insert(
                self::TABLE_NAME,
                [
                    'pid' => $pid,
                    'some_string' => $someString,
                    'json_field' => $someData,
                ],
            );
    }
}
Copied!

Read how to instantiate a connection with the connection pool. See available parameter types.

New in version 12.1

Arguments of the insert() method:

  1. The name of the table the row should be inserted. Required.
  2. An associative array containing field/value pairs. The key is a field name, the value is the value to be inserted. All keys are quoted to field names and all values are quoted to string values. Required.
  3. Specify how single values are quoted. This is useful if a date, number or similar should be inserted. Optional.

    The example below quotes the first value to an integer and the second one to a string:

    EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php
    <?php
    
    declare(strict_types=1);
    
    namespace MyVendor\MyExtension\Domain\Repository;
    
    use TYPO3\CMS\Core\Database\Connection;
    use TYPO3\CMS\Core\Database\ConnectionPool;
    
    final class MyTableRepository
    {
        private const TABLE_NAME = 'tx_myextension_mytable';
    
        public function __construct(
            private readonly ConnectionPool $connectionPool,
        ) {}
    
        public function insertSomething(
            int $pid,
            string $someString,
        ): void {
            $this->connectionPool
                ->getConnectionForTable(self::TABLE_NAME)
                ->insert(
                    self::TABLE_NAME,
                    [
                        'pid' => $pid,
                        'some_string' => $someString,
                    ],
                    [
                        Connection::PARAM_INT,
                        Connection::PARAM_STR,
                    ],
                );
        }
    }
    
    Copied!

    Read how to instantiate a connection with the connection pool. See available parameter types.

insert() returns the number of affected rows. Guess what? That is the number 1 ... If something goes wrong, a \Doctrine\DBAL\Exception is thrown.

bulkInsert()

This method insert multiple rows at once:

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

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;

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

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

    public function bulkInsertSomething(
        int $pid1,
        int $pid2,
        string $someString1,
        string $someString2,
    ): void {
        $this->connectionPool
            ->getConnectionForTable(self::TABLE_NAME)
            ->bulkInsert(
                self::TABLE_NAME,
                [
                    [$pid1, $someString1],
                    [$pid2, $someString2],
                ],
                [
                    'pid',
                    'title',
                ],
                [
                    Connection::PARAM_INT,
                    Connection::PARAM_STR,
                ],
            );
    }
}
Copied!

Read how to instantiate a connection with the connection pool. See available parameter types.

Arguments of the bulkInsert() method:

  1. The name of the table the row should be inserted. Required.
  2. An array of the values to be inserted. Required.
  3. An array containing the column names of the data which should be inserted. Optional.
  4. Specify how single values are quoted. Similar to insert(); if omitted, everything will be quoted to strings. Optional.

The number of inserted rows are returned. If something goes wrong, a \Doctrine\DBAL\Exception is thrown.

update()

Create an UPDATE statement and execute it. The example from FAL's ResourceStorage sets a storage to offline:

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

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;

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

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

    public function updateSomething(
        int $uid,
        string $someValue,
        array $someData,
    ): void {
        $this->connectionPool
            ->getConnectionForTable(self::TABLE_NAME)
            ->update(
                self::TABLE_NAME,
                [
                    'some_value' => $someValue,
                    'json_data' => $someData,
                ],
                ['uid' => $uid],
                [Connection::PARAM_INT],
            );
    }
}
Copied!

New in version 12.1

Read how to instantiate a connection with the connection pool. See available parameter types.

Arguments of the update() method:

  1. The name of the table to update. Required.
  2. An associative array containing field/value pairs to be updated. The key is a field name, the value is the value. In SQL they are mapped to the SET keyword. Required.
  3. The update criteria as an array of key/value pairs. The key is the field name, the value is the value. In SQL they are mapped in a WHERE keyword combined with AND. Required.
  4. Specify how single values are quoted. Similar to insert(); if omitted, everything will be quoted to strings. Optional.

The method returns the number of updated rows. If something goes wrong, a \Doctrine\DBAL\Exception is thrown.

delete()

Execute a DELETE query using equal conditions in WHERE, example from BackendUtility, to mark rows as no longer locked by a user:

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

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;

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

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

    public function deleteSomething(
        int $uid,
    ): void {
        $this->connectionPool
            ->getConnectionForTable(self::TABLE_NAME)
            ->delete(
                self::TABLE_NAME,
                ['uid' => $uid],
                [Connection::PARAM_INT],
            );
    }
}
Copied!

Read how to instantiate a connection with the connection pool. See available parameter types.

Arguments of the delete() method:

  1. The name of the table. Required.
  2. The delete criteria as an array of key/value pairs. The key is the field name, the value is the value. In SQL they are mapped in a WHERE keyword combined with AND. Required.
  3. Specify how single values are quoted. Similar to insert(); if omitted, everything will be quoted to strings. Optional.

The method returns the number of deleted rows. If something goes wrong, a \Doctrine\DBAL\Exception is thrown.

truncate()

This method empties a table, removing all rows. It is usually much faster than a delete() of all rows. This typically resets "auto increment primary keys" to zero. Use with care:

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

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyCacheRepository
{
    private const TABLE_NAME = 'cache_myextension';

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

    public function truncateSomething(
        int $uid,
    ): void {
        $this->connectionPool
            ->getConnectionForTable(self::TABLE_NAME)
            ->truncate(self::TABLE_NAME);
    }
}
Copied!

Read how to instantiate a connection with the connection pool.

The argument is the name of the table to be truncated. If something goes wrong, a \Doctrine\DBAL\Exception is thrown.

count()

This method executes a COUNT query. Again, this becomes useful when very simple COUNT statements are to be executed. The example below returns the number of active rows (not hidden or deleted or disabled by time) from the table tx_myextension_mytable whose field some_value field set to $something:

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

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

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

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

    public function countSomething(
        int $something,
    ): int {
        $connection = $this->connectionPool
            ->getConnectionForTable(self::TABLE_NAME);
        return $connection->count(
            '*',
            self::TABLE_NAME,
            ['some_value' => $something],
        );
    }
}
Copied!

Read how to instantiate a connection with the connection pool.

Arguments of the count() method:

  1. The field to count, usually * or uid. Required.
  2. The name of the table. Required.
  3. The select criteria as an array of key/value pairs. The key is the field name, the value is the value. In SQL they are mapped in a WHERE keyword combined with AND. Required.

The method returns the counted rows.

Remarks:

  • Connection::count() returns the number directly as an integer, unlike the method of the query builder it is not necessary to call ->fetchColumns(0) or similar.
  • The third argument expects all WHERE values to be strings, each single expression is combined with AND.
  • The restriction builder kicks in and adds additional WHERE conditions based on TCA settings.
  • Field names and values are quoted automatically.
  • If anything more complex than a simple equal condition on WHERE is needed, the query builder methods are the better choice: next to select(), the ->count() query is often the least useful method of the Connection object.

select()

This method creates and executes a simple SELECT query based on equal conditions. Its usage is limited, the restriction builder kicks in and key/value pairs are automatically quoted:

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

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use Doctrine\DBAL\Result;
use TYPO3\CMS\Core\Database\ConnectionPool;

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

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

    public function countSomething(
        int $something,
    ): Result {
        return $this->connectionPool
            ->getConnectionForTable(self::TABLE_NAME)
            ->select(
                ['*'],
                self::TABLE_NAME,
                ['some_value' => $something],
            );
    }
}
Copied!

Read how to instantiate a connection with the connection pool.

Arguments of the select() method:

  1. The columns of the table which to select as an array. Required.
  2. The name of the table. Required.
  3. The select criteria as an array of key/value pairs. The key is the field name, the value is the value. In SQL they are mapped in a WHERE keyword combined with AND. Optional.
  4. The columns to group the results by as an array. In SQL they are mapped in a GROUP BY keyword. Optional.
  5. An associative array of column name/sort directions pairs. In SQL they are mapped in an ORDER BY keyword. Optional.
  6. The maximum number of rows to return. In SQL it is mapped in a LIMIT keyword. Optional.
  7. The first result row to select (when used the maximum number of rows). In SQL it is mapped in an OFFSET keyword. Optional.

In contrast to the other short-hand methods, ->select() returns a Result object ready for ->fetchAssociative() to get single rows or for ->fetchAllAssociative() to get all rows at once.

Remarks:

  • For non-trivial SELECT queries it is often better to switch to the according method of the query builder object.
  • The restriction builder adds default WHERE restrictions. If these restrictions do not match the query requirements, it is necessary to switch to the QueryBuilder->select() method for fine-grained WHERE manipulation.

lastInsertId()

Changed in version 13.0

The method no longer accepts the table name as first argument and the name of the auto-increment field as second argument.

This method returns the uid of the last insert() statement. This is useful if the ID is to be used directly afterwards:

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

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

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

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

    public function insertSomething(
        array $someData,
    ): int {
        $connection = $this->connectionPool
            ->getConnectionForTable(self::TABLE_NAME);
        $connection
            ->insert(
                self::TABLE_NAME,
                $someData,
            );
        return (int)$connection->lastInsertId();
    }
}
Copied!

Read how to instantiate a connection with the connection pool.

Remarks:

  • The last inserted ID needs to be retrieved directly before inserting a record to another table. That should be the usual workflow used in the wild - but be aware of this.

createQueryBuilder()

The query builder should not be reused for multiple different queries. However, sometimes it is convenient to first fetch a connection object for a specific table and execute a simple query, and later create a query builder for a more complex query from that connection object. The usefulness of this method is limited, however, and at the time of writing no good example could be found in the Core.

The method can also be useful in loops to save some precious code characters:

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

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

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

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

    public function useQueryBuilder(
        array $someData,
    ): void {
        $connection = $this->connectionPool->getConnectionForTable(self::TABLE_NAME);
        foreach ($someData as $value) {
            $queryBuilder = $connection->createQueryBuilder();
            $myResult = $queryBuilder
                ->select('*')
                ->from(self::TABLE_NAME)
                ->where(
                    $queryBuilder->expr()->eq(
                        'some_field',
                        $queryBuilder->createNamedParameter($value),
                    ),
                )
                ->executeQuery()
                ->fetchAllAssociative();
            // do something
        }
    }
}
Copied!

Read how to instantiate a connection with the connection pool.

Native JSON database field type support

New in version 12.1

TYPO3 Core's Database API based on Doctrine DBAL supports the native database field type json, which is already available for all supported DBMS of TYPO3 v12.

JSON-like objects or arrays are automatically serialized during writing a dataset to the database, when the native JSON type was used in the database schema definition.

By using the native database field declaration json in ext_tables.sql file within an extension, TYPO3 converts arrays or objects of type \JsonSerializable into a serialized JSON value in the database when persisting such values via Connection->insert() or Connection->update() if no explicit DB types are handed in as additional method argument.

TYPO3 now utilizes the native type mapping of Doctrine to convert special types such as JSON database field types automatically for writing.

Example:

EXT:my_extension/ext_tables.sql
CREATE TABLE tx_myextension_mytable
(
    some_string varchar(200) DEFAULT '',
    json_field  json
);
Copied!
EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

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

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

    public function insertSomething(
        int $pid,
        string $someString,
        array $someData,
    ): void {
        $this->connectionPool
            ->getConnectionForTable(self::TABLE_NAME)
            ->insert(
                self::TABLE_NAME,
                [
                    'pid' => $pid,
                    'some_string' => $someString,
                    'json_field' => $someData,
                ],
            );
    }
}
Copied!