PLATFORM_STRATEGY.md

Platform Storage Strategy

Executive Summary

Recommendation: Use graceful degradation with platform-specific optimizations and automatic adapter selection.

// Single API for all platforms
import { createDatabase } from '@framers/sql-storage-adapter';

const db = await createDatabase({ 
  priority: ['indexeddb', 'sqljs'],  // Auto-detects best adapter
});

---

Platform Matrix: Pros & Cons

🌐 Web (Browser)

Adapter	Pros	Cons	Best For
IndexedDB (NEW)	✅ Native browser storage API ✅ Async, non-blocking ✅ 50MB-1GB+ quota ✅ sql.js wrapper (full SQL support) ✅ Persistent across sessions	❌ Uses sql.js WASM (500KB load) ❌ IndexedDB quotas vary by browser ❌ Not a separate SQL engine (sql.js + IDB persistence)	Primary choice for web Offline PWAs Privacy-first apps
sql.js	✅ Full SQLite in WASM ✅ In-memory fast reads ✅ Optional IDB persistence ✅ Zero dependencies	❌ 500KB WASM load ❌ Slow writes to IDB ❌ Single-threaded	Fallback for web Edge functions
LocalStorage	✅ 5-10MB simple API	❌ Synchronous (blocks UI) ❌ String-only ❌ No transactions	❌ NOT RECOMMENDED

Winner: IndexedDB adapter (sql.js + IndexedDB persistence wrapper)

• sql.js provides SQL execution (WASM SQLite)
• IndexedDB provides browser-native persistence (stores SQLite file as blob)
• Auto-save batching minimizes IDB overhead
• Works offline, respects privacy
• Note: This is sql.js with IndexedDB persistence, not a separate SQL engine

---

🖥️ Electron (Desktop)

Adapter	Pros	Cons	Best For
better-sqlite3	✅ FASTEST (native C++) ✅ Full SQLite features ✅ WAL mode for concurrency ✅ Synchronous API (no async overhead) ✅ Mature, battle-tested	❌ Requires native compilation ❌ Must rebuild for Electron ABI ❌ Large binary (~5MB)	Primary choice for Electron Production desktop apps
sql.js	✅ No rebuild needed ✅ Cross-platform WASM	❌ 3-5x slower than native ❌ Async overhead	Quick prototyping CI/CD without build tools
IndexedDB	✅ Available in Electron renderer	❌ Slower than better-sqlite3 ❌ Unnecessary abstraction	❌ Use better-sqlite3 instead

Winner: better-sqlite3

• Native performance is unbeatable for desktop
• Electron already handles native modules
• Fallback to sql.js if build fails

---

📱 Mobile (Capacitor: iOS/Android)

Adapter	Pros	Cons	Best For
@capacitor-community/sqlite	✅ BEST native SQLite on mobile ✅ iOS: Core Data integration ✅ Android: Native SQLite ✅ Encryption support ✅ Multi-threaded	❌ Capacitor-specific ❌ Requires native plugins	Primary choice for mobile Capacitor apps only
IndexedDB	✅ Available in WebView ✅ Works without Capacitor	❌ Slower than native ❌ Limited mobile quota ❌ Browser quirks on mobile	PWA-style mobile apps Ionic without Capacitor
sql.js	✅ Universal fallback	❌ WASM overhead on mobile ❌ Battery drain	Emergency fallback only

Winner: @capacitor-community/sqlite for Capacitor apps, IndexedDB for web-based mobile

---

☁️ Cloud (Node.js, Serverless)

Adapter	Pros	Cons	Best For
PostgreSQL	✅ BEST for multi-user ✅ Connection pooling ✅ JSONB, full-text search ✅ Horizontal scaling ✅ Cloud-native (RDS, Supabase, Neon)	❌ Requires hosted DB ❌ Network latency ❌ Cost at scale	Primary choice for cloud Multi-tenant SaaS Real-time sync
better-sqlite3	✅ Fast for single-user ✅ No external DB needed ✅ Simple deployment	❌ File-based (hard to scale) ❌ No network access ❌ Single-writer limitation	Personal cloud instances Dev/staging
sql.js (ephemeral)	✅ Serverless edge (Cloudflare Workers) ✅ No cold start for DB	❌ In-memory only ❌ State lost on restart	Stateless functions Cache layer

Winner: PostgreSQL for production, better-sqlite3 for dev/staging

---

Graceful Degradation Strategy

Priority Cascade by Platform

const PLATFORM_PRIORITIES: Record<Platform, AdapterKind[]> = {
  web: ['indexeddb', 'sqljs'],                    // NEW: IndexedDB first
  electron: ['better-sqlite3', 'sqljs'],          // Native first
  capacitor: ['capacitor', 'indexeddb', 'sqljs'], // Native mobile > WebView IDB
  node: ['better-sqlite3', 'postgres', 'sqljs'],  // Native > Cloud > WASM
  cloud: ['postgres', 'better-sqlite3', 'sqljs'], // Cloud-first
};

Automatic Detection

function detectPlatform(): Platform {
  if (typeof window !== 'undefined') {
    if (window.Capacitor?.isNativePlatform?.()) return 'capacitor';
    if (window.indexedDB) return 'web';
  }
  if (typeof process !== 'undefined') {
    if (process.versions?.electron) return 'electron';
    if (process.env.DATABASE_URL) return 'cloud';
    return 'node';
  }
  return 'unknown';
}

---

Usage Examples

Web Application

import { IndexedDbAdapter } from '@framers/sql-storage-adapter';

const db = new IndexedDbAdapter({
  dbName: 'my-app-db',
  autoSave: true,
  saveIntervalMs: 5000,
});

await db.open();
await db.run('CREATE TABLE sessions (id TEXT PRIMARY KEY, data TEXT)');

Desktop Application (Electron)

import { createDatabase } from '@framers/sql-storage-adapter';
import path from 'path';

const db = await createDatabase({
  filePath: path.join(app.getPath('userData'), 'app.db'),
});

await db.open();

Mobile Application (Capacitor)

import { createDatabase } from '@framers/sql-storage-adapter';

const db = await createDatabase({
  priority: ['capacitor', 'indexeddb'],
});

Cloud Application (Node.js)

import { createDatabase } from '@framers/sql-storage-adapter';

const db = await createDatabase({
  postgres: { connectionString: process.env.DATABASE_URL },
});

---

Summary Table

Platform	Primary	Fallback	Notes
Web	IndexedDB	sql.js	Browser-native persistence
Electron	better-sqlite3	sql.js	Native performance
Capacitor	capacitor	IndexedDB	Native mobile > WebView
Node	better-sqlite3	Postgres	Local-first, cloud optional
Cloud	Postgres	better-sqlite3	Multi-tenant requires Postgres

TL;DR: Use IndexedDB for web, better-sqlite3 for desktop, capacitor for mobile, Postgres for cloud. The adapter automatically selects the best option based on your runtime environment.