Integrating SQLite with React Native: A Beginner's Tutorial

Learn how to seamlessly integrate SQLite into your React Native application, ensuring efficient and persistent data storage. This approach…

Julien C.

~10 min read · November 12, 2023 (Updated: November 13, 2023) · Free: Yes

Learn how to seamlessly integrate SQLite into your React Native application, ensuring efficient and persistent data storage. This approach is perfect for offline-first mobile applications or projects without a backend.

SQLite is widely used to store data persistently in web and mobile applications. It's lightweight, serverless and requires no configuration! That's the reason why so many developers choose to use it.

If you want to store user preferences of simple data structures in your React Native app, it can be relevant to use SQLite. No server setup or extensive documentation needed — this article is a straight-to-the-point tutorial.

Here's a quick overview of things you'll learn today:

Install and configure your SQLite database
Create tables and / or delete them
Read and update tables

Install and configure SQLite for React Native

To make it easier, we'll use react-native-sqlite-storage, a great library that provides all the methods we need to store data with SQLite database engine.

You can install this package with one of the following commands:

npm install --save react-native-sqlite-storage (with npm)
yarn add react-native-sqlite-storage (with yarn)

There are additional steps to finish configuring this external library, but they depend on your version of React Native. Open your package.json file and look for the version you're using.

React Native version 0.60 or higher

Use the following command at the root of the repository to configure the library for iOS as well: cd ios && pod install && cd .. . Nothing else is required!

React Native version below 0.60

For older versions, both iOS and Android projects need to be configured correctly in order to use the package. Look at the official documentation to know what you really need to know according to your application requirements.

Open the SQLite database and create tables

Now that react-native-sqlite-storage is installed in your project, you're ready to create your first tables.

In this tutorial, we will create 2 distinct tables.

The first, named UserPreferences, will hold a single record. This record will be updated whenever there's a change in the user preferences, ensuring that no new entries are added.
The second, named Contacts , will be used to insert or delete contacts in our app. Each contact contains several details like first name, name and phone number.

Knowing this, you can go to the root of your project, then go to app folder, and create another folder called db . It's going to be the folder which will contain all our methods to create, read, edit and delete data.

If you intend to create a lot of tables, it would be a good idea to create a file for each table. For example, in our case, we can create a file called userPreferences.ts and another one called contacts.ts in db folder. A last one, db.ts , will be used for general actions.

Get access to the database

Before creating tables, you need to establish a connection with the SQLite database stored on the device.

To do so, you can import the library we previously installed and use the following function:

// app/db/db.ts

import {
  enablePromise,
  openDatabase,
} from "react-native-sqlite-storage"

// Enable promise for SQLite
enablePromise(true)

export const connectToDatabase = async () => {
  return openDatabase(
    { name: "yourProjectName.db", location: "default" },
    () => {},
    (error) => {
      console.error(error)
      throw Error("Could not connect to database")
    }
  )
}

Before carrying out any operations, it's necessary to connect to the database using the function mentioned earlier. This allows you to save the return value in a variable, for example: const db = await connectToDatabase().

This variable, will be given as an argument to other functions we will create in this article.

NB: you can edit the project name, the location, the success callback (() => {}) and the error callback according to your needs.

Create data tables

The SQLite statement I recommend to create tables is CREATE TABLE IF NOT EXISTS . It's the best way to avoid any error because it checks if the table already exists before creating it.

Do you remember our function connectToDatabase ? It returns a SQLiteDatabase object which contains executeSql method. This is what we need to execute SQLite commands! Here is the function you can create to initialize your first tables:

// app/db/db.ts

export const createTables = async (db: SQLiteDatabase) => {
  const userPreferencesQuery = `
    CREATE TABLE IF NOT EXISTS UserPreferences (
        id INTEGER DEFAULT 1,
        colorPreference TEXT,
        languagePreference TEXT,
        PRIMARY KEY(id)
    )
  `
  const contactsQuery = `
   CREATE TABLE IF NOT EXISTS Contacts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      firstName TEXT,
      name TEXT,
      phoneNumber TEXT
   )
  `
  try {
    await db.executeSql(userPreferencesQuery)
    await db.executeSql(contactsQuery)
  } catch (error) {
    console.error(error)
    throw Error(`Failed to create tables`)
  }
}

As tables are created only once, createTables function can be used within a useEffect in App.tsx or App.js . In fact, it should be part of the app's initialization process and should be called as soon as possible in the React Native code.

You can do something like this:

// App.tsx or App.js

const loadData = useCallback(async () => {
  try {
    const db = await connectToDatabase()
    await createTables(db)
  } catch (error) {
    console.error(error)
  }
}, [])

useEffect(() => {
  loadData()
}, [loadData])

Display and delete tables

When testing your mobile application, it's essential to implement two more functions: one to delete tables and another to view the tables currently present in the SQLite database.

// app/db/db.ts

export const getTableNames = async (db: SQLiteDatabase): Promise<string[]> => {
  try {
    const tableNames: string[] = []
    const results = await db.executeSql(
      "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"
    )
    results?.forEach((result) => {
      for (let index = 0; index < result.rows.length; index++) {
        tableNames.push(result.rows.item(index).name)
      }
    })
    return tableNames
  } catch (error) {
    console.error(error)
    throw Error("Failed to get table names from database")
  }
}

export const removeTable = async (db: SQLiteDatabase, tableName: Table) => {
  const query = `DROP TABLE IF EXISTS ${tableName}`
  try {
    await db.executeSql(query)
  } catch (error) {
    console.error(error)
    throw Error(`Failed to drop table ${tableName}`)
  }
}

You certainly noticed that I used Table type instead of string in removeTable function. This is a type containing all the table names you're using in your app. It is important to specify it instead of string because otherwise it can lead to potential security issues.

In our case, Table looks like this: type Table = "Contacts" | "UserPreferences" . You can create your types in a .typing.ts file and export every type you create to reuse them later.

CRUD operations with SQLite in React Native

Now that our tables have been created, we are ready to handle Create, Read, Update and Delete operations and dive into the most interesting part of this tutorial.

Insert data into a table

The first thing you want to do when you have created your tables is adding data into it, right? Well, we can use SQLite INSERT INTO for that. For the Contacts table, is pretty straight-forward:

// app/db/contacts.ts

export const addContact = async (db: SQLiteDatabase, contact: Contact) => {
  const insertQuery = `
   INSERT INTO Contacts (firstName, name, phoneNumber)
   VALUES (?, ?, ?)
 `
  const values = [
    contact.firstName,
    contact.name,
    contact.phoneNumber,
  ]
  try {
    return db.executeSql(insertQuery, values)
  } catch (error) {
    console.error(error)
    throw Error("Failed to add contact")
  }
}

If you're not familiar with SQLite command syntax, it's important to know that ? placeholders are replaced by the values specified in the values array. The order of these values needs to be in accordance with the query, as they are taken exactly as provided.

The query creates a new row in the Contacts database, containing all the information of your new contact. It is very simple and this approach works for all array-like tables.

However, we also have the take care of our userPreferences single-row table. What we want to do is inserting a new row containing the user preferences if it doesn't exist, otherwise update the first row.

Since 2018, SQLite provides INSERT...ON CONCLICT which is perfect to perform some actions when there is already a row containing user preferences. This command is available since SQLite version 3.24.0 and it will be suitable for most modern iOS and Android devices.

In the following function, were are updating a single user preference, whether the theme color or the language. We are targeting the id = 1 because this is the primary key for our user preferences table row.

It first tries to insert into UserPreferences table a new row with id = 1 and the new user preference you're trying to add. If there is no row with id = 1 , there is no conflict and the new data is inserted, otherwise it just updates the row with the new value.

NB: the conflict is triggered when we try to insert another row with the same id as an existing one, because the table uses id as the primary key. It means that each row should have a unique id .

// app/db/userPreferences.ts

type SingleUserPreference = "colorPreference" | "languagePreference"

// This function works for both inserting and updating one user preference
// It can be color preference or language preference
export const updateSingleUserPreference = async (
  db: SQLiteDatabase,
  singleUserPreference: SingleUserPreference,
  newValue: string
) => {
  const query = `
      INSERT INTO UserPreferences (id, ${singleUserPreference})
      VALUES (1, ?)
      ON CONFLICT(id) DO UPDATE SET ${singleUserPreference} = ?
  `
  try {
    return db.executeSql(query, [newValue, newValue])
  } catch (error) {
    console.error(error)
    throw Error(`Failed to update ${singleUserPreference}`)
  }
}

Read data from the database

Now that you can populate your tables with data, the next question is: How do you access this data? In a React Native app, reading from an SQLite database is accomplished using the SELECT keyword.

To get all rows of a table, you can use SELECT * FROM Table . That is what we're going to use to get the full array of contacts in our app.

// app/db/contacts.ts

export const getContacts = async (db: SQLiteDatabase): Promise<Contact[]> => {
  try {
    const contacts: Contact[] = []
    const results = await db.executeSql("SELECT * FROM Contacts")
    results?.forEach((result) => {
      for (let index = 0; index < result.rows.length; index++) {
        contacts.push(result.rows.item(index))
      }
    })
    return contacts
  } catch (error) {
    console.error(error)
    throw Error("Failed to get Contacts from database")
  }
}

As demonstrated, we store each element of each row, which represents an individual contact, in an array. This allows us to return a TypeScript variable that can be utilized in our React Native app for displaying contact information.

For user preferences, the process is nearly identical, but it's important to specify the id (which should be set to 1) to ensure accuracy and avoid any mistakes.

// app/db/userPreferences.ts

export const getUserPreferences = async (
  db: SQLiteDatabase,
) => {
  const query = `SELECT * FROM UserPreferences WHERE id = 1`
  try {
    const results = await db.executeSql(query)
    if (results[0]?.rows?.length) {
      return results[0].rows.item(0)
    } else {
      return null
    }
  } catch (error) {
    console.error(error)
    throw Error("Failed to get user preferences from database")
  }
}

// Here is another version if you need to retrieve only one user preference.

export const getSingleUserPreference = async (
  db: SQLiteDatabase,
  userPreference: SingleUserPreference
): Promise<string | null> => {
  const query = `SELECT ${userPreference} FROM UserPreferences WHERE id = 1`
  try {
    const results = await db.executeSql(query)
    if (results[0]?.rows?.length) {
      return results[0].rows.item(0)[userPreference]
    } else {
      return null
    }
  } catch (error) {
    console.error(error)
    throw Error(`Failed to get ${userPreference} from database`)
  }
}

Keep in mind that each row can contain multiple columns. That's why we use item(0) in the functions mentioned earlier.

Update rows in SQLite tables

Now that you know how to store and access data in your React Native app, you may need to update it as well. Good news, UPDATE command does the job perfectly.

Returning to our examples of the phone contact list and user preferences, when you need to update a specific contact, it's necessary to instruct SQLite which variable should be used to identify and access the contact's information.

A common method for pinpointing the data to be edited is by using the WHERE keyword in conjunction with the primary key of the table. For instance, in our contact table, each row has a unique id. We can then use WHERE id = ? .

// app/db/contacts.ts

export const updateContact = async (
  db: SQLiteDatabase,
  updatedContact: Contact
) => {
  const updateQuery = `
    UPDATE Contacts
    SET firstName = ?, name = ?, phoneNumber = ?
    WHERE id = ?
  `
  const values = [
    updatedContact.firstName,
    updatedContact.name,
    updatedContact.phoneNumber,
    updatedContact.id,
  ]
  try {
    return db.executeSql(updateQuery, values)
  } catch (error) {
    console.error(error)
    throw Error("Failed to update contact")
  }
}

You might have noticed that the function used for inserting a user preference is named updateSingleUserPreference. This name was chosen because the function is designed not only to insert the row initially but also to update it when it already exists. Consequently, there's no need to create an additional function for this purpose.

Delete persistent data in the database

The last operation you need to learn when you use SQLite with React Native is deleting. Again, there is a DML command for this purpose, and its name is DELETE .

Similar to the processes for reading and updating, when deleting data, you need to provide a specific condition for the database to match in order to delete the correct table row.

In the case of a contact list, this could be the phone number or any other relevant information that serves as a unique key.

// app/db/contacts.ts

export const deleteContact = async (db: SQLiteDatabase, contact: Contact) => {
  const deleteQuery = `
    DELETE FROM Contacts
    WHERE id = ?
  `
  const values = [contact.id]
  try {
    return db.executeSql(deleteQuery, values)
  } catch (error) {
    console.error(error)
    throw Error("Failed to remove contact")
  }
}

It does not make sense to remove the user preferences, as we always need to know the theme color to display, or the language to use in the app. That's why I only show you how to delete a contact in the Contacts table.

That's all you need to know to get started with SQLite data manipulation in React Native, and this tutorial will be enough for most cases. If you need help with a specific case, feel free to leave a comment!

Did you like this article? You can let me know by different ways:

Clap the article.
Comment your thoughts on my story.
Subscribe to my account.
Follow me on GitHub.

You can also subscribe to my newsletter to receive my latest posts in your mailbox.

#react-native #mobile-development #sqlite #app-development #react-native-development