Making a note-taking app: day 2, in which we add a database

In the previous post I started building my app by creating a basic user interface. But I couldn't stop thinking about data persistence.
Should I save notes as files? Should I use a database? Does it make sense to use a database if I only want the data to exist on the user's phone? What if I want the option to upload it to the cloud later?
As a developer, I feel more comfortable making queries to a database, and my data has a structured format - pages belong to notebooks, notes belong to pages, and each has metadata. This would make it a good fit for a relational database. And I don't want to deal with opening and editing files. But I still had doubts: isn't it natural to save your notes as Markdown files, like Obsidian does?
In the end, I settled on a database. Since my app is inspired by messaging app interfaces, any individual notes would be short, so I might end up with a lot of Markdown files. Surely opening and editing files adds some overhead, and databases are designed to let you quickly access your data, make queries and update your data quickly. Does this reasoning sound vague? Ultimately, the sooner I made the decision, the sooner I could build something and figure out if it can work!

So I started by installing expo-sqlite, and adding this file to deal with creating (and resetting) the database:

import * as FileSystem from "expo-file-system";
import * as SQLite from "expo-sqlite";

const DB_NAME = "notes.db";

let db: SQLite.SQLiteDatabase;

export async function initDatabase() {
  if (__DEV__) {
    await resetDatabaseInDev();
  }

  db = SQLite.openDatabaseSync(DB_NAME);
  await setupSchema();
  return db;
}

async function resetDatabaseInDev() {
  const dir = `${FileSystem.documentDirectory}SQLite`;
  const dbFile = `${dir}/${DB_NAME}`;
  try {
    const fileInfo = await FileSystem.getInfoAsync(dbFile);
    if (fileInfo.exists) {
      await FileSystem.deleteAsync(dbFile, { idempotent: true });
      console.log("💥 Deleted old DB in dev mode");
    }
  } catch (e) {
    console.warn("Could not delete DB file", e);
  }
}

function setupSchema(): Promise<void> {
  return new Promise((resolve, reject) => {
    db.execAsync(
      `
          CREATE TABLE IF NOT EXISTS notebooks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL
          );
          CREATE TABLE IF NOT EXISTS pages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            notebook_id INTEGER,
            content TEXT,
            created_at TEXT,
            updated_at TEXT,
            FOREIGN KEY(notebook_id) REFERENCES notebooks(id)
          );
        `
    )
      .then(() => {
        console.log("✅ Schema created");
        resolve();
      })
      .catch((error) => {
        console.error("❌ Schema creation error:", error);
        reject(error);
      });
  });
}

export function getDb() {
  if (!db) throw new Error("DB not initialized. Call initDatabase() first.");
  return db;
}

Ok, so I let AI generate it for me, and then fixed syntax errors and replaced calls to non-existent functions. All this file does is define some functions that are called from elsewhere in the code.
initDatabase creates notes.db, and I call it in my main _layout.tsx file. (note this line: db = SQLite.openDatabaseSync(DB_NAME), this will open 'notes.db' if it exists, OR create it if it doesn't)
I also want to make sure to recreate the database when reloading the app, because I expect the schema to change a few times during development. (For example, created_at and updated_at shouldn't be TEXT fields, but for the moment I wanted something that works and I don't know off the top of my head what data types exist in SQLite)

The next step was to confirm that I indeed created a database and I am able to connect to it, so I did that using my favorite method: I added two buttons, one that creates a new notebook, and one that queries the list of notebooks and prints the result to the console:

          <Pressable
            onPress={() =>
              getDb().execSync(
                "INSERT INTO notebooks (name) VALUES ('New Notebook')"
              )
            }
          >
            <Text style={{ fontSize: 40 }}>+</Text>
          </Pressable>
          <Pressable
            onPress={() => {
              const res = getDb().getAllSync(
                "SELECT * FROM notebooks"
              )
              console.log(res)
            }
            }
          >
            <Text style={{ fontSize: 40 }}>O</Text>
          </Pressable>

And it worked! Now that we confirmed that (1) a database is indeed created (2) we can connect to it and insert data, we can focus on making the schema reflect what we actually want (not only do we have notebooks and pages, but those pages have notes inside them), and replacing the dummy data on the screen with data from the database. We should probably seed the database with test data as well!

See you next time!