Impossible to import SQLite db

How to / Third Party Tooling
1

Hi everyone, I’m a newbie to React Native and I want to implement an SQLite database from “/assets/test.db” as you can see on sqlitebrowser: browser

I tried many ways but it never work so I based my code on the most popular way I found : [SOLVED] Import asset DB

These things been sayed, here’s my code:

//App.js 

import React from 'react';
import { StyleSheet, Text, View, Button } from 'react-native';
import * as FileSystem from 'expo-file-system'
import * as SQLite from 'expo-sqlite'
import { Asset } from 'expo-asset'

export default class App extends React.Component {
 constructor(props) {
   super(props)
   this.state = {
     item: null
   }
 }

 componentDidMount() {
   this.makeSQLiteDirAsync()
   FileSystem.downloadAsync(
     Asset.fromModule(require("./assets/test.db")).uri,
     `${FileSystem.documentDirectory}SQLite/test.db`
   )
   .then(function(){
     const db = SQLite.openDatabase('test.db');
     console.log(db);
     db.transaction(tx => {
       tx.executeSql('SELECT * FROM User;',
       [],
       (_, {rows}) => console.log(rows))
     },
     );
   });
 }

 makeSQLiteDirAsync = async() => {
   const dbTest = SQLite.openDatabase('dummy.db');
   try {
     await dbTest.transaction(tx => tx.executeSql(''));
   } catch(e) {
     if (this.state.debugEnabled) console.log('error while executing SQL in dummy DB');
   }
 }

 render() {
   return (
     <View style={styles.container}>
       <Text>Open up App.js to start working on your app!</Text>
     </View>
   );
 }
}

const styles = StyleSheet.create({
 container: {
   flex: 1,
   backgroundColor: '#fff',
   alignItems: 'center',
   justifyContent: 'center',
 },
});

And here’s the result on terminal:

WebSQLDatabase {
  "_currentTask": null,
  "_db": SQLiteDatabase {
    "_closed": false,
    "_name": "test.db",
  },
  "_running": false,
  "_txnQueue": Queue {
    "length": 0,
  },
  "exec": [Function anonymous],
  "version": "1.0",
}

Many peoples also advised to change ‘app.json’ and ‘metro.config.js’ the following way:

//app.json

{
  "expo": {
    "name": "Test: server",
    "slug": "snack-42ed5540-be82-47b4-898a-d9acb300c559",
    "privacy": "public",
    "sdkVersion": "35.0.0",
    "platforms": [
      "ios",
      "android",
      "web"
    ],
    "version": "1.0.0",
    "orientation": "portrait",
    "icon": "./assets/settings.png",
    "splash": {
      "image": "./assets/settings.png",
      "resizeMode": "contain",
      "backgroundColor": "#ffffff"
    },
    "updates": {
      "fallbackToCacheTimeout": 0
    },
    "assetBundlePatterns": [
      "**/*"
    ],
    "ios": {
      "supportsTablet": true
    },
    "packagerOpts": {
      "assetExts": ["db"]
    }
  }
}

//metro.config.js

module.exports = {

    resolver: {
    
    assetExts: ["db", "mp3", "ttf"]
    
    }
    
    }

I didn’t find anything really different on the other topics or forums so there must be something I did wrong but I don’t see anything I can do to manage it. If you have any question or solution I’ll be there !

2

I have tried the same thing with no success.
I can create a non-existing db without issue but when trying to bundle a pre-populated db file, it does not work.

1 Like
3

has anyone ever had any success with this?

4

I gave this a try last night and today with partial success.

SDK 39

dependencies:

   "dependencies": {
+    "@expo/metro-config": "^0.1.36",
     "expo": "~39.0.2",
+    "expo-asset": "~8.2.0",
+    "expo-file-system": "~9.2.0",
+    "expo-sqlite": "~8.4.0",
     "expo-status-bar": "~1.0.2",
     "react": "16.13.1",
     "react-dom": "16.13.1",

metro.config.js

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

const defaultConfig = getDefaultConfig(__dirname);

module.exports = {
  resolver: {
    assetExts: [...defaultConfig.resolver.assetExts, 'db'],
  },
};

Getting the local URI of the database:

  Asset.loadAsync(require('./assets/test.db'))
    .then(data => {
      console.log('Loaded asset', data);
      setUri(data[0].localUri);
    })
    .catch(error => {
      console.log('Error loading asset');
    });

Instead of trying to use FileSystem.downloadAsync() like some of the older answers, I used FileSystem.copyAsync(). I also tried FileSystem.moveAsync(), but that does not seem to be a good idea.

Something along the lines of:

      const destUri = `${FileSystem.documentDirectory}SQLite/test.db`;

      FileSystem.copyAsync({ from: uri, to: destUri })
      [...]
      const db = SQLite.openDatabase('test.db');

      db.transaction(
        tx => {
          tx.executeSql(
            'SELECT id, name, value FROM colours;',
            [],
            (tx, rs) => {
              console.log('SQL statement succeeded', JSON.stringify(rs));
              setData(rs.rows._array);
            },
            (tx, error) => {
              console.log('SQL statement failed', JSON.stringify(error));
            }
          );
        },
        error => {
          console.log('ERROR', JSON.stringify(error));
        },
        () => {
          console.log('Transaction succeeded');
        }
      );

I ran into problems in development trying to update the database because it seemed to be cached somehow. I even tried using FileSystem.deleteAsync() to delete the copied database, but that did not help. I had to clear the Expo app’s data on my phone before it would “see” a modified database.

Then I tried publishing the app, but no data was loaded. Would probably have to add something like Sentry or some alert() calls to figure out what’s going on there.

I hope the above helps someone else get further with this.

5

I was able to make it work like this. I haven’t tested it past the development phase (not published).

import * as FileSystem from "expo-file-system";
import * as SQLite from "expo-sqlite";

import { Asset } from "expo-asset";

export const openDatabase = async () => {
  if (!(await FileSystem.getInfoAsync(FileSystem.documentDirectory + "SQLite")).exists) {
    await FileSystem.makeDirectoryAsync(FileSystem.documentDirectory + "SQLite");
  }
  const [{ uri }] = await Asset.loadAsync(require("./assets/db/store.db"));
  await FileSystem.downloadAsync(uri, FileSystem.documentDirectory + "SQLite/store.db");
  return SQLite.openDatabase("//store.db");
};

The use of the SQLite directory is very important since it is defaulting to this path in openDatabase().