Build a small notes database

Let’s finish the course by building one database from an empty directory.

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;

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;

Inspect a real lookup before adding an index:

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

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.

Create a live backup:

.backup notes-backup.db

Leave with .quit, then open the restored copy:

sqlite3 notes-backup.db

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.