Keep two roles separate: an immutable database shipped with the React Native app (or a database downloaded from your server) and a writable, app-local SQLite mirror that the UI actually queries. At startup, try the network first when connectivity is available; if the download succeeds, validate and merge it into the writable copy. If the network is unavailable, open the last known-good local copy instead. For row-level refreshes, use SQLite INSERT ... ON CONFLICT ... DO UPDATE against a stable unique key. That UPSERT-MERGE changes existing rows in place, so readers never observe the delete-then-insert gap.
What a read-only offline mirror means
A read-only mirror is a local database whose source data is treated as authoritative but whose packaged source file is never modified. The app reads from a writable copy in its sandbox. “Read-only” describes the source artifact, not the queries the app is allowed to run against the mirror: the mirror can receive refreshes, indexes, and (if your product needs them) separate local fields.
As an Amazon Associate I earn from qualifying purchases.
This split prevents a common deployment mistake. An SQLite file inside an application bundle is an asset, and mobile operating systems do not make that bundle location writable. OP-SQLite documents moving an asset database before writing to it; its moveAssetsDatabase configuration is idempotent, so invoking the move during startup is safe even when the copy already exists. See the OP-SQLite configuration guide.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11The lifecycle
- Ship a baseline SQLite asset, or download a current database to a permitted sandbox path.
- Ensure there is an app-writable local database. On first launch, copy or move the asset; on later launches, retain the existing mirror.
- Attempt a bounded network refresh before opening the mirror for normal reads when connectivity exists.
- Validate the downloaded file or incoming rows before making them active.
- Commit a whole-file replacement or a row-level UPSERT transaction.
- Open the last successful local copy when the request fails, times out, or the device is offline.
The result is network-first behavior, not network-only behavior. A user should still see data when airplane mode, captive portals, server errors, or a temporary timeout prevents refresh.
#1 Best Overall
Choose the source: bundled asset or downloaded database
| Source | When it fits | Required local handling | Important limitation |
|---|---|---|---|
| Packaged SQLite asset | Every installation needs a baseline catalog, reference data, or migration seed. | Move the asset to an app-writable location before opening it for writes; configure moveAssetsDatabase as documented by OP-SQLite. |
The bundle copy remains immutable. Updates require a later refresh or a new app release. |
| Network-downloaded SQLite file | A server publishes complete snapshots and row-level synchronization is unnecessary or too expensive. | Download to a platform-permitted path, validate the file, then open it or refresh the active local copy. | A failed or partial download must never replace the last known-good database. |
OP-SQLite’s examples place a downloaded database in the iOS library directory or Android database location. The same documentation explains that iOS filesystem sandboxing restricts arbitrary paths and recommends a fallback when an Android external-files path is unavailable. Use the paths and fallback behavior documented for the OP-SQLite version you ship rather than assuming a desktop-style filesystem. The platform-specific details are in the configuration documentation.
Build a network-first startup sequence
Keep acquisition and local commit as separate stages. A response that downloaded successfully is not automatically a valid database, and a valid download is not active until the local commit succeeds.
- Locate the mirror. Check whether the app-writable database exists. If it does, record its schema or data version so a failed refresh can continue using it.
- Set a bounded request. When connectivity is available, request the latest snapshot or changes with a timeout appropriate for startup. Do not make the first screen wait indefinitely for a server.
- Write to a staging path. Save a downloaded file separately from the active database. Never stream over the file that readers are currently using.
- Validate before activation. Verify that the file can be opened, expected tables exist, the schema version is supported, and required metadata is present. If validation fails, delete the staging file and retain the active mirror.
- Apply the refresh. For a complete snapshot, follow a replacement procedure verified for your target platform and OP-SQLite release. For incremental data, open the writable mirror and run UPSERT statements in a transaction.
- Record success. Store the server version or cursor only after the local commit completes. This prevents a crash between download and commit from causing the next launch to skip data.
- Open locally on failure. A timeout, HTTP error, invalid file, or interrupted commit should select the last successful mirror. If there is no mirror yet, fall back to the packaged asset when one exists and show the UI’s offline or loading state.
The inspected OP-SQLite material does not guarantee a universal atomic whole-file swap procedure. Do not describe a rename sequence as automatically safe on every iOS and Android version. If whole-file replacement is required, verify locking, open-connection behavior, journaling mode, and the exact OP-SQLite release documentation for your deployment.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Make the packaged database writable without damaging the source
Configure the asset move at startup and treat the destination as the mirror. Because the documented move operation is idempotent, repeated startup calls do not require a custom “already copied” flag. Keep any migrations and indexes on the writable destination, not in the application bundle.
Rank #2
For a downloaded source, use the iOS library or Android database location shown in OP-SQLite’s configuration documentation. Android external-files storage may be unavailable in some environments, so implement the documented fallback and check that the resulting path is readable after process restarts. A path that works in a simulator is not proof that it is valid on every production device.
Whole-file refresh or row-level UPSERT?
| Decision | Whole-file refresh | Row-level UPSERT-MERGE |
|---|---|---|
| Input | A complete database snapshot. | Rows or batches keyed by stable identities. |
| Bandwidth | Can be large when only a few records changed. | Sends only changed records when the server supports deltas. |
| Local work | Validate and activate a separate file; replacement semantics are platform- and version-specific. | Apply explicit SQL updates inside a transaction. |
| Preserving local-only columns | Requires a schema and merge strategy across files. | Easy to preserve by listing only server-owned columns in the update clause. |
| Deletion handling | The snapshot can represent the complete set. | Requires tombstones, a deletion feed, or an explicit reconciliation pass; missing rows are not automatically deletions. |
Use a whole-file approach when the publisher controls a complete, internally consistent snapshot and can tolerate its transfer cost. Use UPSERT-MERGE when the mirror contains local state, when updates are incremental, or when readers must never see a transient empty table.
Update rows without deleting and reinserting them
1. Define a stable conflict target
SQLite UPSERT is triggered by a uniqueness-constraint conflict. Give every server-owned entity an immutable primary key or another unique constraint that remains stable across refreshes.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteCREATE TABLE item (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
server_version INTEGER NOT NULL,
server_updated_at TEXT NOT NULL,
local_note TEXT
);The id value must identify the same logical item on every download. Do not use a display name that can change, or a row number that can be reordered.
Rank #3
2. Name exactly what the server may overwrite
INSERT INTO item (
id, name, server_version, server_updated_at
) VALUES (?, ?, ?, ?)
ON CONFLICT (id) DO UPDATE SET
name = excluded.name,
server_version = excluded.server_version,
server_updated_at = excluded.server_updated_at
WHERE excluded.server_version > item.server_version;excluded is SQLite’s incoming row. The explicit column list means local_note survives a refresh. The optional WHERE clause rejects an older response that arrives after a newer one. If the service provides no monotonic version, define another freshness rule—such as a trusted update timestamp—or accept that arrival order determines the winner. SQLite supplies the conflict and update mechanics; it does not choose your field precedence or freshness policy. The formal syntax is documented at SQLite UPSERT.
3. Apply a batch in one transaction
BEGIN;
-- execute the UPSERT above once for each incoming row
COMMIT;Rollback the transaction if any row fails validation or violates a constraint. A transaction keeps the batch internally consistent: readers see the state before the commit or the state after it, rather than an application-defined sequence of partial changes. The transaction does not itself fetch data, retry requests, or synchronize with a server; those are separate application responsibilities.
4. Reconcile deletions deliberately
UPSERT handles inserts and updates when a key conflicts. It does not infer that an absent row was deleted. Choose one of these server contracts:
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →- Send tombstone rows with the stable key and a deletion marker, then process them explicitly.
- Send a change feed with deletion events and a cursor that the mirror stores after commit.
- Publish a complete snapshot and run a separate reconciliation that removes only rows proven absent from that snapshot.
Do not add a “delete everything, then insert the response” phase to a live mirror. That creates an observable empty interval, loses local-only columns, and increases the chance that a crash leaves no usable data.
Keep offline user writes separate from downloaded server state
A read-only mirror is appropriate when the app only consumes server data. If users can edit records while offline, model those edits as pending mutations rather than silently treating them as another copy of server truth.
A durable outbox entry normally contains the operation, stable entity ID, payload, creation time, and a generated idempotency key. Persist the entry before presenting the edit as safely queued. On reconnection, an executor sends the request, retries transient failures, and removes the entry only after an acknowledged result.
The server must deduplicate repeated idempotency keys. A retry can occur after the server applied the first request but before the client received its response; without server-side deduplication, one user action can be applied twice. TanStack’s React Native offline-transactions guide demonstrates an architecture in which OP-SQLite-persisted collection rows and an outbox in AsyncStorage are restored with stable names and IDs. It is an optional pattern, not an OP-SQLite requirement; see the guide.
When an outbox mutation succeeds, merge the server’s canonical response into the mirror with the same stable key. Decide how conflicts between a pending local edit and a newer server row are resolved; UPSERT syntax alone cannot decide whether local, server, or field-level last-write-wins is correct.
Best Value
Cloud synchronization is optional
A device-local file does not require a cloud synchronization product. You can download snapshots from an existing API and keep all reads and refreshes on the device. SQLite AI describes local offline-first operation with synchronization to SQLite Cloud, PostgreSQL, or Supabase when online; that is an option for distributed synchronization, not a prerequisite for an OP-SQLite database downloaded to a phone. Choose it only when you need multi-device convergence, server-side conflict handling, or a managed replication service.
Failure modes and recovery
| Symptom | Likely cause | Recovery |
|---|---|---|
| “Read-only database” or write failures on first launch | The app opened the bundled asset directly. | Use the documented asset-move configuration and open the writable destination. |
| Refresh works in development but not on a device | The download path is outside the platform sandbox or an Android external-files path is unavailable. | Use the iOS library or Android database location and the documented Android fallback; test after a cold restart. |
| The table briefly disappears during refresh | A delete-then-insert algorithm is exposing an intermediate state. | Use a transaction containing explicit UPSERTs, or adopt a verified whole-file activation strategy. |
| Older data overwrites newer data | Responses arrive out of order and the UPSERT has no freshness predicate. | Store a server revision or trusted timestamp and add a WHERE condition to the update. |
| Rows never disappear after server deletion | The client assumes that absence from a delta means deletion. | Implement tombstones, deletion events, or complete-snapshot reconciliation. |
| An offline edit is applied twice | The retrying client generated no durable idempotency key, or the server does not deduplicate it. | Persist one key per logical mutation and make the server treat repeats as the same request. |
| A downloaded file opens but contains unusable data | Validation checked download completion but not schema, required tables, or metadata. | Validate in a staging path and keep the last known-good mirror when checks fail. |
Platform and version decisions you still need to verify
The OP-SQLite repository lists supported platforms and capabilities, but a repository overview is not a release-specific compatibility matrix. Pin the package version, then read that release’s documentation for supported React Native versions, iOS and Android requirements, native build settings, and database-opening APIs. The project repository is at OP-SQLite on GitHub.
The exact design for a dedicated read-only reader connection, WAL or other journaling mode, concurrent refreshes, and connection lifetime is application- and version-specific. Do not add those settings merely because they appear in an unrelated SQLite example. Verify locking and concurrency behavior with the OP-SQLite and SQLite documentation that matches your selected release.
Production checklist
- The packaged source is never opened as the writable working file.
moveAssetsDatabaseis configured according to the OP-SQLite release documentation, and its idempotent startup behavior is understood.- Downloaded files use a permitted iOS or Android path and a tested fallback.
- Network acquisition has a timeout and cannot prevent local fallback.
- Downloads are staged and validated before activation.
- Every UPSERT conflict target has a stable primary key or unique constraint.
- The
DO UPDATE SETlist contains only server-owned columns. - Freshness, field precedence, and deletion reconciliation are explicit product rules.
- Row batches commit in a transaction and never use a live delete-then-insert gap.
- Offline mutations use a durable outbox and server-enforced idempotency keys.
- Cloud synchronization is added only when the product needs distributed convergence.
- Runtime, platform, journaling, and connection choices are checked against the pinned OP-SQLite version.
The durable pattern is simple even though its policies are not: preserve an immutable source, maintain one writable local mirror, try current data first, fall back locally, and use key-based UPSERTs for row refreshes. That keeps offline reads available and removes the dangerous delete-then-insert interval without pretending SQLite or OP-SQLite can decide your synchronization rules for you.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
