Project 6: Notes App with expo-sqlite

When your data has many rows, relationships or needs searching and sorting, a real database is the right tool. SQLite is a small database that lives in a file on the phone, and Expo provides it through expo-sqlite. You write SQL, and your data survives app restarts.

Concepts in this project

• SQL basics: CREATE TABLE defines columns, INSERT adds a row, UPDATE changes one, DELETE removes one and SELECT ... WHERE ... ORDER BY reads.
• Parameters: put ? in the SQL and pass the values in an array. This avoids SQL injection and quoting problems. Never join user text into SQL yourself.
• Async API: openDatabaseAsync, execAsync, runAsync and getAllAsync all return promises.
• A small data layer: all SQL is in db.ts. The screen calls listNotes, saveNote and deleteNote and knows nothing about SQL.
• Modal: a pop-up screen used here as the editor.

Create the project

1. Use a blank Expo TypeScript project and install the library: npx expo install expo-sqlite.
2. Add db.ts and replace App.tsx.

`db.ts`

import * as SQLite from "expo-sqlite";

export type Note = { id: number; title: string; body: string; updatedAt: number };

let dbPromise: Promise<SQLite.SQLiteDatabase> | null = null;

export function getDb() {
  if (!dbPromise) {
    dbPromise = (async () => {
      const db = await SQLite.openDatabaseAsync("notes.db");
      await db.execAsync(`
        CREATE TABLE IF NOT EXISTS notes (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          title TEXT NOT NULL,
          body TEXT NOT NULL,
          updatedAt INTEGER NOT NULL
        );
      `);
      return db;
    })();
  }
  return dbPromise;
}

export async function listNotes(query: string): Promise<Note[]> {
  const db = await getDb();
  const like = `%${query}%`;
  return db.getAllAsync<Note>(
    "SELECT * FROM notes WHERE title LIKE ? OR body LIKE ? ORDER BY updatedAt DESC",
    [like, like]
  );
}

export async function saveNote(note: { id?: number; title: string; body: string }): Promise<void> {
  const db = await getDb();
  const now = Date.now();
  if (note.id) {
    await db.runAsync("UPDATE notes SET title = ?, body = ?, updatedAt = ? WHERE id = ?", [note.title, note.body, now, note.id]);
  } else {
    await db.runAsync("INSERT INTO notes (title, body, updatedAt) VALUES (?, ?, ?)", [note.title, note.body, now]);
  }
}

export async function deleteNote(id: number): Promise<void> {
  const db = await getDb();
  await db.runAsync("DELETE FROM notes WHERE id = ?", [id]);
}

getDb opens the database once and creates the table if it is missing, then reuses the same connection. listNotes filters with LIKE and sorts the newest first.

`App.tsx`

import { useCallback, useEffect, useState } from "react";
import { SafeAreaView, View, Text, TextInput, Pressable, FlatList, Modal, StyleSheet } from "react-native";
import { Note, deleteNote, listNotes, saveNote } from "./db";

export default function App() {
  const [notes, setNotes] = useState<Note[]>([]);
  const [query, setQuery] = useState("");
  const [editing, setEditing] = useState<Partial<Note> | null>(null);

  const refresh = useCallback(async () => setNotes(await listNotes(query)), [query]);

  useEffect(() => {
    refresh();
  }, [refresh]);

  const save = async () => {
    if (!editing?.title?.trim()) return;
    await saveNote({ id: editing.id, title: editing.title.trim(), body: editing.body ?? "" });
    setEditing(null);
    refresh();
  };

  const remove = async (id: number) => {
    await deleteNote(id);
    refresh();
  };

  return (
    <SafeAreaView style={styles.screen}>
      <Text style={styles.title}>Notes</Text>
      <TextInput style={styles.input} value={query} onChangeText={setQuery} placeholder="Search notes" />

      <FlatList
        data={notes}
        keyExtractor={(n) => String(n.id)}
        ListEmptyComponent={<Text style={styles.empty}>No notes yet. Tap + to add one.</Text>}
        renderItem={({ item }) => (
          <Pressable style={styles.card} onPress={() => setEditing(item)} onLongPress={() => remove(item.id)}>
            <Text style={styles.cardTitle}>{item.title}</Text>
            <Text numberOfLines={2} style={styles.cardBody}>{item.body}</Text>
          </Pressable>
        )}
      />

      <Pressable style={styles.fab} onPress={() => setEditing({ title: "", body: "" })}>
        <Text style={styles.fabText}>+</Text>
      </Pressable>

      <Modal visible={editing !== null} animationType="slide" onRequestClose={() => setEditing(null)}>
        <SafeAreaView style={styles.modal}>
          <TextInput
            style={styles.input}
            placeholder="Title"
            value={editing?.title ?? ""}
            onChangeText={(t) => setEditing((e) => ({ ...e, title: t }))}
          />
          <TextInput
            style={[styles.input, styles.bodyInput]}
            placeholder="Write your note"
            multiline
            value={editing?.body ?? ""}
            onChangeText={(t) => setEditing((e) => ({ ...e, body: t }))}
          />
          <View style={styles.row}>
            <Pressable style={styles.secondary} onPress={() => setEditing(null)}>
              <Text>Cancel</Text>
            </Pressable>
            <Pressable style={styles.primary} onPress={save}>
              <Text style={styles.primaryText}>Save</Text>
            </Pressable>
          </View>
        </SafeAreaView>
      </Modal>
    </SafeAreaView>
  );
}

const styles = StyleSheet.create({
  screen: { flex: 1, padding: 20, backgroundColor: "#f5f6fa" },
  title: { fontSize: 28, fontWeight: "700", marginBottom: 12 },
  input: { backgroundColor: "#fff", borderRadius: 10, padding: 12, marginBottom: 10, borderWidth: 1, borderColor: "#d0d4dc" },
  bodyInput: { height: 220, textAlignVertical: "top" },
  empty: { textAlign: "center", color: "#888", marginTop: 40 },
  card: { backgroundColor: "#fff", padding: 14, borderRadius: 10, marginBottom: 8 },
  cardTitle: { fontSize: 17, fontWeight: "600" },
  cardBody: { color: "#555", marginTop: 4 },
  fab: { position: "absolute", right: 24, bottom: 32, width: 56, height: 56, borderRadius: 28, backgroundColor: "#3b5bdb", alignItems: "center", justifyContent: "center" },
  fabText: { color: "#fff", fontSize: 30 },
  modal: { flex: 1, padding: 20 },
  row: { flexDirection: "row", justifyContent: "flex-end", gap: 12 },
  primary: { backgroundColor: "#3b5bdb", paddingVertical: 12, paddingHorizontal: 24, borderRadius: 10 },
  primaryText: { color: "#fff", fontWeight: "600" },
  secondary: { padding: 12 },
});

How it works

• refresh runs the query for the current search text. The effect calls it at the start and whenever the text changes, so results narrow as you type.
• Tapping a note opens the editor with its values. The + button opens it empty.
• saveNote updates when the note has an id, and inserts otherwise.
• Long-pressing a note deletes it.

Expected output

The first screen shows Notes, a search box and the message "No notes yet. Tap + to add one." Tap +, enter a title and text and press Save: the note appears. Add two more. Type a word from one note in the search box and only the matching notes remain. Tap a note to edit it and save: it moves to the top because it has the newest update time. Long-press to delete. Close and reopen the app: the notes are still there.

Your task

1. Build the app and add three notes, then search.
2. Add a pinned column and show pinned notes first.
3. Ask the user to confirm before a long-press delete using Alert.alert.
4. Show the update date on each card.

How to check it worked

• Notes persist after closing the app.
• Search narrows the list as you type.
• All SQL uses ? parameters.