SQLite

Lead Summary

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine — and it is the dominant choice for client-side storage in local-first applications across web, mobile, and desktop platforms. Where most databases run as separate server processes, SQLite is a library that runs inside the application itself, with the entire database stored in a single file.

What makes SQLite remarkable in the current landscape is the breadth of contexts it now inhabits: from native mobile apps to the browser (compiled to WebAssembly), from edge servers (Cloudflare D1, Turso) to sync engines that mirror Postgres state onto client devices in real time. Its SQL interface, query planner, and zero-administration model make it an unusually portable foundation for building offline-capable, locally-reactive applications.

Core Concepts

SQL as a Declarative Language

SQLite uses SQL, which is a canonical example of a declarative programming language. Developers specify what data they want to retrieve — using set operations and relational logic — without specifying the control flow or execution strategy. The SQLite query engine determines the most efficient way to execute each query. As Wikipedia notes, the programmer writes down the logical specification of the desired result, and the system determines how to compute it.

SQL query planners translate declarative intent into imperative execution plans, computing multiple possible paths and selecting the optimal one — the same pattern found in React's reconciler and Terraform's planner.

Database query planners use two main strategies: heuristic-based rules (static pattern matching) and cost-based search (estimating execution costs of equivalent plans). Both serve the same purpose: finding an efficient path from current state to desired state without requiring developers to specify the exact steps. Fivetran's database demystified series documents this process in detail.

Embeddability and Zero Administration

Unlike PostgreSQL or MySQL, SQLite requires no server process, no configuration, and no administration. The database engine is a library linked directly into the application binary. The entire database lives in a single file on disk (or in memory). This model makes SQLite uniquely suited to environments where running a separate database server is impractical — embedded devices, mobile apps, browser contexts, and edge functions.

SQLite in the Browser via WebAssembly

The most technically significant development of recent years is SQLite's arrival in the browser through WebAssembly (WASM) compilation.

The Official WASM Build

The SQLite project released an official beta WASM build in 2022 and now provides WASM as an officially supported distribution alongside traditional SQLite deliverables. This marks the first official effort to provide WASM builds as first-class SQLite artifacts, supporting both synchronous and asynchronous JavaScript API variants. The full documentation is available at sqlite.org/wasm.

Full SQL in the Browser

SQLite compiled to WebAssembly supports the full SQL feature set — including JOINs, GROUP BY, subqueries, and complex relational queries — when running in the browser. This makes SQLite WASM fundamentally more capable than IndexedDB for relational data patterns. IndexedDB lacks support for complex queries and only provides key-value iteration capabilities.

Performance Advantage over IndexedDB

The performance gap is significant. Browser storage benchmarks show Chrome's IndexedDB implementation exhibiting latencies around 10ms for basic operations, while SQLite WASM operations commonly execute in 0.01ms — roughly a 1000x performance advantage for relational query workloads.

The Early Limitation: sql.js

Before the official WASM build, developers relied on sql.js, which has been in use since 2014. sql.js only supports in-memory SQLite databases and does not provide built-in persistence. Data persistence with sql.js requires manual import/export of the entire database file at application boundaries, making it unsuitable for true offline-first applications without additional storage infrastructure.

Persistence in the Browser: OPFS

Getting data to survive browser restarts requires a proper storage backend. The Origin Private File System (OPFS) has emerged as the recommended persistence layer for SQLite WASM.

How OPFS Works

OPFS provides a dedicated persistence layer for SQLite WASM that eliminates the need for IndexedDB as an intermediary. OPFS is only available in Worker-thread contexts (not the main UI thread), and the synchronous access path via createSyncAccessHandle significantly improves performance compared to asynchronous alternatives, though it adds architectural complexity requiring Web Worker integration.

SQLite WASM with OPFS persistence survives browser closures and restarts, storing data locally without server intervention. According to SQLite's own persistence documentation, data persists across browser closures with the OPFS backend without requiring a server.

Browser Storage Limits

Browser storage limits vary significantly by technology and implementation:

• localStorage: consistently capped at 5 MB across browsers
• Chrome IndexedDB: up to 80% of available disk space in best-effort mode
• Firefox IndexedDB: smaller of 10% of disk or 10 GB in best-effort mode
• OPFS with persistent storage: not subject to the same quota constraints when navigator.storage.persist() permission is granted

wa-sqlite: The Production VFS Library

wa-sqlite reached version 1.0 in July 2024 with support for multiple Virtual File System (VFS) implementations including OPFSPermutedVFS, OPFSAdaptiveVFS, and in-memory storage. As of 2025, wa-sqlite's OPFSCoopSyncVFS is the recommended general-purpose VFS for client-side SQLite, offering excellent performance with large databases and broad support across all major browsers (Chrome, Firefox, Safari, Edge) without requiring COOP/COEP headers. (PowerSync's 2025 persistence update documents this recommendation in detail.)

Multi-Tab Concurrency

OPFS readwrite-unsafe mode (available in Chrome 121+) enables concurrent file access across tabs. wa-sqlite's readwrite-unsafe mode with VFS implementations like OPFSPermutedVFS and OPFSAdaptiveVFS provide some concurrency support, though many VFS implementations still require exclusive locks limiting each origin to a single open connection at a time.

PowerSync's browser implementation supports multiple VFS options — IDBBatchAtomicVFS (using IndexedDB) and OPFS-based alternatives (OPFSCoopSyncVFS and AccessHandlePoolVFS) — with OPFS-based storage providing substantially improved performance compared to IndexedDB.

SQLite as a Sync Target: Local-First Architecture

One of the most significant architectural patterns to emerge around SQLite is its role as the client-side database in local-first sync systems, where a backend database (typically Postgres) is mirrored to a local SQLite instance on each device.

Why SQLite is Dominant for Local-First

SQLite is the dominant client-side storage solution for local-first Postgres sync applications across multiple platforms. PowerSync uses WASM SQLite in browsers and native SQLite on mobile platforms, while ElectricSQL supports both SQLite and PGlite (a WASM Postgres implementation) on clients. This widespread adoption stems from SQLite's:

• Embeddability across native and browser environments
• Zero-administration requirements
• Offline capabilities
• SQL query interface that mirrors the backend Postgres structure

PowerSync: Postgres-to-SQLite Sync

PowerSync automatically syncs backend databases (including PostgreSQL) with embedded SQLite databases on client devices. This approach handles the complexity of moving application state over the network, providing a local database mirror on each client that can be queried and modified locally while syncing changes back to the backend.

PowerSync uses a schemaless replication approach where Postgres data is replicated to client SQLite as schemaless JSON, with views applied on the client side for structured querying. This decouples the server Postgres schema from the client-side schema, allowing the server to evolve independently and clients to continue using older schema versions through Sync Rules transformations.

ElectricSQL: CRDT-Based Sync

ElectricSQL implements CRDT (Conflict-free Replicated Data Type) based synchronization for local-first applications, enabling active-active bi-directional sync between Postgres and SQLite without requiring conflict resolution code. The CRDT approach ensures that concurrent modifications from multiple clients automatically merge into a consistent state, allowing offline-capable applications to handle network partitions and distributed edits without manual conflict resolution.

Reactive Queries Over Local SQLite

Reactive query systems for SQLite WASM can be implemented using table-level tracking and query subscriptions (live queries). The system parses SELECT statements to determine touched tables and re-executes queries only when relevant tables change. This approach maintains hundreds of active subscriptions without degrading UI thread latency, achieving microsecond-scale "Write → Notification → Re-render" cycles.

PowerSync's watch API is the primary mechanism for applications to subscribe to local SQLite query results and receive real-time updates whenever the underlying data changes. For high-performance scenarios, PowerSync also offers trigger-based table diffs that use SQLite triggers to record changes as they occur, making overhead proportional to the number of changed rows rather than the result set size — though this feature is currently experimental and limited to single-table queries.

Schema Design for Sync

Two schema design patterns become essential when SQLite is a sync target:

UUID primary keys: UUID primary keys are the practical standard for local-first sync schemas because they can be generated on the client without server coordination, enabling offline record creation. This eliminates the need for server-based sequence generation (like SERIAL or IDENTITY columns) which requires a round-trip to the server and blocks offline-first workflows.

Soft deletes: Soft deletes (marking records as deleted rather than physically removing them) are practical for local-first sync schemas because they preserve data history and enable synchronization of deletion events across disconnected clients. Deletion becomes a data update (changing deleted_at) rather than a destructive operation, allowing conflicts and late-arriving deletes to be properly sequenced during sync.

Write Queuing and Upload

PowerSync's SDK automatically queues all writes made to the local SQLite database and invokes an uploadData() method on the backend connector to upload these mutations to the backend. When transient errors occur (network failures, server unavailability), changes remain in the upload queue and are retried at 5-second intervals while preserving original write order.

Schema evolution during sync requires coordination across backend and client-side SQLite schemas, with guidance needed for additive column changes, renaming tables and columns on both server and client, and changing column types.

SQLite at the Edge

Beyond the browser, SQLite has become a foundation for edge database products that bring data close to users across distributed infrastructure.

Turso (libSQL) and Cloudflare D1

Turso/libSQL and Cloudflare D1 represent different SQLite-on-the-edge strategies:

• Turso extends SQLite with a client/server protocol and replication, deploying databases with a primary region for writes and edge read replicas that clients connect to over HTTP/WebSockets. Turso also supports concurrent writes (in beta) achieving 4x SQLite write throughput while eliminating SQLITE_BUSY errors.
• Cloudflare D1 is a managed SQLite environment where transactions execute within Cloudflare's infrastructure with platform-managed durability and regional pinning, offering strong transactional semantics and optional Smart Placement for co-location.

Edge Latency Profile

SQLite read latency varies dramatically by deployment model:

This data illustrates the fundamental edge rendering tradeoff: blazing fast reads from local or distributed replicas at the cost of synchronous writes over the network.

Tooling and ORM Support

DrizzleORM

DrizzleORM supports client-side SQLite WASM through libSQL WASM imports (e.g., 'drizzle-orm/libsql/wasm'), enabling shared schema definitions between server (Postgres) and client (browser SQLite). The SQL.js engine provides synchronous API operations suitable for browser environments, and Drizzle Kit supports migration generation for local SQLite stores.

NuxtHub

NuxtHub Database provides type-safe SQL database support powered by Drizzle ORM, supporting PostgreSQL, MySQL, and SQLite with smart detection and automatic migrations at build time. For local development, NuxtHub uses PGlite (embedded PostgreSQL) if no environment variables are set, supporting local-first development workflows.

WebAssembly's Role in SQLite's Reach

SQLite's browser story is part of a broader WebAssembly capability story. WebAssembly enables high-density multi-tenant deployments through shared executable pages across isolated tenants — multiple applications using the same library version share read-only compiled code pages while maintaining separate memory and execution state. This can support tens of thousands of applications per server, at least an order of magnitude higher density than container or VM-based platforms. SQLite compiled to WASM inherits these density properties, making it viable for both per-user database isolation at the edge and per-tab isolation in the browser.