React Native Expo SQLite local storage tutorial

In this post, we’ll look at how to use SQLite to store and persist data locally in our React Native and Expo apps. SQLite is supported by practically all mobile devices. To access it, we must execute SQL queries; however, the data provided is in the form of javascript arrays and objects. To show how to run queries and change React states, we will do CRUD operations. The following aspects will be discussed in this post, and all code will be written in the app.js file for convenience and demonstration purposes:

Packages
Imports and Connection
Initialization and UI
Operations
Read
Create
Update
Delete

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

import Constants from 'expo-constants';
import * as SQLite from 'expo-sqlite';

const db = SQLite.openDatabase('db.testDb'); // returns Database object

class App extends React.Component {
  constructor(props) {
    super(props);
    this.state = {
      data: null,
    };

    if (Platform.OS != 'web') {
      // 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
    }
  }
  // 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)
      );
    });
  };
  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 = (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 });
          }
        }
      );
    });
  };
  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
  };

  render() {
    return (
      <SafeAreaView style={Style.main}>
        <Text style={Style.heading}>Add Random Name with Counts</Text>

        {Platform.OS === 'web' ? (
          <View
            style={{ flex: 1, justifyContent: 'center', alignItems: 'center' }}>
            <Text style={Style.heading}>
              Expo SQlite is not supported on web!
            </Text>
          </View>
        ) : (
          <>
            <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>
                    <View style={Style.list}>
                      <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>
                  </View>
                ))}
            </ScrollView>
          </>
        )}
      </SafeAreaView>
    );
  }
}
export default App;

const Style = StyleSheet.create({
  main: {
    backgroundColor: '#fff',
    flex: 1,
    paddingTop: Constants.statusBarHeight,
    padding: 20,
  },

  white: {
    color: 'white',
  },
  green: {
    backgroundColor: 'green',
    padding: 10,
  },
  boldGreen: {
    backgroundColor: 'green',
    padding: 10,
    borderRadius: 10,
    color: 'white',
    marginRight: 10,
  },
  boldRed: {
    backgroundColor: 'red',
    padding: 10,
    borderRadius: 10,
    color: 'white',
  },
  heading: {
    fontSize: 20,
    fontWeight: 'bold',
    textAlign: 'center',
    paddingVertical: 40,
  },
  list: {
    flexDirection: 'row',
    justifyContent: 'space-between',
    alignItems: 'center',
    borderBottomWidth: 1,
    borderBottomColor: '#ccc',
  },
  widthfull: {
    flex: 1,
  },
});

Snack link

https://snack.expo.dev/@rudiahmad/sqlite-example

Note: testing cannot use web snacks, run the Expo application to do the testing

Reference:

The coding example is taken from the reference below with the improvement of the code so that it runs and added styles because styles are not shared from the original code

https://reactdevstation.github.io/2020/04/04/sqllite.html