.png?table=block&id=1b921ab7-b085-806d-8cb4-cf6fffb529fd&cache=v2)
The choice of database can dramatically impact both performance and developer experience when it comes to building a modern web application. In Reflect, we had previously been relying on IndexedDB. We’ve now fully transferred to SQLite, and it has been a transformative process.
In this article, we’ll dive deep into why we made the switch, the technical challenges we faced, and how this rewrite has improved performance across our web and mobile applications.
The Database Dilemma in Web Applications
Almost every application needs a reliable way to persist data. On mobile and desktop, SQLite has long been the de facto standard—it’s lightweight, battle-tested, and ubiquitous. In contrast, web applications have historically been limited to IndexedDB:
IndexedDB’s Limitations
While IndexedDB provides basic storage capabilities, it’s essentially a “toy” database that comes with a host of limitations. For instance, when storing large datasets, IndexedDB can slow down dramatically. Many teams, including ours, resorted to hacks like bundling multiple rows into a single record to mitigate these issues. On mobile, the performance degradation is even more pronounced, often leading to crashes when handling extensive datasets (e.g., a graph with over 10,000 notes).
The Cross-Platform Challenge
Reflect is built on a single code base that targets both desktop and mobile (via a browser engine on iOS). This means we needed a database solution that worked reliably across all platforms, without the need to maintain separate implementations for desktop and mobile.
Why SQLite? The Emergence of WebAssembly and OPFS
The tide began to turn as new web technologies matured. Two key innovations enabled us to bring SQLite—traditionally a native database—into the browser environment:
- WebAssembly (Wasm):
WebAssembly allows low-level code (typically written in C/C++) to run efficiently in the browser. This means that SQLite’s proven codebase could be compiled into Wasm and executed within Chrome and other modern browsers.
- Origin Private File System (OPFS):
Another breakthrough was the ability to write files directly to disk through the browser’s sandbox (OPFS). Previously, browsers were limited to abstract storage solutions like IndexedDB. With OPFS, SQLite can operate with its conventional file-based architecture, ensuring faster and more reliable storage.
These technologies are now supported by all major browsers (Chrome, Safari, Edge), meaning that a unified SQLite solution is no longer just a mobile hack—it’s a viable, high-performance option for web applications too.
Rewrite Challenges and Approaches
Transitioning from an IndexedDB-based storage layer to a unified SQLite backend wasn’t a simple swap. It required a full rewrite of the core data handling code. Here are some of the key challenges we encountered:
1. Reworking the Data Access Paradigm
- Memory vs. Disk:
With IndexedDB, our previous implementation often loaded large swathes of data directly into memory. This was acceptable for small datasets but led to performance bottlenecks and crashes when scaling. With SQLite, we had to re-architect our data access patterns so that only the data currently needed (e.g., what’s visible on screen) is loaded into memory.
- Pagination and Lazy Loading:
Operations such as exporting notes had to be rethought. Instead of loading tens of thousands of records at once, we implemented pagination strategies similar to those seen in server-driven tables. This change ensures that operations remain efficient regardless of the total number of notes.
2. Unified Code Base Across Platforms
- Single Backend for All Apps:
Maintaining two separate database implementations (SQLite on mobile and IndexedDB on the web) would have required writing and testing duplicate logic. By standardizing on SQLite, we ensured that the same database code could serve both mobile and desktop versions, greatly reducing maintenance overhead.
- Custom Framework for Memory Management:
A significant part of the rewrite was developing a framework that manages data flow between disk and memory seamlessly. This framework automatically loads data as it appears on screen and cleans up when it’s no longer needed, ensuring that the app remains responsive and doesn’t consume excessive memory.
3. Syncing and Offline-First Considerations
- Dumping Firebase’s Caching Layer:
Previously, we relied on Firebase’s JavaScript library for caching, which in turn used IndexedDB. However, Firebase’s caching isn’t designed for offline-first applications. We replaced this with our own syncing engine that manages the reconciliation of changes between the local SQLite database and our server-side storage.
- Asynchronous Code Overhaul:
The rewrite also involved transitioning parts of our code from a synchronous model (where everything was assumed to be in memory) to an asynchronous one, where database I/O happens in the background. This change was non-trivial but necessary for ensuring that our application remains snappy, even when dealing with large datasets.
Performance Benefits and User Impact
The benefits of the rewrite have been immediately apparent, particularly on mobile:
- Instantaneous Launches:
Since we no longer load the entire dataset into memory, opening Reflect is nearly instantaneous—even when handling a graph with thousands of nodes. Only the notes currently in view are loaded, dramatically reducing load times.
- Enhanced Stability:
The risk of mobile crashes due to excessive memory usage is virtually eliminated. Users with large imported graphs (e.g., from tools like Roam or Obsidian) experience a stable and responsive app.
- Scalability:
With the new SQLite backend, the theoretical limits on the number of notes are now bound only by the available disk space—not by memory constraints. This opens up possibilities for significantly larger datasets without a performance penalty.
The transition from IndexedDB to SQLite in Reflect wasn’t merely about adopting a “better” database—it was about rethinking the entire data architecture to create a more robust, scalable, and performant application. By leveraging modern web technologies such as WebAssembly and OPFS, we’ve eliminated long-standing limitations and provided our users with a snappier, more reliable experience.
For technical engineers, this rewrite serves as an instructive case study on how evolving web standards can enable the use of native-grade technologies in browser-based applications. It underscores the importance of designing with scalability and performance in mind, especially when building offline-first, cross-platform apps.
Written by
.png?table=block&id=1b921ab7-b085-802a-80ee-f17277e82b00&cache=v2)