Build a MySQL notes application

Let’s finish the course with one runnable project. Every lesson so far contributed one piece: accounts, grants, schema, transactions, safe queries, and backups. This lab connects them, and each step includes a check that proves it worked.

As the administrator, create the database and accounts using the commands from the privileges module. You need notes_app for row access and notes_migrator for schema changes. Then connect as notes_migrator and create the schema:

USE notes_app;

CREATE TABLE notes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  body TEXT,
  estimated_hours DECIMAL(5,2),
  remind_at DATETIME,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX notes_title_idx (title)
) ENGINE=InnoDB;

CREATE TABLE tags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE note_tags (
  note_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (note_id, tag_id),
  CONSTRAINT note_tags_note_fk
    FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
  CONSTRAINT note_tags_tag_fk
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB;

If CREATE TABLE returns an access-denied error here, you are connected as the wrong account. Run SELECT CURRENT_USER(); and fix the session before continuing.

Now connect as notes_app. Confirm that inserts work and schema changes fail:

INSERT INTO notes (title, estimated_hours)
VALUES ('Plan the week', 1.50);

SELECT id, title, estimated_hours, created_at
FROM notes;

ALTER TABLE notes ADD COLUMN should_fail INT;

The SELECT must return the row you just inserted. The ALTER TABLE must return an access-denied error like ERROR 1142 (42000): ALTER command denied to user 'notes_app'@'localhost'. That failure is the point: a leaked application credential cannot rewrite your schema.

Try one more deliberate failure. Insert into note_tags with a tag_id that does not exist. InnoDB rejects it with ERROR 1452, a foreign key constraint failure. The schema, not application discipline, protects the relationship.

Run the optional Node.js track with the bounded pool and a parameterized lookup:

const [rows] = await pool.execute(
  'SELECT id, title FROM notes WHERE title = ?',
  ['Plan the week']
)

console.log(rows)
await pool.end()

Inspect the lookup with EXPLAIN, then perform one expand-and-contract migration as notes_migrator.

Finally, dump notes_app, restore it into notes_restore, run CHECK TABLE, and query Plan the week from the restored database. The lab is complete only when the restricted account, application query, migration, and restore all work.