Build a MySQL notes application

Let’s finish the course with one runnable project.

As the administrator, create the database and accounts using the commands from the privileges module. 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;

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 ALTER TABLE must return an access-denied error.

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.