Study guide · Database Access and DataHandler

Reading and writing data in TYPO3

As of November 15, 2026 · TYPO3 14.3 LTS · about 19 min read

Almost everything in TYPO3 lives in a database. This page works from the bottom up: databases and SQL, then Doctrine DBAL with QueryBuilder and restrictions, then DataHandler and registry. Afterwards you know which tool to use when, and how to build safe queries.

Databases: relational and key-value

A database management system, or DBMS, is software that stores and manages data and answers queries.

Relational means that data is kept in tables of rows and columns, linked via keys. The primary key uniquely identifies each row. A foreign key points to the primary key of another table. Transactions ensure consistent changes according to the ACID principle.

You query the data with SQL. Every system speaks its own dialect of it. TYPO3 14 runs on MySQL, MariaDB, PostgreSQL and SQLite. TYPO3 does not support Oracle, Microsoft SQL Server or MongoDB.

A key-value store such as Redis or Memcached works quite differently. It stores every value under a unique key and returns it via that key. There is no fixed schema and there are no queries with JOIN. In return it is very fast, and entries can have an expiry time. That makes it a good fit for caches, sessions and configuration values.

Reading and changing data with SQL

SELECT reads rows, and WHERE restricts them. ORDER BY sorts, ascending with ASC and descending with DESC. GROUP BY combines rows into groups, and functions such as COUNT calculate per group. HAVING filters the groups.

SELECT category, COUNT(*) FROM products
WHERE active = 1
GROUP BY category
HAVING COUNT(*) > 5;
SQL example. It counts the active products per category and keeps only categories with more than five products.

The order is fixed: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. WHERE filters rows before grouping, HAVING filters groups after it. That is why COUNT must not appear in WHERE.

COUNT with a star counts all rows. COUNT with a column name only counts rows in which that column is not NULL. If the speaker table has ten rows and three have no email, the result is 10 and 7. An empty string is not NULL.

A JOIN connects tables. An INNER JOIN returns only matching pairs. A LEFT JOIN from categories to products returns all categories, including those without products. The product columns are then NULL.

You write with INSERT, UPDATE and DELETE. INSERT creates a row. DELETE removes rows, while the table itself stays. UPDATE and DELETE without WHERE affect all rows, and the database does not refuse that.

Structure, relationships and TYPO3 tables

Statements for data are called DML. Statements for the structure are called DDL, such as CREATE TABLE, ALTER TABLE and CREATE INDEX. You add a column with ALTER TABLE and ADD.

An index speeds up searching and sorting on its columns. But it takes up storage and slows down writes, so do not index every column. An index on pid and deleted helps queries that filter by both. An index never changes the result of a query.

You describe relationships by their cardinality:

  • One to one: a person has exactly one identity card.
  • One to n: a blog has many posts, and each post belongs to one blog. The foreign key sits on the n side. If a speaker gives many talks, the speaker's uid is stored in the table of talks.
  • M to n: posts have many tags, and tags belong to many posts. This needs an intermediate table with foreign keys to both sides.

In TYPO3 the intermediate table is called an MM table, with the columns uid_local, uid_foreign and sorting. A comma-separated list in one column violates the first normal form, which requires atomic values. The solution is again an MM table.

Every table managed by TYPO3 has fixed fields:

  • uid is the unique primary key of the record.
  • pid is the uid of the page the record is stored on. The value 0 means the root level.
  • tstamp holds the last change, crdate the creation.
  • deleted marks deleted records, hidden hidden ones.

Products or news without a content page of their own go into a page of the type Folder, which is doktype 254.

If ctrl.delete is set in the TCA, deleting in the backend only sets the field deleted to 1. This is called soft delete. The row stays but is hidden everywhere, and the Recycler can restore it. A hidden record, in contrast, stays visible in the backend.

You describe your own tables in ext_tables.sql. TYPO3 creates the management fields itself. The file only needs what differs, for example an index. The schema comparison in the Maintenance module or the command extension:setup applies it to the database.

CREATE TABLE tx_myextension_domain_model_talk (
    KEY event_date (event_date)
);
Excerpt from ext_tables.sql. The talks table gets an index on the column event_date.

Doctrine DBAL and the QueryBuilder

The TYPO3 database layer is based on Doctrine DBAL, the Database Abstraction Layer. It provides connections, the QueryBuilder and schema management, and translates everything into the dialect of the system. Doctrine ORM maps rows to objects and manages their state. It is not part of the Core. Extbase has its own ORM based on the TCA.

The entry point is the ConnectionPool. It returns a connection with getConnectionForTable() and a QueryBuilder with getQueryBuilderForTable().

The Connection has shortcuts such as insert(), update(), delete() and select(). They bind the values as parameters automatically. For a technical table without TCA, such as an import log, this is the usual way. If an import writes to two tables, use a transaction. Then both changes succeed together, or neither does.

$connection = $this->connectionPool->getConnectionForTable('tx_myextension_log');
$connection->insert('tx_myextension_log', ['message' => $message, 'crdate' => time()]);

$connection->transactional(function () use ($connection) { /* both writes */ });

$connection->beginTransaction();
try {
    /* both writes */
    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollBack();
    throw $e;
}
Code example. insert writes to the log. Below, transactional rolls back by itself on an exception, while the manual way uses commit and rollBack.

For everything else you use the QueryBuilder. It has one method per clause, and the ExpressionBuilder behind expr() builds conditions. Get a new QueryBuilder from the ConnectionPool for every query, because it keeps its state.

$qb = $this->connectionPool->getQueryBuilderForTable('tx_myextension_domain_model_talk');
$rows = $qb
    ->select('t.uid', 't.title')
    ->from('tx_myextension_domain_model_talk', 't')
    ->join('t', 'tx_myextension_domain_model_conference', 'c',
        $qb->expr()->eq('c.uid', $qb->quoteIdentifier('t.conference')))
    ->where($qb->expr()->like('t.title',
        $qb->createNamedParameter('%' . $qb->escapeLikeWildcards($term) . '%')))
    ->executeQuery()
    ->fetchAllAssociative();
Code example for the talk search. The join compares two field names, so the second one is wrapped in quoteIdentifier. The search term goes into LIKE with escapeLikeWildcards and createNamedParameter, and fetchAllAssociative returns the rows.

There are two ways to execute a query:

  • executeQuery() is for SELECT and COUNT and returns a result object. From it, fetchAllAssociative() reads all rows, fetchAssociative() the next one and fetchOne() a single value. If there is no row, fetchAssociative() returns false.
  • executeStatement() is for INSERT, UPDATE and DELETE and returns the number of affected rows.

Let the database do the counting. The method count() replaces the SELECT part with COUNT, and fetchOne() reads the number. Loading all rows and counting them in PHP costs memory and time.

$count = $qb->count('uid')
    ->from('tx_myextension_domain_model_talk')
    ->where($qb->expr()->eq('pid', $qb->createNamedParameter(12, Connection::PARAM_INT)))
    ->executeQuery()
    ->fetchOne();

$qb->expr()->in('uid', $qb->createNamedParameter($uids, Connection::PARAM_INT_ARRAY));
Code example. count on uid with the condition pid equals 12, then fetchOne. Below is an IN condition whose list is passed as a parameter of type PARAM_INT_ARRAY.

A list needs an array type. PARAM_INT_ARRAY is the TYPO3 constant for ArrayParameterType::INTEGER from Doctrine, and both are equivalent. For strings there is PARAM_STR_ARRAY.

Unlike the Doctrine QueryBuilder, the TYPO3 one automatically adds restrictions to SELECT and COUNT and quotes names.

Restrictions: what is hidden automatically

A query on tt_content returns no deleted and no hidden records without any action on your part. The reason is restrictions: conditions that the QueryBuilder adds based on the TCA. They apply in the frontend, the backend and on the command line.

DefaultRestrictionContainer always active: frontend, backend, command line DeletedRestriction HiddenRestriction StartTimeRestriction EndTimeRestriction FrontendRestrictionContainer set it yourself: read like a visitor DeletedRestriction HiddenRestriction StartTimeRestriction EndTimeRestriction WorkspaceRestriction FrontendGroupRestriction removeAll() removes all except enforced ones removeByType() removes exactly one, even if enforced
The default for every query, and the container for a visitor's view.

The DefaultRestrictionContainer is always active. It contains the DeletedRestriction, HiddenRestriction, StartTimeRestriction and EndTimeRestriction. It does not check user groups or workspaces.

The FrontendRestrictionContainer adds the WorkspaceRestriction and the FrontendGroupRestriction. You set it yourself, for example when a console command should read like a visitor who is not logged in.

You adjust restrictions via getRestrictions():

  • removeByType() removes exactly one. If a backend module should also show hidden records, remove the HiddenRestriction. Deleted records stay hidden.
  • removeAll() removes all of them, including the one for deleted records.
  • add() adds one. To see everything except deleted records, call removeAll() and then add() with the DeletedRestriction.
$qb->getRestrictions()->removeByType(HiddenRestriction::class);

$qb->getRestrictions()->removeAll()
    ->add(GeneralUtility::makeInstance(DeletedRestriction::class));

$qb->setRestrictions(GeneralUtility::makeInstance(FrontendRestrictionContainer::class));
Code example. Remove only the HiddenRestriction, or remove all and add the DeletedRestriction again, or set the FrontendRestrictionContainer.

An enforced restriction implements the EnforceableQueryRestrictionInterface. It survives removeAll(), but removeByType() still removes it. The Connection shortcuts select() and count() also use the default restrictions. You just cannot adjust them there.

Querying safely

In an SQL injection, input is built into a query in a way that changes its meaning. This happens when you paste a value from the request directly into the SQL text. An attacker then sends SQL instead of a title and reads or deletes data.

$qb->where('title = "' . $request->getQueryParams()['title'] . '"'); // unsafe
$qb->where($qb->expr()->eq('uid', $qb->createNamedParameter($uid, Connection::PARAM_INT))); // safe
Two lines of code. The first is unsafe, it pastes the title from the URL straight into the SQL text. The second is safe, the value goes into the condition via createNamedParameter with the type PARAM_INT.

The defense is the prepared statement. The database first receives the query with placeholders and then, separately, the values. A value can therefore never be read as a command. If the same query runs many times, it can even be faster, because the database parses it only once.

That is why in the QueryBuilder every input goes through createNamedParameter(). With PARAM_INT the value reaches the database as a number, not as text. Custom SQL run with executeStatement() also needs parameters. The Connection shortcuts, Extbase queries using the query object and the DataHandler bind their values themselves.

Prepared statements have limits:

  • Table and column names cannot be passed as placeholders. For field names there is quoteIdentifier(), but it is wrong for values.
  • In LIKE, the percent sign and the underscore in the input stay wildcards. You escape them with escapeLikeWildcards(). You still need your own percent signs around the term, otherwise you only get exact matches.
  • They do not protect against cross-site scripting in the output.

You also limit the damage like this: give the database user only the privileges it needs. Do not show database error messages to visitors, because they reveal the structure. Home-made escaping, such as removing quotes with str_replace, is error-prone.

Persistence in the Core

TYPO3 writes records in two ways. In the backend, the DataHandler saves. Plugins and Extbase modules use Extbase persistence. It writes past the DataHandler via Doctrine DBAL, but reads the TCA for the mapping.

The TCA describes every table with its fields, types and relations. Instead of reading the raw array, use the Schema API: the TcaSchemaFactory returns, via get(), a schema object with the fields and capabilities of a table.

The reference index in sys_refindex stores references between records and files. The backend uses it to show where something is used. The DataHandler keeps it up to date, and since TYPO3 14 Extbase does too. A direct SQL import bypasses this, and afterwards the display is wrong. Then the command referenceindex:update helps, or, since TYPO3 14, the Maintenance module with Check and Update Reference Index.

TASK TOOL Change editorial data with permissions and history DataHandler Domain logic in plugins and Extbase modules Repository Lists, searches, statistics reading many rows QueryBuilder Technical tables writing without TCA Connection Single values for the whole installation Registry
Which tool for which task.

The DataHandler

The DataHandler creates, changes and deletes records the way the backend does. It checks the permissions of the backend user and applies the TCA. It writes history and log entries, maintains the reference index and clears caches. It does not process fields without TCA.

datamap field values per table and uid NEW1 for new records cmdmap commands per table and uid copy, move, delete, discard DataHandler check backend user and permissions apply the TCA rules writes and maintains records history and log reference index clear caches substNEWwithIDs new uids per placeholder errorLog errors, no exception
Two arrays in, everything as when saving in the backend.

The datamap contains field values, ordered by table, uid and field. Instead of a uid, a new record gets a placeholder that starts with NEW, such as NEW1. Leave out underscores, because in relation fields TYPO3 reads an underscore as the separator between table and number. The pid tells it on which page the record is created.

$data['tx_myextension_domain_model_talk']['NEW1'] = ['pid' => 12, 'title' => 'New'];

$dataHandler = GeneralUtility::makeInstance(DataHandler::class);
$dataHandler->start($data, []);
$dataHandler->process_datamap();
if ($dataHandler->errorLog !== []) { /* log the errors */ }
$newUid = $dataHandler->substNEWwithIDs['NEW1'];
Code example from the import. It creates NEW1 with pid 12, runs process_datamap on a fresh DataHandler, checks errorLog and reads the new uid from substNEWwithIDs.

The method process_datamap() returns nothing. The DataHandler collects errors in errorLog, missing permissions included. The new uids are in substNEWwithIDs.

The cmdmap contains commands, ordered by table, uid and command, and runs with process_cmdmap(). Commands include copy, move, delete and localize. New in TYPO3 14 is discard. It discards workspace changes, replaces the old actions clearWSID and flush, and expects the uid of the workspace version. When copying, a positive value is a target page. A negative value is a record of the same table after which the copy is inserted.

$cmd['tx_myextension_domain_model_talk'][5]['delete'] = 1;
$cmd['tt_content'][1203]['copy'] = -303;
$cmd['pages'][$workspaceVersionUid]['discard'] = true;

$dataHandler->start([], $cmd);
$dataHandler->process_cmdmap();
Three commands. The first deletes talk 5 the way an editor would. The second copies content element 1203 to the page of element 303, directly after it. The third discards a workspace version. Then start runs with an empty datamap, followed by process_cmdmap.

Do not set the field deleted yourself via the datamap. That bypasses permissions, history and the reference index.

In your own code, keep this in mind:

  • Get a new DataHandler with makeInstance every time, because it keeps state.
  • It checks the permissions of the backend user, even when you call it from PHP. In a console command you therefore call the method initializeBackendAuthentication() of the class Bootstrap before start(). It then works as the administrator _cli_.
  • It is slower than direct SQL, but sorting, reference index and history stay correct. Process large imports in batches.

You hook your own logic on saving into it. Register the class in ext_localconf.php under processDatamapClass or processCmdmapClass.

$GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_tcemain.php']['processDatamapClass'][]
    = \MyVendor\MyExtension\Hooks\TalkHook::class;
Registering a hook. The class TalkHook is added under SC_OPTIONS, in the old key for the file class.t3lib_tcemain.php, at processDatamapClass.

The method processDatamap_preProcessFieldArray() receives the incoming values by reference, before the DataHandler checks and stores them. processDatamap_afterDatabaseOperations() only runs after writing. Hooks only take effect when data is written through the DataHandler.

The system registry

The registry is a small key-value store in the table sys_registry. It stores values permanently under a namespace and a key. Clearing the caches does not remove them.

$this->registry->set('tx_myextension', 'lastImport', time());
$lastImport = $this->registry->get('tx_myextension', 'lastImport', 0);
Code example. set stores the current time in the namespace tx_myextension under lastImport. get reads the value and returns 0 if nothing has been stored yet.

The third argument of get() is the default value. As the namespace you use the extension key with the prefix tx_. It must be at least two characters long, and core is reserved for the Core.

The registry suits small technical states such as the last import. It does not suit settings that editors maintain. Those belong in records with TCA or in site settings. Cached data belongs in a cache, and large amounts of data belong in the file system.

Key points

  • TYPO3 14 runs on MySQL, MariaDB, PostgreSQL and SQLite, using Doctrine DBAL and not Doctrine ORM.
  • WHERE filters before grouping, HAVING after it. UPDATE and DELETE without WHERE affect all rows.
  • In a one to n relationship the foreign key sits on the n side, and m to n needs an MM table.
  • Get a new QueryBuilder for every query. Read with executeQuery(), write with executeStatement().
  • The DefaultRestrictionContainer hides deleted, hidden and time-controlled records.
  • Every input goes through createNamedParameter(), column names only via a fixed list.
  • The DataHandler needs a backend user. Errors are in errorLog, new uids in substNEWwithIDs.

Terms

DBAL
Database Abstraction Layer, unifies access to different database systems.
datamap
Data array of the DataHandler with field values per table and uid.
cmdmap
Command array of the DataHandler, for example to copy, delete or discard.
MM table
Intermediate table for an m to n relationship.
Prepared statement
Query with placeholders whose values are sent separately.
Reference index
Table sys_refindex with all references between records and files.
Restriction
Condition that the QueryBuilder automatically adds to a query.
Soft delete
Deleting by setting the field deleted instead of removing the row.

As of: November 15, 2026 (TYPO3 14.3 LTS)

Sources:

  • TYPO3 Explained 14.3, chapter Database (Doctrine DBAL, QueryBuilder, restrictions, Connection)
  • TYPO3 Explained 14.3, chapters DataHandler, Reference index and System registry
  • TYPO3 14 changelog (discard command, reference index, QueryBuilderPaginator)
  • TYPO3 14.3 source code (typo3/cms-core), checked for restrictions, Connection, Registry and DataHandler
  • TYPO3 CMS Certified Developer 14 LTS learning objectives, area database access

This study guide was written with AI and checked against the sources.

Certifications & exams

Which exam comes next?

You can already practise for 51 exams, and 16 are up for a vote. See all certifications with their official information, cast your vote or suggest your exam.

See all certifications