In this guide, we are going to take a look at using SQLite to store and persist data locally in our React Native & Expo applications. SQLite is available on nearly all mobile devices. We have to write SQL queries to access it, however, the data returned is in javascript arrays & objects form. We are going to perform CRUD operations to demonstrate how to perform queries and update React states.

Following topics will be covered in this post and for simplicity and demonstration purpose, all the code will be written in app.js file:

Packages

We are going to use the expo-sqlite package.

expo install expo-sqlite

Imports and Connection

New we can import the package and create our database connection. We openDatabase method to create a connection with the database. If the database doesn’t exists then a new one is created.

import React from 'react';
import { View, Text, TouchableOpacity, ScrollView } from 'react-native';

import * as SQLite from 'expo-sqlite'
const db = SQLite.openDatabase('db.testDb') // returns Database object

Initialization and UI

In this stage we will wire up the App class with essential UI, state and basic database check.

class App extends React.Component {
  constructor(props) {
    super(props)
    this.state = {
      data: null
    }
    // Check if the items table exists if not create it
    db.transaction(tx => {
      tx.executeSql(
        'CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT, count INT)'
      )
    })
    this.fetchData() // ignore it for now
  }
  render() {
    return (
        <View style={Style.main}>
        <Text>Add Random Name with Counts</Text>
        <TouchableOpacity onPress={this.newItem} style={Style.green}>
          <Text style={Style.white}>Add New Item</Text>
        </TouchableOpacity>

        <ScrollView style={Style.widthfull}>
        {
            this.state.data && this.state.data.map(data =>
            (
                <View key={data.id} style={Style.list}>
                <Text >{data.text} - {data.count}</Text>
                <TouchableOpacity onPress={() => this.increment(data.id)}>
                    <Text style={Style.boldGreen}> + </Text>
                </TouchableOpacity>
                <TouchableOpacity onPress={() => this.delete(data.id)}>
                    <Text style={Style.boldRed}> DEL </Text>
                </TouchableOpacity>
                </View>
            )
        )}
        </ScrollView>
      </View >
    )
  }
}
export default App
// Styles are removed purpose-fully 
}

Ok so, we are creating a table named ‘items’ if it doesn’t exists using the db connection we created above. We created a button that is supposed to add some gibberish text and a count of how many times it was pressed to the database. You can see the event handlers like addItem, increment and delete but they haven’t been implemented yet, we’ll implement the CRUD operations in the next section.

Operations

In this section, we are going to take a look at how we can perform CRUD operations on SQLite and update React’s state. We’ll be using Transactions for interacting with the db using the method Database.transaction.

Database.transaction(callback, errorCallback, successCallback)

In every transaction main callback, a Transaction object is passed that can be used to execute queries using the method Transaction.executeSql().

Transaction.executeSql(sqlStatementString, argumentsArray, successCallback, errorCallback)

SuccessCallback returns ResultSets, which contains rowsAffected count, insertIds in case of insert and rows of data in case of read.

Read

The first step is to read the data in the table every time the app is leaded. In the transaction method, we only use the main callback where a transaction object tx is passed. We use it to run a standard SQL read query. Since we don’t need to pass any parameters to the query, we send null in second parameter and then we setState the ResultSet.rows._array as data. We have used the ES6 Object matching feature to extract the _array value from the ResultSet object parameter directly into _array variable.

 fetchData = () => {
    db.transaction(tx => {
      // sending 4 arguments in executeSql
      tx.executeSql('SELECT * FROM items', null, // passing sql query and parameters:null
        // success callback which sends two things Transaction object and ResultSet Object
        (txObj, { rows: { _array } }) => this.setState({ data: _array }) 
        // failure callback which sends two things Transaction object and Error
        (txObj, error) => console.log('Error ', error)
        ) // end executeSQL
    }) // end transaction
  }

Create

We use the same pattern of transaction and executeSql as before to insert new row in the items table. To keeps thing simple, we have hardcoded the insert data for insert and state update.

On successful insert, we use the array concat method to update the state. Concat method creates and returns a new array with the added item. It is very useful for state updates, since we don’t want to alter the state object directly.

In the setState, we also needed the Id of the new item, we accessed it from ResultSet object.

// event handler for new item creation
 newItem = () => {
    db.transaction(tx => {
      tx.executeSql('INSERT INTO items (text, count) values (?, ?)', ['gibberish', 0],
        (txObj, resultSet) => this.setState({ data: this.state.data.concat(
            { id: resultSet.insertId, text: 'gibberish', count: 0 }) }),
        (txObj, error) => console.log('Error', error))
    })
  }

Update

We have a count property on each item and we want to increment it every time the plus button of the object is pressed. The methods we’ll be using will be same as above. However, we have to be careful in updating our React states. We can’t update the state directly so we’ll use the map method here.

The map method creates a new array by perform operation on each element of the original array. Thus in the map, we check to see if the Id of an item is same as the updated item. If so, create a new object and alter its count using ES 6 destructuring syntax, else return the object as it is. Thus a new list is created and we can use it to update the state safely.

  increment = (id) => {
    db.transaction(tx => {
      tx.executeSql('UPDATE items SET count = count + 1 WHERE id = ?', [id],
        (txObj, resultSet) => {
          if (resultSet.rowsAffected > 0) {
            let newList = this.state.data.map(data => {
              if (data.id === id)
                return { ...data, count: data.count + 1 }
              else
                return data
            })
            this.setState({ data: newList })
          }
        })
    })
  }

Delete

We also have a delete button on each item to delete it. Once the delete button is pressed, the query will be executed using the methods same as above but how do we update the React state?

We can use the Arrays filter method to create a new array with filtered items. In the filter method, we can specify which elements to include or exclude by returning true or false. Once this new array is created, we can update the state safely.

 delete = (id) => {
    db.transaction(tx => {
      tx.executeSql('DELETE FROM items WHERE id = ? ', [id],
        (txObj, resultSet) => {
          if (resultSet.rowsAffected > 0) {
            let newList = this.state.data.filter(data => {
              if (data.id === id)
                return false
              else
                return true
            })
            this.setState({ data: newList })
          }
        })
    })
  }