SQLite with expo-sqlite in React Native - Deep Dive

Overview - SQLite with expo-sqlite

What is it?

SQLite with expo-sqlite is a way to store and manage data directly on a mobile device using a small database. It lets your app save information like notes, settings, or user data without needing internet. Expo-sqlite is a tool that helps React Native apps use SQLite easily. It works inside your app and keeps data safe and fast.

Why it matters

Without SQLite, apps would struggle to save and organize data locally, forcing users to rely on slow or unreliable internet connections. This would make apps less useful offline and slower to respond. SQLite with expo-sqlite solves this by providing a simple, fast, and reliable way to store data on the device itself, improving user experience and app performance.

Where it fits

Before learning SQLite with expo-sqlite, you should understand basic React Native app structure and JavaScript promises. After this, you can explore more advanced data management like syncing local data with cloud databases or using other storage options like AsyncStorage or Realm.

Mental Model

Core Idea

SQLite with expo-sqlite lets your app keep organized data inside the device using a tiny, fast database that works like a digital notebook.

Think of it like...

Imagine your app has a small filing cabinet inside your phone. SQLite is the cabinet where you store papers (data) in folders (tables). Expo-sqlite is the helper that opens the cabinet, finds the right folder, and files or retrieves papers quickly.

┌─────────────┐
│  Your App   │
└──────┬──────┘
       │ uses
┌──────▼──────┐
│ expo-sqlite │
└──────┬──────┘
       │ manages
┌──────▼──────┐
│  SQLite DB  │
│ (tables,   │
│  queries)  │
└─────────────┘

Build-Up - 7 Steps

1

Foundation

What is SQLite and expo-sqlite

Concept: Introduce SQLite as a small database and expo-sqlite as a React Native tool to use it.

SQLite is a lightweight database that stores data in tables on your device. Expo-sqlite is a library that lets React Native apps talk to SQLite easily. You can create tables, add data, and ask questions (queries) to get data back.

Result

You understand that SQLite is a local database and expo-sqlite is the bridge to use it in your app.

Knowing SQLite is a tiny database helps you see why it fits perfectly inside mobile apps for fast, offline data storage.

2

Foundation

Setting up expo-sqlite in React Native

Concept: Learn how to install and open a database connection using expo-sqlite.

First, install expo-sqlite with npm or yarn. Then, import it and open a database with openDatabase('mydb.db'). This creates or opens a file on the device to store your data.

Result

Your app can now talk to a local database file named 'mydb.db'.

Opening a database is like unlocking your filing cabinet so you can start organizing data.

3

Intermediate

Creating tables and inserting data

🤔Before reading on: do you think you can insert data before creating a table? Commit to yes or no.

Concept: Learn to create tables and add rows of data using SQL commands inside expo-sqlite transactions.

Use db.transaction(tx => { tx.executeSql('CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY NOT NULL, name TEXT);'); tx.executeSql('INSERT INTO items (name) VALUES (?);', ['Apple']); }); This creates a table and adds one item.

Result

A table named 'items' exists with one row containing 'Apple'.

Understanding that SQL commands run inside transactions ensures data is safely written or rolled back if errors happen.

4

Intermediate

Querying and reading data from SQLite

🤔Before reading on: do you think queries return data immediately or asynchronously? Commit to your answer.

Concept: Learn to read data using SELECT queries and handle results with callbacks.

Use db.transaction(tx => { tx.executeSql('SELECT * FROM items;', [], (_, { rows }) => { console.log(rows._array); }); }); This fetches all rows and logs them.

Result

You see an array of objects representing each row in the table.

Knowing queries are asynchronous helps you write code that waits for data before using it.

5

Intermediate

Updating and deleting data safely

Concept: Learn to change or remove data using UPDATE and DELETE SQL commands inside transactions.

Use db.transaction(tx => { tx.executeSql('UPDATE items SET name = ? WHERE id = ?;', ['Banana', 1]); tx.executeSql('DELETE FROM items WHERE id = ?;', [1]); }); This changes or removes rows by id.

Result

Data in the table changes or disappears as requested.

Understanding how to update and delete data lets you keep your database accurate and clean.

6

Advanced

Handling errors and transactions properly

🤔Before reading on: do you think transactions stop on first error or continue? Commit to your answer.

Concept: Learn how expo-sqlite transactions rollback on errors and how to catch them.

Transactions run multiple SQL commands atomically. If one fails, all changes rollback. Use error callbacks to detect failures and success callbacks to confirm completion.

Result

Your app avoids partial data changes and can respond to errors gracefully.

Knowing transactions protect data integrity prevents bugs where data gets half-updated or corrupted.

7

Expert

Optimizing SQLite usage in production apps

🤔Before reading on: do you think opening many transactions at once is good or bad? Commit to your answer.

Concept: Learn best practices like batching queries, indexing tables, and managing database connections efficiently.

Batch multiple related queries in one transaction to reduce overhead. Add indexes on columns you search often to speed queries. Open the database once and reuse the connection to save resources.

Result

Your app runs faster, uses less battery, and handles data smoothly even with many users.

Understanding optimization techniques helps build apps that scale well and feel responsive to users.

Under the Hood

Expo-sqlite uses a native SQLite engine embedded in the device. When you open a database, it creates or accesses a file storing tables and data. SQL commands run inside transactions to ensure atomicity. The JavaScript code sends commands asynchronously to the native layer, which executes them and returns results via callbacks.

Why designed this way?

SQLite was designed as a lightweight, serverless database to run inside applications without needing a separate server. Expo-sqlite wraps this native engine for React Native apps, providing a simple JavaScript interface. This design avoids complex setup and keeps data local for speed and offline use.

┌───────────────┐
│ React Native  │
│ JavaScript    │
└──────┬────────┘
       │ calls
┌──────▼────────┐
│ expo-sqlite   │
│ JS wrapper    │
└──────┬────────┘
       │ bridges
┌──────▼────────┐
│ Native SQLite │
│ engine       │
└──────┬────────┘
       │ reads/writes
┌──────▼────────┐
│ Database file │
│ on device    │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does expo-sqlite automatically sync data to the cloud? Commit yes or no.

Common Belief: Expo-sqlite keeps your data backed up online automatically.

Reality: Expo-sqlite only stores data locally on the device; it does not sync or backup data to the cloud.

Why it matters: Assuming automatic cloud backup can cause data loss if the device is lost or reset.

Quick: Can you run multiple SQL commands outside transactions safely? Commit yes or no.

Common Belief: You can run SQL commands one by one without transactions and still keep data safe.

Reality: Running commands outside transactions risks partial updates and data corruption if errors occur.

Why it matters: Ignoring transactions can cause inconsistent data and bugs that are hard to fix.

Quick: Is expo-sqlite suitable for very large databases with millions of rows? Commit yes or no.

Common Belief: Expo-sqlite can handle huge databases just like server databases.

Reality: Expo-sqlite is best for small to medium data; very large databases may cause performance issues on mobile devices.

Why it matters: Using expo-sqlite for huge data can slow down your app and drain battery.

Quick: Does expo-sqlite support all advanced SQL features like stored procedures? Commit yes or no.

Common Belief: Expo-sqlite supports all SQL features including stored procedures and triggers.

Reality: Expo-sqlite supports core SQLite features but lacks advanced server-side features like stored procedures.

Why it matters: Expecting unsupported features can lead to wasted time and design mistakes.

Expert Zone

1

SQLite uses a journaling system to keep data safe during crashes, but this can affect write speed; knowing when to disable journaling for performance is key.

2

Expo-sqlite transactions are serialized, so running many in parallel can cause delays; managing transaction queues improves responsiveness.

3

The database file size can grow even after deleting data; running VACUUM commands periodically reclaims space.

When NOT to use

Avoid expo-sqlite when your app needs real-time multi-user syncing or very large datasets; consider cloud databases like Firebase or Realm instead.

Production Patterns

In production, apps batch multiple SQL commands in one transaction, index frequently queried columns, and abstract database logic into reusable modules for maintainability.

Connections

LocalStorage in Web Development

Both store data locally but LocalStorage is key-value only, while SQLite supports complex queries.

Understanding SQLite helps web developers appreciate when to use simple storage versus a full database.

File Systems

SQLite stores data in a single file on disk, similar to how file systems organize files and folders.

Knowing file system basics clarifies how SQLite manages data efficiently inside one file.

Library Cataloging Systems

Like a library catalog indexes books for quick search, SQLite uses indexes to speed up data queries.

Seeing database indexes as catalog cards helps understand why indexing improves search speed.

Common Pitfalls

#1 Trying to run SQL commands without wrapping them in a transaction.

Wrong approach:

db.executeSql('INSERT INTO items (name) VALUES ("Orange");');

Correct approach:

db.transaction(tx => { tx.executeSql('INSERT INTO items (name) VALUES (?);', ['Orange']); });

Root cause:

Misunderstanding that expo-sqlite requires transactions to execute SQL commands safely.

#2 Ignoring asynchronous nature and trying to use query results immediately.

Wrong approach:

let items; db.transaction(tx => { tx.executeSql('SELECT * FROM items;', [], (_, { rows }) => { items = rows._array; }); }); console.log(items); // undefined

Correct approach:

db.transaction(tx => { tx.executeSql('SELECT * FROM items;', [], (_, { rows }) => { console.log(rows._array); }); });

Root cause:

Not realizing that SQL queries run asynchronously and results are available only inside callbacks.

#3 Not handling errors in transactions leading to silent failures.

Wrong approach:

db.transaction(tx => { tx.executeSql('DROP TABLE non_existing;'); });

Correct approach:

db.transaction(tx => { tx.executeSql('DROP TABLE non_existing;'); }, error => { console.log('Error:', error); });

Root cause:

Overlooking error callbacks causes bugs to go unnoticed and data issues to persist.

Key Takeaways

SQLite with expo-sqlite provides a powerful way to store structured data locally in React Native apps.

All SQL commands must run inside transactions to ensure data safety and consistency.

Queries and commands are asynchronous, so results must be handled with callbacks or promises.

Proper error handling and transaction management prevent data corruption and app crashes.

Optimizing database usage with batching, indexing, and connection reuse improves app performance.

Practice

1. What is the main purpose of using expo-sqlite in a React Native app?

easy

A. To store and manage data locally using a small database

B. To fetch data from a remote server

C. To style the app interface

D. To handle user authentication

Solution

1. Step 1: Understand expo-sqlite functionality

   expo-sqlite provides a way to store data locally on the device using a SQLite database.

2. Step 2: Compare options with expo-sqlite purpose

   Only To store and manage data locally using a small database describes local data storage with a database, which matches expo-sqlite's role.

3. Final Answer:

   To store and manage data locally using a small database -> Option A

4. Quick Check:

   expo-sqlite = local database storage [OK]

Hint: expo-sqlite is for local data storage, not remote or styling [OK]

Common Mistakes:

• Confusing expo-sqlite with network data fetching
• Thinking expo-sqlite handles UI styling
• Assuming expo-sqlite manages user login

2. Which of the following is the correct way to open a database using expo-sqlite?

easy

A. const db = SQLite.newDatabase('mydb.db');

B. const db = SQLite.createDatabase('mydb.db');

C. const db = SQLite.connect('mydb.db');

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

Solution

1. Step 1: Recall expo-sqlite database opening method

   The correct method to open or create a database is openDatabase.

2. Step 2: Match method names with options

   Only const db = SQLite.openDatabase('mydb.db'); uses openDatabase, which is the correct syntax.

3. Final Answer:

   const db = SQLite.openDatabase('mydb.db'); -> Option D

4. Quick Check:

   openDatabase() opens SQLite DB [OK]

Hint: Use openDatabase() to open or create SQLite DB [OK]

Common Mistakes:

• Using createDatabase() which does not exist
• Using connect() or newDatabase() which are invalid
• Missing the correct method name openDatabase()

3. Given this code snippet, what will be logged to the console?

const db = SQLite.openDatabase('test.db');
db.transaction(tx => {
  tx.executeSql('CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY NOT NULL, name TEXT);');
  tx.executeSql('INSERT INTO items (name) VALUES (?)', ['apple']);
  tx.executeSql('SELECT * FROM items;', [], (_, { rows }) => {
    console.log(rows._array);
  });
});

medium

A. undefined

B. [{ id: 1, name: 'apple' }]

C. []

D. SyntaxError

Solution

1. Step 1: Understand the SQL commands executed

   The code creates a table if missing, inserts one row with name 'apple', then selects all rows.

2. Step 2: Analyze the SELECT query result

   The SELECT returns one row with id 1 and name 'apple', logged as an array of objects.

3. Final Answer:

   [{ id: 1, name: 'apple' }] -> Option B

4. Quick Check:

   Inserted row appears in SELECT result [OK]

Hint: Inserted rows appear in SELECT query result array [OK]

Common Mistakes:

• Expecting empty array before insertion
• Confusing rows._array with undefined
• Assuming syntax error from correct SQL

4. What is wrong with this code snippet that tries to insert data into a SQLite table?

db.transaction(tx => {
  tx.executeSql('INSERT INTO users (name, age) VALUES (?, ?)', ['John']);
});

medium

A. The SQL statement has fewer values than placeholders

B. The table name is incorrect

C. The transaction method is missing a callback

D. The executeSql method cannot use placeholders

Solution

1. Step 1: Check SQL placeholders and values

   The SQL has two placeholders (?, ?) but only one value 'John' is provided.

2. Step 2: Identify mismatch causing error

   Mismatch between placeholders and values causes an error during execution.

3. Final Answer:

   The SQL statement has fewer values than placeholders -> Option A

4. Quick Check:

   Placeholders must match values count [OK]

Hint: Match number of ? placeholders with values array length [OK]

Common Mistakes:

• Ignoring mismatch between placeholders and values
• Assuming table name is wrong without checking
• Thinking executeSql cannot use placeholders

5. You want to display a list of items stored in a SQLite table inside your React Native app. Which approach correctly updates the UI after fetching data from the database?

const [items, setItems] = useState([]);

useEffect(() => {
  db.transaction(tx => {
    tx.executeSql('SELECT * FROM items;', [], (_, { rows }) => {
      // What should go here?
    });
  });
}, []);

hard

A. items = rows._array;

B. return rows._array;

C. setItems(rows._array);

D. console.log(rows._array);

Solution

1. Step 1: Understand React state update

   To update UI, React state setter setItems must be called with new data.

2. Step 2: Identify correct way to update state

   Assigning directly to items or returning data won't update state; logging only shows data.

3. Final Answer:

   setItems(rows._array); -> Option C

4. Quick Check:

   Use setItems() to update state and UI [OK]

Hint: Use setState function to update React state after DB fetch [OK]

Common Mistakes:

• Assigning directly to state variable instead of using setter
• Returning data inside executeSql callback
• Only logging data without updating state