Part 4: Database & ORM

In Part 3, you learned how to separate presentation from logic using templates. But your app still has no memory — every time a user visits, everything starts from scratch.

This part adds a database. You'll learn how to connect to SQLite and MySQL, use F3's SQL Mapper for CRUD operations, handle relationships honestly, and write secure queries.

1. Connecting to a Database

F3 supports multiple SQL databases through PHP's PDO layer. The two most common are SQLite (file-based, no server needed) and MySQL (industry standard, requires server).

SQLite (Recommended for Learning)

SQLite stores your database in a single file. No installation, no configuration. Perfect for development and small projects.

// Connect to SQLite database
$db = new \DB\SQL('sqlite:' . __DIR__ . '/data/app.db');

// Store in F3 hive for global access
$f3->set('DB', $db);

MySQL (For Production)

// Connect to MySQL
$db = new \DB\SQL(
    'mysql:host=localhost;port=3306;dbname=myapp',
    'username',
    'password'
);

$f3->set('DB', $db);

2. What is the SQL Mapper?

F3 uses an Active Record pattern called the SQL Mapper. Think of a mapper object as a PHP representation of a single database row.

When you create a mapper for a table, F3 reads the table structure automatically. You don't need to write model classes that repeat your column definitions — the mapper learns from the database itself.

// Create a mapper for the 'users' table
$user = new \DB\SQL\Mapper($db, 'users');

// At this point, the mapper knows the table structure
// but contains no data yet (dry state)

3. CRUD Operations

The mapper handles Create, Read, Update, and Delete with simple method calls.

Create (Insert)

$user = new \DB\SQL\Mapper($db, 'users');
$user->name = 'Dimas';
$user->email = '[email protected]';
$user->role = 'admin';
$user->save();  // INSERT into database

Read (Load)

$user = new \DB\SQL\Mapper($db, 'users');
$user->load(['id=?', 1]);  // SELECT * FROM users WHERE id = 1

if ($user->dry()) {
    echo 'User not found';
} else {
    echo $user->name;
}

Update

// Load first, then modify
$user = new \DB\SQL\Mapper($db, 'users');
$user->load(['id=?', 1]);
$user->name = 'Dimas Pradana';
$user->save();  // UPDATE users SET name = '...' WHERE id = 1

Delete

$user = new \DB\SQL\Mapper($db, 'users');
$user->load(['id=?', 1]);
$user->erase();  // DELETE FROM users WHERE id = 1

4. Querying Data

F3 provides two main methods for retrieving data: load() for single records and find() for multiple records.

load() — Single Record

$user = new \DB\SQL\Mapper($db, 'users');

// Find by email
$user->load(['email=?', '[email protected]']);

// Find by multiple conditions
$user->load(['role=? AND active=?', 'admin', 1]);

// Check if found
if ($user->dry()) {
    echo 'No user found';
}

find() — Multiple Records

$user = new \DB\SQL\Mapper($db, 'users');

// Get all admins, ordered by name
$admins = $user->find(
    ['role=?', 'admin'],
    ['order' => 'name ASC', 'limit' => 10]
);

foreach ($admins as $u) {
    echo $u->name . '<br>';
}

The dry() Method

After calling load(), use dry() to check if a record was found:

// dry() returns TRUE if no record was loaded
if ($user->dry()) {
    // No record found
} else {
    // Record is loaded
    echo $user->name;
}

5. Pagination

F3 has built-in pagination. The paginate() method returns a result set with metadata.

$user = new \DB\SQL\Mapper($db, 'users');

// Page 0 (first page), 10 records per page
$page = $user->paginate(0, 10);

// $page contains:
// ['subset'] => array of mapper objects
// ['total']  => total number of records
// ['limit']  => records per page
// ['count']  => total pages
// ['pos']    => current page number

// Pagination with conditions
$page = $user->paginate(0, 10, ['active=?', 1]);

<!-- In your template -->
<repeat group="{{ @page.subset }}" value="{{ @u }}">
    <p>{{ @u.name }} - {{ @u.email }}</p>
</repeat>

<p>Page {{ @page.pos + 1 }} of {{ @page.count }}</p>
<p>Total: {{ @page.total }} users</p>

6. Relationships in F3

F3 does not have hasMany(), belongsTo(), or hasOne() methods like Laravel's Eloquent. This is intentional — F3 avoids ORM complexity to keep things fast and simple.

Here are three practical ways to handle relationships in F3:

Option 1: Manual Loading (Recommended)

Load related records explicitly in your controller. This is the most common and straightforward approach.

// Get a user and their posts
$user = new \DB\SQL\Mapper($db, 'users');
$user->load(['id=?', 1]);

$post = new \DB\SQL\Mapper($db, 'posts');
$posts = $post->find(['user_id=?', $user->id]);

// Pass to template
$f3->set('user', $user);
$f3->set('posts', $posts);

Option 2: Virtual Fields

For computed values (like counts), define virtual fields using SQL subqueries.

// Add a virtual field for post count
$user = new \DB\SQL\Mapper($db, 'users');
$user->post_count = '(SELECT COUNT(*) FROM posts WHERE user_id = users.id)';
$user->load(['id=?', 1]);

echo $user->name . ' has ' . $user->post_count . ' posts';

Option 3: Database Views

For complex JOINs, create a database view and map it like a regular table.

-- Create a view in your database
CREATE VIEW user_with_stats AS
SELECT
    u.id,
    u.name,
    u.email,
    COUNT(p.id) AS post_count,
    MAX(p.created_at) AS last_post_at
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

// Map the view like a regular table
$user = new \DB\SQL\Mapper($db, 'user_with_stats');
$user->load(['id=?', 1]);

echo $user->name . ' - ' . $user->post_count . ' posts';

7. Raw SQL When Needed

Sometimes the mapper isn't enough. You can always drop down to raw SQL with $db->exec().

// Get the database object
$db = $f3->get('DB');

// Simple query
$results = $db->exec('SELECT * FROM users WHERE active = 1');

// With parameterized values
$results = $db->exec(
    'SELECT * FROM users WHERE role = ? AND created_at > ?',
    ['admin', '2026-01-01']
);

// Batch transactions (automatic rollback on error)
$db->exec([
    'INSERT INTO logs (message) VALUES (?)',
    'UPDATE users SET last_login = NOW() WHERE id = ?',
    'SELECT * FROM users WHERE id = ?'
], [
    ['User logged in'],
    [42],
    [42]
]);

8. Security: Parameterized Queries

Never concatenate user input directly into SQL queries. This creates SQL injection vulnerabilities.

// ❌ DANGEROUS — SQL Injection vulnerability
$email = $f3->get('GET.email');
$db->exec("SELECT * FROM users WHERE email = '$email'");

// If someone passes: ' OR 1=1 --
// The query becomes: SELECT * FROM users WHERE email = '' OR 1=1 --'
// Result: ALL users are returned!

// ✅ SAFE — Parameterized query
$email = $f3->get('GET.email');
$db->exec('SELECT * FROM users WHERE email = ?', $email);

// The value is passed as a bound parameter, not interpolated into the SQL string

9. Custom Models (Extending Mappers)

For larger applications, you can extend the mapper into a model class. This lets you add business logic while keeping database access organized.

// models/User.php
class User extends \DB\SQL\Mapper {
    public function __construct(\DB\SQL $db) {
        parent::__construct($db, 'users');
    }

    // Custom query methods
    public function findActive() {
        return $this->find(['active=?', 1], ['order' => 'name ASC']);
    }

    public function findByEmail($email) {
        $this->load(['email=?', $email]);
        return $this->dry() ? null : $this;
    }

    // Business logic
    public function isAdmin() {
        return $this->role === 'admin';
    }

    public function deactivate() {
        $this->active = 0;
        $this->save();
    }
}

// Usage in your route
$f3->route('GET /user/@id', function($f3) {
    $user = new User($f3->get('DB'));
    $user->load(['id=?', $f3->get('PARAMS.id')]);

    if ($user->dry()) {
        $f3->error(404);
        return;
    }

    $f3->set('user', $user);
    echo \Template::instance()->render('views/user.htm');
});

Putting It Together

Here's a complete blog example combining routing (Part 2), templates (Part 3), and database (Part 4):

<?php
require 'vendor/autoload.php';
$f3 = \Base::instance();

// Connect to SQLite database
$db = new \DB\SQL('sqlite:' . __DIR__ . '/data/blog.db');
$f3->set('DB', $db);

// Create tables (run once)
$db->exec([
    'CREATE TABLE IF NOT EXISTS posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        slug TEXT UNIQUE,
        title TEXT,
        body TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )',
    'CREATE TABLE IF NOT EXISTS comments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        post_id INTEGER,
        author TEXT,
        content TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (post_id) REFERENCES posts(id)
    )'
]);

// Blog listing
$f3->route('GET /blog', function($f3) {
    $post = new \DB\SQL\Mapper($f3->get('DB'), 'posts');
    $posts = $post->find([], ['order' => 'created_at DESC']);
    $f3->set('posts', $posts);
    echo \Template::instance()->render('views/blog.htm');
});

// Single post with comments
$f3->route('GET /blog/@slug', function($f3) {
    $post = new \DB\SQL\Mapper($f3->get('DB'), 'posts');
    $post->load(['slug=?', $f3->get('PARAMS.slug')]);

    if ($post->dry()) {
        $f3->error(404);
        return;
    }

    // Load comments for this post
    $comment = new \DB\SQL\Mapper($f3->get('DB'), 'comments');
    $comments = $comment->find(['post_id=?', $post->id], ['order' => 'created_at ASC']);

    $f3->set('post', $post);
    $f3->set('comments', $comments);
    echo \Template::instance()->render('views/post.htm');
});

// Submit comment (POST)
$f3->route('POST /blog/@slug/comment', function($f3) {
    $post = new \DB\SQL\Mapper($f3->get('DB'), 'posts');
    $post->load(['slug=?', $f3->get('PARAMS.slug')]);

    if ($post->dry()) {
        $f3->error(404);
        return;
    }

    $comment = new \DB\SQL\Mapper($f3->get('DB'), 'comments');
    $comment->post_id = $post->id;
    $comment->author = $f3->scrub($f3->get('POST.author'));
    $comment->content = $f3->scrub($f3->get('POST.content'));
    $comment->save();

    $f3->reroute('/blog/' . $post->slug);
});

$f3->run();

Troubleshooting

What's Next?

You now have a working database layer. In Part 5: Configuration & Utilities, you'll learn:

• Configuration files (INI, JSON, PHP)
• Built-in caching (file, APC, Memcached)
• Session handling for user authentication
• Multilingual support (i18n)
• Useful helpers (Audit, Base utilities)

A good configuration makes your app flexible. Let's learn how to configure F3 properly in Part 5.