Designing and Deploying a Modern Publishing Architecture with SQLite and Next.js

The Modern Publishing Conundrum #

In the contemporary web ecosystem, publishing technical content has frequently become entangled in excessive layers of abstraction. Traditional CMS solutions often demand multi-container database architectures, heavy runtime frameworks, and complex caching strategies just to deliver a few kilobytes of text and code.

When designing this platform for makhlwf.ly, our objective was distinct: build an independent, sovereign publishing engine that prioritizes performance, zero bloat, WCAG 2.2 accessibility, and total data portability.

// High-performance SQLite database connection with Write-Ahead Logging
import Database from 'better-sqlite3';

export function initializeDatabase(path: string): Database.Database {
  const db = new Database(path);
  // Enable Write-Ahead Logging for non-blocking concurrent reads
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
  db.pragma('synchronous = NORMAL');
  return db;
}

Why SQLite with WAL Mode? #

Most personal publications and high-traffic blogs exhibit an asymmetric read-to-write ratio: roughly 99.9% of incoming requests are reads, while writes occur almost exclusively when authors create or update articles.

Under SQLite's Write-Ahead Logging (WAL):

1. Readers never block writers, and writers never block readers.
2. Queries execute in-process, eliminating socket latency and network round-trips.
3. Memory consumption when idle is practically 0 MB, conserving server RAM.
4. Backups are atomic and reliable: copying the .db file guarantees 100% data integrity without taking the site offline.

Benchmarking Database Query Latency #

Below is a latency comparison measured across 10,000 requests on a typical Ubuntu cloud VPS:

[!NOTE] The sub-millisecond response of SQLite means our Next.js server components can render and stream complete HTML documents in single-digit milliseconds without requiring an external caching layer like Redis.

Syntax Highlighting with Multi-Language Support #

Technical articles require crystal-clear code rendering. Here is an example of an asynchronous Python worker:

import asyncio
import aiohttp
from typing import List, Dict

async def fetch_telemetry(endpoints: List[str]) -> List[Dict]:
    """Fetch health telemetry from internal microservices concurrently."""
    async with aiohttp.ClientSession() as session:
        tasks = [session.get(url, timeout=aiohttp.ClientTimeout(total=5)) for url in endpoints]
        responses = await asyncio.gather(*tasks, return_exceptions=True)
        return [{"status": r.status} for r in responses if not isinstance(r, Exception)]

if __name__ == "__main__":
    results = asyncio.run(fetch_telemetry(["https://makhlwf.ly/api/health"]))
    print(f"Collected telemetry: {results}")

And a systemd service definition for production supervision:

[Unit]
Description=Makhlwf Custom Blog Platform
After=network.target

[Service]
Type=simple
User=root
WorkingDirectory=/var/www/makhlwf-blog
ExecStart=/usr/local/bin/node server.js
Restart=always
RestartSec=5
Environment=NODE_ENV=production
Environment=PORT=3000

[Install]
WantedBy=multi-user.target

Content Portability and Sovereign Ownership #

Your thoughts and code should never be trapped inside a proprietary database format. This platform features:

• 1-Click Markdown Export: Every article can be exported as an independent .md file with standard YAML frontmatter.
• Full JSON Backups: Complete snapshot of all articles, revisions, categories, and settings.
• Direct SQLite Portability: Simply copy data/blog.db anywhere.

Happy writing!