In expo-sqlite, how do you ship a prebuilt database through SQLiteProvider's assetSource, and why might a newer bundled copy never reach existing users?

seniorshould knowfreq 25%

answer

copied, not opened in place
require the .db as an asset
import runs before open and onInit
existing file wins by default
forceOverwrite defaults to false

basics

~20 s

Bundle the .db file and pass assetSource={{ assetId: require('./assets/catalog.db') }}; the provider copies it into the database directory before opening. The copy is skipped when a file with that name exists, because forceOverwrite defaults to false.

solid answer

~40 s

With <SQLiteProvider databaseName="prompts.db" assetSource={{ assetId: require('./assets/prompts.db') }}>, expo-sqlite resolves the asset through expo-asset, copies it into the database directory under databaseName, then opens it and runs onInit. Expo's Metro config already lists db as an asset extension, so the require resolves. The native copy returns early if a file of that name exists and forceOverwrite is false, the default. So version 2 of the app can bundle a new file and existing users keep the old one. Options: give the bundled data a versioned file name and delete the old one with deleteDatabaseAsync; use forceOverwrite: true only for read-only reference data, since it replaces the file on every provider start; or ship migrations and stamp the prebuilt file's user_version.

With `<SQLiteProvider databaseName="prompts.db" assetSource={{ assetId: require('./assets/prompts.db') }}>`, expo-sqlite resolves the asset through `expo-asset`, copies it into the database directory under `databaseName`, then opens it and runs `onInit`. Expo's Metro config already lists `db` as an asset extension, so the `require` resolves. The native copy returns early if a file of that name exists and `forceOverwrite` is false, the default. So version 2 of the app can bundle a new file and existing users keep the old one. Options: give the bundled data a versioned file name and delete the old one with `deleteDatabaseAsync`; use `forceOverwrite: true` only for read-only reference data, since it replaces the file on every provider start; or ship migrations and stamp the prebuilt file's `user_version`.

code

tsx · 13 lines

tsx

import { SQLiteProvider } from 'expo-sqlite';
import type { ReactNode } from 'react';

export function PromptsDatabase({ children }: { children: ReactNode }) {
  return (
    <SQLiteProvider
      databaseName="prompts-v2.db"
      assetSource={{ assetId: require('./assets/prompts-v2.db') }}
    >
      {children}
    </SQLiteProvider>
  );
}

go deeper

for a junior

Recall the prop shape assetSource={{ assetId: require(...) }} and that the file is copied into the database directory before it is opened.

for a middle

Explain the import, open, onInit order and why an existing file blocks the copy while forceOverwrite stays at its default of false.

for a senior

Plan data updates up front: separate reference and user files, versioned names or forceOverwrite for read-only data, and a stamped user_version for migrated data.

for a principal

Decide whether bundled data belongs in the binary at all, or whether downloading it after install keeps the app smaller and updates independent of releases.

When a prebuilt database makes sense

Some apps need data on first launch that is too large or too structured to insert from code: a dictionary, a catalog, or, in a journaling app, a library of a few thousand writing prompts with categories. Building that SQLite file on a development machine and shipping it inside the app is faster and simpler than seeding it row by row on the device.

How assetSource works

SQLiteProvider takes an assetSource prop of type { assetId: number; forceOverwrite?: boolean }, where assetId is the value of require('./assets/prompts.db'). When the provider starts, expo-sqlite 57 does three things in order:

Import. It resolves the asset with expo-asset (Asset.fromModule(assetId).downloadAsync()), then asks the native module to copy that file into the database directory under the provider's databaseName.
Open. It opens the copied file with openDatabaseAsync.
Initialise. It runs onInit, if you passed one.

Points that follow from this:

The bundled file is copied, never opened in place. The app works on a writable copy in its own data directory.
Metro must treat .db as an asset. Expo's Metro config (@expo/metro-config) adds db to the resolver's asset extensions by default. A project on a plain Metro config would have to add it.
The helper the provider calls, importDatabaseFromAssetAsync, is exported but marked hidden in the source ("exposed only for testing purposes"); assetSource is the documented path.

The trap: the existing file wins

The native import, on both Android and iOS, starts with a check: if a file already exists at the database path and forceOverwrite is false, it returns without copying. forceOverwrite defaults to false.

That default is what you want on day one: the user's copy is not replaced on every launch. It becomes a trap on the first data update. Version 2 of the app bundles a richer prompts.db, the provider starts, sees prompts.db already on the device, and skips the copy. New installs get the new data; existing users never do, and nothing reports an error.

Ways to ship updated bundled data

Strategy	How	Good for	Cost
Versioned file name	bundle `prompts-v2.db`, set `databaseName` to match, remove the old file with `deleteDatabaseAsync`	read-only reference data	old file must be cleaned up
`forceOverwrite: true`	recopy the bundled file every time the provider starts	small read-only data	any writes to that file are lost; copy cost on each start
Migrations	stamp the prebuilt file's `user_version`, ship ladder steps for its changes	data the user also edits	you write the migration SQL

The strategies combine well with one principle: keep user-written data and bundled reference data in separate database files. Journal entries live in journal.db, which is migrated and never overwritten; prompts live in a bundled file that can be replaced wholesale. Mixing them forces every data update to become a careful migration of a file holding the user's writing.

Cleaning up after a versioned name

The versioned-name strategy needs a small cleanup routine:

Point databaseName and the require at prompts-v2.db; the import copies it because no file of that name exists yet.
After the provider is ready, call deleteDatabaseAsync('prompts-v1.db'), ignoring the error if it is already gone.
Never delete a file that some other part of the app still has open.

Old versions otherwise accumulate in the app's data directory and count against the user's storage.

Stamping the prebuilt file

If the prebuilt file will also be migrated later, set its PRAGMA user_version when you build it, to the schema version it represents. Otherwise it reads 0 on the device and a ladder in onInit would try to run its create-table steps against tables that already exist. With the right stamp, the ladder runs only the later steps.

Two practical checks:

The bundled file adds to the app's download size, so strip unused indexes and run VACUUM before bundling.
If the file was built with WAL mode, make sure everything is checkpointed into the main file first; only the .db file is bundled.

## When a prebuilt database makes sense Some apps need data on first launch that is too large or too structured to insert from code: a dictionary, a catalog, or, in a journaling app, a library of a few thousand writing prompts with categories. Building that SQLite file on a development machine and shipping it inside the app is faster and simpler than seeding it row by row on the device. ## How assetSource works `SQLiteProvider` takes an `assetSource` prop of type `{ assetId: number; forceOverwrite?: boolean }`, where `assetId` is the value of `require('./assets/prompts.db')`. When the provider starts, expo-sqlite 57 does three things in order: 1. **Import.** It resolves the asset with `expo-asset` (`Asset.fromModule(assetId).downloadAsync()`), then asks the native module to copy that file into the database directory under the provider's `databaseName`. 2. **Open.** It opens the copied file with `openDatabaseAsync`. 3. **Initialise.** It runs `onInit`, if you passed one. Points that follow from this: - The bundled file is **copied, never opened in place**. The app works on a writable copy in its own data directory. - **Metro must treat `.db` as an asset.** Expo's Metro config (`@expo/metro-config`) adds `db` to the resolver's asset extensions by default. A project on a plain Metro config would have to add it. - The helper the provider calls, `importDatabaseFromAssetAsync`, is exported but marked hidden in the source ("exposed only for testing purposes"); `assetSource` is the documented path. ## The trap: the existing file wins The native import, on both Android and iOS, starts with a check: if a file already exists at the database path and `forceOverwrite` is false, it returns without copying. `forceOverwrite` defaults to `false`. That default is what you want on day one: the user's copy is not replaced on every launch. It becomes a trap on the first data update. Version 2 of the app bundles a richer `prompts.db`, the provider starts, sees `prompts.db` already on the device, and skips the copy. New installs get the new data; existing users never do, and nothing reports an error. ## Ways to ship updated bundled data | Strategy | How | Good for | Cost | |---|---|---|---| | Versioned file name | bundle `prompts-v2.db`, set `databaseName` to match, remove the old file with `deleteDatabaseAsync` | read-only reference data | old file must be cleaned up | | `forceOverwrite: true` | recopy the bundled file every time the provider starts | small read-only data | any writes to that file are lost; copy cost on each start | | Migrations | stamp the prebuilt file's `user_version`, ship ladder steps for its changes | data the user also edits | you write the migration SQL | The strategies combine well with one principle: **keep user-written data and bundled reference data in separate database files.** Journal entries live in `journal.db`, which is migrated and never overwritten; prompts live in a bundled file that can be replaced wholesale. Mixing them forces every data update to become a careful migration of a file holding the user's writing. ## Cleaning up after a versioned name The versioned-name strategy needs a small cleanup routine: 1. Point `databaseName` and the `require` at `prompts-v2.db`; the import copies it because no file of that name exists yet. 2. After the provider is ready, call `deleteDatabaseAsync('prompts-v1.db')`, ignoring the error if it is already gone. 3. Never delete a file that some other part of the app still has open. Old versions otherwise accumulate in the app's data directory and count against the user's storage. ## Stamping the prebuilt file If the prebuilt file will also be migrated later, set its `PRAGMA user_version` when you build it, to the schema version it represents. Otherwise it reads 0 on the device and a ladder in `onInit` would try to run its create-table steps against tables that already exist. With the right stamp, the ladder runs only the later steps. Two practical checks: - The bundled file adds to the app's download size, so strip unused indexes and run `VACUUM` before bundling. - If the file was built with WAL mode, make sure everything is checkpointed into the main file first; only the `.db` file is bundled.

Why keep bundled prompts and the user's journal entries in two database files?
The bundled file can then be replaced wholesale with a versioned name or `forceOverwrite` without touching anything the user wrote, while the journal file is only ever changed by migrations. One mixed file would turn every data update into a migration of user data.
What goes wrong if the prebuilt file's user_version is left at 0?
A migration ladder in `onInit` reads 0 and runs its first steps, trying to create tables the prebuilt file already has. Stamp the file with the schema version it represents when you build it, so only later steps run.

saying these in an interview costs you the question

A new .db asset in an app update replaces the user's existing copy
forceOverwrite: true copies the asset only when the bundled file has changed
The bundled database is opened read-only straight from the app bundle
A prebuilt file's user_version does not matter to later migrations

In expo-sqlite, how do you ship a prebuilt database through SQLiteProvider's assetSource, and why might a newer bundled copy never reach existing users?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

When a prebuilt database makes sense

How assetSource works

The trap: the existing file wins

Ways to ship updated bundled data

Cleaning up after a versioned name

Stamping the prebuilt file

▾common follow-ups2 questions

▾red flags4 flags

When a prebuilt database makes sense

How assetSource works

The trap: the existing file wins

Ways to ship updated bundled data

Cleaning up after a versioned name

Stamping the prebuilt file

answer

go deeper