Query Builder

Foundation Database wraps common wpdb operations with prepared bindings, quoted identifiers, consistent exceptions, and a small fluent query builder. It remains intentionally close to SQL so developers can inspect exactly what WordPress will execute.

Database OverviewInstall the package and register DatabaseProvider before injecting application table classes.

Tables used for queries do not need to be managed by Foundation migrations. A wrapper around an existing WordPress, WooCommerce, or legacy table can extend the supplied Table base class and provide only its stable name. For example, src/Database/Tables/Import_Log_Table.php can target an externally managed table without declaring ownership of its schema:

<?php declare(strict_types=1);

namespace Plugin\Database\Tables;

use StellarWP\Foundation\Database\Table\Table;

final readonly class Import_Log_Table extends Table {

	public function unprefixedName(): string {
		return 'import_log';
	}
}

The same table shape works whether Foundation migrations, another plugin, or an external system owns its schema.

Read rows

Inject the table object into the class that owns its queries. The table keeps physical naming and common database operations together. For example, create src/Report/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 $table
	) {
	}

	/**
	 * @return list<array<string, mixed>>
	 */
	public function published( int $limit = 100 ): array {
		return $this->table
			->query( 'r' )
			->select( 'r.id', 'r.status', 'r.payload', 'r.created_at' )
			->where( 'r.status', '=', 'published' )
			->orderBy( 'r.created_at', 'DESC' )
			->limit( $limit )
			->get();
	}
}

first() returns one row or null. get() returns a list of associative rows. Qualified identifiers such as r.created_at are quoted as `r`.`created_at` rather than as one identifier.

max( 'column' ) returns the maximum value matching the builder’s where() predicates. Aggregate queries ignore ordering and pagination because those clauses do not constrain the rows being aggregated.

Use null with equality operators for SQL null checks:

$unpublished = $this->table
	->query()
	->where( 'published_at', '=', null )
	->get();

$published = $this->table
	->query()
	->where( 'published_at', '!=', null )
	->get();

These comparisons compile to IS NULL and IS NOT NULL. Other operators with null are rejected because they do not have useful SQL semantics.

Write rows

Use the table object for inserts, updates, and deletes so physical table naming stays in one place:

$reportId = $this->table->insertGetId( [
	'status'     => 'draft',
	'payload'    => wp_json_encode( $payload ),
	'created_at' => current_time( 'mysql', true ),
] );

$updated = $this->table->update(
	[
		'status'     => 'published',
		'updated_at' => current_time( 'mysql', true ),
	],
	[ 'id' => $reportId ]
);

$deleted = $this->table->delete( [ 'id' => $reportId ] );

insert() returns the affected row count, while insertGetId() returns the generated integer ID. update() and delete() return affected row counts.

Add table-specific operations

Add an intent-revealing method to the table class when specialized SQL belongs to that table and should be reused by its consumers. The base Table exposes its table-scoped database gateway to subclasses through the protected database() method.

For example, add archive_status() to src/Database/Tables/Reports_Table.php:

/**
 * Archive reports with the supplied status.
 *
 * @throws \StellarWP\Foundation\Database\Exceptions\DatabaseException When the physical table name is invalid.
 * @throws \StellarWP\Foundation\Database\Exceptions\QueryException    When the update fails.
 */
public function archive_status( string $status ): int {
	return $this->database()->execute(
		'UPDATE %i SET status = %s WHERE status = %s',
		$this->name(),
		'archived',
		$status
	);
}

The method uses the same database service as the inherited table operations. Calling name() resolves and validates the physical table name for the active WordPress site when the operation runs.

Keep these methods scoped to one table. Cross-table queries, multi-step workflows, and business rules belong in a repository or feature service.

Inspect and execute SQL

Build a query before executing it when logging or diagnostics need the SQL shape and separate bindings:

$query = $this->table
	->query( 'r' )
	->select( 'r.id', 'r.status' )
	->where( 'r.status', '=', 'failed' )
	->limit( 25 )
	->toQuery();

$sql         = $query->toSql();
$bindings    = $query->bindings();
$preparedSql = $query->toPreparedSql();
$rows        = $query->get();

Prefer toSql() plus bindings() for structured diagnostics. A fully prepared SQL string may contain customer or application data and should not be logged without considering its sensitivity.

Inject QueryReader in addition to the table when specialized read SQL belongs to a repository workflow rather than one table, or when a query spans multiple tables. Add it explicitly to that repository’s constructor:

use StellarWP\Foundation\Database\Contracts\QueryReader;

public function __construct(
	private Reports_Table $table,
	private QueryReader $database
) {
}

The contract exposes prepared low-level read operations:

$row = $this->database->row(
	'SELECT * FROM %i WHERE id = %d',
	$this->table->name(),
	$reportId
);

$count = $this->database->value(
	'SELECT COUNT(*) FROM %i WHERE status = %s',
	$this->table->name(),
	'published'
);

Use QueryExecutor instead when the same repository must also execute raw writes:

use StellarWP\Foundation\Database\Contracts\QueryExecutor;

public function __construct(
	private Reports_Table $table,
	private QueryExecutor $database
) {
}

$affected = $this->database->execute(
	'UPDATE %i SET status = %s WHERE status = %s',
	$this->table->name(),
	'archived',
	'published'
);

Use prepare() when another WordPress API requires the prepared SQL string. Keep values in placeholders instead of concatenating untrusted input.

For normal application code, follow one of these paths:

Inject the concrete table for queries and writes scoped to that table.
Inject QueryReader for specialized raw or cross-table reads.
Inject QueryExecutor when the same raw workflow also executes writes.

The aggregate Database contract is available when one infrastructure collaborator genuinely needs the complete API. The narrower contracts are public extension capabilities for specialized raw-SQL repositories, adapters, and decorators. They are not setup choices every application must make: routine table-scoped repositories should continue injecting their concrete table.

Handle query failures

Database operations throw QueryException when wpdb reports an error. The exception retains the SQL template, bindings, and database error separately:

use Psr\Log\LoggerInterface;
use StellarWP\Foundation\Database\Exceptions\QueryException;

try {
	$rows = $query->get();
} catch ( QueryException $exception ) {
	$this->logger->error( 'Report query failed.', [
		'sql'            => $exception->sql(),
		'bindings'       => $exception->bindings(),
		'database_error' => $exception->databaseError(),
	] );

	throw $exception;
}

Avoid exposing database errors or bindings to end users. They may contain schema details or sensitive values.

Testing

Use wpunit tests for repositories and query behavior. Create the real table, exercise the real WordPress database, and remove the table during cleanup. This catches placeholder, collation, identifier, and MariaDB behavior that a mocked wpdb cannot reproduce.