SQLite Database Bridge

SQLite Database Bridge & Local Storage Engine

Build rich, offline-first data applications using the embedded native SQLite 3 database engine and promise-based JavaScript bridge.

---

🗄️ Architecture Overview

While browser localStorage and IndexedDB work well for small settings, complex applications require relational queries, foreign keys, transactions, and multi-gigabyte storage capacity without browser quota eviction.

WebToApp Studio Pro embeds a native SQLite 3 C-engine directly inside the host process and exposes an asynchronous, promise-based API on window.desktopApp.db:

┌────────────────────────────────────────────────────────────────────────┐
│                   WebView2 JavaScript Client Context                   │
│         await window.desktopApp.db.query("SELECT * FROM notes")        │
└───────────────────────────────────┬────────────────────────────────────┘
                                    │ IPC Bridge (JSON-RPC)
                                    ▼
┌────────────────────────────────────────────────────────────────────────┐
│                   Host Process SQLite Security Gate                    │
│      - Origin Isolation Check (blocks unauthorized iframes)            │
│      - Path Traversal & Filename Guard (%LocalAppData%\AppName)        │
│      - Dangerous PRAGMA Blocking (ATTACH, journal_mode, writable_schema)│
└───────────────────────────────────┬────────────────────────────────────┘
                                    │ Native C# ADO.NET (Microsoft.Data.Sqlite)
                                    ▼
┌────────────────────────────────────────────────────────────────────────┐
│                   Native SQLite 3 Embedded Database                    │
│                 %LocalAppData%\<YourApp>\database.db                   │
└────────────────────────────────────────────────────────────────────────┘

---

💻 JavaScript Developer API (window.desktopApp.db)

The SQLite API is automatically injected into the global window scope when Tab 5 (SQLite Database) is enabled.

1. Execute SQL (DDL / INSERT / UPDATE / DELETE)

// Create tables or run modifications
await window.desktopApp.db.execute(`
    CREATE TABLE IF NOT EXISTS notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        body TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
`);

// Insert records using parameterized queries (prevents SQL injection!)
const result = await window.desktopApp.db.execute(
    "INSERT INTO notes (title, body) VALUES (?, ?)",
    ["My First Note", "Created completely offline with native SQLite!"]
);
console.log("Inserted ID:", result.lastInsertId);
console.log("Rows affected:", result.rowsAffected);

2. Query Rows (SELECT)

// Query returns an array of plain JavaScript objects
const notes = await window.desktopApp.db.query(
    "SELECT * FROM notes WHERE title LIKE ? ORDER BY id DESC",
    ["%First%"]
);

notes.forEach(note => {
    console.log(`[#${note.id}] ${note.title}: ${note.body}`);
});

3. Atomic Transactions

Execute multiple SQL statements atomically. If any statement fails, the entire transaction rolls back cleanly:

await window.desktopApp.db.transaction([
    {
        sql: "UPDATE accounts SET balance = balance - ? WHERE id = ?",
        params: [100, 1]
    },
    {
        sql: "UPDATE accounts SET balance = balance + ? WHERE id = ?",
        params: [100, 2]
    },
    {
        sql: "INSERT INTO audit_log (action, amount) VALUES (?, ?)",
        params: ["TRANSFER", 100]
    }
]);

---

🔒 Security Hardening & Defenses

1. Origin Isolation: The database bridge strictly validates the calling frame's window.location.origin. External untrusted websites or third-party iframes cannot execute queries.
2. Sandbox Path Isolation: Databases are strictly stored inside the application's isolated %LocalAppData%\<AppName>\ directory. Path traversal sequences (../, absolute paths) are blocked.
3. Dangerous PRAGMA Blocking: Commands that attempt to manipulate filesystem paths or database headers (PRAGMA journal_mode, ATTACH DATABASE, PRAGMA writable_schema) are blocked at the bridge layer.

---

📦 Studio Schema Seed Presets

Tab 5 in the Studio interface provides 1-click initial schema seeds:

• Notes / Markdown Journal: Structured table with id, title, content, tags, and timestamps.
• Key-Value Store: High-performance JSON key-value cache table with indexed keys.
• User Accounts & Roles: User authentication and settings table.