Working with SQLite Databases in React Native

Course: React Native Training

Topic: Working with SQLite Databases

Course Link: React Native Training at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to SQLite Databases

SQLite is a lightweight, relational database engine that stores data in a database file. It is widely used in mobile applications because it can store and manage structured data locally without requiring a separate database server.

In React Native, SQLite can help applications store information such as notes, tasks, offline records, shopping lists, transaction histories, and cached content. The saved data can remain available after the application closes and opens again.

Unlike simple key-value storage, SQLite organizes information into tables containing rows and columns. Developers can use SQL (Structured Query Language) to insert, retrieve, update, delete, filter, sort, and relate records.

Examples of SQLite Usage

• A notes application that saves notes locally.
• A task manager that stores tasks and completion status.
• A shopping application that maintains a local cart.
• An expense tracker that stores transactions.
• An offline-first application that stores downloaded records.
• An educational application that saves lessons and learning progress.

2. Learning Objectives

After studying this topic, learners should be able to:

• Understand SQLite and relational database concepts.
• Identify situations where SQLite is suitable for React Native applications.
• Install and configure a SQLite library.
• Open and initialize a local database.
• Create tables and define columns, primary keys, and constraints.
• Perform CRUD operations using SQL queries.
• Use parameterized queries to handle user input safely.
• Display database records in React Native components.
• Handle asynchronous database operations and errors.
• Understand transactions, indexes, relationships, and database migrations.
• Build a practical offline notes application.

3. What Is a Relational Database?

A relational database stores information in tables. Each table represents a particular type of information, and each row represents one record.

For example, a task management application might have a tasks table containing the task ID, title, description, completion status, and creation date.

Example of a Tasks Table

In this example, each row represents one task. The id identifies the record, while the completed column represents whether the task has been completed.

4. Why Use SQLite in React Native?

Many mobile applications need to preserve structured data even when an internet connection is unavailable. SQLite provides a local database that supports structured queries and relationships.

• Offline access: Users can read and modify locally stored records without an internet connection.
• Persistent data: Records generally remain available after application restarts.
• Structured queries: SQL supports filtering, sorting, grouping, and joining data.
• Relational data: Tables can reference related records through foreign keys.
• Transactions: Multiple database changes can be committed together or rolled back.
• Efficient searching: Indexes can improve queries on frequently searched columns.
• Local processing: Many database operations can happen directly on the device.
• No separate database server: SQLite runs within the application environment.

5. SQLite vs AsyncStorage vs Remote Databases

Different storage systems solve different problems. Select a solution based on the structure of your data, security requirements, offline needs, and synchronization requirements.

SQLite and a remote database can be used together. The mobile application can keep a local copy of important records and synchronize changes with a backend when connectivity is available.

6. Choosing a SQLite Library

React Native does not provide a single universal JavaScript SQLite API for every project setup. Developers usually select a library that fits their framework, React Native version, and native build configuration.

Expo SQLite

expo-sqlite provides access to a SQLite database in Expo projects and can also be used in appropriately configured React Native projects. Its modern asynchronous API includes methods such as openDatabaseAsync(), runAsync(), getFirstAsync(), and getAllAsync().

Installing Expo SQLite

npx expo install expo-sqlite

Use the installation instructions that match your Expo SDK. For a bare React Native project, ensure the Expo package is configured and complete any required native setup.

Official documentation: Expo SQLite API Documentation.

Other SQLite Libraries

Other community libraries may be suitable for bare React Native applications. Check their current maintenance status, compatibility, native installation instructions, and API documentation before selecting one.

7. Understanding the SQLite Workflow

A typical React Native SQLite implementation follows these steps:

1. Install a compatible SQLite library.
2. Open a database file.
3. Create tables if they do not already exist.
4. Insert or retrieve records through SQL queries.
5. Store retrieved results in React state.
6. Display the results using React Native components.
7. Update or delete records as required.
8. Handle errors, schema changes, and data migrations.

8. Importing SQLite and Opening a Database

With Expo SQLite, import the module and open a database using its asynchronous API.

import * as SQLite from 'expo-sqlite';

const db = await SQLite.openDatabaseAsync('notes.db');

The file name notes.db identifies the database. The library opens or creates the database in its configured location. The database is generally persistent across application restarts.

Database initialization should happen before queries that depend on the tables. In a real application, place initialization in a database service or use SQLiteProvider to share an initialized database with child components.

9. Creating a Database Table

A table defines the structure of the data to be stored. Use CREATE TABLE IF NOT EXISTS to create a table only when it does not already exist.

import * as SQLite from 'expo-sqlite';

const db = await SQLite.openDatabaseAsync('notes.db');

await db.execAsync(`
  CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TEXT NOT NULL
  );
`);

Understanding the Columns

PRIMARY KEY identifies a record uniquely. AUTOINCREMENT asks SQLite to generate increasing integer identifiers. NOT NULL prevents a column from containing a SQL NULL value.

10. Initializing the Database Once

Database setup should be repeatable and safe. A common approach is to create a database initialization function that opens the file and creates the required tables.

// database.js
import * as SQLite from 'expo-sqlite';

export const initializeDatabase = async () => {
  const db = await SQLite.openDatabaseAsync('notes.db');

  await db.execAsync(`
    PRAGMA foreign_keys = ON;

    CREATE TABLE IF NOT EXISTS notes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      content TEXT NOT NULL,
      created_at TEXT NOT NULL
    );
  `);

  return db;
};

Call the initializer before using the database. Avoid opening and initializing the same database unnecessarily for every button press. A shared database instance or provider can make the lifecycle easier to manage.

11. Inserting Data into SQLite

Use the SQL INSERT INTO statement to add a new record. With Expo SQLite, runAsync() is suitable for write operations.

const insertNote = async (db, title, content) => {
  const createdAt = new Date().toISOString();

  const result = await db.runAsync(
    `INSERT INTO notes (title, content, created_at)
     VALUES (?, ?, ?)`,
    title,
    content,
    createdAt
  );

  return result.lastInsertRowId;
};

The question marks are parameter placeholders. The supplied values are bound separately from the SQL text, helping prevent SQL injection and handling special characters correctly.

Example Usage

const db = await initializeDatabase();

const newNoteId = await insertNote(
  db,
  'React Native Learning',
  'Practice SQLite database operations.'
);

console.log('Created note ID:', newNoteId);

In the example, the note is inserted into the database, and the returned identifier can be used to retrieve or update the new record.

12. Retrieving All Records

The SQL SELECT statement retrieves data. Use getAllAsync() when the result set is reasonably small and you want all matching rows in memory.

const getAllNotes = async (db) => {
  return await db.getAllAsync(
    'SELECT id, title, content, created_at FROM notes ORDER BY id DESC'
  );
};

Displaying the Result

const notes = await getAllNotes(db);

notes.forEach((note) => {
  console.log(note.id, note.title, note.content);
});

Use ORDER BY to define a predictable display order. For large tables, prefer a query with LIMIT and pagination instead of loading every row into memory.

13. Retrieving a Single Record

Use getFirstAsync() when you need only one matching record.

const getNoteById = async (db, id) => {
  return await db.getFirstAsync(
    'SELECT id, title, content, created_at FROM notes WHERE id = ?',
    id
  );
};

If no row matches, the result is normally null. Check the result before trying to access its properties.

const note = await getNoteById(db, 1);

if (note) {
  console.log(note.title);
} else {
  console.log('Note not found');
}

14. Updating Records

Use UPDATE to modify an existing record. The WHERE clause specifies which rows should change.

const updateNote = async (db, id, title, content) => {
  const result = await db.runAsync(
    `UPDATE notes
     SET title = ?, content = ?
     WHERE id = ?`,
    title,
    content,
    id
  );

  return result.changes;
};

The result's changes property indicates how many rows were affected. Always include a suitable WHERE clause unless you intentionally want to update every row.

15. Deleting Records

Use DELETE FROM to remove a record. Deleting a row is different from removing the entire database file.

const deleteNote = async (db, id) => {
  const result = await db.runAsync(
    'DELETE FROM notes WHERE id = ?',
    id
  );

  return result.changes;
};

Before deletion, consider whether the interface should ask for confirmation. If the application supports undo, implement a deliberate recovery strategy rather than assuming a deleted row can be restored.

16. Understanding CRUD Operations

CRUD stands for Create, Read, Update, and Delete. These are the four fundamental operations used to manage records in a database.

Most applications that manage structured local records rely on CRUD operations. Understanding them is essential for building a notes app, task manager, inventory tool, or offline data-entry application.

17. Using Parameterized Queries Safely

Parameterized queries separate SQL commands from user-provided values. This helps protect the database from SQL injection and avoids errors caused by quotes or special characters in text.

Unsafe Pattern

// Avoid building SQL by concatenating user input.
const sql = `SELECT * FROM notes WHERE title = '${userInput}'`;

If the input contains special characters or malicious SQL, concatenating it directly can break the query or create a security risk.

Safer Pattern

const results = await db.getAllAsync(
  'SELECT * FROM notes WHERE title = ?',
  userInput
);

Use placeholders for values in insert, update, and delete queries as well as searches. SQL identifiers, such as table names and column names, generally cannot be bound like ordinary values; if identifiers must vary, validate them against a strict allowlist.

18. Searching Records with SQL

SQLite supports filtering records through the WHERE clause. A notes application can use this feature to search titles or content.

const searchNotes = async (db, searchText) => {
  const pattern = `%${searchText}%`;

  return await db.getAllAsync(
    `SELECT id, title, content, created_at
     FROM notes
     WHERE title LIKE ? OR content LIKE ?
     ORDER BY id DESC
     LIMIT 100`,
    pattern,
    pattern
  );
};

The percent sign in the pattern represents any sequence of characters. The limit prevents the application from retrieving an unnecessarily large result set. If you need literal matching for user-supplied percent or underscore characters, escape those wildcard characters and use an appropriate SQL ESCAPE clause.

19. Sorting and Pagination

When a table contains many records, sorting and pagination make the interface easier to use and help control memory consumption.

Sort by Newest First

const recentNotes = await db.getAllAsync(
  'SELECT * FROM notes ORDER BY created_at DESC LIMIT 20'
);

Retrieve a Page of Records

const getNotesPage = async (db, limit, offset) => {
  return await db.getAllAsync(
    `SELECT id, title, content, created_at
     FROM notes
     ORDER BY id DESC
     LIMIT ? OFFSET ?`,
    limit,
    offset
  );
};

Validate limit and offset before passing them into a query. For large or frequently changing datasets, keyset pagination based on the last-seen ID can be more reliable than a large offset.

20. Displaying SQLite Data with FlatList

React Native's FlatList is designed for rendering lists efficiently. Database records can be loaded into component state and displayed through FlatList.

import React, { useEffect, useState } from 'react';
import { View, Text, FlatList } from 'react-native';

export default function NotesList({ db }) {
  const [notes, setNotes] = useState([]);

  const loadNotes = async () => {
    try {
      const rows = await db.getAllAsync(
        'SELECT id, title, content, created_at FROM notes ORDER BY id DESC'
      );
      setNotes(rows);
    } catch (error) {
      console.error('Failed to load notes:', error);
    }
  };

  useEffect(() => {
    loadNotes();
  }, [db]);

  return (
    <FlatList
      data={notes}
      keyExtractor={(item) => String(item.id)}
      renderItem={({ item }) => (
        <View>
          <Text>{item.title}</Text>
          <Text>{item.content}</Text>
        </View>
      )}
      ListEmptyComponent={
        <Text>No notes have been saved yet.</Text>
      }
    />
  );
}

This example assumes the parent component supplies an initialized database object through the db prop. In a full application, refresh the list after insert, update, or delete operations so that the displayed state reflects the database.

21. Using SQLiteProvider and useSQLiteContext

Expo SQLite offers SQLiteProvider and useSQLiteContext() to share a database instance with components in a component tree.

import React from 'react';
import { SQLiteProvider } from 'expo-sqlite';
import { View, Text } from 'react-native';

export default function App() {
  return (
    <SQLiteProvider
      databaseName="notes.db"
      onInit={initializeSchema}
    >
      <MainScreen />
    </SQLiteProvider>
  );
}

async function initializeSchema(db) {
  await db.execAsync(`
    CREATE TABLE IF NOT EXISTS notes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      content TEXT NOT NULL,
      created_at TEXT NOT NULL
    );
  `);
}

function MainScreen() {
  return (
    <View>
      <Text>SQLite Notes Application</Text>
    </View>
  );
}

The provider opens the database and runs the initialization callback before its children are rendered. Components inside the provider can call useSQLiteContext() to access the database.

import { useSQLiteContext } from 'expo-sqlite';

function NotesScreen() {
  const db = useSQLiteContext();

  // Use db in event handlers or effects to query the database.
  return null;
}

Do not call useSQLiteContext() outside the provider. For more details, consult the official Expo SQLite documentation.

22. Handling Database Errors

Database operations can fail because of invalid SQL, missing tables, incompatible data, storage problems, or unexpected application states. Use error handling around asynchronous operations.

const safeLoadNotes = async (db) => {
  try {
    return await db.getAllAsync(
      'SELECT id, title, content FROM notes ORDER BY id DESC'
    );
  } catch (error) {
    console.error('Database query failed:', error);
    return [];
  }
};

Returning an empty array can be useful for a list, but do not treat every database failure as if the database were genuinely empty. In a production application, distinguish between a valid empty result and an operation failure so the user can retry or receive an appropriate message.

23. Database Transactions

A transaction groups related database operations into a single unit. When the transaction is committed, its changes are saved together. If an operation fails, a transaction can be rolled back so that the database is not left in a partially updated state.

Transactions are useful for bank-transfer simulations, bulk imports, order creation, and operations that update multiple related tables.

Example: Updating Related Records

await db.withTransactionAsync(async () => {
  await db.runAsync(
    'UPDATE accounts SET balance = balance - ? WHERE id = ?',
    100,
    1
  );

  await db.runAsync(
    'UPDATE accounts SET balance = balance + ? WHERE id = ?',
    100,
    2
  );
});

This illustrates two related updates within a transaction. A real implementation must validate the amount, verify that both accounts exist, check that the first account has sufficient funds, and handle errors. Use the transaction API documented for your installed library version. With Expo SQLite, be aware of the documented behavior of withTransactionAsync() and consider withExclusiveTransactionAsync() when you need the transaction scope to be explicit.

24. Primary Keys, Foreign Keys, and Relationships

A primary key uniquely identifies a row. A foreign key links a row in one table to a related row in another table.

For example, a notes application might organize notes into categories. Each category has an ID, and each note may reference its category through a foreign key.

CREATE TABLE IF NOT EXISTS categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  category_id INTEGER,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

This design avoids repeating the category name in every note. When using foreign keys in SQLite, enable foreign-key enforcement on the database connection with PRAGMA foreign_keys = ON where supported and appropriate.

25. Joining Related Tables

A SQL JOIN retrieves related information from multiple tables. For example, you can display a note alongside its category name.

const rows = await db.getAllAsync(`
  SELECT
    notes.id,
    notes.title,
    notes.content,
    categories.name AS category_name
  FROM notes
  LEFT JOIN categories
    ON notes.category_id = categories.id
  ORDER BY notes.id DESC
`);

A LEFT JOIN includes notes even when they do not have a matching category. The category_name may be NULL for notes without a category.

26. Indexes and Query Performance

An index can improve query performance when a column is frequently used for searching, sorting, or joining. However, indexes require additional storage and can make inserts and updates more expensive.

CREATE INDEX IF NOT EXISTS idx_notes_created_at
ON notes(created_at);

Create indexes based on actual query patterns rather than indexing every column. Use SQLite's query planning tools, including EXPLAIN QUERY PLAN, when investigating slow queries.

27. Database Migrations and Schema Changes

As an application evolves, tables may need new columns or different relationships. A database migration is a controlled procedure that changes an existing schema while preserving the data that should remain.

For example, a later version of a notes application might add an is_favorite column. Simply changing the original CREATE TABLE IF NOT EXISTS statement will not add that column to an existing database.

ALTER TABLE notes
ADD COLUMN is_favorite INTEGER NOT NULL DEFAULT 0;

Run schema changes only when required. Track the current schema version and execute each migration once. Before changing or removing columns, consider data preservation, rollback strategy, and compatibility with existing installations.

28. Offline-First Applications and Synchronization

SQLite is useful for offline-first applications because the application can read and write local records without waiting for a server response. When the device reconnects, the app can synchronize local changes with a backend.

Example Synchronization Workflow

1. Read records from the local database.
2. Display available information immediately.
3. Allow the user to create, edit, or delete local records.
4. Mark local changes as pending synchronization when appropriate.
5. Send pending changes to the backend when connectivity is available.
6. Process server responses and conflicts.
7. Update local records with the confirmed server state.

Synchronization requires more than a local SQLite database. Design record identifiers, timestamps or version fields, deletion handling, retry behavior, and conflict resolution. Do not assume that a local change has reached the server just because it has been saved locally.

29. Data Security and Privacy

• Store only the data needed by the application.
• Validate user input before saving it.
• Use parameterized queries for values supplied by users.
• Do not assume a local database is encrypted by default.
• Keep sensitive credentials out of ordinary database tables unless an appropriate security design is in place.
• Use platform-backed secure storage for credentials and keys where suitable.
• Consider database encryption requirements and platform compatibility for sensitive local data.
• Protect sensitive information in backups, exports, logs, and debugging tools.
• Define how local data is handled when a user logs out or deletes an account.
• Apply authorization checks on the server for remote operations; local database data is not a security boundary.

SQLite is a storage engine, not a complete security system. Assess the sensitivity of the stored information and follow the relevant platform and application security requirements.

30. Common Mistakes and Troubleshooting

31. Practical Project: Offline Notes Application

Project objective: Build a React Native notes application that uses SQLite to store and manage notes locally.

Project Features

• Create a note with a title and content.
• Display saved notes in a list.
• Open a note and view its details.
• Edit an existing note.
• Delete a note with confirmation.
• Search notes by title or content.
• Sort notes by creation date.
• Preserve notes after application restarts.
• Show loading indicators and error messages.
• Handle an empty list with a helpful message.

Suggested Project Structure

SQLiteNotesApp/
├── App.js
├── src/
│   ├── database/
│   │   ├── database.js
│   │   └── noteRepository.js
│   ├── screens/
│   │   ├── NotesListScreen.js
│   │   ├── AddNoteScreen.js
│   │   └── EditNoteScreen.js
│   ├── components/
│   │   ├── NoteItem.js
│   │   └── SearchBar.js
│   └── utils/
│       └── validators.js

Development Steps

1. Create a React Native or Expo project.
2. Install and configure Expo SQLite.
3. Create a database initialization function.
4. Define the notes table and its columns.
5. Implement insert, select, update, and delete functions.
6. Create the notes list screen using FlatList.
7. Build a form for adding and editing notes.
8. Validate required fields before saving.
9. Add searching, sorting, and pagination if needed.
10. Test that saved records remain available after restarting the application.
11. Test error handling, duplicate actions, and schema initialization.
12. Document the project and prepare it for your portfolio.

Expected Result

The completed application should let users create, read, update, search, and delete notes using a local SQLite database. It should work without an internet connection for local note operations and retain successfully saved records across normal application restarts.

32. Exercises and Practice Questions

1. Explain what SQLite is and why it is useful in React Native.
2. Create a table named students with an ID, name, and email.
3. Write a query to insert a student record.
4. Retrieve all student records ordered by name.
5. Retrieve a student by ID using a parameterized query.
6. Update a student's email address.
7. Delete a student by ID.
8. Explain the purpose of a primary key and a foreign key.
9. Write a search query using LIKE.
10. Explain why parameterized queries are safer than string concatenation.
11. Create two related tables and retrieve their data using a JOIN.
12. Explain when an index can improve query performance.
13. Describe how database migrations preserve existing data.
14. Explain how SQLite can support an offline-first application.
15. Build a small CRUD application and test it after an application restart.

33. Interview Questions and Answers

Q1. What is SQLite?

Answer: SQLite is a lightweight relational database engine that stores structured data locally, typically in a database file, without requiring a separate database server.

Q2. Why use SQLite in React Native?

Answer: SQLite is useful for persistent structured data, offline access, SQL queries, relationships, and local CRUD operations.

Q3. What is the difference between SQLite and AsyncStorage?

Answer: SQLite provides relational tables and SQL queries. AsyncStorage provides simple persistent key-value string storage and is often suitable for small, non-sensitive settings.

Q4. What does CRUD mean?

Answer: CRUD stands for Create, Read, Update, and Delete, the fundamental operations for managing records.

Q5. What is a primary key?

Answer: A primary key uniquely identifies each row in a table.

Q6. What is a foreign key?

Answer: A foreign key represents a relationship between tables and can enforce referential integrity when configured correctly.

Q7. What is a parameterized query?

Answer: A parameterized query binds values separately from SQL text, helping prevent SQL injection and handling special characters correctly.

Q8. What is a database transaction?

Answer: A transaction groups related database operations into a unit that can be committed or rolled back as a whole.

Q9. What is an index?

Answer: An index is a data structure that can speed up queries on selected columns, at the cost of additional storage and write overhead.

Q10. What is a database migration?

Answer: A migration is a controlled schema or data change used to update an existing database as an application evolves.

Q11. Does SQLite require an internet connection?

Answer: No. Local SQLite queries can run offline. Internet access is needed only for features that communicate with remote services or synchronize data.

Q12. How do you display SQLite records in React Native?

Answer: Query the database asynchronously, store the results in React state, and render them using components such as FlatList.

Q13. Is a SQLite database encrypted by default?

Answer: No. The standard SQLite database is not automatically encrypted. Additional protection may be needed based on the application's security requirements.

Q14. How do you prevent a table-not-found error?

Answer: Initialize the database schema before executing queries that depend on the table.

Q15. Can SQLite synchronize data between devices automatically?

Answer: No. Cross-device synchronization requires additional application logic and usually a remote backend.

34. Quick Revision Checklist

• Understand relational database tables, rows, and columns.
• Know the differences between SQLite, AsyncStorage, and remote databases.
• Install and configure a compatible SQLite library.
• Open and initialize the database before running queries.
• Create tables with suitable constraints.
• Use INSERT, SELECT, UPDATE, and DELETE for CRUD operations.
• Use parameterized queries for user-provided values.
• Display query results using React state and FlatList.
• Handle asynchronous operations and database errors.
• Understand transactions, joins, foreign keys, and indexes.
• Use migrations to manage schema changes.
• Plan offline synchronization when remote data is required.
• Test persistence, security, and performance.

35. Conclusion

SQLite is a valuable tool for building React Native applications that need persistent, structured local data. It enables developers to store records, execute SQL queries, manage relationships, and support core functionality when the device is offline.

To build reliable applications, initialize the database correctly, use parameterized queries, handle asynchronous operations, design an appropriate schema, and manage database migrations as the application grows. Combine SQLite with React state to keep the interface updated, and add a synchronization strategy when data must be shared across devices.

36. Additional Learning Resources

• Expo SQLite API Documentation
• Expo Guide to Storing Data
• Official SQLite Documentation
• React Native FlatList Documentation
• React useEffect Documentation
• Explore React Native Training at JustAcademy
• Register for a JustAcademy Course Demo
• Visit JustAcademy