Queries and Transactions

Register DatabaseProvider and inject your concrete table into the service that owns its queries. A table can wrap an existing WordPress or third-party table without owning its migrations.

Read and write rows

A repository is an optional way to organize reusable application queries:

In src/Report/Report_Repository.php:

Report_Repository.php

<?php declare(strict_types=1);

namespace Plugin\Report;

use Plugin\Database\Tables\Reports_Table;

final readonly class Report_Repository {

	public function __construct(
		private Reports_Table $reports,
	) {
	}

	/**
	 * @return array<string, mixed>|false
	 */
	public function find( int $id ): array|false {
		return $this->reports->query()
			->select( 'id', 'title' )
			->where( 'id = :id' )
			->setParameter( 'id', $id )
			->fetchAssociative();
	}
}

query() returns a fresh native Doctrine builder with the table configured as its source. Use query('report') for an alias. Supply SQL expressions yourself and bind external values using setParameter() or setParameters(). SQL fragments, column names, sorting, and aliases must come from application-owned choices, not request input.

$rows = $this->reports->query()
	->select( 'id', 'title' )
	->where( 'status = :status' )
	->setParameter( 'status', 'ready' )
	->orderBy( 'id', 'DESC' )
	->setMaxResults( 50 )
	->fetchAllAssociative();

$count = $this->reports->query()->select( 'COUNT(*)' )->fetchOne();

Doctrine supplies fetchAssociative() (one row or false), fetchAllAssociative() (rows), fetchOne() (one value or false), and executeQuery() for a native result. Database drivers may return numeric values as strings. Convert results according to your application contract.

Common writes stay table-scoped:

$id = $this->reports->insertGetId( [ 'title' => 'Weekly', 'status' => 'draft' ] );
$this->reports->update( [ 'status' => 'ready' ], [ 'id' => $id ] );
$this->reports->delete( [ 'id' => $id ] );

insert(), update(), and delete() return Doctrine’s affected-row count (int|string). insertGetId() returns the generated identifier (int|string) without truncation. Each accepts an optional $types map for Doctrine parameter types. Update and delete criteria must be nonempty; use the connection for an intentional whole-table operation.

Inject Doctrine\DBAL\Connection for joins, specialized SQL, and operations spanning tables. Resolve physical names through each table’s quotedName():

$db->executeStatement(
	'UPDATE ' . $reports->quotedName() . ' SET status = ? WHERE id = ?',
	[ 'ready', $id ],
);

Queries, results, types, and DBAL exceptions are supported native Doctrine APIs. See Doctrine’s query builder reference for expressions and advanced usage.

Choose a transaction boundary

Inject the shared connection into the service that owns the complete unit of work. Collaborating repositories can continue to use their injected tables.

use Doctrine\DBAL\Connection;
use Plugin\Database\Tables\Reports_Table;

final readonly class Create_Report {

	public function __construct(
		private Connection $db,
		private Reports_Table $reports,
	) {
	}

	public function create( string $title ): int|string {
		return $this->db->transactional( function () use ( $title ): int|string {
			return $this->reports->insertGetId( [ 'title' => $title, 'status' => 'draft' ] );
		} );
	}
}

transactional() returns the callback’s value only after commit is acknowledged. false and null are ordinary callback results, not rollback signals. Throw an exception to cancel work. The original escaping exception is preserved if cleanup also fails.

Nested transactions use savepoints. An inner success is provisional until the outer transaction commits. A database execution failure is terminal for the entire managed transaction, even if application code catches its exception. An inner business exception can be caught after its savepoint is rolled back.

Handle failures deliberately

• Ordinary SQL failures use Doctrine exceptions, such as Doctrine\DBAL\Exception\UniqueConstraintViolationException.
• TransactionFailed means a caught database failure prevented successful completion. Start fresh work at an application boundary after cleanup.
• CommitOutcomeUnknown means the server did not confirm commit. The data may already be committed. Inspect durable application state or use an idempotency key before retrying.
• A detected site or connection change interrupts managed work. Foundation never replays failed SQL or reconnects in the middle of a transaction.

The Foundation exceptions above live under StellarWP\Foundation\Database\Exceptions and extend DatabaseException. Native Doctrine SQL exceptions retain their own hierarchy.

Keep managed work on the shared connection

Use transactional tables. Do not issue DDL, manual transaction-control SQL, direct WordPress queries, or switch_to_blog() inside a managed transaction. DDL can implicitly commit MySQL transactions, and writes through other sessions are outside this transaction. External HTTP calls cannot be rolled back with database writes.

Between completed operations, WordPress may replace its mysqli connection. New queries and transactions use the replacement automatically. Prepared statements from the old connection are rejected; prepare them again for the new operation.

Test application behavior

Unit-test application decisions against your own repository contracts when useful. Use integration tests with real InnoDB tables for transaction boundaries, SQL behavior, and recovery. Test that an observer on a second connection cannot see uncommitted rows, that a failed operation leaves prior rows intact, and that caught database failures cannot produce a successful result.