An application that forgets everything after each request is not finished.
Controllers and views already make the application feel functional. Yet without durable storage, no task survives the next restart. We will give the project real memory with SQLite and reproducible schema history through migrations.
The goal is not merely creating a .sqlite file. You will learn where it belongs, how to configure it without exposing secrets, how to build tasks on every machine, and how to evolve structure without improvising in production.
Configure a private SQLite database, create the tasks migration, verify migrate and rollback, then prepare separate development, test, and production databases.
Why a database?
So far, the application can receive a task and display an interface, but data kept only in a variable disappears after the request. A database provides durable memory shared by all requests.
A relational database organizes information into tables, rows, and columns. It can enforce types, required values, unique identifiers, and relationships that code alone may not guarantee.
SQLite stores the entire database in one file and requires no separate server. It is excellent for learning, testing, local development, and small deployments.
The database guarantees persistence and an essential part of data integrity.
Configure through the environment
Code needs a driver and database location, but those values differ across local, test, and production environments. They belong to configuration rather than PHP classes.
A local .env can define DB_CONNECTION=sqlite and DB_DATABASE=runtime/database/app.sqlite. phpaml.json describes non-secret project choices, while secrets must never be committed.
Use aml env:init to create the environment from a documented example. A new installation should understand expected variables without receiving private values from your machine.
The repository documents keys; every environment owns its values.
DB_CONNECTION=sqlite
DB_DATABASE=runtime/database/app.sqliteChoose the SQLite file path
The SQLite file contains real data and changes at runtime. It must not live in public, where visitors could download it, or in a versioned source folder.
PHPAML places runtime data under runtime/database. The Web process needs write permission there, while public/index.php remains the only Internet entry point.
Use an absolute path resolved from the project root so a changed working directory cannot accidentally create a second empty database.
A SQLite database is private runtime data, never a public asset.
Understand PDO and connection
PDO provides one interface for SQL databases. A SQLite connection uses a DSN beginning with sqlite:, followed by the file path. The framework centralizes creation so models do not rebuild connections.
Enable exception error mode and foreign-key constraints. Silent failures create inconsistent data; clear exceptions can be logged and translated into appropriate responses.
Always prepare queries containing external values. Bound parameters separate SQL code from data and prevent user input from changing query structure.
Connection is centralized; external values use prepared parameters.
Design the tasks table
Before writing a migration, describe what a task represents. It has an identifier, title, optional description, completion state, and creation and update timestamps.
Choose constraints from business meaning: title cannot be null, completed gets a default, and id is the primary key. Database constraints also protect writes from scripts or future APIs.
Do not add every imaginable column. A minimal explicit schema evolves better than a table full of speculative fields.
Schema translates durable business invariants into data constraints.
Create a migration
A migration is a versioned, reproducible schema change. It describes how to advance with up() and, when safe, how to return with down().
The create_tasks_table name communicates intent. A sequence or timestamp orders migrations so every installation applies the same history.
A migration must remain deterministic: it cannot depend on data found only on your machine or ask interactive questions during deployment.
Schema belongs to the project's versioned history, not manual manipulation.
final class CreateTasksTable extends Migration
{
public function up(): void
{
Schema::create('tasks', function (Blueprint $table): void {
$table->id();
$table->string('title', 120);
$table->text('description')->nullable();
$table->boolean('completed')->default(false);
$table->timestamps();
});
}
public function down(): void
{
Schema::dropIfExists('tasks');
}
}Run and verify migrations
aml migrate applies only missing migrations and records them in an internal table. Running it again must not recreate existing tables.
After running, inspect status, the actual tasks structure, and important constraints. A command ending without error alone does not prove schema matches intent.
Run migrations in CI against a fresh database. This catches hidden dependency on a locally hand-edited database.
A migration is reliable when it repeatedly builds the correct database from zero.
aml make:migration create_tasks_table
aml migrate
aml migrate:statusRoll back without losing data
Rollback runs down() for the latest batch. Dropping a table reverses structure but destroys rows, so a technically reversible operation can remain dangerous.
In development, rollback helps correct a recent migration. In production, prefer a corrective migration, back up first, and consider code versions still running.
Some data transformations need several stages: add a nullable column, populate values, then strengthen the constraint in a later migration.
Before rollback, distinguish schema reversibility from actual data recovery.
aml migrate:rollback
# Verify data impact before production rollback.Separate development, test, and production
Every environment needs its own database. Tests must never touch developer tasks, and a local command must never erase production.
Use a temporary or dedicated test database recreated automatically. Demo data comes from explicit seeders or fixtures rather than uncontrolled production copies.
In production, limit permissions, back up SQLite, and prevent concurrent deployments from running the same migrations without coordination.
Environments share schema, never data or secrets.
Back up and diagnose
A file database simplifies backup, but copying during a write can be unsafe. Use appropriate SQLite mechanisms or briefly stop writes for a consistent snapshot.
When errors occur, inspect the resolved path, folder existence, permissions, PDO SQLite extension, and migration status. An unexpected empty database often means a wrong path rather than magical data loss.
Monitor size, disk space, and backup frequency. Test restoration too: a backup that cannot restore is only a feeling of safety.
Persistence is complete only when backup, diagnosis, and restoration are planned.
Guided workshop
Build the application database.
- Initialize .env and select SQLite.
- Place the database under runtime/database.
- Create the tasks migration and constraints.
- Apply and inspect the migration.
- Test rollback with temporary data.
- Rebuild the entire database from zero.
- Configure a separate test database.
- Document production backup and restoration.
Reasoned solution
Prove the schema does not depend on your computer.
Delete only the test database, recreate it with migrations, then run checks. If everything works without manual changes, schema history is truly reproducible.
In summary
The database is now durable, private, and reproducible.
You separated configuration from code, placed SQLite under runtime, designed a minimal schema, and turned that schema into versioned migrations. You also know why rollback, backup, and environment separation require care.
- keep the database outside public and source control
- describe configuration through .env and phpaml.json
- version every schema change
- test migrations from an empty database
- back up before destructive operations
In chapter 9, we will build the Task model that uses this database to read, create, update, and delete tasks with safe queries and transactions.