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.

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 YourPlugin\Report;

use YourPlugin\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 Database contract 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 the Database contract in addition to the table when specialized 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\Database;

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

The contract exposes prepared low-level operations for specialized SQL:

$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'
);

$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.

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.