Databases

Most WebEngine applications eventually move from static content to persistent state. A to-do list needs somewhere to store items. An address book needs records that survive page refreshes. User accounts need data that belongs to one person and is still there tomorrow.

That is where the database layer becomes part of the normal application path. The lower-level database package is documented in full at https://www.php.gt/docs/Database/.

Connecting to a database

WebEngine reads database settings from configuration and constructs the database service for you. That means page logic and application classes can request the database dependency without having to build the connection manually first.

The main settings live in the [database] section of config.ini, where you choose the driver and connection details. SQLite, MySQL, and other supported drivers all use the same basic approach: WebEngine reads the config, creates the database service, and makes it available through the service container.

Organising SQL and queries

SQL is easier to maintain when it lives outside page logic. Rather than scattering query strings through PHP files, WebEngine projects usually keep them in the query/ directory.

The database package calls these organised sets of queries "query collections". A named query can then be called from PHP by its path-like name rather than by embedding the SQL directly into the page handler.

For example:

query/user/getById.sql

Can be called like this:

use Gt\Database\Database;

function go(Database $database):void {
	$row = $database->fetch("user/getById", 123);
}

The getById query can be called from PHP without the page needing to know the full SQL string. That keeps the PHP focused on the application's request state, while the SQL remains focused on the data manipulation.

The dedicated docs for query collections are here:

• https://www.php.gt/docs/Database/Query-collections/
• https://www.php.gt/docs/Database/Examples/

Query Collections

To further encapsulate the database access for highly maintainable code, the individual directories that contain the queries are called Query Collections, and can be passed to application classes in isolation, forcing an application class to only have access to one Query Collection. This makes the Repository-Entity pattern very powerful. For example:

function go(Database $database):void {
	$userRepository = new UserRepository($database->queryCollection("user"));
	$userRepository->checkCredentials(...);
}

In the above example, the UserRepository class is being constructed with access to only the user QueryCollection. The Query Collection behaves just as if it's a full database collection, but queries from other collections are not accessible. Imagine a service class for Shipping and a service class for Payment - the Shipping class shouldn't have access to all the queries across the whole database like the query/payment queries, so it's safer to pass the Shipping service only the query/shipping query collection.

Using database code from the app

Page logic should normally call an application class or service rather than mixing raw query work everywhere. That way the page handler can stay focused on the request and the response, while the application class can own the data-loading rules and result shaping.

That also gives us a clearer place to decide whether the page should receive:

• raw rows
• a small data object
• a domain entity
• a list already prepared for binding

For more information on making database behaviour strongly typed and reusable, see the Repository-Entity pattern.

Using the SQL Builder

As a project grows, some queries may be easier to express as PHP classes rather than plain SQL files. PHP.GT/SqlBuilder exists for that job and is documented at https://www.php.gt/docs/SqlBuilder/.

Query classes can extend other query classes, which is useful when several related queries share common clauses or conditions. That said, SQL files are still often the clearest starting point. Use the builder when the query genuinely benefits from composition, not simply because it is available.

Example:

query/Product.php

namespace App\Query;

use GT\SqlBuilder\SelectBuilder;

class User {
	// The baseline get query for all other user getBy*:
	private function get():SelectBuilder {
		return new SelectBuilder()
			->select("id", "name", "email")
			->from("user");		
	}
	
	// getById extends the baseline get with just the modifications it needs:
	public function getById():SelectBuilder {
		return $this->get()
			->where("id = ?")
			->limit(1);
	}
}

Then call it in page logic or an application class like this:

use GT\Database\Database;

function go(Database $database):void {
	$row = $database->fetch("user/getById", 123);
}

The default namespace for PHP query classes is App\Query, so a file such as query/Product.php can be resolved automatically. Query methods may return a SQL string, a Stringable object, or a phpgt/sqlbuilder builder.

---

See how everything is loaded on demand in the service container.