Getting started with SQLite in React-Native | Creating, Using & Integrating the React Native SQLite Database

React Native apps come with a simple user interface, code reusability, and allows for the production of stable apps. With React-Native being one of the most popular and ideal frameworks for creating cross-platform mobile applications, the vast majority of the developers and engineers depend on the structure of the framework to deliver high-performing local applications.

Because of this, it’s often challenging for developers to choose the right technology stack (including the appropriate database for React Native). In this article, we will be discussing SQLite as a local database for React-Native, and work on how to pre-populate data into the database so as to use it in the application.

React Native SQLite Database

The word “lite” in SQLite describes it as being a lightweight library-based database that requires minimal setup. SQLite can be coordinated with a versatile application, allowing us to get to the database in an easy, straightforward manner. It’s also helpful to note that SQLite was designed to provide local storage to mobile apps.

The two main benefits of using SQLite database are:

  1. It’s ACID-compliant (an acronym for atomicityconsistencyisolation, and durability)
  2. Its offline persistence (which means that it works even when the device is offline)

Also Check:

Creating a Database

First, we need to create a database and store some data in it. SQLite Online can help in creating an SQLite database.

CREATE TABLE table_name (
 column1 datatype,
 column2 datatype,
 column3 datatype,
 ….
);

For example-

CREATE TABLE Users(
Title varchar(20),
Age number
);

Now we need to insert values to this table structure:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

For example:

INSERT INTO Users(Title,Age)
VALUES ("Rohit" , 42);

Now your database is ready and you can download the user.db that you have created. For now, store the file in a temporary location.

How to Use React-native-SQLite-storage?

First, we have to import the library like shown below:

import { openDatabase } from 'react-native-sqlite-storage';

Then, open the database with the help of the below code

var db = openDatabase({ name: 'UserDatabase.db' });

Now, whenever you want to execute some database calls, you can utilize db variable to perform the database

query.db.transaction(function(txn)
{
txn.executeSql(
query, //Query to execute as prepared statement
argsToBePassed[], //Argument to pass for the prepared statement
function(tx, res) {} //Callback function to handle the result
);
});

Integrating the database with react-native

Step 1: After setting up your react native environment install the SQLite package

npm install react-native-sqlite-storage
react-native link

Step 2:

For iOS:

open ./ios/podfile
pod 'react-native-sqlite-storage', :path => '../node_modules/react-native-sqlite-storage'
cd ./ios && pod install

For Android:

Open and modify android/settings.gradle file as follows:

rootProject.name = 'react_native_sqlite_storage_exercise'
...
 include ':react-native-sqlite-storage'
 project(':react-native-sqlite-storage').projectDir = new    
 File(rootProject.projectDir, '../node_modules/react-native-sqlite-          storage/src/android')
...
include ':app'

Modify android/app/build.gradle file as follows:

...   
dependencies {    
implementation fileTree(dir: "libs", include: ["*.jar"])    implementation"com.android.support:appcompatv7:${rootProject.ext.supportLibVersion}"    
implementation "com.facebook.react:react-native:+"  
...    
implementation project(':react-native-sqlite-storage')   \
} 
...

Open and modify MainApplication.java file as below:

...   
import org.pgsqlite.SQLitePluginPackage;   
...   
public class MainApplication extends Application implements ReactApplication { 
...   
...  
@Override
protected List<ReactPackage> getPackages() {
return Arrays.<ReactPackage>asList(
...
new SQLitePluginPackage(),
...
new MainReactPackage()
  );
 }
}

Step 3: Adding the database file to the correct location

For iOS:

Create ios/[project name]/www folder and copy the database on it.

For Android:

Create www folder in the main project directory and copy the database on it.

Step 4: Create a constructor and add these lines of code:

constructor(props) {
    super(props);
    this.state={
      userList:[],
    }
    db = SQLite.openDatabase(
      {
        name: 'user.db',
        createFromLocation : 1,
      },
      this.success.bind(this),  //okCallback
      this.fail                // error callback
    );
    }

The constructor consists of four parts:

  1. The state — which is initially declared as an empty array (this will later be populated with data from the database).
  2. SQLite is imported and openDatabase function is called where the argument it takes is the name of the database and the location of the database.
  3. The success callback function ( which is called if there are no errors).
  4. The error callback function ( which is executed if there is an error).

Step 5:

Now we have to add the success and the fail call back functions:

success=()=>{
      db.transaction(tx => {
        tx.executeSql('SELECT * FROM user', [], (tx, results) => {  // sql query to get all table data and storing it in 'results' variable
          let data = results.rows.length;                          
          let users = [];    //creating empty array to store the rows of the sql table data
  
          for (let i = 0; i < results.rows.length; i++) {
            users.push(results.rows.item(i));                   //looping through each row in the table and storing it as object in the 'users' array
          }
  
           this.setState({ userList:users});         //setting the state(userlist) with users array which has all the table data
        });
      });
      // alert("ok")
    }
  
    fail=(error)=>{ 
      console.error(error) // logging out error if there is one
    }

In the success function, we declare a function db.transaction wherein the first argument we run the SQL query Select * from table_name (this selects all the rows in the SQL database). These rows are stored in a variable called “results”. Now we have to loop through each row of the “results” so that we can store data row by row in the array, thus we use the for loop. Now, after all the rows are pushed to the array, we use setState to update the state value.

In the fail function definition, we console.error the error so that debugging can be easier

Step 6: 

<ScrollView>
        {
           this.state.userList.map(function(item, i){  //map through each element of array(each row of the sql table)
             return(
               <View key={i} style={styles.card}>   //we display the name and age of the user
                <Text>{item.Title}</Text>  
                <Text>{item.age}</Text>   
               </View>
             )
           })
         }    
</ScrollView>

We use Scrollview to make a scrollable view of the list of all items we stored in the database. Now, since the rows are stored as an array of objects in the userList variable, we have to map through each element of the array and print the title and the age of the person.

Run and Test React Native and SQLite Offline Mobile App

In the first step, we have to run the React Native and SQLite app with the help of the below command.

react-native run-android
react-native run-ios

Once the new terminal window opens, simply go to the project folder then run this command.

react-native start

Finally, you will observe the whole application on the Android/iOS Device as shown in below image:

sqlite database run on android device

react native SQLite Offline Android

Conclusion

That’s it! In just six simple steps we have the basic setup of an SQLite database that is rendering its data to the front-end using React-Native. As discussed earlier, React-Native can be an incredibly helpful tool for developers and engineers, but knowing which technology stack is right for the task at hand can be a complicated process.

Leave a Comment