--- title: Whats the Best Database Setup for Apps That Work Offline description: Choose the right database setup for offline apps before you build. Covers local-first, sync and hybrid approaches, plus conflict resolution. image: https://weareaffective.com/hubfs/learning-centre-images/whats-the-best-database-setup-for-apps-that-work-offline.webp --- [Skip to content](https://weareaffective.com/learning-centre/whats-the-best-database-setup-for-apps-that-work-offline-1#main-content) [![we\_are\_affective\_logo\_200](https://weareaffective.com/hs-fs/hubfs/we_are_affective_logo_200.png?width=175&height=48&name=we_are_affective_logo_200.png "we_are_affective_logo_200")](https://weareaffective.com) - [Home](https://weareaffective.com) - About Us - [Our Story](https://weareaffective.com/about) - [How We Work](https://weareaffective.com/how-we-work) - Our Services - [App Planning & Strategy](https://weareaffective.com/app-planning-strategy) - [App Design](https://weareaffective.com/app-design-agency) - [App UX Design](https://weareaffective.com/app-ux-design) - [App UI Design](https://weareaffective.com/app-ui-design) - [App Technical Architecture](https://weareaffective.com/app-architecture) - [Existing App Audits](https://weareaffective.com/app-audit) - [Case Studies](https://weareaffective.com/case-studies) - [Pricing](https://weareaffective.com/pricing) - [Learning Centre](https://weareaffective.com/learning-centre) - [Get Started](https://weareaffective.com/get-started) Expert Guide Series # Whats the Best Database Setup for Apps That Work Offline Table of Contents A user opens your travel app somewhere in rural Patagonia. No signal. The app they have been trusting all day to show their route, saved pins, and hostel booking details shows a 'not connected' error and locks up completely. Everything they need is sitting on a server they cannot reach. We worked with a product exactly like this, built for exotic holidays and backpacking in remote wilderness areas, and when the product expanded into new trip types, that connectivity assumption became an absolute disaster for users in the field. The offline capability had never been designed in. It had to be retrofitted, expensively, after the fact. The decisions that led to that moment were made before a single line of code was written. They were [database decisions, architecture decisions](https://weareaffective.com/app-architecture-design-we-are-affective), and, underneath both, a failure to ask what the app needed to do when the network disappeared. Getting the database setup right for offline functionality is one of the least glamorous and most consequential choices in mobile product development. #### When local-first fits Local-first works well for productivity tools, note-taking apps, task managers, and any product where individual users generate data that belongs to them. It also suits apps in genuine low-connectivity environments, [field service tools, wilderness navigation](https://weareaffective.com/learning-centre/5-things-that-make-the-difference-between-so-so-apps-and-stellar-apps-what-your-), healthcare apps used in areas with poor signal. #### Where it struggles Local-first gets complicated with shared data. If two users are looking at the same record and both edit it while offline, you have a conflict problem the moment they both reconnect. It also adds device storage overhead and makes it harder to run centralised queries across all users, which matters if your product depends on aggregate data or real-time feeds. SQLite appears in around [31% of developer setups according to the Stack Overflow 2023 Developer Survey](https://survey.stackoverflow.co/2023#:~:text=PostgreSQL%2045.55,7%2C507), a figure that reflects its prevalence in exactly this kind of mobile-first use case. ## Sync-Based Architecture: How It Works and When to Choose It Sync-based architecture keeps the server as the primary data store but adds a local cache and a sync layer that replicates relevant data to the device. The app reads from the local cache during normal use. When the device has connectivity, it pulls updates from the server and pushes any local changes back. The difference from local-first is subtle but real. In a sync-based system, the server remains authoritative. Local data is a copy, not the original. This matters when consistency across users is more important than any individual user's ability to work offline. #### When sync-based fits Sync-based architecture suits collaborative products where multiple users interact with shared records, team project tools, messaging apps, booking platforms. It also fits products where regulatory requirements mean server-side records must be the system of record. If your product needs real-time data that the device should stay current with rather than create independently, sync-based is usually the right framing. #### The complexity cost Sync layers are not simple to build well. Deciding what to cache locally, when to invalidate it, how to handle partial syncs, and what happens when the user's local copy diverges from the server all require careful design. A rushed sync implementation produces an app that feels stale, slow to update, or inconsistent in ways that are difficult to diagnose. The decision to use a sync-based approach needs to be [made deliberately, with time and budget](https://weareaffective.com/learning-centre/what-belongs-in-a-product-strategy-before-you-talk-to-a-development-team) allocated for the sync logic itself, not as an afterthought. ## Hybrid Approaches: Combining Local and Remote Data Most real-world products end up with a hybrid approach, even if they did not plan for one. Some data lives locally and is written locally first. Other data is fetched fresh each time. The challenge with hybrid approaches is that the boundary between local and remote needs to be clearly defined, or the product ends up inconsistent in ways users cannot predict. A reasonable hybrid pattern for a travel app would cache static content, maps, venue details, itinerary items, locally, while keeping live data like pricing or availability as network-only. The user can view their saved trip plan offline but cannot book or check current prices without connectivity. That distinction is clear and communicable. Users can understand it, and the app can signal it without confusion. Define the local/remote boundary in writing before development starts. Every data type in your app should sit on one side or the other, with a clear rationale for where it lives and what happens to it when the network drops. #### Where hybrids go wrong The failure mode in hybrid architectures is inconsistency without communication. If some parts of the app work offline and others do not, but the app does not tell the user which is which, you get a confusing and frustrating experience. The user taps something, nothing happens, and they have no idea whether the app is broken or the content simply requires a connection. #### Making the hybrid legible Good hybrid design makes the boundary visible. Offline-available content is clearly labelled or signposted. Actions that require connectivity are disabled with a clear message explaining why. The user always knows what they are looking at and what they can do with it. This is [design work, not just engineering work](https://weareaffective.com/learning-centre/why-your-social-media-app-needs-more-than-just-pretty-design), and it needs to happen before the app is built rather than as a layer added afterwards. ## Conflict Resolution: The Problem Nobody Talks About Until It Bites Them Conflict resolution is what happens when two versions of the same data disagree. A user edits a record on their phone while offline. Someone else edits the same record on the web. When the phone reconnects, both versions exist and one of them has to win, or the conflict has to be surfaced to the user for manual resolution. Teams routinely under-estimate this problem. In a product where data is read-only on the device, conflicts cannot happen. But in any product where users write data while offline, conflicts are a guaranteed outcome for any user who does meaningful work across multiple sessions or devices. #### Automatic vs manual resolution Automatic conflict resolution uses rules to decide which version wins. "Last write wins" is the simplest rule and often the wrong one, because it silently discards data without telling the user. Operational transformation and CRDTs (Conflict-free Replicated Data Types) are more sophisticated approaches that allow multiple edits to merge without data loss, but they are architecturally complex and expensive to implement from scratch. #### When to surface conflicts to the user For some products, the right answer is to show the user both versions and ask them to choose. This works in contexts where the user has enough understanding of their own data to make that decision, a document editor, a note-taking app, a personal tracker. It fails in consumer-facing products where the average user has no idea what a sync conflict is or why they are being asked to resolve one. [Design the conflict resolution approach for the real user](https://weareaffective.com/learning-centre/what-a-development-team-actually-needs-to-know-about-the-user-before-sprint-one), not the technically literate one. Decide your conflict resolution strategy before you choose your database. Some databases have conflict handling built in. Others leave it entirely to the application layer. Choosing the database first and then discovering it has no conflict tools is a painful and expensive sequence. ## The Cost of Retrofitting Offline Capability Adding offline capability to a product that was not designed for it is expensive. This is not a theoretical concern. We worked on the backpacking travel app where offline had to be retrofitted after the product was already in market. The cost in developer time, rework, and delayed feature development was substantial, and the result was still a compromise rather than a clean architecture. The reason retrofit is so expensive is that offline capability is an architectural property. The data model, the sync logic, the caching strategy, the error states, the user communication, all of these need to be designed as a system. Retrofitting means unpicking decisions that are baked into every layer of the product and replacing them, usually without being able to start from scratch. A related lesson from a different project: on the alcohol buying and selling platform we built, being forced to embed web elements instead of a proper API layer added approximately 20% uplift in work across the entire project, and we had to drop features to stay within budget. Architectural constraints discovered mid-build always cost more than architectural decisions made upfront. Offline is the same category of problem. If there is any chance your app will need offline capability, even in a future version, build the data architecture to support it from the start. Adding a sync layer later costs far more than designing for it at the beginning. ## How Database Choice Constrains Your Offline Options The database you choose determines what offline patterns are even available to you. This is worth stating plainly because teams often choose a database based on familiarity or backend convention and then discover the offline implications later. PostgreSQL and MySQL are excellent server-side relational databases. They are the right choice for a huge range of products, and they dominate professional use: [the Stack Overflow 2023 Developer Survey](https://survey.stackoverflow.co/2023#:~:text=76%2C634%20responses) found PostgreSQL in use by around 46% of respondents and MySQL by around 41%. But neither runs natively on a mobile device. If your mobile app is talking directly to Postgres, every data operation requires a network round-trip. Offline capability then depends entirely on what caching layer you build in front of that connection, and that layer is not provided by the database. #### Databases with offline support built in SQLite runs on the device. It is a full relational database that requires no server, no network, and no round-trips. WatermelonDB and Realm are mobile-first databases designed with sync as a first-class concern. Firebase Realtime Database and Firestore both have offline persistence modes with built-in sync. These are architecturally different tools from a server-side Postgres instance, and choosing one over another is a decision about what offline patterns are possible, not just a preference about query syntax. #### The API layer question A clean API layer between your mobile app and your backend database is good practice, and it is what makes switching or layering databases possible. Without it, you are tightly coupled to whatever sits on the server. We saw this clearly on the alcohol trading platform project, where the existing web application had been built in a way that made a proper API layer impossible to add cleanly. The mobile product ended up embedded with web elements, creating coupling that made any future architectural change, including offline support, far harder than it needed to be. ## What Good Offline UX Looks Like in Practice The engineering questions matter, but so does [what the user actually sees](https://weareaffective.com/learning-centre/how-to-read-a-user-session-recording-for-emotional-signal-rather-than-task-compl). Good offline UX is characterised by one thing above everything else: the user always knows what state the app is in and what they can do. The mapping app model is instructive here. When a navigation app loses connectivity, it does not blank out. It continues showing the map at whatever detail level it has already cached. If the user tries to load an area that was not pre-downloaded, they see a lower-resolution overview rather than an empty screen. The app tells them clearly what mode they are in. They are never left staring at something with no indication of what is happening or why. #### Communicating offline states clearly Every data state in an offline-capable app needs a corresponding UI state. That means at least three distinct conditions to design for: fully connected and live, connected but syncing, and offline with cached data. Each of these should look and feel different in a way that a non-technical user can immediately read. An icon, a banner, a subtle colour shift, the specific mechanism matters less than the consistency of the signal. #### Deferred actions and optimistic UI When a user takes an action offline, saving a record, completing a form, marking something done, the app should confirm that action immediately, even though the data has not yet reached the server. This is called optimistic UI. The confirmation is genuine: the data is saved locally and will sync when connectivity returns. What the user should never experience is [submitting an action and seeing nothing happen](https://weareaffective.com/learning-centre/why-do-some-apps-feel-like-they-were-made-just-for-you), with no indication of whether it worked or failed. That silence is the failure mode, and designing against it is as much a UX problem as an engineering one. ## The Decision You Need to Make Before Development Starts There is one question that has to be answered before database selection, before framework choice, before the first sprint: what does your app need to do when there is no internet connection? The answer sits on a spectrum. At one end, the app is a pure online tool, a real-time dashboard or a live booking system, where offline capability is genuinely not a requirement. At the other end, the app must function fully and independently without any network, storing and syncing data when connection is eventually restored. Most products sit somewhere in between, and the exact position on that spectrum determines the architecture. | Offline requirement | Architecture fit | Database approach | Conflict risk | | --- | --- | --- | --- | | None (always online) | Server-first | Remote DB only | None | | Read-only cache | Sync-based | Remote DB + local cache | Low | | Read and write offline | Local-first or hybrid | SQLite or mobile-first DB | High | | Full offline independence | Local-first | On-device DB with sync layer | Very high | This decision needs to be made explicitly, by [the people who understand the product and the users](https://weareaffective.com/learning-centre/what-happens-to-your-ip-when-developers-leave-your-project), before development begins. Leaving it implicit means the developers make it by default, usually by choosing the simplest path, which is always the always-online assumption. That default is not wrong for some products. But it is a choice, and it should be made consciously. 1. Define your minimum viable offline experience in plain language. 2. Map every data type your app uses to either local-first, cached, or network-only. 3. Choose your database to support that data map, not the other way around. 4. Design the conflict resolution approach before writing sync logic. 5. Build the UI states for offline, syncing, and error before launch, not after. ## Conclusion Offline capability is an architectural property, and architecture is decided at the start of a project. The travel app in the wilderness, the alcohol trading platform with its embedded web elements, the social football product that launched on one platform instead of two, these are all cases where a constraint discovered mid-build, or after launch, cost far more to address than it would have cost to plan for at the beginning. The database question is real, and the options are genuinely different from one another. SQLite on the device is not the same kind of tool as a Postgres instance on a server, and choosing between them is choosing between fundamentally different models of where data lives and when it moves. Making that choice without understanding the offline implications is how products end up needing expensive retrofits. Get clear on what your users need when the network is gone. Build that requirement into the architecture from day one. Design the UI states that communicate the offline condition honestly. The rework you avoid will pay for the planning time several times over. If you are building a product where offline behaviour matters and you are not yet sure which architecture fits, [let's talk about your app](https://weareaffective.com/get-started). ## Frequently Asked Questions What is local-first architecture and when should I use it? Local-first architecture stores data primarily on the user's device, meaning the app functions fully without a network connection. It works best for productivity tools, note-taking apps, task managers, and any product used in low-connectivity environments such as wilderness navigation or remote healthcare settings. What is sync-based architecture and how does it differ from local-first? Sync-based architecture keeps the server as the primary data store and replicates relevant data to the device as a local cache. Unlike local-first, the server remains authoritative, so local data is treated as a copy rather than the original source of truth. Which database setup is better for apps with multiple users editing shared records? Sync-based architecture is generally the better fit when multiple users interact with shared records, as it keeps the server in control of what the definitive version of data looks like. Local-first can create difficult conflict problems when two users edit the same record offline and then reconnect. What are the main drawbacks of local-first for offline apps? Local-first adds device storage overhead and makes centralised queries across all users much harder to run. It also struggles with shared data, since offline edits from multiple users to the same record can cause conflicts that are complex to resolve cleanly. Why is getting the database setup right so important before development starts? The database and architecture decisions made before coding begins determine how the app behaves when a network connection disappears. Retrofitting offline capability after launch is expensive and disruptive, as the article illustrates with a travel app that had to be rebuilt after connectivity assumptions broke in the field. What makes sync layers difficult to build well? Sync layers require careful decisions about what data to cache locally, when to invalidate it, and how to handle situations where the local copy diverges from the server. A poorly built sync implementation can make an app feel stale, slow to update, or inconsistent in ways that are hard to diagnose and fix. How common is SQLite as a database choice for mobile apps? According to the Stack Overflow 2023 Developer Survey, SQLite appears in around 31% of developer setups. This reflects its widespread use in mobile-first products, particularly those that need local storage for offline functionality. Should offline capability be added to an app after it has already launched? Retrofitting offline capability after launch is possible but costly, both in development time and in the disruption it causes to existing users and codebases. The article strongly suggests treating offline functionality as a design requirement from the very beginning of the product development process. ## Related Articles [![We Are Affective](https://weareaffective.com/hubfs/we_are_affective_logo_mark.svg)](https://weareaffective.com) 20-22 Wenlock Road London, N1 7GU United Kingdom +44 20 4572 8062 [hello@weareaffective.com](mailto:hello@weareaffective.com) Services [App planning & strategy](https://weareaffective.com/app-planning-strategy) [App design](https://weareaffective.com/app-design-agency) [App UX design](https://weareaffective.com/app-ux-design) [App UI design](https://weareaffective.com/app-ui-design) [App technical architecture](https://weareaffective.com/app-architecture) [Existing app audits](https://weareaffective.com/app-audit) Legal [Privacy policy](https://app.termly.io/policy-viewer/policy.html?policyUUID=b8fa9921-7518-4fb5-8ddd-9dc7f5977ed2) [Terms](https://app.termly.io/policy-viewer/policy.html?policyUUID=8b6a6ad5-91bd-4176-a5f7-6d36b0398f70) Case studies [TravAI](https://weareaffective.com/case-studies/travai) [Meditech](https://weareaffective.com/case-studies/harley) [WorkingWeight](https://weareaffective.com/case-studies/workingweight) [SkinSync](https://weareaffective.com/case-studies/skinsync) [Three Lochs](https://weareaffective.com/case-studies/three-lochs) [Drift](https://weareaffective.com/case-studies/drift) About us [Our Story](https://weareaffective.com/about) [How We Work](https://weareaffective.com/how-we-work) Guides [Creating an app](https://weareaffective.com/how-to-create-an-app) [Building an MVP](https://weareaffective.com/building-an-mvp) [Cost and budgeting](https://weareaffective.com/app-development-cost) [App technology](https://weareaffective.com/app-development) [Planning and strategy](https://weareaffective.com/app-planning-strategy) [User research](https://weareaffective.com/app-user-research) [Onboarding design](https://weareaffective.com/app-onboarding-design) [User psychology](https://weareaffective.com/user-psychology-app-design) [Launch and growth](https://weareaffective.com/app-launch-growth) Copyright © 2026, weareaffective.com. All rights reserved.