Profpatsch/users/Profpatsch/sqlite-apps

View full contents ↓

This project explores a novel approach to application distribution and deployment where complete applications are embedded within SQLite database files. Users can deploy applications by simply drag-and-dropping database files onto a web interface, which automatically extracts and runs the embedded application code while using the same SQLite file for data storage.

Problem: Traditional application deployment involves complex dependency management, installation procedures, and separate concerns for application code, configuration, and data storage.

Solution: Embed compressed squashfs images of Nix store paths into SQLite database files, then dynamically generate OCI runtime bundles at execution time, creating atomic, portable, self-contained applications with optimal storage efficiency.

Each application database contains a single system table managed by the runtime:

-- Single table for SQLite-embedded applications
-- squashfs_data MUST be the last column for efficient reading
CREATE TABLE __app (
  name TEXT NOT NULL,
  version TEXT NOT NULL,
  entry_point TEXT NOT NULL,        -- Executable path within squashfs
  description TEXT,
  author TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  squashfs_data BLOB NOT NULL       -- Compressed squashfs image (zstd)
);

-- All other tables are created and managed by the application itself
-- Applications handle their own schema initialization and migrations


┌─────────────────────────────────────────────────────────────────┐
│                          Host System                            │
│                                                                 │
│  ┌─────────────────┐                                           │
│  │   Web Interface │  ← Drag & drop .db files                  │
│  │   (Runtime)     │                                           │
│  └─────────┬───────┘                                           │
│            │                                                   │
│            ▼                                                   │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │              Application Instance                       │   │
│  │                                                         │   │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  │   │
│  │  │   Squashfs   │  │     runc     │  │   SQLite     │  │   │
│  │  │ Extract +    │  │   Container  │  │   Database   │  │   │
│  │  │Dynamic OCI   │  │  (rootless)  │  │(bind-mounted)│  │   │
│  │  └──────────────┘  └──────────────┘  └──────────────┘  │   │
│  │                                                         │   │
│  │  Container features:                                    │   │
│  │  • User namespace isolation (uid/gid mapping)          │   │
│  │  • PID/Network/Mount/IPC/UTS namespace isolation      │   │
│  │  • Database bind-mounted to /data/db.sqlite           │   │
│  │  • Squashfs extracted directly to rootfs              │   │
│  │  • stdout/stderr inherited for simplicity             │   │
│  └─────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────┘

  1. Deployment: User runs sqlite-apps run timetracker.db
  2. Extraction: Runtime extracts squashfs image from __app table to temp file
  3. Extract: unsquashfs extracts squashfs directly into OCI bundle rootfs
  4. Prepare: OCI config.json generated dynamically with proper user namespace mappings
  5. Execution: runc spawns rootless container with extracted rootfs + database bind-mounted
  6. Operation: App reads/writes data tables using standard SQLite database
  7. Cleanup: Temporary files cleaned up automatically on exit

Purpose: Convert Nix derivations into SQLite-embedded applications

# Build the tool
nix-build ~/kot/Profpatsch -A users.Profpatsch.sqlite-apps

# Usage
sqlite-apps package timetracker my-timetracker.nix timetracker.db

# With options
sqlite-apps package --verbose -d "Time tracking app" -a "Author" timetracker ./timetracker.nix timetracker.db

# Process:
# 1. nix-build derivation to get store paths
# 2. nix copy complete closure to temporary nix store
# 3. mksquashfs temp_nix_store app.squashfs -comp zstd
# 4. CREATE TABLE __app (..., squashfs_data BLOB)
# 5. INSERT INTO __app (..., squashfs_data) VALUES (..., readfile('app.squashfs'))

Input: App name, Nix derivation, output filename Output: SQLite database with embedded squashfs image (no application schema)

Key Principle: Packager is schema-agnostic - applications manage their own data structures

Purpose: Extract and execute SQLite-embedded applications

# Usage
sqlite-apps run timetracker.db

# With options
sqlite-apps run --verbose --keep-temp timetracker.db

# Process:
# 1. SELECT entry_point, squashfs_data FROM __app
# 2. Extract squashfs to /tmp/app-{timestamp}/app.squashfs
# 3. unsquashfs -f -d bundle/rootfs app.squashfs (direct extraction)
# 4. Generate OCI config.json with rootless user mappings + database bind mount
# 5. runc --rootless run with extracted rootfs + database mounted to /data/db.sqlite
# 6. Application handles its own schema init/migration on startup
# 7. Cleanup temporary files automatically on exit

Input: SQLite database with embedded application Output: Running OCI container instance with observability

Key Principle: Runtime is schema-agnostic - extracts squashfs, generates OCI config, and runs rootless container

Purpose: User-friendly application deployment and management

Features:

Purpose: Reference implementation demonstrating the pattern

Features:

  1. User Namespace Isolation: Applications run in rootless containers with uid/gid mapping (container uid 0 → host uid 1000)
  2. Filesystem Isolation: Applications run with chroot to extracted squashfs rootfs, cannot access host filesystem
  3. Process Isolation: PID namespace isolation prevents seeing/interacting with host processes
  4. Network Isolation: Applications have no network access by default (no network namespace configured)
  5. IPC/UTS Isolation: Mount, IPC, and UTS namespaces provide additional isolation boundaries

Applications emit OTEL-compatible JSON traces to file descriptor 5:

{
  "timestamp": "2024-01-15T10:30:45.123Z",
  "trace_id": "abc123...",
  "span_id": "def456...",
  "operation": "time_entry.create",
  "duration_ms": 45,
  "attributes": {
    "client": "Acme Corp", 
    "hours": 2.5
  }
}

  1. Standard Development: Build applications using normal Nix/language tooling
  2. Packaging: Use packager to embed into SQLite database
  3. Testing: Deploy locally using runtime for integration testing
  4. Distribution: Share single .db file containing complete application

  1. Component Testing: Test packager, runtime, and web interface separately
  2. Integration Testing: End-to-end tests with example applications
  3. Security Testing: Verify isolation and sandboxing mechanisms
  4. Performance Testing: Measure startup times, resource usage, throughput

This system is designed for true self-hosting - applications that work out-of-the-box without the complexity of production database management, logging infrastructure, or backup strategies. Applications must be designed to work within embedded database capabilities and scale appropriately.