Local-First Databases

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

Most applications today treat the cloud as the source of truth. Your data lives on someone else’s server, and every interaction—reading a note, editing a document, checking off a task—requires a round trip over the network. When the network is fast, this works. When it isn’t, or when it disappears entirely, the application breaks.

Local-first software flips this model. The database on your device is the primary copy. Changes happen instantly against local storage, then sync to other devices and the cloud in the background. The result is an application that’s fast by default, works offline, and gives users real ownership of their data.

In this post, we’ll cover:

• The local-first manifesto - Where the idea came from and why it matters
• CRDTs - The data structures that make conflict-free sync possible
• The browser as a database runtime - OPFS and WebAssembly are changing what’s possible client-side
• Frameworks and tools - ElectricSQL, PowerSync, cr-sqlite, and others building the local-first stack
• Linear as a case study - How one of the fastest-growing dev tools is built on local-first
• Tradeoffs - Where local-first shines and where it falls short

The local-first manifesto

In 2019, Martin Kleppmann and researchers at Ink & Switch published Local-first software: You own your data, in spite of the cloud. The paper laid out a simple argument: when data lives primarily in the cloud, users become tenants of their own information. The service provider controls access, and if the service shuts down, the data goes with it.

The paper proposed seven ideals for local-first software: it should be fast (no spinners), work across multiple devices, function offline, support collaboration, remain available for the long term (“longevity”), give users full ownership of their data (“you own your data”), and respect user privacy. Cloud apps satisfy some of these—collaboration, multi-device access—but fail on others, particularly speed, offline support, and longevity.

The core technical insight was that Conflict-Free Replicated Data Types (CRDTs) could bridge the gap. CRDTs allow multiple devices to edit data independently, then merge changes automatically without conflicts. No central server is needed to coordinate edits.

How CRDTs enable local-first

CRDTs are data structures designed for distributed systems. Each replica of a CRDT can be updated independently—even without a network connection—and when replicas reconnect, their states merge deterministically into a consistent result. No manual conflict resolution is needed.

There are two main families of CRDTs. State-based CRDTs (also called convergent replicated data types, or CvRDTs) sync by periodically sending their full state to other replicas, which merge it using a mathematical join operation. Operation-based CRDTs (also called commutative replicated data types, or CmRDTs) sync by broadcasting individual operations, which are designed to be commutative—they produce the same result regardless of the order they’re applied.

In practice, most local-first frameworks use operation-based CRDTs or hybrid approaches. For example, a collaborative text editor might represent the document as a sequence CRDT where each character has a unique ID and a position relative to its neighbors. Two users can type simultaneously in different parts of the document, and when their edits sync, the characters interleave correctly without overwriting each other.

The two most widely used CRDT libraries are Automerge, created by Martin Kleppmann and the Ink & Switch team, and Yjs, created by Kevin Jahns. Automerge provides a JSON-like document model with rich merging semantics. Yjs focuses on performance and is widely used for real-time collaborative editing—it powers the collaboration features in tools like Notion and Tiptap.

The browser as a database runtime

Two browser APIs have made local-first practical for web applications: the Origin Private File System (OPFS) and WebAssembly (WASM).

OPFS provides near-native file I/O from within the browser. Unlike IndexedDB, which adds overhead through its transactional API and structured cloning, OPFS offers a synchronous access handle that reads and writes directly to a file. This is exactly what embedded databases like SQLite need—a fast, reliable storage layer.

WebAssembly lets developers compile C, C++, and Rust code to run in the browser at near-native speed. SQLite itself is written in C, and sql.js and the official SQLite WASM build both compile it to WASM. DuckDB-WASM does the same for analytical workloads. Combined with OPFS for storage, you can run a full relational database engine entirely in the browser with persistent data that survives page reloads.

Before OPFS and WASM, browser-based local-first apps were limited to IndexedDB, which is asynchronous, has inconsistent performance across browsers, and lacks the query capabilities of SQL. The combination of WASM-compiled database engines and OPFS storage has removed those limitations.

Frameworks and tools

The local-first ecosystem has grown rapidly. Here are the most notable projects, organized by their approach.

SQLite-based

cr-sqlite, created by Matt Wonlaw, adds CRDT support directly to SQLite as an extension. It uses Causal-Length (CLSet) CRDTs on top of regular SQLite tables, allowing any SQLite database to sync conflict-free across devices. The extension is lightweight and works anywhere SQLite runs.

SQLite Sync takes a similar approach, embedding CRDT algorithms into SQLite for conflict-free sync. Devices update data independently, even offline, and when they reconnect, changes merge automatically.

ElectricSQL takes a different approach: it uses PostgreSQL as the backend and syncs data to local SQLite databases on client devices. The sync engine sits between your Postgres database and your app, using Postgres logical replication to capture changes and push them to local SQLite instances. This gives you the full power of Postgres on the server with the speed of local reads on the client.

PowerSync is a sync layer that connects a backend Postgres (or MongoDB or MySQL) database to local SQLite databases on client devices. It provides a real-time sync engine with offline support and conflict resolution. The PowerSync SDK is available for React Native, Flutter, web, and other platforms.

Standalone sync frameworks

Replicache provides a client-side sync framework that works with any backend. Instead of syncing at the database level, it uses a “mutation” model where the client sends operations to the server and the server pushes back authoritative state. This gives developers fine-grained control over conflict resolution at the application level.

Evolu, created by Daniel Steigerwald, is a local-first framework built on SQLite and CRDTs with end-to-end encryption. It’s designed for React and provides a typed query API that runs against a local SQLite database, with sync handled automatically.

Jazz provides collaborative data structures (called CoValues) that sync automatically across devices. It handles authentication, permissions, and real-time sync out of the box, with a focus on making local-first development feel like working with regular objects.

Triplit is a full-stack database that syncs between server and client. It provides a built-in query engine, schema enforcement, and real-time sync with offline support.

CRDT libraries

If you need lower-level building blocks rather than a full framework, Automerge and Yjs provide CRDT implementations you can integrate into any architecture. Automerge gives you a JSON-like document model; Yjs gives you high-performance shared types optimized for real-time collaboration.

Local-first in practice: Linear

The best argument for local-first architecture isn’t theoretical—it’s Linear. The project management tool has become one of the most admired developer products in recent years, and its speed is the first thing people notice. Page transitions happen in under 50ms. Search is effectively instant. There are no loading spinners. That performance comes directly from a local-first architecture.

Linear’s co-founder and CTO, Tuomas Artman, has spoken extensively about their approach. In his Local-First Conf 2024 talk and on the localfirst.fm podcast, he explained that Linear’s team built their sync engine before building the product itself. Artman had already spent over a decade building sync engines at companies like Groupon and Uber, and the founding team made a deliberate bet that local-first would be a competitive advantage worth the upfront investment.

The architecture works like this: when you open Linear, the app downloads your workspace’s data and stores it in IndexedDB in the browser. The UI reads from and writes to in-memory MobX observables backed by that local store. Every interaction—searching, filtering, navigating between views—hits local data with zero network latency. Mutations are applied optimistically to the local state, sent to the server via GraphQL, and only rolled back if the server rejects them. Real-time updates from other users arrive over WebSockets and are applied to the local store incrementally.

One detail worth noting: Linear doesn’t use CRDTs for most of its data model. Their sync engine uses a last-write-wins (LWW) strategy with the server maintaining a total ordering of all transactions through an incrementing sync ID. As Artman has pointed out, conflicts in a project management tool are rare enough that LWW is sufficient. The only place Linear uses CRDTs is for rich-text issue descriptions, where multiple users might edit the same document simultaneously. A reverse-engineering of Linear’s sync engine by wzhudev—which Artman endorsed as “a pretty awesome (and correct) write-up”—documents this architecture in detail.

The infrastructure benefits are striking. Artman shared at Local-First Conf 2024 that Linear serves their EU data center on roughly two CPU cores costing about $80/month, with the CPU nearly idle. Because reads are served from local data on the client, the server only handles writes and sync—a fraction of the traffic a traditional server-rendered application would generate.

Linear demonstrates that local-first isn’t just about offline support or data ownership. Even for a team collaboration tool that’s always online, the architecture delivers tangible benefits: a UI that feels instant, simpler frontend code (no loading states, no optimistic UI hacks), and dramatically lower infrastructure costs. It’s a compelling proof point for any team evaluating whether local-first is worth the upfront investment in a sync engine.

Where local-first shines

Local-first architecture is strongest in applications where latency and offline access matter. Note-taking apps, task managers, writing tools, and collaborative editors all benefit from instant local writes. Users on unreliable connections—mobile apps, field tools, apps used in areas with poor connectivity—get a dramatically better experience.

Privacy is another advantage. Since data is stored on the user’s device, sensitive information doesn’t need to leave the client. Combined with end-to-end encryption (as frameworks like Evolu provide), users retain genuine control over their data.

For developers, local-first can simplify certain parts of the stack. There’s no loading state for cached data, no optimistic UI that might roll back, and no need to build complex offline modes on top of a cloud-first architecture. The local database is the source of truth, and the network is an optimization.

Where local-first falls short

Sync is the fundamental challenge. Nikita Prokopov (Tonsky) made this point clearly in his post Local, first, forever: even personal local-first apps need to sync between your own devices, and syncing doesn’t work without a server. If the company providing sync shuts down, that capability disappears. His proposed workaround—using file sync services like Dropbox or Syncthing combined with CRDTs—is elegant but still introduces complexity.

Schema migrations in a distributed system are harder than in a centralized database. When you change a table schema on the server, you can run a migration and be done. In a local-first system, clients may be running different versions of the schema simultaneously, and you need to handle that gracefully.

Storage limits are real, especially in the browser. OPFS quotas vary by browser and available disk space. On mobile devices, the OS can evict app data under storage pressure. For applications with large datasets, local-first may not be practical without aggressive data management.

Finally, local-first adds complexity to features that are simple in a server-first model: access control, audit logging, server-side validation, and aggregate queries across all users’ data all require careful thought when the primary data store is distributed across devices.

When you still want a server database

Local-first isn’t a replacement for server databases—it’s a complement. If your application needs complex server-side queries, aggregations across all users, strong access control enforced at the data layer, or integration with other backend services, you still need a server database.

The most practical architecture for many applications is a hybrid: a server database like PostgreSQL handles the authoritative data model, access control, and server-side logic, while local-first sync brings a subset of that data to the client for fast reads and offline access. This is exactly the model that ElectricSQL and PowerSync implement.

If you’re already using PostgreSQL, you don’t have to choose between server-first and local-first. Tools like ElectricSQL let you keep your existing Postgres schema and add local-first capabilities incrementally. For more on the PostgreSQL ecosystem, check out our posts on top managed PostgreSQL cloud providers and top PostgreSQL extensions.

Wrapping up

Local-first databases represent a meaningful shift in how applications handle data. Instead of treating the server as the source of truth and the client as a thin cache, local-first puts a real database on the device and syncs in the background. CRDTs provide the mathematical foundation for conflict-free merging, and tools like OPFS and WebAssembly have made it practical to run full database engines in the browser.

The ecosystem is maturing rapidly. Frameworks like ElectricSQL, PowerSync, cr-sqlite, and Evolu have made it possible to build local-first applications without implementing CRDTs from scratch. The tradeoffs are real—sync complexity, schema migrations, storage limits—but for the right use cases, the benefits in speed, offline support, and data ownership are substantial.

If you’re evaluating whether local-first makes sense for your application, start by asking two questions: do your users need offline access or instant responsiveness, and is your data model simple enough to sync without server-side coordination? If the answer to both is yes, local-first is worth exploring.