The table exists. Now it needs a business language.
The previous chapter built storage, but controllers should not know columns, write SQL, or manage transactions themselves. A Task class will become the meeting point between application concepts and SQLite rows.
We will create a model API simple enough to read and save, yet rigorous enough to retain identity, prevent unexpected field assignment, handle empty updates, and guarantee compound operations.
Implement Task and CRUD operations, add QueryBuilder filters, protect creation plus history with a transaction, and test every contract on an isolated database.
Understand the model
A model represents a business concept and coherent operations around it. Task is not merely a SQL row: it is a task with a title, state, and evolution rules.
In a small application, the model can combine data access and simple behavior. As the domain grows, repositories and services may separate persistence and use cases without changing controller responsibilities.
Models know nothing about HTML, flash messages, or redirects. They can serve Web controllers, APIs, commands, and tests without presentation coupling.
The model carries the data's durable meaning and rules.
Define Task and identity
Task reflects useful table properties: id, title, description, completed, and timestamps. PHP types expose expectations before any query.
Identity connects an object to one exact row. After insertion, object and database must retain the same identity; a manual identifier must never be silently replaced.
Decide which properties external input may change. A fillable list or explicit constructor prevents mass assignment of sensitive fields such as user_id or created_at.
Identity and writable fields form an explicit model contract.
final class Task extends Model
{
protected static string $table = 'tasks';
protected array $fillable = ['title', 'description'];
public ?int $id = null;
public string $title;
public ?string $description = null;
public bool $completed = false;
public function complete(): void
{
$this->completed = true;
$this->save();
}
}Read a collection
Task::query() starts a query without immediately executing it. Add ordering, filters, and limits, then call all() for the collection.
Select only required data and impose stable ordering. Without ORDER BY, the database promises no order even when local rows appear consistent.
Large lists need pagination. Loading thousands of tasks to show twenty wastes memory and time and slows every later step.
Build the query, constrain its result, then execute explicitly.
$tasks = Task::query()
->where('completed', '=', false)
->where('user_id', '=', $userId)
->orderBy('created_at', 'desc')
->limit(20)
->all();Find one task
find($id) searches by primary key and returns Task or null. This forces callers to consider missing resources instead of failing later.
A require() or findOrFail() variant can throw a dedicated exception translated into 404. Choose one convention so every controller handles absence consistently.
For another property, use where with a bound value. Never concatenate title or user input into SQL text.
A precise lookup returns a typed resource or explicitly signals absence.
Create a task
Creation receives already validated data, applies business defaults, then inserts a row. The generated identifier is assigned back to the model before returning.
Do not blindly accept the whole form array. Build allowed attributes and let the database fill completed and timestamps when it owns that responsibility.
Read again only when the database generates required values the driver cannot otherwise return. An automatic extra query after every create may be unnecessary cost.
After create, the PHP object and stored row must represent exactly the same task.
Update state
An update starts from an existing task, applies permitted changes, and creates an UPDATE targeted by identity. A query without an identity condition could modify every row.
Prefer behaviors such as complete() and reopen() when transitions have meaning. They prevent duplicated completed=true assignments and offer a natural place for completed_at.
When no property changed, the model can treat the operation as a no-op rather than generating invalid UPDATE table SET WHERE SQL.
An update expresses a valid transition and always targets a known identity.
$task->title = $validated['title'];
$task->save(); // no query when nothing changedDelete without surprises
Deletion receives an existing identity and issues a limited DELETE. The result should report whether a row was actually removed so success differs from an already missing resource.
Before deletion, consider relationships and rules: delete history, refuse, or soft-delete with deleted_at? The answer comes from business needs, not technical habit.
Soft deletion helps restoration and auditing but complicates every read, which must exclude deleted rows by default. Add that complexity only for a real need.
Deletion must be targeted, verifiable, and coherent with relationships.
Compose with QueryBuilder
QueryBuilder builds a query step by step: where, orderBy, limit, select, and pagination. Values remain bound parameters, while column names and operators come from controlled lists.
Create readable scopes for repeated filters: pending(), completed(), or ownedBy($userId). Controllers then express intent without copying condition details.
Inspect SQL and parameters for diagnosis while masking sensitive values. Correct queries can remain slow without indexes supporting frequent filters.
QueryBuilder makes dynamic queries composable without sacrificing value safety.
Use a transaction
A transaction groups several writes as one unit: either all succeed or none persist. It protects invariants when an operation creates a task and its history together.
Start the transaction at the use-case level that knows the complete operation. A model save() does not always know which other writes belong.
Keep transactions short, roll back on every exception, and avoid network calls while open. For SQL nesting, the framework can use savepoints rather than pretending to start independent transactions.
A transaction protects a rule spanning several writes, not one isolated query.
$db->transaction(function () use ($task): void {
$task->save();
TaskHistory::record($task->id, 'created');
});Test the model
Model tests use a dedicated database recreated through migrations. They verify create, read, update, delete, constraints, and transactions without touching development data.
Add edge cases: maximum title, missing identity, no-change update, rollback after exception, and deletion of a missing resource.
Test observable contracts rather than PDO itself. Prove Task retains correct identity and state, not that the database library can execute INSERT.
A model suite protects data and transitions the rest of the application assumes true.
Guided workshop
Complete the Task model.
- Declare table, types, identity, and writable fields.
- Implement all, find, and pending/completed filters.
- Create a task and verify identity.
- Add complete, reopen, and no-change update.
- Implement targeted, verifiable deletion.
- Compose a paginated QueryBuilder query.
- Protect task and history with a transaction.
- Test CRUD, errors, rollback, and identity.
Reasoned solution
Always compare the object with the stored row.
After every important write, tests should prove that PHP identity and state match what the database finds. This catches dangerous defects before update or delete targets the wrong resource.
In summary
Task has become a model, not merely a row.
You can represent identity, restrict writable properties, compose safe reads, and implement CRUD transitions. QueryBuilder keeps filters readable and transactions protect operations that must succeed together.
- retain the same identity in object and database
- separate external values from SQL structure
- handle absence and empty updates explicitly
- wrap transactions around the complete use case
- test on an isolated database rebuilt through migrations
In chapter 10, we will connect routes, controllers, model, and views to complete the first end-to-end MVC application.