How to Build an Offline Mobile App with React Native, Expo, and SQLite

In this article, we will discuss how to create an expense tracker mobile app using React Native, Expo, and a smartphone's local SQLite database. SQLite is similar to MySQL but is designed for mobile and embedded applications. Every smartphone has a local SQLite database that apps can use. However, locating these databases on your device can be tricky because they are stored within the app's sandbox, and each app has its own sandboxed storage.

In this guide, we will focus on an offline expense tracker that stores your expense data in a local SQLite database. For this example, we have named the database my.db. The app stores information such as the expense ID, day, date, expense amount, and any optional comments related to the expense.

Within the my.db database, we have created two tables: users and todos. The users table stores the id, day, date, and expense amount, while the todos table stores the comments. The two tables are linked using a foreign key, allowing the app to display comments associated with specific expenses, if any have been added.

Code for Creating a Database

import * as SQLite from 'expo-sqlite';

const db = SQLite.openDatabase('mydb.db'); 

export const initDB = () => {
  db.transaction(tx => {
    // Enable foreign key constraints
    tx.executeSql('PRAGMA foreign_keys = ON;');

    // Users table
    tx.executeSql(
      `CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        day TEXT,
        date TEXT,
        amount REAL
      );`
    );

    // Todos table (linked to users)
    tx.executeSql(
      `CREATE TABLE IF NOT EXISTS todos (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        title TEXT,
        description TEXT,
        FOREIGN KEY(user_id) REFERENCES users(id)
      );`
    );
  });
};

export default db;

See how to render the SQLite data

import React, { useEffect, useState } from 'react';
import { View, Text, StyleSheet, ScrollView, Button } from 'react-native';
import * as SQLite from "expo-sqlite";

let db;
(async () => {
  db = await SQLite.openDatabaseAsync("mydb.db");
})();

const getUsersWithTodos = async (callback) => {
  if (!db) return;

  const users = await db.getAllAsync(`SELECT * FROM users;`);
  const todos = await db.getAllAsync(`SELECT * FROM todos;`);

  const usersWithTodos = users.map(user => ({
    ...user,
    todos: todos.filter(t => t.user_id === user.id),
  }));

  const totalAmount = users.reduce((sum, u) => sum + (u.amount || 0), 0);

  callback({ data: usersWithTodos, totalAmount });
};

const currencySymbols = {
  USD: '$',
  GBP: '£',
  EUR: '€',
  JPY: '¥',
  INR: '₹',
};

const UserList = ({ refreshFlag, currency }) => {
  const [usersData, setUsersData] = useState({ data: [], totalAmount: 0 });

  useEffect(() => {
    getUsersWithTodos(setUsersData);
  }, [refreshFlag]);

  const formatCurrency = (amount) =>
    `${currencySymbols[currency] || ''}${amount.toFixed(2)}`;

  const deleteUser = async (id) => {
    if (!db) return;

    await db.runAsync(`DELETE FROM users WHERE id = ?;`, [id]);
    await db.runAsync(`DELETE FROM todos WHERE user_id = ?;`, [id]);

    getUsersWithTodos(setUsersData);
  };

  return (
    
      Expenses
      
        Total Amount: {formatCurrency(usersData.totalAmount)}
      

      {usersData.data.map(user => (
        
          ID: {user.id}
          Day: {user.day}
          Date: {user.date}
          Amount: {formatCurrency(user.amount)}
          {user.todos.length > 0 ? (
            user.todos.map((t, idx) => (
              Comment: {t.title} - {t.description}
            ))
          ) : (
            No Comment
          )}
          
      ))}
    
  );
};

const styles = StyleSheet.create({
  container: { flex: 1, padding: 16, backgroundColor: '#fff' },
  title: { fontSize: 22, fontWeight: 'bold', marginBottom: 8 },
  total: {
    fontSize: 16,
    fontWeight: '600',
    marginBottom: 12,
    color: '#1e3a8a',
  },
  item: {
    backgroundColor: '#f3f4f6',
    padding: 12,
    borderRadius: 8,
    marginBottom: 10,
    borderWidth: 1,
    borderColor: '#d1d5db',
  },
});

export default UserList;
export { getUsersWithTodos, db };

 

How to Render Data from a Local SQLite Database

To retrieve and render data from the local SQLite database (mydb.db), we use simple SQL queries. For example:

const users = await db.getAllAsync(`SELECT * FROM users;`);
const todos = await db.getAllAsync(`SELECT * FROM todos;`);
    

These commands execute SQL SELECT statements to fetch all records from the users and todos tables, respectively.

Visit Github

Further Reading

1.