SQLite and React Native: Building Offline Mobile Apps

Mobile apps have become an integral part of our daily lives. However, building robust and scalable mobile apps comes with its own set of challenges. One such challenge is managing data storage and retrieval efficiently, especially when the app needs to work offline. This is where SQLite comes into the picture.

SQLite is an open-source, embedded relational database that is self-contained, serverless, zero-configuration, and transactional. Its lightweight nature and native support across platforms like iOS, Android, and Windows make SQLite an ideal choice for mobile developers.

When combined with React Native – a popular JavaScript framework for building natively rendered mobile apps for iOS and Android – SQLite can enable developers to build fast, offline capable, data-driven mobile applications.

In this comprehensive guide, we will learn how to:

• Set up SQLite in a React Native project
• Establish a database connection
• Create tables
• Perform CRUD operations like insert, query, update, and delete data
• Build a sample Todo app with offline capabilities using SQLite

So let’s get started!

Why Choose SQLite for React Native Apps?

Here are some of the key advantages of using SQLite for React Native mobile development:

• Lightweight: SQLite database is stored in a single file in the filesystem, adding practically no overhead or bloat to the app.
• Native Support: It has out-of-the-box support across iOS, Android, and Windows, requiring no complex configurations.
• Robust and Reliable: SQLite offers full transactional guarantees like atomic commits, consistency, isolation, and durability, even in crashes or power failures.
• Offline Capabilities: The lightweight nature allows apps to respond faster than client-server models by eliminating network latency during read/write operations.
• Cross-platform: A single SQLite database file can work seamlessly across iOS, Android, and Windows apps with no code changes.
• Client-side Encryption: SQLite supports client-side encryption for secure data storage on the user’s device.

This combination of efficiency, seamless integration and strong reliability makes SQLite a great fit for building offline capable React Native mobile apps.

Setting Up SQLite with React Native

We will use the react-native-sqlite-storage package to establish SQLite database connection in our React Native project. So let’s get started!

Step 1: Install Dependency

Run the following command to install the SQLite storage library:

npm install react-native-sqlite-storage --save

Or if you use Yarn:

yarn add react-native-sqlite-storage

Step 2: Link Native Code

We need to link the platform-specific code for iOS and Android projects:

react-native link react-native-sqlite-storage

• For React Native >= 0.60, auto-linking will add the required native code.
• For lower versions, you may need to manually link the library by following the installation instructions.

That completes the setup. We are now ready to use SQLite database in our React Native app.

Establishing a Database Connection

We will create a separate Data Access Object (DAO) to manage all database operations. Let’s create a file called DBManager.js.

We first need to import SQLite and enable promise support:

import SQLite from "react-native-sqlite-storage";
SQLite.enablePromise(true);

Next, we will create a database instance and open a connection:

const database_name = "Reactoffline.db";
const database_version = "1.0";
const database_displayname = "SQLite React Offline Database";
const database_size = 200000;

const db = SQLite.openDatabase(
  {
    name: database_name,
    version: database_version, 
    displayName: database_displayname,
    size: database_size,
    location: 'default',
  },
  () => {},
  error => {
    console.log(error);  
  }
);

This creates a database file called Reactoffline.db and opens a connection db that we can use for further CRUD operations.

With the connection setup, our DBManager.js now looks like:

import SQLite from "react-native-sqlite-storage";
SQLite.enablePromise(true);

const database_name = "Reactoffline.db";
// Rest database configuration ...

const db = SQLite.openDatabase(
  {
    name: database_name 
  // ...
  },
  () => {},
  error => {
    console.log(error);
  }  
);

export default db; 

We export the database instance to use in other files of our project.

Creating SQLite Tables

Once our database connection is ready, we can create required tables programmatically.

Let’s build a Todo app with ability to store todo items locally using SQLite.

We will need a todos table with columns like id, title, isCompleted etc. The executeSql() method can execute raw SQL queries.

So we can run a CREATE TABLE query to create the todos table:

// DBManager.js

import SQLite from "react-native-sqlite-storage"; 

const db = SQLite.openDatabase({..});

export const createTable = () => {
  const query =
    "CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY NOT NULL, title TEXT NOT NULL, isCompleted BOOLEAN NOT NULL)";

  return new Promise((resolve, reject) => {  
    db.transaction(tx => {
      tx.executeSql(
        query,
        [], 
        (tx, results) => resolve(results),
        (tx, error) => reject(error)
      ); 
    });
  });
};

We can call this method before performing any CRUD operations to ensure the table exists.

Inserting Data into SQLite Database

To insert records into the todos table, we can again leverage executeSql() and pass an INSERT query with placeholder values.

Let’s add a addTodo() method:

export const addTodo = (title) => {
  const insertQuery =
    "INSERT INTO todos (title, isCompleted) values (?, ?)";
  
  const params = [title, false];
  
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        insertQuery,
        params,
        (tx, result) => resolve(result),
        (tx, error) => reject(error)  
      );
    });
  });
};

A few things to note here:

• We use ? placeholder for values to avoid SQL injection attacks
• Pass the params array containing actual values
• Access result in callback to get inserted record details

Similarly, we can build methods to insert bulk records by iterating over the array of data.

Querying Data from SQLite Database

To fetch existing records from the todos table, we will again use executeSql() along with a SELECT query.

Let’s add a getTodos() method:

export const getTodos = () => {

  const selectQuery = "SELECT * FROM todos";
  
  return new Promise((resolve, reject) => {
    
    db.transaction(tx => {   
      tx.executeSql(
        selectQuery, 
        [],
        (tx, results) => resolve(results),
        (_, error) => reject(error)
      );
    });

  });

};

The results argument in callback contains arrays of rows that we can process as needed.

Updating Data in SQLite Table

To update existing records, we can use the UPDATE query.

Let’s add an updateTodo() method:

export const updateTodo = (id, title, isCompleted) => {

  const updateQuery =
    `UPDATE todos
     set title = ?, isCompleted = ?  
     where id = ?`;
     
  const params = [title, isCompleted, id];
   
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
       tx.executeSql(
        updateQuery, 
        params,
        (tx, results) => resolve(results),
        (_, error) => reject(error)
      );
    });
  });
  
};

Here we construct a parameterized query to update title and completion status for given todo id.

Deleting Records in SQLite

To delete one or more records from the SQLite database, we can use DELETE FROM query.

Let’s add a deleteTodo() method:

export const deleteTodo = (id) => {

  const deleteQuery = `DELETE from todos where id = ?`;
  
  const params = [id];
  
  return new Promise((resolve, reject) => {
    
    db.transaction(tx => {
      tx.executeSql(
        deleteQuery,
        params,
        (tx, results) => resolve(results),
        (_, error) => reject(error)  
      );
    });

  });

};

We simply pass the ID of the record to delete from the todos table.

That covers basic CRUD operations with SQLite!

Now let’s build a Todo app UI and integrate these database methods.

Building a Todo App with Offline Storage

Let’s build the UI workflow for a simple Todo app with React Native:

It has a form to add new todos, the ability to mark them complete or incomplete and delete existing todos.

We will use:

• FlatList to display the list of todo items
• Custom <TodoItem> component to render individual todo row
• Basic form with TextInput and Button to add a new item

For state management, we use React hooks like:

• useState hook to manage the todos array in the state
• useEffect hook to load data on the start

Rendering Todos List

Let’s build the <TodoList> component to display the list of todos:

// TodoList.js

import { FlatList } from "react-native"; 

import TodoItem from "./TodoItem";

const TodoList = ({todos, onToggle, onDelete}) => {

  const renderItem = ({item}) => (
    <TodoItem 
      item={item}
      onToggle={onToggle}
      onDelete={onDelete} 
    />
  );
   
  return (
    <FlatList
      data={todos}
      renderItem={renderItem}
      keyExtractor={item => item.id}
    />
  );
  
}

export default TodoList;

It simply uses FlatList to render <TodoItem> by passing item data.

The <TodoItem> component can display the title, completion status and related action buttons:

// TodoItem.js

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

const TodoItem = ({item, onToggle, onDelete}) => {

  return (
    <View style={styles.itemContainer}>
     <Text>{item.title}</Text>
     
     <Button title="Toggle" onPress={() => onToggle(item.id)}/>

     <Button title="Delete" onPress={() => onDelete(item.id)}/>
     
    </View>
  );

};

const styles = StyleSheet.create({

  itemContainer: {
    backgroundColor: "#fff", 
    padding: 20,
  },

});

export default TodoItem;

It handles onToggle and onDelete events to modify given todo item.

Loading Todos from SQLite

Now let’s integrate our SQLite database methods to load saved todos.

We will call getTodos() inside our App.js using the useEffect() hook:

// App.js

import { useState, useEffect } from "react";
import { 
  View, 
  TextInput,
  Button
} from "react-native";

import TodoList from "./TodoList";
import { getTodos } from "./DBManager";

export default App = () => {

  const [todos, setTodos] = useState([]);
 
  useEffect(() => {

    getTodos().then(results => {
      setTodos(results.rows._array); 
    }).catch(error => {
      console.log(error);  
    });

  }, []); 

  //...
  
};

This fetches records from SQLite and updates todos state on component mount.

Adding New Todos

Let’s add a form to allow adding new todos:

// App.js

export default App = () => {

  const [textInput, setTextInput] = useState('');  

  const addTodo = () => {
    
    // Call SQLite method to insert  
    addTodo(textInput)
      .then(_ => {
        // Refresh todos state  
        getTodos().then(/* ... */); 
      }).catch(/* ... */);

  }
  
  return (
    <View>
     <TextInput 
       value={textInput}
       onChangeText={text => setTextInput(text)} 
     />
     
     <Button title="Add" onPress={addTodo} />
     
    </View>
  )

};

We grab the text input value to pass to addTodo() method on button click.

This will:

1. Insert new record into SQLite table
2. Fetch updated rows into state

And our new todo will render automatically!

Updating Todo Items

Let’s add ability to toggle todo completion status.

We can call the updateTodo() method on toggle:

// TodoList.js

const TodoList = ({todos, onToggle}) => {

  const handleToggle = (id) => {
   
    const item = todos.find(todo => todo.id === id);
    
    updateTodo(id, item.title, !item.isCompleted)
      .then(_ => {
        onToggle(id);  
      }).catch(/* ... */);

  };

  return (
    <FlatList
      data={todos}
      renderItem={({item}) => (  
        <TodoItem 
          item={item}
          onToggle={() => handleToggle(item.id)}
        />
      )}
    />
  );
  
};

This finds the todo item using id, flips the completion status and updates using SQLite method.

We locally flip the status by calling parent’s onToggle() callback. This updates state immmediately instead of waiting for SQLite call.

Deleting SQLite Records

Finally, we can remove todo items by calling deleteTodo():

// TodoList.js

const TodoList = ({todos, onDelete}) => {

  const handleDelete = (id) => {

    deleteTodo(id)
      .then(_ => {
        onDelete(id); 
      }).catch(/* ... */);

  };

  return (
    <FlatList
      data={todos}
      renderItem={({item}) => (
        <TodoItem  
          item={item}
          onDelete={() => handleDelete(item.id)} 
        />
      )}
    />
  );

};

We first delete in database and call parent’s onDelete() to remove from state.

And that’s it! Thanks to the local SQLite storage, our Todo app can now function completely offline.

Here is an improved conclusion for the article:

Conclusion

SQLite enables efficient local data storage and retrieval in mobile apps, while React Native facilitates building cross-platform native interfaces using JavaScript. Combining them lets developers build high-performance mobile apps with offline capabilities.

In this article, we went through a complete guide on using SQLite in React Native:

• Understanding the benefits of SQLite like lightweight footprint, native integration, robustness and offline storage
• Setting up SQLite database connection in React Native project
• Creating database tables programmatically
• Performing fundamental CRUD operations to manage data
• Building a Todo app with offline persistent storage using React hooks

Through the Todo app example, we saw how SQLite database methods can integrate cleanly with managing React state. Features like adding, updating and deleting todos worked flawlessly against the local SQLite store.

The promise-based APIs also made the database operations easy to call and handle programmatically. SQLite ensured pure JavaScript based logic could work smoothly across the underlying native platforms.

So SQLite brings efficient embedded data management, while React Native enables building user interfaces using React paradigms – together they remove technological barriers in building robust mobile apps suitable for modern requirements.

I hope this tutorial helped you get started with building your own offline-capable mobile apps by leveraging SQLite and React Native. Let me know if you have any other questions!