expo sqlite use existing database

Question

According to the expo sqlite documentation for react-native I can initialize a db like so:

const db = SQLite.openDatabase('db.db');

This works and I can update the db like so:

  update() {
    db.transaction(tx => {
      tx.executeSql(
        `select * from items where done = ?;`,
        [this.props.done ? 1 : 0],
        (_, { rows: { _array } }) => this.setState({ items: _array })
      );
    });
  }

From my limited understanding this creates a database in the device. And then it's manipulated keeping all the db local.

I have a database with all the necessary tables already setup. How can I have it use the current database I already have setup?

For example: (not correct syntax)

const db = SQLite.openDatabaseIShipWithApp('mypath/mydb.db');

I couldn't find any documentation to help me with this. The only reason I mention the above is because I already have the db with the tables and data.

Any help would be appreciated!

Answer 1

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.

Answer 2

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});
}

Answer 3

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.

Answer 4

I don't believe this is possible in expo. There is a way to use an existing database if you are using a bare android project which involves writing some native code to copy the database from the project assets to the standard location on the phone (/data/ etc) for your application.

https://medium.com/@johann.pardanaud/ship-an-android-app-with-a-pre-populated-database-cd2b3aa3311f

I have always personally created the database myself with CREATE TABLE IF NOT EXISTS since sqlite requires you to define the schema before you query it. If you need to seed the database, this step would then be followed by additional steps to insert the required data.

In most cases, you will need to also check your reference data and update it from the server at regular intervals (it might even change from publishing your apk to someone downloading the app) and this code would also work when there is no reference data in the database.

There are a couple of services which try and take his hassle away from you (e.g. parse) but you will need to decide if you are happy with them hosting your data.) I haven't used them so not sure how this works exactly but I'm told it tries to solve the offline first type problems.

Remember that in future iterations you may need to modify the structure for future versions (to add fields etc) so you will probably need to define some code that loads when the application is first started, checks the database version and applies any changes that are required to bring the database up to the appropriate level.