Building a Content Management API with PHP and SQLite

SQLite is often overlooked for web APIs, but for read-heavy content platforms it excels. Here's how I built the content API for TrendVidStream using PHP 8.3 and SQLite.

Database Setup

<?php

class Database
{
    private static ?PDO $instance = null;

    public static function connect(string $dbPath): PDO
    {
        if (self::$instance === null) {
            self::$instance = new PDO("sqlite:$dbPath");
            self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            self::$instance->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

            // Performance pragmas
            self::$instance->exec('PRAGMA journal_mode=WAL');
            self::$instance->exec('PRAGMA synchronous=NORMAL');
            self::$instance->exec('PRAGMA cache_size=-64000'); // 64MB cache
            self::$instance->exec('PRAGMA temp_store=MEMORY');
        }
        return self::$instance;
    }

    public static function migrate(PDO $db): void
    {
        $db->exec('
            CREATE TABLE IF NOT EXISTS videos (
                id TEXT NOT NULL,
                region TEXT NOT NULL,
                title TEXT NOT NULL,
                description TEXT,
                thumbnail TEXT,
                channel_id TEXT,
                channel_title TEXT,
                category_id INTEGER,
                view_count INTEGER DEFAULT 0,
                published_at TEXT,
                fetched_at TEXT DEFAULT (datetime("now")),
                PRIMARY KEY (id, region)
            );

            CREATE INDEX IF NOT EXISTS idx_videos_category ON videos(category_id);
            CREATE INDEX IF NOT EXISTS idx_videos_region ON videos(region);
            CREATE INDEX IF NOT EXISTS idx_videos_fetched ON videos(fetched_at);
        ');
    }
}

API Router

<?php

class Router
{
    private array $routes = [];

    public function get(string $pattern, callable $handler): void
    {
        $this->routes['GET'][$pattern] = $handler;
    }

    public function resolve(string $method, string $path): mixed
    {
        foreach ($this->routes[$method] ?? [] as $pattern => $handler) {
            $regex = preg_replace('/\{(\w+)\}/', '(?P<$1>[^/]+)', $pattern);
            if (preg_match("#^{$regex}$#", $path, $matches)) {
                $params = array_filter($matches, 'is_string', ARRAY_FILTER_USE_KEY);
                return $handler($params);
            }
        }
        http_response_code(404);
        return ['error' => 'Not found'];
    }
}

API Endpoints

<?php

$router = new Router();
$db = Database::connect(__DIR__ . '/data/app.db');
$cache = new Cache(__DIR__ . '/data/cache');

// GET /api/videos?region=AE&category=music&limit=20
$router->get('/api/videos', function(array $params) use ($db, $cache) {
    $region = $_GET['region'] ?? null;
    $category = $_GET['category'] ?? null;
    $limit = min((int)($_GET['limit'] ?? 20), 50);
    $offset = max((int)($_GET['offset'] ?? 0), 0);

    $cacheKey = "api:videos:" . md5(serialize($_GET));
    $cached = $cache->get($cacheKey);
    if ($cached) return $cached;

    $sql = 'SELECT * FROM videos WHERE 1=1';
    $params = [];

    if ($region) {
        $sql .= ' AND region = ?';
        $params[] = $region;
    }
    if ($category) {
        $sql .= ' AND category_id = ?';
        $params[] = $category;
    }

    $sql .= ' ORDER BY fetched_at DESC LIMIT ? OFFSET ?';
    $params[] = $limit;
    $params[] = $offset;

    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $videos = $stmt->fetchAll();

    $result = [
        'data' => $videos,
        'meta' => [
            'count' => count($videos),
            'limit' => $limit,
            'offset' => $offset,
        ],
    ];

    $cache->set($cacheKey, $result, 1800); // 30 min cache
    return $result;
});

// GET /api/videos/{id}
$router->get('/api/videos/{id}', function(array $params) use ($db) {
    $stmt = $db->prepare('SELECT * FROM videos WHERE id = ?');
    $stmt->execute([$params['id']]);
    $video = $stmt->fetch();

    if (!$video) {
        http_response_code(404);
        return ['error' => 'Video not found'];
    }

    return ['data' => $video];
});

// GET /api/categories
$router->get('/api/categories', function() use ($db, $cache) {
    $cached = $cache->get('api:categories');
    if ($cached) return $cached;

    $stmt = $db->query('
        SELECT c.*, COUNT(v.id) as video_count
        FROM categories c
        LEFT JOIN videos v ON v.category_id = c.id
        GROUP BY c.id
        ORDER BY video_count DESC
    ');
    $categories = $stmt->fetchAll();

    $result = ['data' => $categories];
    $cache->set('api:categories', $result, 86400);
    return $result;
});

// GET /api/regions
$router->get('/api/regions', function() use ($db) {
    $stmt = $db->query('
        SELECT region, COUNT(*) as video_count, MAX(fetched_at) as last_fetch
        FROM videos
        GROUP BY region
        ORDER BY video_count DESC
    ');
    return ['data' => $stmt->fetchAll()];
});

// Dispatch
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');

$result = $router->resolve($_SERVER['REQUEST_METHOD'], parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH));
echo json_encode($result, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);

Performance Characteristics

SQLite with WAL mode and proper indexing delivers consistent performance:

• Simple queries: < 1ms
• Filtered list queries: 2-5ms
• Full-text search: 5-15ms
• With cache hit: < 0.1ms

This API powers TrendVidStream, handling requests for trending videos across 8 global regions.

The key takeaway: SQLite is a production-ready choice for read-heavy content APIs, especially when combined with proper caching.