Reading a pre-populated sqlite database using react native with expo..

I recently needed to read an existing sqlite database, and return its contents to the react native front-end on an android emulator. I read several related posts on the subject but they all give examples of console logging, never returning. I faced quite some difficulties, and hence decided to create this post as a blog. Hope it helps someone who comes here looking for a solution.

Usually, there are two main issues that you would face. One, you would notice that simply opening a database and fetching won't work. You need to copy the local database to an internal document store and then use that database. Second, you would notice that there is no way of 'returning' the value of  a variable in the traditional sense of the term. You must handle it within the async function itself.

So follow me for the solution. 

Step 1.

Create the root folder. From the terminal in the root folder, create the react native app with expo using :

expo init newApp

Choose the blank template.

This will create the folder newApp in your root folder with starter files for the expo app.

Step 2.

Run the project with yarn android to ensure the basic app is working.

Step 3. 

Create a folder in newApp called src. We will keep all our required components in this folder.

Step 4.

npx expo install expo-asset expo-file-system expo-sqlite @expo/metro-config

Install these libraries since we are going to need them.

Step 5.

In the newApp folder, create a file called metro.config.js and place the following contents in it.

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

const defaultConfig = getDefaultConfig(__dirname);

defaultConfig.resolver.assetExts.push("db");

module.exports = defaultConfig;

The above configuration basically tells the react native engine to include files with '.db' extension in its assets.

Step 6.

I have a pre-populated sqlite3 database called movie.db which I have created using sqliteStudio.exe and have placed it in the src folder. This file contains a table tmovies, and is populated with movie names and hints. You can download sqliteStudio here.

I have about a 1000 records in the table. I will attempt to retrieve one record with a randomly generated rowid. 

Step 7.

Inside newApp, create an src folder. In this folder we will create all our components and also store our database, lets call it 'movie.db'

Step 8.

Lets start b making changes to the App.js. Remove the status bar component. Replace text with contents as below. We will include the DemoComponent from the src folder. We will develop this in the next step. I have posted the final App.js file contents below:

______________________________________________________________________________

import { StyleSheet, Text, View } from 'react-native';

import DemoComponent from './src/DemoComponent';

export default function App() {

 

  return (

    <View style={styles.container}>

      <Text>Reading from existing database demo </Text>

      <DemoComponent />

    </View>

  );

}

const styles = StyleSheet.create({

  container: {

    flex: 1,

    backgroundColor: '#fff',

    alignItems: 'center',

    justifyContent: 'center',

  },

});

_______________________________________________________________________________

Step 9.

Create the DemoComponent.js file in the src folder. The contents should be as below:

_______________________________________________________________________________

import {useState, useEffect} from 'react';

import { View, Text, Button} from "react-native";

import GetMovie from './GetMovieTitle';

export default function DemoComponent () {

  [movieName, setMovieName] = useState("")

  useEffect(() => {

      console.log('useEffect')

      fetchMovie()

      console.log('movieName', movieName)

  },[])

  const fetchMovie = async () => {

    console.log("in fetchMovie");

    return await GetMovie()

            .then (movie => setMovieName(movie))

            .catch (error => console.log('error',error))

  };      

   

  const loadMovie = () => {

    console.log('loadMovie')

    fetchMovie()

    console.log('movieName', movieName)

  }

  return (

    <View>

      <Button title="fetch Movie" onPress={loadMovie}/>

      <Text>{movieName}</Text>

    </View>

  );

};

_______________________________________________________________________________

What it does:

The DemoComponent function will return a view with a button called 'Fetch Movie' and the name of the fetched movie in the next line. It will fetch a random movie from the database on an initial render and also subsequently every time the 'Fetch Movie' button is pressed.

How it works:

We will define a state variable called movieName to hold the movie title. We expect fetchMovie function to populate this state variable. 

Since we want to display a random movie name from the database at initial render, we will use the useEffect hook, in which we will call the async function fetchMovie and confirm that it has populated the useState hook variable movieName.

We also define a loadMovie() function, which will call the fetchMovie function and confirm setting of the movieName state variable. This loadMovie function will be triggered when the button 'Fetch Movie' is pressed.

The fetchMovie function is defined in the GetMovieTitle component. It is an async function. which means that it will return a Promise. So no matter how much you try, you will never be able to 'return' a value to a regular non async calling function in the traditional sense. This function is described in better detail in the later steps of this post.

 

In this function, we await the return of the async GetMovie function (defined in src/GetMovieTitle.js), use the .then clause to populate the movieName state, and a .catch clause to show an error on the console if there is an error. More explanation follows later.

Step 10.

Create the GetMovieTitle.js file in src. Here are the contents.

_______________________________________________________________________________

import * as SQLite from "expo-sqlite";

import { Asset } from "expo-asset";

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

export default async function GetMovie() {

  console.log("open database");

  const db = await openDatabase();

  console.log("db", db);

  const pk = Math.floor(Math.random() * 100) + 1;

  const selectSql = "SELECT movie_title FROM tmovies where rowid = ?";

  console.log("sql", selectSql, pk);

  let movie = await rValue(db, selectSql, pk);

  console.log(movie);

  return movie;

}

const rValue = async (db, selectSql, pk) => {

  return new Promise(function (resolve, reject) {

    db.transaction((tx) => {

      tx.executeSql(

        selectSql,

        [pk],

        (tx, results) => {

          if (results && results.rows && results.rows._array) {

            console.log("all rows returned");

            console.log(results.rows.item(0)['movie_title']);

            resolve(results.rows.item(0)["movie_title"]);

          }

        },

        (tx, error) => {

          console.log(error);

          reject(error); // return error to caller

        }

      );

    });

  });

};

async function openDatabase() {

  if (

    !(await FileSystem.getInfoAsync(FileSystem.documentDirectory + "SQLite"))

      .exists

  ) {

    await FileSystem.makeDirectoryAsync(

      FileSystem.documentDirectory + "SQLite"

    );

  }

  console.log(FileSystem.documentDirectory);

  await FileSystem.downloadAsync(

    Asset.fromModule(require("./movies.db")).uri,

    FileSystem.documentDirectory + "SQLite/myMovies.db"

  );

  return SQLite.openDatabase("myMovies.db");

}

_______________________________________________________________________________

What it does:

2. things:

1. Ensure the database connection object is available. There are 3 steps to this.

    1. Checks if internal location exists. If not, creates it.
    2. Copies the original source database (our database) to this internal location, 
    3. Returns an object that refers to the new file.

2. Based on the select clause and the randomly generated number between 1 and 1000, it fetches a record from the new database and returns it to the calling function.

How it works:

Part 1 - 1. First we need to ensure that the db connection is ready. 

You will take quite a bit of time to realise that simply using SQLite.openDatabase('..location to source file') does not work. Why? Because SQLite.openDatabase tries to find the file in its internal location and creates a new database if it does not find one. So you would spend hours trying to figure out why it throws an error saying table doesn't exist, when your tables are very much alive and kicking. Now, this internal location is defined by FileSystem.documentDirectory.

Once we understand that it requires your pre-populated database to be available in the internal location, we write a custom openDatabase code that will ensure so, and return a connection.

We now must make our original source database to be available in SQLite folder, inside of the FileSystem.documentDirectory. But since there is no provision of moving the physical db file, we must create a copy of our database in the internal location. 

We want to wait until the system checks if the folder SQLite exists in {FileSystem.documentDirectory}. If it doesn't exist, further wait until the system creates it. We do this using the FileSystem.getInfoAsync and FileSystem.makeDirectory Async methods.

Part 1 - 2 - copy source database to internal location.

Now that the folder is ready, we further wait until the system copies the original database (our source database 'movies.db'). We do this using FileSystem.downloadAsync function, which requires two parameters - first, the location of the original source file, which it will pickup using its Asset module, and second, the location (and filename) to which the file must be copied. 

Note that 'require' will not work with variables and requires a hardcoded relative path to the file. In our case, since we have placed the database movies.db in the same src/ folder, we can simply refer to it by './movies.db'. If you place it elsewhere, you would need to give relative path to that location and filename.

Now, note that you can await only within an async function. Hence the custom function openDatabase must be defined as an async function.

Part 1- 3. Return the db connection to the calling function.

Now we use SQLite.openDatabase function to return a db connection to the internally located file with the same (or different) name.

Part 2 - Getting the movie name.

const pk = Math.floor(Math.random() * 100) + 1;
const selectSql = "SELECT movie_title FROM tmovies where rowid = ?";

The Math.random() static method returns a floating-point, pseudo-random number that's greater than or equal to 0 and less than 1, with approximately uniform distribution over that range — which you can then scale to your desired range.Thus, pk gives me a random number between 1 and 1000, which I will provide as rowid to the select clause. selectSql is the select clause.

Now, this is how you actually return data from an async sql transaction function to the calling async function.

let movie = await rValue(db, selectSql, pk);

Note that we will now await a return value from rValue. movie will get populated once rValue returns a value..

Inside of rValue, we are returning a Promise. 

function (resolve, reject) produces a value after an async operation completes successfully, or an error if  it fails due to any reason. Success is indicated by resolve function call, and error is indicated by reject function call.

Thus, the rValue Promise resolves itself to a successful resolved value or a failed reject value. 

This final value (resolved or rejected) wrapped as a promise, is what gets assigned to the 'movies' variable. 

Next, we return the 'movie' variable to the calling function fetchMovie from DemoComponent.

Step 11.

Now we try and understand how fetchMovie() works. Here is a breakdown on its structure and functionality.

  const fetchMovie = async () => {

    console.log("in fetchMovie");

    return await GetMovie()

            .then (movie => setMovieName(movie))

            .catch (error => console.log('error',error))

  };  

The function is defined using the async keyword, indicating that it's an asynchronous function that can use await to pause execution and wait for promises to resolve.

• The return statement is used to return the result of the asynchronous operation. In this case, it returns the result of the GetMovie() function.

• The await keyword is used to pause execution and wait for the GetMovie() function to complete and resolve to a value. The await keyword can only be used inside an async function.

• After the await statement, a then method is chained to the GetMovie() promise. The then method takes a callback function (movie => setMovieName(movie)) that will be executed when the GetMovie() promise is resolved successfully.

• The callback function (movie => setMovieName(movie)) takes the resolved value of the GetMovie() promise (referred to as movie) and sets the state variable movieName. Here is a key note. You cannot send the value of movie back to a calling non-sync function, Hence we are using the .then chaining feature to do whatever procedure we want to do once the promise is successfully resolved. 

• If an error occurs during the promise resolution, the .catch method is chained to handle the error. The catch method takes a callback function (error => console.log('error', error)) that will be executed when the promise is rejected or an error is thrown. It logs the error message to the console. Note that this catch handler only logs the error and does not propagate the error further.

In summary, the fetchMovie function logs a debug message, awaits the completion of the GetMovie() promise, sets the movie name using the resolved value, and logs any errors that occur during the promise resolution.

Note: You can't return a value because the value depends on something that is not available yet when your function is called. That is why you can use callbacks and promises, the function has to return something so it returns a promise of a value that may resolve or reject in the future. The async await syntax suggests that the function waits but in fact it doesn't. It just always returns a promise. It is our job to setup the value when it arrives to a usable form for the App to use.

In our case, we set the useState variable movieName to the value movie, so the value will retain state once the function is over. We use this value to return a JSX to the main calling App.js, where it shows itself.

return (

    <View>

      <Button title="fetch Movie" onPress={loadMovie}/>

      <Text>{movieName}</Text>

    </View>

  );

Hope this post was useful. Feel welcome to post any corrections, more explanations or views iin your comments below.

P.S The source code is available in my github repo