Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 33 ยท Projects

Project: A Notes App Database

Build the database behind a notes app in SQLite: notes, tags and a many-to-many link table, search queries, soft delete and trigger-based timestamps.

Nearly every app that stores user content needs the same three ideas: records that can be edited, labels that can be attached to many records at once, and a way to find things again. A notes app is the smallest honest example. In this project you design the whole database for one, with a notes table, a tags table, a link table between them, searches, a "trash" that can be restored, and triggers that keep updated_at correct no matter which code edits a note.

You will use ideas from earlier lessons: STRICT tables, foreign keys, views and triggers and joins. Every step runs live, and you can edit each example freely.

What we are building

notes Title, body, timestamps.
tags Unique labels.
note_tags Links notes to tags.

Many notes can share many tags.

1 / 2

Step 1: the schema

A note has a title and a body. Tags are separate rows, because a label like work is shared by many notes and should be stored (and renamed) in one place. Since one note has many tags and one tag has many notes, the relationship needs a link table (also called a junction table) with one row per pairing.

Design decisions in the schema below:

  • STRICT tables make SQLite enforce column types.
  • Timestamps are ISO-8601 text from datetime('now'), which sorts correctly as text (see Dates, Times and Booleans).
  • deleted_at is NULL for live notes and holds a time for trashed ones (soft delete).
  • Tag names are UNIQUE and compared with COLLATE NOCASE, so Work and work are one tag.
  • The link table's primary key is the pair (note_id, tag_id), which makes duplicate links impossible, and it is a WITHOUT ROWID table because it has no need for a separate rowid.
  • A second index on tag_id makes "all notes with this tag" fast; the primary key already covers "all tags of this note".
SQL Create the schema and add sample notes

Press Run (or Ctrl + Enter) to see the result here.

You should see 4 notes, 4 tags and 6 links. In an application, the link rows are usually written in the same transaction as the note (see Transactions), so a note is never saved without its tags.

Step 2: reading notes with their tags

The most common screen shows a list of notes, each with its tags on one line. That is a join plus group_concat, which folds many rows into one string. Putting the tags in a sorted subquery first makes the order stable.

SQL Notes with their tags on one line

Press Run (or Ctrl + Enter) to see the result here.

Query 2 matched MONEY against the stored money because of COLLATE NOCASE on the column. Query 3 is the classic "must have all of these" pattern: filter to the wanted tags, group per note and require the group to contain as many distinct tags as you asked for. Query 4 uses LEFT JOIN so that a tag with no notes still appears with a zero, instead of vanishing.

Step 3: searching

For a small app, LIKE is a perfectly good search. It is case-insensitive for ASCII letters by default, and % matches any run of characters. Search both columns, and remember to hide trashed notes.

SQL Search title and body, plus a tag filter

Press Run (or Ctrl + Enter) to see the result here.

Both notes mention "budget" (one in the title, one in the body); only "Budget plan" also has the home tag. LIKE '%word%' cannot use an index, so it scans every note; that is fine for thousands of notes. When you outgrow it, Full-Text Search with FTS5 is the upgrade path. In real code the search text is a bound parameter (see Parameters and SQL Injection), and % or _ typed by the user need escaping with an ESCAPE clause if they should match literally.

Step 4: soft delete

Users expect a trash can. Instead of DELETE, a soft delete sets deleted_at. Restoring is setting it back to NULL, and a scheduled "empty trash" job removes rows trashed long ago. To avoid forgetting deleted_at IS NULL in every query, wrap it in a view.

SQL Trash, restore and purge

Press Run (or Ctrl + Enter) to see the result here.

The first line shows 2 live notes, since the shopping list and the book ideas survive. After the restore there are 3. The purge deletes only "Old draft" (trashed in January); its tag link goes with it because ON DELETE CASCADE is declared and PRAGMA foreign_keys = ON was set. That pragma is off by default in SQLite and is per connection, so an app must turn it on every time it connects.

One cost of soft delete: a UNIQUE column such as a title or email stays taken by trashed rows. If that matters, make the uniqueness a partial index, CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL.

Step 5: triggers that keep updated_at honest

Application code forgets things. A trigger does not. This AFTER UPDATE trigger sets updated_at whenever the title or body changes. It fires only for those two columns (UPDATE OF title, body), so trashing a note does not count as editing it, and the trigger's own update to updated_at does not call itself again.

SQL An updated_at trigger

Press Run (or Ctrl + Enter) to see the result here.

The first query returns 1 (the trash did not touch updated_at) and the second returns 1 (the edit did). The exact time written by datetime('now') differs on every run, which is why the example compares instead of printing it. SQLite triggers cannot assign to NEW directly, so the standard approach is an AFTER UPDATE trigger that runs a second UPDATE on the same row.

What to take further

You now have a complete schema that handles tagging, search, restore and timestamps. Natural extensions are a notebooks table (one-to-many from notebooks to notes), pinning with an is_pinned column, full-text search over title and body, and a version history table filled by another trigger. Each one reuses a pattern from this page.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does a notes app need a separate note_tags table?

    A many-to-many relationship is modelled with a table that holds one row per note and tag pair.
  2. 2. What makes it impossible to link the same tag to the same note twice?

    A primary key must be unique, so the pair of ids can appear only once.
  3. 3. How do you find notes that carry ALL of a given set of tags?

    IN alone matches notes with any of the tags. Grouping and counting distinct matches proves every tag is present.
  4. 4. Why does the updated_at trigger use AFTER UPDATE OF title, body?

    Limiting the trigger to those columns means a change to deleted_at or to updated_at itself does not fire it.
  5. 5. Cascading deletes on note_tags do nothing in your app. What is the most likely cause?

    Foreign key enforcement is off by default and is a per-connection setting, so it must be enabled each time you connect.

Summary

  • Model tags with a tags table and a note_tags link table whose primary key is the pair of ids.
  • Use group_concat for tag lists, LEFT JOIN to include unused tags, and HAVING count(DISTINCT ...) for "has all of these tags".
  • LIKE is enough for small searches; FTS5 is the upgrade.
  • Soft delete with a nullable deleted_at, a view that hides trashed rows, and a purge job that relies on cascades (with foreign_keys on).
  • An AFTER UPDATE OF trigger keeps updated_at correct for every writer.

Next: Project: Personal Budget Tracker, where constraints and window functions do the heavy lifting.