SQLite with expo-sqlite in React Native - Build, Publish & Deploy

Build & Publish - SQLite with expo-sqlite

Performance Impact

Using expo-sqlite allows your app to store and query data locally on the device. This means faster data access compared to network calls, improving app responsiveness and offline capability.

However, heavy or complex queries can slow down the UI thread if not handled properly, causing frame drops below 60fps. SQLite operations are asynchronous by default, so large transactions may block the UI if not managed correctly.

Memory usage is generally low, but storing large blobs or many records can increase memory and storage use, potentially affecting app stability on low-end devices.

Battery impact is minimal for typical use, but frequent writes or queries can increase CPU usage and drain battery faster.

Optimization Tips

• Run queries asynchronously: Use transaction callbacks to avoid blocking the UI thread.
• Batch operations: Group multiple inserts or updates in a single transaction to reduce overhead.
• Index your tables: Create indexes on columns used in WHERE clauses to speed up queries.
• Limit data size: Avoid storing large blobs or unnecessary data in SQLite.
• Use pagination: Load data in small chunks instead of all at once to keep UI smooth.
• Close unused database connections: Prevent resource leaks.

App Bundle Size and Startup Time

expo-sqlite is included as part of the Expo SDK, so it adds minimal extra size to your app bundle.

Since SQLite is a native library, it does not increase JavaScript bundle size significantly.

Startup time impact is negligible because the database opens quickly and only when needed.

However, large local databases can increase app storage usage and slow down initial data loading if you preload data on startup.

iOS vs Android Differences

• Underlying SQLite: Both platforms use native SQLite, but Android may have different SQLite versions depending on OS version.
• File storage: Database files are stored in platform-specific app directories with different access permissions.
• Performance: Generally similar, but Android devices vary more in hardware, so performance can differ.
• Backup: iOS automatically backs up app data including SQLite files to iCloud unless excluded; Android backup behavior varies by device and settings.
• Concurrency: Android may handle concurrent database access differently; always use transactions to avoid conflicts.

Store Review Guidelines

• Data privacy: Ensure you handle user data securely and comply with privacy laws (e.g., GDPR, CCPA).
• Data storage: Do not store sensitive data unencrypted in SQLite; use encryption if required.
• App size: Keep database size reasonable to avoid app bloat.
• Performance: Avoid UI freezes caused by database operations to meet smoothness guidelines.
• Permissions: No special permissions are needed for SQLite, but if you export or share data, follow platform rules.

Self-Check Question

Your app takes 5 seconds to load this screen that queries SQLite. What is likely wrong?

• You might be running heavy or multiple synchronous queries on the main thread blocking UI rendering.
• You may be loading too much data at once instead of using pagination.
• Database indexes might be missing, causing slow queries.
• Transactions might not be batched, increasing overhead.

Key Result

Using expo-sqlite enables fast local data storage with minimal bundle size impact, but to keep your app smooth at 60fps, run queries asynchronously, batch operations, and limit data size. Be mindful of platform differences and store guidelines for data privacy and 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