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 the Database contract.

Read rows

Inject the Database contract and the table object into the class that owns the query. For example, create src/Report/Report_Repository.php:

Report_Repository.php

<?php declare(strict_types=1);

namespace YourPlugin\Report;

use StellarWP\Foundation\Database\Contracts\Database;
use YourPlugin\Database\Tables\Reports_Table;

final readonly class Report_Repository {

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

	/**
	 * @return list<array<string, mixed>>
	 */
	public function published( int $limit = 100 ): array {
		return $this->database
			->table( $this->table, '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.

Use null with equality operators for SQL null checks:

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

$published = $this->database
	->table( $this->table )
	->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->database->insertGetId( $this->table, [
	'status'     => 'draft',
	'payload'    => wp_json_encode( $payload ),
	'created_at' => current_time( 'mysql', true ),
] );

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

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

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

Inspect and execute SQL

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

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

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

The Database contract also exposes prepared low-level operations for queries that do not fit the builder:

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