SQLite with expo-sqlite in React Native - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is expo-sqlite used for in React Native?

It is a library that allows you to use a lightweight SQL database (SQLite) inside your React Native app to store and manage data locally on the device.

beginner

How do you open or create a database using expo-sqlite?

You use SQLite.openDatabase('name.db') which opens the database if it exists or creates it if it doesn't.

intermediate

What method is used to run SQL commands like creating tables or inserting data?

You use db.transaction(tx => { tx.executeSql(sql, params, success, error) }) to run SQL commands inside a transaction.

intermediate

Why do we use transactions when working with expo-sqlite?

Transactions ensure that a group of SQL commands run safely together. If one command fails, the whole transaction can be rolled back to keep data consistent.

intermediate

How can you retrieve data from the database using expo-sqlite?

Inside a transaction, use executeSql with a SELECT query. The results are in the resultSet.rows._array property inside the success callback.

Which function opens or creates a SQLite database in expo-sqlite?

A SQLite.openDatabase('mydb.db')

B SQLite.createDatabase('mydb.db')

C SQLite.connect('mydb.db')

D SQLite.initDatabase('mydb.db')

The correct method to open or create a database is SQLite.openDatabase('mydb.db').

What is the purpose of the transaction method in expo-sqlite?

A To open the database connection

B To backup the database

C To close the database

D To run multiple SQL commands safely as a group

Transactions group SQL commands so they run safely together, ensuring data integrity.

Where do you find the rows returned by a SELECT query in expo-sqlite?

A resultSet.records

B resultSet.rows._array

C resultSet.rowsArray

D resultSet.data

The rows returned are inside resultSet.rows._array in the success callback.

Which SQL command creates a new table in expo-sqlite?

A CREATE TABLE IF NOT EXISTS tablename (...)

B NEW TABLE tablename (...)

C MAKE TABLE tablename (...)

D ADD TABLE tablename (...)

The correct SQL syntax to create a table if it doesn't exist is CREATE TABLE IF NOT EXISTS.

What happens if one SQL command fails inside a transaction?

A The database closes automatically

B Only the failed command is ignored, others continue

C The whole transaction rolls back and no changes are saved

D The app crashes

Transactions ensure all commands succeed or none do, so failure rolls back all changes.

Explain how to create a simple table and insert data using expo-sqlite in React Native.

Think about opening the database, then running SQL commands inside a transaction.

Describe how to retrieve and display data from a SQLite database using expo-sqlite.

Focus on running a SELECT query and accessing the results in the callback.

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