Comprehensive Guide to SQLite in Expo Projects

Using SQLite with Expo: A Step-by-Step Guide

Expo is a popular framework for building React Native applications. It supports SQLite databases, making it easy to manage data locally in mobile apps. Using the expo-sqlite module, developers can perform database operations such as creating tables, inserting data, querying, and updating records. This guide provides an in-depth explanation of working with SQLite in Expo, complete with examples and code snippets.

Setting Up SQLite in an Expo Project

Step 1: Install expo-sqlite

To use SQLite in your Expo project, install the expo-sqlite package.

expo install expo-sqlite

Step 2: Import and Initialize SQLite

// Import the SQLite module from expo-sqlite
import * as SQLite from 'expo-sqlite';

// Open or create a database file named 'myDatabase.db'
const db = SQLite.openDatabase('myDatabase.db');

Common SQLite Tasks with expo-sqlite

1. Creating a Table

Code:

// Create a table named 'users' with columns for id, name, and age
db.transaction((tx) => {
  tx.executeSql(
    `CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      age INTEGER NOT NULL
    );`,
    [], // No parameters
    () => console.log('Table created successfully'), // Success callback
    (error) => console.error('Error creating table', error) // Error callback
  );
});

Explanation:

• CREATE TABLE IF NOT EXISTS: Ensures the table is only created if it does not already exist.
• tx.executeSql: Executes the SQL command within a transaction.

2. Inserting Data

Code:

// Insert data into the users table
db.transaction((tx) => {
  tx.executeSql(
    `INSERT INTO users (name, age) VALUES (?, ?);`, // Query with placeholders
    ['Alice', 25], // Parameters to replace placeholders
    () => console.log('Data inserted successfully'), // Success callback
    (error) => console.error('Error inserting data', error) // Error callback
  );
});

Explanation:

• ?: Placeholder for parameters to prevent SQL injection.
• ['Alice', 25]: Parameters replace placeholders in the query.

3. Querying Data

Code:

// Retrieve all data from the users table
db.transaction((tx) => {
  tx.executeSql(
    `SELECT * FROM users;`,
    [], // No parameters
    (_, { rows }) => console.log('Query Results:', rows._array), // Success callback
    (error) => console.error('Error querying data', error) // Error callback
  );
});

Explanation:

• rows._array: Contains the result set as an array of objects.
• The success callback processes and logs the results.

4. Updating Data

Code:

// Update age for a specific user
db.transaction((tx) => {
  tx.executeSql(
    `UPDATE users SET age = ? WHERE name = ?;`, // Query with placeholders
    [30, 'Alice'], // Parameters to replace placeholders
    () => console.log('Data updated successfully'), // Success callback
    (error) => console.error('Error updating data', error) // Error callback
  );
});

Explanation:

• UPDATE SET: Modifies existing records in the database.
• WHERE: Ensures the update applies only to matching records.

5. Deleting Data

Code:

// Delete a user from the table
db.transaction((tx) => {
  tx.executeSql(
    `DELETE FROM users WHERE name = ?;`, // Query with a placeholder
    ['Alice'], // Parameter to replace the placeholder
    () => console.log('Data deleted successfully'), // Success callback
    (error) => console.error('Error deleting data', error) // Error callback
  );
});

Explanation:

• DELETE FROM: Removes records from the table.
• The parameter ensures only the specified user is deleted.

Using SQLite in React Native Components

Here is an example of using SQLite in a React Native component:

Code:

import React, { useEffect } from 'react';
import { View, Text } from 'react-native';
import * as SQLite from 'expo-sqlite';

const db = SQLite.openDatabase('myDatabase.db');

const App = () => {
  useEffect(() => {
    // Create table on component mount
    db.transaction((tx) => {
      tx.executeSql(
        `CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER);`
      );
    });
  }, []);

  return (
    <View>
      <Text>SQLite with Expo</Text>
    </View>
  );
};

export default App;

Benefits of Using SQLite with Expo

1. Offline Data Storage: Ideal for storing data in mobile apps without requiring an internet connection.

2. Lightweight: SQLite is a compact and efficient database engine.

3. Native Integration: The expo-sqlite module simplifies SQLite usage in Expo apps.

4. Customizable: Allows developers to create and manage complex databases.

Tips for Efficient SQLite Usage

• Use transactions for batch operations to improve performance.
• Regularly optimize database queries and manage indexes for faster retrieval.
• Backup the database file to ensure data safety.

Conclusion

SQLite integration with Expo through the expo-sqlite module provides a powerful yet simple solution for managing local databases in mobile apps. By following the examples above, you can efficiently create, query, and manage your database within an Expo project.

Practical Guides to SQLite Snippets and Examples.