React Native course · Module 9: Data storage

expo-sqlite - Database

2 min read ·
In this lesson3

For complex relational data, use SQLite!

Installation

npx expo install expo-sqlite

Basic operations

import * as SQLite from 'expo-sqlite';

// Open/create database
const db = await SQLite.openDatabaseAsync('atlantis.db');

// Create table
await db.execAsync(`
  CREATE TABLE IF NOT EXISTS treasures (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    value INTEGER DEFAULT 0,
    found_at TEXT
  );
`);

// INSERT
await db.runAsync(
  'INSERT INTO treasures (name, value) VALUES (?, ?)',
  ['Golden Pearl', 500]
);

// SELECT all
const treasures = await db.getAllAsync<{ id: number; name: string; value: number }>(
  'SELECT * FROM treasures'
);

// SELECT one
const treasure = await db.getFirstAsync<{ name: string }>(
  'SELECT name FROM treasures WHERE id = ?',
  [1]
);

// UPDATE
await db.runAsync(
  'UPDATE treasures SET value = ? WHERE id = ?',
  [1000, 1]
);

// DELETE
await db.runAsync('DELETE FROM treasures WHERE id = ?', [1]);

Database Service

class TreasureDB {
  private db: SQLite.SQLiteDatabase | null = null;

  async init() {
    this.db = await SQLite.openDatabaseAsync('atlantis.db');
    await this.db.execAsync(`
      CREATE TABLE IF NOT EXISTS treasures (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        value INTEGER DEFAULT 0,
        rarity TEXT DEFAULT 'common'
      );
    `);
  }

  async getAll() {
    return this.db?.getAllAsync('SELECT * FROM treasures ORDER BY value DESC');
  }

  async create(name: string, value: number, rarity: string) {
    const result = await this.db?.runAsync(
      'INSERT INTO treasures (name, value, rarity) VALUES (?, ?, ?)',
      [name, value, rarity]
    );
    return result?.lastInsertRowId;
  }

  async delete(id: number) {
    await this.db?.runAsync('DELETE FROM treasures WHERE id = ?', [id]);
  }
}

export const treasureDB = new TreasureDB();
Code for this lesson: App.tsx
import React, { useEffect, useState } from 'react';
import { View, Text, FlatList, StyleSheet } from 'react-native';
import * as SQLite from 'expo-sqlite';

type Treasure = { id: number; name: string; value: number };

// Database service for the treasures of Atlantis
class TreasureDB {
  private db: SQLite.SQLiteDatabase | null = null;

  async init() {
    this.db = await SQLite.openDatabaseAsync('atlantis.db');
    // TODO: create the table via this.db.execAsync with the query:
    //   CREATE TABLE IF NOT EXISTS treasures (
    //     id INTEGER PRIMARY KEY AUTOINCREMENT,
    //     name TEXT NOT NULL,
    //     value INTEGER DEFAULT 0
    //   );

  }

  async create(name: string, value: number) {
    // TODO: insert a row via this.db.runAsync with (?, ?) parameters:
    //   'INSERT INTO treasures (name, value) VALUES (?, ?)', [name, value]

  }

  async getAll(): Promise<Treasure[]> {
    // TODO: return all rows via this.db.getAllAsync<Treasure>(
    //   'SELECT * FROM treasures ORDER BY value DESC')
    return [];
  }
}

const treasureDB = new TreasureDB();

export default function App() {
  const [treasures, setTreasures] = useState<Treasure[]>([]);

  useEffect(() => {
    (async () => {
      await treasureDB.init();
      await treasureDB.create('Golden Pearl', 500);
      setTreasures(await treasureDB.getAll());
    })();
  }, []);

  return (
    <View style={styles.container}>
      <Text style={styles.title}>Treasures of Atlantis</Text>
      <FlatList
        data={treasures}
        keyExtractor={(item) => String(item.id)}
        renderItem={({ item }) => (
          <Text style={styles.item}>{item.name} - {item.value}</Text>
        )}
      />
    </View>
  );
}

const styles = StyleSheet.create({
  container: {
    flex: 1,
    padding: 20,
    paddingTop: 60,
    backgroundColor: '#1a5276',
  },
  title: {
    fontSize: 22,
    color: '#fff',
    fontWeight: '700',
    marginBottom: 16,
  },
  item: {
    fontSize: 16,
    color: '#aed6f1',
    paddingVertical: 8,
  },
});

Docs entries used in this lesson

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. How to install expo-sqlite?

  2. 2. When to use execAsync instead of runAsync?

These are 2 of 6 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Horizontal ordering

    Arrange opening the database:

  • Vertical ordering

    Arrange the table creation:

  • Click in order

    Click the INSERT elements:

  • Code editor

    Complete the Database Service code.

  1. QuestionInstalling SQLite
  2. Put in orderopenDatabaseAsync
  3. QuestionexecAsync
  4. Put in orderCREATE TABLE
  5. QuestiongetAllAsync
  6. QuestiongetFirstAsync
  7. Click in orderrunAsync INSERT
  8. QuestionQuery parameters
  9. QuestionlastInsertRowId
  10. Coding taskTreasureDB
  11. Project to submitProject: Storage
  12. VideoModule summary

Useful articles