Chapter 5: Database#

1. From Arrays to Real Data#

In Chapters 2 and 3, data lived in PHP arrays. Every server restart wiped the slate. That works for learning routing and responses. Real applications need persistence. This chapter covers Tina4's database layer: raw queries, parameterised queries, transactions, schema inspection, helper methods, migrations, and seeding.

Tina4 supports five database engines: SQLite, PostgreSQL, MySQL, Microsoft SQL Server, and Firebird. The API stays identical across all of them. Switch databases by changing one line in .env.

2. Connecting to a Database#

The Default: SQLite#

tina4 init creates an empty data/ directory but does not create a database file. The default .env contains:

bash

TINA4_DEBUG=true

No explicit TINA4_DATABASE_URL means Tina4 defaults to sqlite:///data/app.db. The SQLite file is created automatically the first time Tina4 opens a database connection (for example, when you run a query, execute a migration, or the framework initialises the database layer at startup with a TINA4_DATABASE_URL configured).

Connection Strings for Other Databases#

Set TINA4_DATABASE_URL in .env:

bash

# SQLite (explicit)TINA4_DATABASE_URL=sqlite:///data/app.db# PostgreSQLTINA4_DATABASE_URL=postgres://localhost:5432/myapp# MySQLTINA4_DATABASE_URL=mysql://localhost:3306/myapp# Microsoft SQL ServerTINA4_DATABASE_URL=mssql://localhost:1433/myapp# FirebirdTINA4_DATABASE_URL=firebird://localhost:3050/path/to/database.fdb

Firebird URL Forms#

Firebird is the awkward one: every other engine has a server-side database name (postgres://host:port/dbname), but Firebird wants either an absolute file path on the server, a Windows drive-letter path, or an alias. The classic URI form needs a double slash to keep the leading / of an absolute path through parse_url, which is unintuitive.

Tina4 normalises five equivalent forms. Pick whichever reads best:

bash

# Classic double-slash absolute path -- the URL spec wayTINA4_DATABASE_URL=firebird://SYSDBA:masterkey@localhost:3050//firebird/data/app.fdb# Single-slash absolute path -- what most people instinctively typeTINA4_DATABASE_URL=firebird://SYSDBA:masterkey@localhost:3050/firebird/data/app.fdb# Windows drive-letter path (also accepts /C%3A/Data/app.fdb)TINA4_DATABASE_URL=firebird://SYSDBA:masterkey@host:3050/C:/Data/app.fdb# Firebird alias (single token, no slashes)TINA4_DATABASE_URL=firebird://SYSDBA:masterkey@localhost:3050/employee

For ops setups that keep the server URL and database location in separate config layers -- or for Windows backslash paths -- set TINA4_DATABASE_FIREBIRD_PATH:

bash

TINA4_DATABASE_FIREBIRD_PATH=C:\firebird\data\app.fdbTINA4_DATABASE_URL=firebird://SYSDBA:masterkey@localhost:3050/ignored

Firebird Connection Charset#

Firebird connects as UTF8 by default. A database created under the NONE charset stores raw bytes, and reading UTF-8 text out of it as UTF8 double-encodes every non-ASCII character. Override the charset with a ?charset= query on the URL or the TINA4_DATABASE_CHARSET environment variable. The URL query wins, then the env var, then the UTF8 default, so existing connections are unchanged.

bash

# Connection URL queryTINA4_DATABASE_URL=firebird://SYSDBA:masterkey@localhost:3050/app.fdb?charset=NONE# Or via the environment variable (applies to every Firebird connection)TINA4_DATABASE_CHARSET=WIN1252

The env override wins over whatever path is in the URL.

Firebird: Dual-Driver Support#

The Firebird adapter works with either the ibase_* or fbird_* PHP functions. It auto-detects which set is available at connection time. If you have ext-interbase installed, it uses ibase_*. If you have the newer fbird_* functions, it uses those instead. No configuration needed -- install whichever extension is available for your platform and the adapter picks it up.

Separate Credentials#

Keep credentials out of the connection string. Better for production:

bash

TINA4_DATABASE_URL=postgres://localhost:5432/myappTINA4_DATABASE_USERNAME=myuserTINA4_DATABASE_PASSWORD=secretpassword

Tina4 merges these at startup. Separate variables take precedence over anything embedded in the URL.

Connection Pooling#

For applications that handle many concurrent requests, enable connection pooling with the pool parameter:

php

$db = new Database("postgres://localhost/mydb", pool: 5);

The pool parameter controls how many database connections are maintained:

pool: 0 (the default) -- a single connection is used for all queries
pool: N (where N > 0) -- N connections are created and rotated round-robin across queries

Pooled connections are thread-safe. Each query is dispatched to the next available connection in the pool. This eliminates contention when multiple route handlers query the database simultaneously.

Verifying the Connection#

Update .env. Restart. Check:

bash

curl http://localhost:7145/health

json

{  "status": "ok",  "version": "3.0.0",  "uptime": 3.14,  "framework": "tina4-php"}

3. Getting the Database Object#

Access the database through the global Tina4\Database class:

php

<?phpuse Tina4\Router;use Tina4\Database;Router::get("/api/test-db", function ($request, $response) {    $db = Database::getConnection();    $result = $db->fetch("SELECT 1 + 1 AS answer");    return $response->json($result);});

bash

curl http://localhost:7145/api/test-db

json

{"answer": 2}

Database::getConnection() returns the active connection. Call fetch(), execute(), and fetchOne() on it.

4. Raw Queries#

fetch() -- Get Multiple Rows#

php

$db = Database::getConnection();// Returns an array of associative arrays$products = $db->fetch("SELECT * FROM products WHERE price > 50");

Each row is an associative array:

php

// $products looks like:[    ["id" => 1, "name" => "Keyboard", "price" => 79.99],    ["id" => 4, "name" => "Standing Desk", "price" => 549.99]]

DatabaseResult#

fetch() returns a DatabaseResult object. It behaves like an array but carries extra metadata about the query.

Properties#

php

$result = $db->fetch("SELECT * FROM users WHERE active = ?", [1]);$result->records;      // [["id" => 1, "name" => "Alice"], ["id" => 2, "name" => "Bob"]]$result->columns;      // ["id", "name", "email", "active"]$result->count;        // total number of matching rows$result->limit;        // query limit (if set)$result->offset;       // query offset (if set)

Iteration#

A DatabaseResult is iterable. Use it directly in foreach:

php

foreach ($result as $user) {    echo $user["name"];}

Index Access#

Access rows by index like a regular array:

php

$firstUser = $result[0];

Countable#

count() works on the result:

php

echo count($result); // number of records in this result set

Conversion Methods#

php

$result->toJson();      // JSON string of all records$result->toCsv();       // CSV string with column headers$result->toArray();     // plain array of associative arrays$result->toPaginate();  // ["records" => [...], "count" => 42, "limit" => 10, "offset" => 0]

toPaginate() is designed for building paginated API responses. It bundles the records with the total count, limit, and offset in a single array.

Schema Metadata with columnInfo()#

columnInfo() returns detailed metadata about the columns in the result set. The data is lazy-loaded -- it only queries the database schema when you call the method for the first time:

php

$info = $result->columnInfo();// [//     ["name" => "id", "type" => "INTEGER", "size" => null, "decimals" => null, "nullable" => false, "primaryKey" => true],//     ["name" => "name", "type" => "TEXT", "size" => null, "decimals" => null, "nullable" => false, "primaryKey" => false],//     ["name" => "email", "type" => "TEXT", "size" => 255, "decimals" => null, "nullable" => true, "primaryKey" => false],//     ...// ]

Each column entry contains:

Field	Description
`name`	Column name
`type`	Database type (e.g. `INTEGER`, `TEXT`, `REAL`)
`size`	Maximum size (or `null` if not applicable)
`decimals`	Decimal places (or `null`)
`nullable`	Whether the column allows `NULL`
`primaryKey`	Whether the column is part of the primary key

This is useful for building dynamic forms, generating documentation, or validating data before insert.

fetchOne() -- Get a Single Row#

php

$product = $db->fetchOne("SELECT * FROM products WHERE id = 1");// Returns: ["id" => 1, "name" => "Keyboard", "price" => 79.99]

No match returns null.

execute() -- Run a Statement#

For INSERT, UPDATE, DELETE, and DDL -- statements that do not return rows:

php

$db->execute("INSERT INTO products (name, price) VALUES ('Widget', 9.99)");$db->execute("UPDATE products SET price = 89.99 WHERE id = 1");$db->execute("DELETE FROM products WHERE id = 5");$db->execute("CREATE TABLE IF NOT EXISTS logs (id INTEGER PRIMARY KEY, message TEXT, created_at TEXT)");

Full Example: A Simple Query Route#

php

<?phpuse Tina4\Router;use Tina4\Database;Router::get("/api/products", function ($request, $response) {    $db = Database::getConnection();    $products = $db->fetch("SELECT * FROM products ORDER BY name");    return $response->json([        "products" => $products,        "count" => count($products)    ]);});

bash

curl http://localhost:7145/api/products

json

{  "products": [    {"id": 1, "name": "Keyboard", "price": 79.99, "in_stock": 1},    {"id": 2, "name": "Mouse", "price": 29.99, "in_stock": 1},    {"id": 3, "name": "Monitor", "price": 399.99, "in_stock": 0}  ],  "count": 3}

5. Parameterised Queries#

Never concatenate user input into SQL strings. That is how SQL injection happens:

php

// NEVER do this:$db->fetch("SELECT * FROM products WHERE name = '" . $userInput . "'");

Use parameterised queries instead. Parameters go in the second argument:

php

$db = Database::getConnection();// Named parameters$product = $db->fetchOne(    "SELECT * FROM products WHERE id = :id",    ["id" => 42]);// Positional parameters$products = $db->fetch(    "SELECT * FROM products WHERE price BETWEEN ? AND ? ORDER BY price",    [10.00, 100.00]);

The database driver handles escaping. Your input never touches the SQL string.

A Safe Search Endpoint#

php

<?phpuse Tina4\Router;use Tina4\Database;Router::get("/api/products/search", function ($request, $response) {    $db = Database::getConnection();    $q = $request->params["q"] ?? "";    $maxPrice = (float) ($request->params["max_price"] ?? 99999);    if (empty($q)) {        return $response->json(["error" => "Query parameter 'q' is required"], 400);    }    $products = $db->fetch(        "SELECT * FROM products WHERE name LIKE :query AND price <= :maxPrice ORDER BY name",        ["query" => "%" . $q . "%", "maxPrice" => $maxPrice]    );    return $response->json([        "query" => $q,        "max_price" => $maxPrice,        "results" => $products,        "count" => count($products)    ]);});

bash

curl "http://localhost:7145/api/products/search?q=key&max_price=100"

json

{  "query": "key",  "max_price": 100,  "results": [    {"id": 1, "name": "Wireless Keyboard", "price": 79.99, "in_stock": 1}  ],  "count": 1}

6. Transactions#

Group operations that must succeed or fail as one unit:

php

<?phpuse Tina4\Router;use Tina4\Database;Router::post("/api/orders", function ($request, $response) {    $db = Database::getConnection();    $body = $request->body;    try {        $db->startTransaction();        // Create the order        $db->execute(            "INSERT INTO orders (customer_id, total, status) VALUES (:customerId, :total, 'pending')",            ["customerId" => $body["customer_id"], "total" => $body["total"]]        );        // Get the new order ID        $order = $db->fetchOne("SELECT last_insert_rowid() AS id");        $orderId = $order["id"];        // Create order items        foreach ($body["items"] as $item) {            $db->execute(                "INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (:orderId, :productId, :qty, :price)",                [                    "orderId" => $orderId,                    "productId" => $item["product_id"],                    "qty" => $item["quantity"],                    "price" => $item["price"]                ]            );            // Decrease stock            $db->execute(                "UPDATE products SET stock = stock - :qty WHERE id = :productId",                ["qty" => $item["quantity"], "productId" => $item["product_id"]]            );        }        $db->commit();        return $response->json(["order_id" => $orderId, "status" => "created"], 201);    } catch (\Exception $e) {        $db->rollback();        return $response->json(["error" => "Order failed: " . $e->getMessage()], 500);    }});

Any step fails. rollback() undoes everything. The database never lands in a half-finished state.

Call commit() to save. Forget it and the transaction rolls back when the connection closes.

7. Schema Inspection#

Tina4 exposes methods to inspect your database structure at runtime. The Database object delegates to the underlying adapter, so these work across all five engines.

getTables()#

php

$db = Database::getConnection();$tables = $db->getTables();

Returns an array of table names:

php

["orders", "order_items", "products", "users"]

getColumns()#

php

$columns = $db->getColumns("products");

Returns column definitions:

php

[    ["name" => "id", "type" => "INTEGER", "nullable" => false, "primary" => true],    ["name" => "name", "type" => "TEXT", "nullable" => false, "primary" => false],    ["name" => "price", "type" => "REAL", "nullable" => true, "primary" => false],    ["name" => "in_stock", "type" => "INTEGER", "nullable" => true, "primary" => false]]

tableExists()#

php

if ($db->tableExists("products")) {    // Table exists, safe to query}

getDatabaseType()#

php

$engine = $db->getDatabaseType();// "sqlite", "postgresql", "mysql", "mssql", or "firebird"

Returns a lowercase string identifying the active database engine. Use this when you need engine-specific SQL in a multi-database setup.

A Schema Info Endpoint#

Combine these methods to build a schema browser:

php

<?phpuse Tina4\Router;use Tina4\Database;Router::get("/api/schema", function ($request, $response) {    $db = Database::getConnection();    $tables = $db->getTables();    $schema = [];    foreach ($tables as $table) {        $schema[$table] = $db->getColumns($table);    }    return $response->json([        "engine" => $db->getDatabaseType(),        "tables" => $schema    ]);});

Schema inspection powers admin dashboards, migration generators, and dynamic form builders. The database tells you its own structure -- no guesswork required.

8. Batch Operations with executeMany()#

Insert or update many rows in one call:

php

$db = Database::getConnection();$products = [    ["name" => "Widget A", "price" => 9.99],    ["name" => "Widget B", "price" => 14.99],    ["name" => "Widget C", "price" => 19.99],    ["name" => "Widget D", "price" => 24.99]];$db->executeMany(    "INSERT INTO products (name, price) VALUES (:name, :price)",    $products);

executeMany() prepares the statement once and executes it for each item. The SQL is parsed once. Four rows inserted. Much faster than four separate execute() calls.

9. Helper Methods: insert(), update(), delete()#

Shorthand methods for simple operations. No SQL needed.

insert()#

php

$db = Database::getConnection();// Insert a single row$db->insert("products", [    "name" => "Wireless Mouse",    "price" => 34.99,    "in_stock" => 1]);// Insert multiple rows$db->insert("products", [    ["name" => "USB Cable", "price" => 9.99, "in_stock" => 1],    ["name" => "HDMI Cable", "price" => 14.99, "in_stock" => 1],    ["name" => "DisplayPort Cable", "price" => 19.99, "in_stock" => 0]]);

update()#

php

// Update rows matching a filter$db->update("products", ["price" => 39.99, "in_stock" => 1], "id = :id", ["id" => 7]);

Third argument: WHERE clause. Fourth argument: parameters.

delete()#

php

// Delete rows matching a filter$db->delete("products", "id = :id", ["id" => 7]);

What they return#

insert(), update(), and delete() each return a DatabaseResult. The object is truthy on success, so if ($db->insert(...)) still reads naturally. It carries ->affectedRows and ->lastId. The new row id lands on ->lastId after an insert(); it is null for update() and delete().

php

$result = $db->insert("products", ["name" => "Wireless Mouse", "price" => 34.99]);$result->lastId;        // the new row id$result->affectedRows;  // 1$changed = $db->update("products", ["price" => 39.99], "id = :id", ["id" => 7]);$changed->affectedRows; // rows updated

A failed write raises a DatabaseException. It never returns false, so wrap writes in try/catch rather than testing the return value.

These helpers generate SQL for you. Use them for simple CRUD. For joins, subqueries, or aggregations, reach for raw queries.

10. Migrations#

Migrations are versioned SQL scripts that evolve your schema over time. Write migration files. Tina4 applies them in order and tracks what has run. No manual CREATE TABLE calls against production.

File Naming#

Two naming patterns are supported:

Sequential: 000001_create_products_table.sql
Timestamp: 20260322143000_create_products_table.sql (YYYYMMDDHHMMSS)

Both work. Pick one pattern and stick with it throughout a project. Do not mix them.

Generating a Migration#

bash

tina4 generate migration create_products_table

Created migration: migrations/20260322143000_create_products_table.sqlCreated migration: migrations/20260322143000_create_products_table.down.sql

The generator creates two files: the migration itself and a matching .down.sql file for rollback.

Writing the Migration#

Edit migrations/20260322143000_create_products_table.sql:

sql

CREATE TABLE products (    id INTEGER PRIMARY KEY AUTOINCREMENT,    name TEXT NOT NULL,    category TEXT NOT NULL DEFAULT 'Uncategorized',    price REAL NOT NULL DEFAULT 0.00,    in_stock INTEGER NOT NULL DEFAULT 1,    created_at TEXT DEFAULT CURRENT_TIMESTAMP,    updated_at TEXT DEFAULT CURRENT_TIMESTAMP);

Edit migrations/20260322143000_create_products_table.down.sql:

sql

DROP TABLE IF EXISTS products;

The .down.sql file is optional but recommended for production projects. It contains the SQL that reverses the migration.

Running Migrations#

bash

tina4 migrate# ortina4php migrate

Running migrations...  [APPLIED] 20260322143000_create_products_table.sqlMigrations complete. 1 applied.

Tina4 applies pending files in numeric-prefix order -- the leading number drives the sort, so 9_create_x.sql runs before 10_create_y.sql (a plain alphabetical sort would put 10 first). Files without a numeric or timestamp prefix sort last and log a warning, since their order is undefined. Each call to tina4 migrate is a batch. All pending migrations applied in a single run share the same batch number. This matters for rollback.

Each migration file is wrapped in its own transaction. Per-file atomicity is real only on engines with transactional DDL (PostgreSQL): there a failed statement rolls the whole file back. MySQL, Firebird, and SQLite auto-commit DDL, so a half-applied file leaves its earlier statements in place. Keep one logical change per file. A failed migration stops the run and raises -- already-applied files stay applied. Fix the SQL and re-run; the runner picks up where it left off.

CREATE TABLE and ALTER TABLE ... ADD are idempotent on Firebird and MSSQL, which lack IF NOT EXISTS: the runner checks whether the table or column already exists and skips that statement on a re-run instead of erroring. Only a genuine already-exists is skipped -- every other error still raises. SQLite, MySQL, and PostgreSQL use native IF NOT EXISTS.

Automatic Migrations on Startup#

Tina4 runs your pending migrations the moment the server boots. It checks for a migrations/ folder (with at least one migration file) and a resolvable database, then applies anything outstanding before the first request lands. No extra deploy step. Ship the code, start the service, and the schema catches up.

This runs the same migration engine as the CLI, so the result matches tina4 migrate. The startup hook fires after routes and the database bind, just before the server accepts traffic.

Startup migration is non-breaking. If a migration fails, Tina4 logs the error and the service still boots:

Startup auto-migration failed: <error> - the service is starting anyway. Run `tina4 migrate` to retry.

A successful run logs how many it applied:

Applied 2 pending migration(s) on startup

The explicit tina4 migrate CLI stays fail-fast - it exits non-zero on failure, so CI keeps a real exit code to gate on. Only the startup hook swallows the error to keep the service available.

Set TINA4_AUTO_MIGRATE=false (or 0, no, off) to turn the hook off. The default is true.

Multi-instance caveat. When several instances boot at once against one database, they race to apply the same migrations. For multi-instance production, disable the hook and migrate as a separate deploy step:

bash

TINA4_AUTO_MIGRATE=false   # in each instance's environmenttina4 migrate              # run once, before rolling out the new instances

Checking Migration Status#

bash

tina4php migrate:status

Migration                                    Status     Applied At---------                                    ------     ----------20260322143000_create_products_table.sql      applied    2026-03-22 14:30:0020260322150000_create_orders_table.sql        pending    -

Shows which migrations have been applied and which are still pending.

Rolling Back#

bash

tina4php migrate:rollback

Rolling back last batch...  [ROLLED BACK] 20260322150000_create_orders_table.sqlRollback complete. 1 rolled back.

Rollback undoes the entire last batch. It finds each migration's .down.sql file and executes it, then removes the tracking records. If you applied three migrations in one tina4 migrate call, rollback undoes all three.

The .down.sql file must share the exact same base name as the migration. For 20260322150000_create_orders_table.sql, the down file is 20260322150000_create_orders_table.down.sql.

Tracking Table#

Tina4 creates a tina4_migration table to track applied migrations. It has six columns:

Column	Type	Description
`id`	integer	Auto-increment primary key
`migration_name`	VARCHAR(500)	The migration filename (unique)
`description`	VARCHAR(500)	Human-readable description derived from the filename
`batch`	integer	Batch number (increments each `tina4 migrate` run)
`executed_at`	VARCHAR(50)	ISO-8601 timestamp string of when the migration ran
`passed`	integer	`1` marks the migration as applied

A migration counts as applied when a row exists for it with passed = 1. The runner writes only passed = 1 rows. A failed file rolls back and writes no row, so the next tina4 migrate retries it. You should not modify this table directly, but inspecting it can help debug migration issues.

Advanced SQL: Stored Procedures and Block Comments#

The migration runner handles more than simple semicolon-delimited statements. It correctly parses:

$$ delimited blocks -- PostgreSQL stored procedures and functions
// blocks -- alternative delimiter blocks
/* */ block comments -- skipped during parsing
-- line comments -- skipped during parsing

A PostgreSQL stored procedure migration works without issues:

sql

CREATE OR REPLACE FUNCTION update_modified_column()RETURNS TRIGGER AS $$BEGIN    NEW.updated_at = NOW();    RETURN NEW;END;$$ LANGUAGE plpgsql;CREATE TRIGGER set_updated_at    BEFORE UPDATE ON products    FOR EACH ROW    EXECUTE FUNCTION update_modified_column();

No special configuration needed. The SQL splitter recognises $$ boundaries and treats the block as a single statement.

A Real Migration Sequence#

migrations/├── 20260322143000_create_products_table.sql├── 20260322143000_create_products_table.down.sql├── 20260322143100_create_users_table.sql├── 20260322143100_create_users_table.down.sql├── 20260322143200_create_orders_table.sql├── 20260322143200_create_orders_table.down.sql├── 20260322143300_create_order_items_table.sql├── 20260322143300_create_order_items_table.down.sql└── 20260323091500_add_email_index_to_users.sql

The index migration without a .down.sql -- this is fine. Down migrations are optional. If you roll back and no .down.sql exists, the tracking record is removed but no reversal SQL runs.

The index migration:

sql

CREATE INDEX idx_users_email ON users (email);

Its down file (20260323091500_add_email_index_to_users.down.sql), if you choose to create one:

sql

DROP INDEX IF EXISTS idx_users_email;

Migrations run in numeric-prefix order. Each runs only once.

11. Query Caching#

For read-heavy applications:

bash

TINA4_DB_CACHE=true

Tina4 caches results of fetch() and fetchOne() calls. Identical queries with identical parameters return cached results instead of hitting the database.

The cache invalidates when you call execute(), insert(), update(), or delete() on the same table.

Per-query control:

php

// Force a fresh query (bypass cache)$products = $db->fetch("SELECT * FROM products", [], false); // third arg = use cache// Clear the entire cache$db->clearCache();

12. Exercise: Build a Notes App#

A notes application backed by SQLite. Migration for the table. Full CRUD API.

Requirements#

Create a migration for a notes table:
- id -- integer, primary key, auto-increment
- title -- text, not null
- content -- text, not null
- tag -- text, default "general"
- created_at -- text, default current timestamp
- updated_at -- text, default current timestamp
Build these endpoints:

Method	Path	Description
`GET`	`/api/notes`	List all notes. Support `?tag=` and `?search=` filters.
`GET`	`/api/notes/{id:int}`	Get a single note. 404 if not found.
`POST`	`/api/notes`	Create a note. Validate title and content are not empty.
`PUT`	`/api/notes/{id:int}`	Update a note. 404 if not found.
`DELETE`	`/api/notes/{id:int}`	Delete a note. 204 on success, 404 if not found.

Test with:#

bash

# Createcurl -X POST http://localhost:7145/api/notes \  -H "Content-Type: application/json" \  -d '{"title": "Shopping List", "content": "Milk, eggs, bread", "tag": "personal"}'# List allcurl http://localhost:7145/api/notes# Searchcurl "http://localhost:7145/api/notes?search=shopping"# Filter by tagcurl "http://localhost:7145/api/notes?tag=personal"# Get onecurl http://localhost:7145/api/notes/1# Updatecurl -X PUT http://localhost:7145/api/notes/1 \  -H "Content-Type: application/json" \  -d '{"title": "Updated Shopping List", "content": "Milk, eggs, bread, butter"}'# Deletecurl -X DELETE http://localhost:7145/api/notes/1

13. Solution#

Migration#

Generate the migration:

bash

tina4 generate migration create_notes_table

Edit migrations/20260322143000_create_notes_table.sql:

sql

CREATE TABLE notes (    id INTEGER PRIMARY KEY AUTOINCREMENT,    title TEXT NOT NULL,    content TEXT NOT NULL,    tag TEXT NOT NULL DEFAULT 'general',    created_at TEXT DEFAULT CURRENT_TIMESTAMP,    updated_at TEXT DEFAULT CURRENT_TIMESTAMP);

Edit migrations/20260322143000_create_notes_table.down.sql:

sql

DROP TABLE IF EXISTS notes;

Run it:

bash

tina4 migrate

Running migrations...  [APPLIED] 20260322143000_create_notes_table.sqlMigrations complete. 1 applied.

Routes#

Create src/routes/notes.php:

php

<?phpuse Tina4\Router;use Tina4\Database;// List all notes with optional filtersRouter::get("/api/notes", function ($request, $response) {    $db = Database::getConnection();    $tag = $request->params["tag"] ?? "";    $search = $request->params["search"] ?? "";    $sql = "SELECT * FROM notes";    $params = [];    $conditions = [];    if (!empty($tag)) {        $conditions[] = "tag = :tag";        $params["tag"] = $tag;    }    if (!empty($search)) {        $conditions[] = "(title LIKE :search OR content LIKE :search)";        $params["search"] = "%" . $search . "%";    }    if (!empty($conditions)) {        $sql .= " WHERE " . implode(" AND ", $conditions);    }    $sql .= " ORDER BY updated_at DESC";    $notes = $db->fetch($sql, $params);    return $response->json([        "notes" => $notes,        "count" => count($notes)    ]);});// Get a single noteRouter::get("/api/notes/{id:int}", function ($request, $response) {    $db = Database::getConnection();    $id = $request->params["id"];    $note = $db->fetchOne("SELECT * FROM notes WHERE id = :id", ["id" => $id]);    if ($note === null) {        return $response->json(["error" => "Note not found", "id" => $id], 404);    }    return $response->json($note);});// Create a noteRouter::post("/api/notes", function ($request, $response) {    $db = Database::getConnection();    $body = $request->body;    // Validate    $errors = [];    if (empty($body["title"])) {        $errors[] = "Title is required";    }    if (empty($body["content"])) {        $errors[] = "Content is required";    }    if (!empty($errors)) {        return $response->json(["errors" => $errors], 400);    }    $db->execute(        "INSERT INTO notes (title, content, tag) VALUES (:title, :content, :tag)",        [            "title" => $body["title"],            "content" => $body["content"],            "tag" => $body["tag"] ?? "general"        ]    );    $note = $db->fetchOne("SELECT * FROM notes WHERE id = last_insert_rowid()");    return $response->json($note, 201);});// Update a noteRouter::put("/api/notes/{id:int}", function ($request, $response) {    $db = Database::getConnection();    $id = $request->params["id"];    $body = $request->body;    $existing = $db->fetchOne("SELECT * FROM notes WHERE id = :id", ["id" => $id]);    if ($existing === null) {        return $response->json(["error" => "Note not found", "id" => $id], 404);    }    $db->execute(        "UPDATE notes SET title = :title, content = :content, tag = :tag, updated_at = CURRENT_TIMESTAMP WHERE id = :id",        [            "title" => $body["title"] ?? $existing["title"],            "content" => $body["content"] ?? $existing["content"],            "tag" => $body["tag"] ?? $existing["tag"],            "id" => $id        ]    );    $note = $db->fetchOne("SELECT * FROM notes WHERE id = :id", ["id" => $id]);    return $response->json($note);});// Delete a noteRouter::delete("/api/notes/{id:int}", function ($request, $response) {    $db = Database::getConnection();    $id = $request->params["id"];    $existing = $db->fetchOne("SELECT * FROM notes WHERE id = :id", ["id" => $id]);    if ($existing === null) {        return $response->json(["error" => "Note not found", "id" => $id], 404);    }    $db->execute("DELETE FROM notes WHERE id = :id", ["id" => $id]);    return $response->json(null, 204);});

Expected output for create:

json

{  "id": 1,  "title": "Shopping List",  "content": "Milk, eggs, bread",  "tag": "personal",  "created_at": "2026-03-22 14:30:00",  "updated_at": "2026-03-22 14:30:00"}

(Status: 201 Created)

Expected output for list:

json

{  "notes": [    {      "id": 1,      "title": "Shopping List",      "content": "Milk, eggs, bread",      "tag": "personal",      "created_at": "2026-03-22 14:30:00",      "updated_at": "2026-03-22 14:30:00"    }  ],  "count": 1}

Expected output for search:

json

{  "notes": [    {      "id": 1,      "title": "Shopping List",      "content": "Milk, eggs, bread",      "tag": "personal",      "created_at": "2026-03-22 14:30:00",      "updated_at": "2026-03-22 14:30:00"    }  ],  "count": 1}

Expected output for validation error:

json

{"errors": ["Title is required", "Content is required"]}

(Status: 400 Bad Request)

14. Seeder -- Generating Test Data#

Testing with an empty database tells you nothing. Testing with hand-typed rows is slow and brittle. The FakeData class generates realistic test data. The seedTable() function inserts it in bulk.

FakeData#

php

use Tina4\FakeData;$fake = new FakeData();$fake->name();       // "Grace Lopez"$fake->email();      // "bob.anderson@demo.net"$fake->phone();      // "+1 (547) 382-9104"$fake->sentence();   // "Magna exercitation lorem ipsum dolor sit amet consectetur."$fake->paragraph();  // Four sentences of filler text$fake->integer();    // 7342$fake->decimal();    // 481.29$fake->date();       // "2023-07-14"$fake->uuid();       // "a3f1b2c4-d5e6-f7a8-b9c0-d1e2f3a4b5c6"$fake->address();    // "742 Oak Ave, Tokyo"$fake->boolean();    // true

Every method draws from built-in word banks. No network calls. No external packages.

Deterministic Output#

Pass a seed to get reproducible results. The same seed produces the same sequence every time:

php

$fake = new FakeData(42);$fake->name();   // Always "Wendy White" with seed 42$fake->email();  // Always the same email with seed 42

Deterministic data means deterministic assertions. This matters for tests.

Seeding a Table#

seedTable() combines FakeData with your database. Pass a field map -- an associative array where each key is a column name and each value is a callable that generates data:

php

use Tina4\FakeData;use Tina4\Database;use function Tina4\seedTable;$db = Database::getConnection();$fake = new FakeData(1);seedTable($db, "users", 100, [    "name" => [$fake, "name"],    "email" => [$fake, "email"],    "phone" => [$fake, "phone"],    "bio" => [$fake, "sentence"],]);

This inserts 100 rows into the users table. Each row calls $fake->name(), $fake->email(), and so on to generate values. The function commits after all rows insert.

Overrides#

Static values that apply to every row go in the overrides array:

php

seedTable($db, "users", 50, [    "name" => [$fake, "name"],    "email" => [$fake, "email"],], [    "role" => "member",    "active" => 1,]);

Every row gets role = "member" and active = 1. The field map generates the rest.

When to Use It#

Populating a development database with realistic data
Writing integration tests that need rows in the database
Load testing with thousands of records
Demos and screenshots that look real without using real data

15. Gotchas#

1. Forgetting commit()#

Problem: Queries run inside startTransaction(), but changes vanish on the next request.

Cause: No commit(). The transaction rolls back when the connection closes.

Fix: Always call $db->commit() on success. Use try/catch with $db->rollback() in the catch.

2. Connection String Formats#

Problem: Database refuses to connect. Cryptic error about the connection string.

Cause: Missing port. A common mistake is mysql://user:pass@host/db without the port number.

Fix: Always include the port:

Engine	Default Port
PostgreSQL	5432
MySQL	3306
MSSQL	1433
Firebird	3050
SQLite	(file path, no port)

3. SQLite File Paths#

Problem: SQLite creates a new empty database instead of using the existing one.

Cause: Wrong slash count. sqlite:// (two slashes) instead of sqlite:/// (three slashes). Or a relative path resolving to the wrong directory.

Fix: Three slashes for a relative path: sqlite:///data/app.db. Four slashes for absolute: sqlite:////var/data/app.db. The third slash separates the scheme. The fourth starts the absolute path.

4. Parameterised Queries with LIKE#

Problem: WHERE name LIKE :q with ["q" => "%search%"] works. WHERE name LIKE '%:q%' does not.

Cause: Parameters inside quotes are literal text, not placeholders.

Fix: Put the % wildcards in the parameter value: ["q" => "%" . $search . "%"]. The SQL should be WHERE name LIKE :q.

5. Boolean Values in SQLite#

Problem: You insert true or false. The database stores 1 or 0. Reading it back gives integers, not booleans.

Cause: SQLite has no native boolean type. Booleans become integers.

Fix: Cast in PHP: "in_stock" => (bool) $row["in_stock"]. Or accept that 1 and 0 work as truthy/falsy.

6. Migration Already Applied#

Problem: You edited a migration file and ran tina4 migrate again. Nothing changed.

Cause: Tina4 tracks applied migrations by filename in the tina4_migration table. Once applied, a migration will not run again regardless of content changes.

Fix: Create a new migration for schema changes. Never edit applied migrations. For early development, use tina4php migrate:rollback first, then tina4 migrate to reapply.

7. Down Migration Not Found During Rollback#

Problem: tina4php migrate:rollback removes the tracking record but does not reverse the schema change.

Cause: The .down.sql file is missing, or its name does not match the migration. For 20260322143000_create_products_table.sql, the down file must be 20260322143000_create_products_table.down.sql -- same base name with .down.sql appended before the extension.

Fix: Always generate migrations with tina4 generate migration which creates both files automatically. If you created migration files manually, add the .down.sql file with the exact matching name.

8. fetch() Returns Empty Array, Not Null#

Problem: if ($result === null) never matches, even when the table is empty.

Cause: fetch() always returns an array. Empty result is [], not null. Only fetchOne() returns null.

Fix: Check with if (empty($result)) or if (count($result) === 0).