Build a small notes database

Let’s finish the course by building one database from an empty directory. Every step is something you’ve done in an earlier lesson; the point is doing them in order, the way a real project would.

Open notes.db, enable foreign keys, and create the schema:

sqlite3 notes.db

PRAGMA foreign_keys = ON;

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT,
  archived INTEGER NOT NULL DEFAULT 0
    CHECK (archived IN (0, 1)),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

CREATE TABLE tags (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
) STRICT;

CREATE TABLE note_tags (
  note_id INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
  tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (note_id, tag_id)
) STRICT;

Notice the schema carries its own rules: STRICT types, a CHECK that keeps archived boolean-shaped, a UNIQUE tag name, and cascading deletes so removing a note cleans up its links. None of this depends on application code behaving.

Insert related data as one transaction:

BEGIN IMMEDIATE;
INSERT INTO notes (title, body)
VALUES ('Plan the week', 'Pick the three important tasks');
INSERT INTO tags (name) VALUES ('planning');
INSERT INTO note_tags (note_id, tag_id)
SELECT notes.id, tags.id
FROM notes, tags
WHERE notes.title = 'Plan the week'
  AND tags.name = 'planning';
COMMIT;

The three inserts describe one fact — a tagged note — so they belong in one transaction. If the third insert failed, a ROLLBACK would leave no half-linked data behind. BEGIN IMMEDIATE claims the write lock up front, so a competing writer surfaces now rather than mid-transaction.

Inspect a real lookup before adding an index:

EXPLAIN QUERY PLAN
SELECT id, title FROM notes WHERE title = 'Plan the week';

You should see SCAN notes — a full table walk. Now add the index and run the plan again:

CREATE INDEX notes_title_idx ON notes(title);

The second plan should report a search using notes_title_idx instead of a table scan. That before-and-after check is the habit that keeps you from collecting indexes that do nothing.

Create a live backup:

.backup notes-backup.db

Leave with .quit, then open the restored copy:

sqlite3 notes-backup.db

This is the step most people skip: actually opening the backup. Finish with these checks:

PRAGMA foreign_keys = ON;
PRAGMA integrity_check;
PRAGMA foreign_key_check;
SELECT notes.title, tags.name
FROM notes
JOIN note_tags ON note_tags.note_id = notes.id
JOIN tags ON tags.id = note_tags.tag_id;

You are done when integrity_check returns ok, foreign_key_check returns no rows, and the final query returns Plan the week|planning.

If any check fails, the backup is the suspect, not the original — repeat the .backup while the source database is quiet and verify again. And if the join returns nothing, re-run it against notes.db: an empty result there means the transaction never committed, an empty result only in the copy means you backed up before the commit.

That’s the full loop: schema with rules, transactional writes, evidence-based indexing, and a backup you proved restores. Every database you ship deserves the same treatment.