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

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 2

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 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.