Dilantha Prasanjith I am a Senior Software Engineer at MOQ Digital, currently working as a full-stack developer with React Native, React, and AWS.

Using SQLite with React Native 

6 min read 1837

Build App React Native Sqlite

It’s very common for developers to use SQLite, a C-language library, as the datastore in mobile applications. SQLite is especially useful for offline applications, and many platforms include support for SQLite out of the box, making it straightforward to install.

In this article, we’ll use SQLite in a React Native application to set up a simple to-do list application that will show us how all the CRUD operations are working. We’ll also use TypeScript on account of its advantages like code quality and maintainability.

Prerequisites:

  • Basic understanding of React and React Native
  • Familiarity with TypeScript

Getting started

We’ll create a to-do list application that includes the following:

  • Done button: clear out finished items
  • Add ToDo button: add new items
  • Two useState calls: one for keeping a to-do list and one for tracking new to-do items
  • App component: handles user events like adding and deleting to-do list items
  • Dumb component: shows a to-do list item

Note that we’ll use a set of functional components and several new hook APIs to achieve state management.

Setting up React Native and TypeScript

We’ll start by creating a React Native app using TypeScript:

npx react-native init MyApp --template react-native-template-typescript

You can clone the React application and work along as you read through the article.

You’ll see there are two branches in the repository, start and main. We’ll begin with the start branch.

Introducing SQLite

Let’s introduce SQLite to our application. To connect with SQLite, we are going to use the react-native-sqlite-storagelibrary.

To install SQLite, run the following code in your terminal:

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

Install React Native packages

iOS

If you’re using iOS, run the command below to install the necessary React Native packages:

We made a custom demo for .
No really. Click here to check it out.

cd ios && pod install && cd ..

If you’re running React Native version 0.59 or lower, you have two options to install React Native packages, depending on whether you’re using CocoaPods.

With CocoaPods:

If you’re running CocoaPods, add the code below to your podfile:

pod 'React', :path => '../node_modules/react-native'
pod 'react-native-sqlite-storage', :path => '../node_modules/react-native-sqlite-storage'

Run pod install or pod update.

Without CocoaPods

If you’re not running CocoaPods, you have to use react-native link. If you run into any errors, you’ll have to open the project from Xcode and add dependencies manually. For more details, please refer to the library documentation.

Android

If you’re using your device’s SQLite in React Native .60 or above, you don’t have to take any extra steps.

However, if you’re using SQLite bundled with the react-native-sqlite-storage library, you can add the code below to your react-native.config.js file:

module.exports = {
  ...,
  dependencies: {
    ...,
    "react-native-sqlite-storage": {
      platforms: {
        android: {
          sourceDir:
            "../node_modules/react-native-sqlite-storage/platforms/android-native",
          packageImportPath: "import io.liteglue.SQLitePluginPackage;",
          packageInstance: "new SQLitePluginPackage()"
        }
      }
    }
    ...
  }
  ...
};

If you’re running an older version of React Native, you have to manually update the Gradle files. For full configuration, please refer to the library documentation.

Implement a datastore service

Now, we’re all set to implement a datastore service. We’ll introduce a new .ts file called db-service.ts where we can add all our db operations. First, let’s create a method to get a db connection.

Since we’re using TypeScript, we can install @types/react-native-sqlite-storage to use the included types. If you stick to JavaScript, you don’t have to install this library.

Add the db connection method using the code below:

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

export const getDBConnection = async () => {
  return openDatabase({name: 'todo-data.db', location: 'default'});
};

If a table does not already exist when we start the application, we need to create one. Run the following code to add another method:

export const createTable = async (db: SQLiteDatabase) => {
  // create table if not exists
  const query = `CREATE TABLE IF NOT EXISTS ${tableName}(
        value TEXT NOT NULL
    );`;

  await db.executeSql(query);
};

Since we are using promise-based APIs in the library, it’s important to add the code below to our db-service.ts file:

enablePromise(true);

Next, we’ll add methods to save, delete, and get our to-do items. After adding these methods, our db service file will look like the code block below:

import { enablePromise, openDatabase, SQLiteDatabase } from 'react-native-sqlite-storage';
import { ToDoItem } from '../models';

const tableName = 'todoData';

enablePromise(true);

export const getDBConnection = async () => {
  return openDatabase({ name: 'todo-data.db', location: 'default' });
};

export const createTable = async (db: SQLiteDatabase) => {
  // create table if not exists
  const query = `CREATE TABLE IF NOT EXISTS ${tableName}(
        value TEXT NOT NULL
    );`;

  await db.executeSql(query);
};

export const getTodoItems = async (db: SQLiteDatabase): Promise<ToDoItem[]> => {
  try {
    const todoItems: ToDoItem[] = [];
    const results = await db.executeSql(`SELECT rowid as id,value FROM ${tableName}`);
    results.forEach(result => {
      for (let index = 0; index < result.rows.length; index++) {
        todoItems.push(result.rows.item(index))
      }
    });
    return todoItems;
  } catch (error) {
    console.error(error);
    throw Error('Failed to get todoItems !!!');
  }
};

export const saveTodoItems = async (db: SQLiteDatabase, todoItems: ToDoItem[]) => {
  const insertQuery =
    `INSERT OR REPLACE INTO ${tableName}(rowid, value) values` +
    todoItems.map(i => `(${i.id}, '${i.value}')`).join(',');

  return db.executeSql(insertQuery);
};

export const deleteTodoItem = async (db: SQLiteDatabase, id: number) => {
  const deleteQuery = `DELETE from ${tableName} where rowid = ${id}`;
  await db.executeSql(deleteQuery);
};

export const deleteTable = async (db: SQLiteDatabase) => {
  const query = `drop table ${tableName}`;

  await db.executeSql(query);
};

We’ve added a deleteTable method, which will be useful when we’re developing our application. Later, we’ll add a feature for users that clears all the data using the deleteTable method.

We can use rowid, which comes with SQLite, as the primary key. We’ve updated our to-do items to have an ID and a value instead of a simple string so that we can easily delete items.

Next, we’ll add a model for our ToDoItem type. Add the following code in a file named index.ts in another folder called models in :

export type ToDoItem = {
  id: number;
  value: string;
};

Using the db service

We have to use our db service in App.tsx. Follow these four steps:

1.) Update the ToDoItem component and the App Component to use the new ToDoItem type
2.) Load data from SQLite
3.) Save data to the db
4.) Update deleted items in the db

First, let’s finish setting up our db, then we’ll have a look at the final result of the App.tsx and ToDoItem.tsx files.

Loading data

To load the data in our application, we’ll use the useEffect and useCallback Hooks:

const loadDataCallback = useCallback(async () => {
    try {
      const initTodos = [{ id: 0, value: 'go to shop' }, { id: 1, value: 'eat at least a one healthy foods' }, { id: 2, value: 'Do some exercises' }];
      const db = await getDBConnection();
      await createTable(db);
      const storedTodoItems = await getTodoItems(db);
      if (storedTodoItems.length) {
        setTodos(storedTodoItems);
      } else {
        await saveTodoItems(db, initTodos);
        setTodos(initTodos);
      }
    } catch (error) {
      console.error(error);
    }
  }, []);

  useEffect(() => {
    loadDataCallback();
  }, [loadDataCallback]);

In the code snippet above, we’re reading data from the db. If we have any to-do items stored, we initialize the app with those. If not, we’ll persist initial values to the db and initialize the app using that data.

Adding an item

To add a to-do item, run the following code:

const addTodo = async () => {
    if (!newTodo.trim()) return;
    try {
      const newTodos = [...todos, {
        id: todos.reduce((acc, cur) => {
          if (cur.id > acc.id) return cur;
          return acc;
        }).id + 1, value: newTodo
      }];
      setTodos(newTodos);
      const db = await getDBConnection();
      await saveTodoItems(db, newTodos);
      setNewTodo('');
    } catch (error) {
      console.error(error);
    }
  };

Deleting an item

Lastly, run the code below to delete a to-do item:

const deleteItem = async (id: number) => {
    try {
      const db = await getDBConnection();
      await deleteTodoItem(db, id);
      todos.splice(id, 1);
      setTodos(todos.slice(0));
    } catch (error) {
      console.error(error);
    }
  };

Our final App.tsx file should look like the code below:

/**
 * Sample React Native App
 * https://github.com/facebook/react-native
 *
 * Generated with the TypeScript template
 * https://github.com/react-native-community/react-native-template-typescript
 *
 * @format
 */
import React, { useCallback, useEffect, useState } from 'react';
import {
  Button,
  SafeAreaView,
  ScrollView,
  StatusBar,
  StyleSheet,
  Text,
  TextInput,
  useColorScheme,
  View,
} from 'react-native';
import { ToDoItemComponent } from './components/ToDoItem';
import { ToDoItem } from './models';
import { getDBConnection, getTodoItems, saveTodoItems, createTable, clearTable, deleteTodoItem } from './services/db-service';
const App = () => {
  const isDarkMode = useColorScheme() === 'dark';
  const [todos, setTodos] = useState<ToDoItem[]>([]);
  const [newTodo, setNewTodo] = useState('');
  const loadDataCallback = useCallback(async () => {
    try {
      const initTodos = [{ id: 0, value: 'go to shop' }, { id: 1, value: 'eat at least a one healthy foods' }, { id: 2, value: 'Do some exercises' }];
      const db = await getDBConnection();
      await createTable(db);
      const storedTodoItems = await getTodoItems(db);
      if (storedTodoItems.length) {
        setTodos(storedTodoItems);
      } else {
        await saveTodoItems(db, initTodos);
        setTodos(initTodos);
      }
    } catch (error) {
      console.error(error);
    }
  }, []);
  useEffect(() => {
    loadDataCallback();
  }, [loadDataCallback]);
  const addTodo = async () => {
    if (!newTodo.trim()) return;
    try {
      const newTodos = [...todos, {
        id: todos.length ? todos.reduce((acc, cur) => {
          if (cur.id > acc.id) return cur;
          return acc;
        }).id + 1 : 0, value: newTodo
      }];
      setTodos(newTodos);
      const db = await getDBConnection();
      await saveTodoItems(db, newTodos);
      setNewTodo('');
    } catch (error) {
      console.error(error);
    }
  };
  const deleteItem = async (id: number) => {
    try {
      const db = await getDBConnection();
      await deleteTodoItem(db, id);
      todos.splice(id, 1);
      setTodos(todos.slice(0));
    } catch (error) {
      console.error(error);
    }
  };
  return (
    <SafeAreaView>
      <StatusBar barStyle={isDarkMode ? 'light-content' : 'dark-content'} />
      <ScrollView
        contentInsetAdjustmentBehavior="automatic">
        <View style={[styles.appTitleView]}>
          <Text style={styles.appTitleText}> ToDo Application </Text>
        </View>
        <View>
          {todos.map((todo) => (
            <ToDoItemComponent key={todo.id} todo={todo} deleteItem={deleteItem} />
          ))}
        </View>
        <View style={styles.textInputContainer}>
          <TextInput style={styles.textInput} value={newTodo} onChangeText={text => setNewTodo(text)} />
          <Button
            onPress={addTodo}
            title="Add ToDo"
            color="#841584"
            accessibilityLabel="add todo item"
          />
        </View>
      </ScrollView>
    </SafeAreaView>
  );
};
const styles = StyleSheet.create({
  appTitleView: {
    marginTop: 20,
    justifyContent: 'center',
    flexDirection: 'row',
  },
  appTitleText: {
    fontSize: 24,
    fontWeight: '800'
  },
  textInputContainer: {
    marginTop: 30,
    marginLeft: 20,
    marginRight: 20,
    borderRadius: 10,
    borderColor: 'black',
    borderWidth: 1,
    justifyContent: 'flex-end'
  },
  textInput: {
    borderWidth: 1,
    borderRadius: 5,
    height: 30,
    margin: 10,
    backgroundColor: 'pink'
  },
});
export default App;

Lastly, our final ToDoItem.tsx file should look the following code block:

import React from 'react';
import {
  Button,
  StyleSheet,
  Text,
  View,
} from 'react-native';
import { ToDoItem } from '../models';
export const ToDoItemComponent: React.FC<{
  todo: ToDoItem;
  deleteItem: Function;
}> = ({ todo: {id, value}, deleteItem }) => {
  return (
    <View style={styles.todoContainer}>
      <View style={styles.todoTextContainer}>
        <Text
          style={styles.sectionTitle}>
          {value}
        </Text>
      </View>
      <Button
        onPress={() => deleteItem(id)}
        title="done"
        color="#841584"
        accessibilityLabel="add todo item"
      />
    </View>
  );
};
const styles = StyleSheet.create({
  todoContainer: {
    marginTop: 10,
    paddingHorizontal: 24,
    backgroundColor: 'deepskyblue',
    marginLeft: 20,
    marginRight: 20,
    borderRadius: 10,
    borderColor: 'black',
    borderWidth: 1,
  },
  todoTextContainer: {
    justifyContent: 'center',
    flexDirection: 'row',
  },
  sectionTitle: {
    fontSize: 20,
    fontWeight: '400',
  }
});

There you have it! Our finished React Native to-do application should look like the image below:

React Native Sqlite Final Todo Application

Conclusion

In this tutorial, we’ve learned how to connect a SQLite database with a React Native application, then created our own application using TypeScript.

We used react-native-sqlite-storage, a connector library, and completed the CRUD operation. Then, we combined these operations with our React state updates and considered the difference between simple state management and persisting data.

I hope you enjoyed the article, and don’t forget to comment any thoughts and improvements. Happy coding!

: Full visibility into your web apps

LogRocket is a frontend application monitoring solution that lets you replay problems as if they happened in your own browser. Instead of guessing why errors happen, or asking users for screenshots and log dumps, LogRocket lets you replay the session to quickly understand what went wrong. It works perfectly with any app, regardless of framework, and has plugins to log additional context from Redux, Vuex, and @ngrx/store.

In addition to logging Redux actions and state, LogRocket records console logs, JavaScript errors, stacktraces, network requests/responses with headers + bodies, browser metadata, and custom logs. It also instruments the DOM to record the HTML and CSS on the page, recreating pixel-perfect videos of even the most complex single-page apps.

.
Dilantha Prasanjith I am a Senior Software Engineer at MOQ Digital, currently working as a full-stack developer with React Native, React, and AWS.

Leave a Reply