expo sqlite use existing database

It's pretty straight forward If you bundle your app, you have to move the Database from the asset folder to the document directory first. In order to do that, check if a folder named SQLite exists. If not, create it. Why do you need a folder called SQLite? That is because SQLite.openDatabase(databaseName) looks per default in FileSystem.documentDirectory + 'SQLite'. Then, when the folder is created, you can download the database from the asset folder. Make sure you have your database in a folder called asset. Locate the foler asset under src/asset of your app document tree. Also, make sure to configure your app.json and metro.config.js.

import * as SQLite from 'expo-sqlite';
import * as FileSystem from 'expo-file-system';
import { Asset } from 'expo-asset';

const FOO = 'foo.db'

if (!(await FileSystem.getInfoAsync(FileSystem.documentDirectory + 'SQLite')).exists) {
  await FileSystem.makeDirectoryAsync(FileSystem.documentDirectory + 'SQLite');
};

await FileSystem.downloadAsync(
    // the name 'foo.db' is hardcoded because it is used with require()
    Asset.fromModule(require('../../asset/foo.db')).uri,
    // use constant FOO constant to access 'foo.db' whereever possible
    FileSystem.documentDirectory + `SQLite/${FOO}`
);

// Then you can use the database like this
SQLite.openDatabase(FOO).transaction(...);


// app.json
{
  "name": "Your App name",
  "displayName": "Your App name",
  "assetBundlePatterns": [
    "assets/**"
  ],
  "packagerOpts": {
    "assetExts": ["db"]
  }
}

// metro config
const { getDefaultConfig } = require('@expo/metro-config');
const defaultConfig = getDefaultConfig(__dirname);

module.exports = {
  resolver: {
    assetExts: [...defaultConfig.resolver.assetExts, 'db', 'json'],
  },
  transformer: {
    getTransformOptions: async () => ({
      transform: {
        experimentalImportSupport: false,
        inlineRequires: false,
      },
    }),
  },
};

This is all extracted from the documentation of expo.

I was able to achieve this by using expo's FileSystem.downloadAsync:

first I import it since I'm using expo managed app:

import { FileSystem } from 'expo';

Then I download it from a server like so:

// load DB for expo
FileSystem.downloadAsync(
  'http://example.com/downloads/data.sqlite',
  FileSystem.documentDirectory + 'data.sqlite'
)
.then(({ uri }) => {
  console.log('Finished downloading to ', uri)
})
.catch(error => {
  console.error(error);
})

The first parameter is the uri for the location, the second one is where I'd like to place it. Here I am using documentDirectory.

If using local prepopulated database in assets:

import * as FileSystem from "expo-file-system";
import {Asset} from "expo-asset";

async function openDatabaseIShipWithApp() {
    const internalDbName = "dbInStorage.sqlite"; // Call whatever you want
    const sqlDir = FileSystem.documentDirectory + "SQLite/";
    if (!(await FileSystem.getInfoAsync(sqlDir + internalDbName)).exists) {
        await FileSystem.makeDirectoryAsync(sqlDir, {intermediates: true});
        const asset = Asset.fromModule(require("../assets/database/mydb.sqlite"));
        await FileSystem.downloadAsync(asset.uri, sqlDir + internalDbName);
    }
    this.database = SQLite.openDatabase(internalDbName);
}

This creates the SQLite directory and database if not exists. Otherwise FileSystem.downloadAsync() will throw an error on fresh installed app.

Some remarks:

• You cannot use variable in require() (only string). See e.g. this.

• You have to explicitly allow file extension .db or .sqlite to be loadable in Expo, see this. You have to create a file metro.config.js in root:

const defaultAssetExts = require("metro-config/src/defaults/defaults").assetExts;

module.exports = {
    resolver: {
        assetExts: [
            ...defaultAssetExts,
            "db", "sqlite"
        ]
    }
};

• And may add following to app.json

"expo": {
  "assetBundlePatterns": [
    "**/*"
  ]
}

• If want to delete loaded database (e.g. for testing) you have to clear whole Expo App data in Phone settings (deleting cache not sufficient). Or write a method like this:

async function removeDatabase() {
    const sqlDir = FileSystem.documentDirectory + "SQLite/";
    await FileSystem.deleteAsync(sqlDir + "dbInStorage.sqlite", {idempotent: true});
}