The Ticket, and Its First Slice in JavaScript

A new ticket arrives: "Customers can save products for later." You'll ship it in slices, one layer at a time: the database, the API, the page's data, the page, and a test. This lesson is the first slice, in server/db.js.

The shop's database changes through migrations: numbered changes that each run once. migrate() reads the database's version from the schema_version table, runs every change with a higher number, and records each new number with setVersion. A change number 4 for a coupons table would look like this:

if (version < 4) {
  db.exec("CREATE TABLE coupons (code TEXT PRIMARY KEY, percent INTEGER NOT NULL)");
  setVersion(4);
}

The wishlist needs a table wishlist_items with user_id, product_id and added, the time of the save, which SQLite fills in by itself. Its primary key is the pair (user_id, product_id), so each customer can save each product only once.

The check prints the table's columns, starts the server a second time on the same shop.db, and saves Ana's Teapot twice, straight into the table. It must print:

> the columns of wishlist_items
  name | type | notnull | dflt_value | pk
  user_id | INTEGER | 1 | NULL | 1
  product_id | INTEGER | 1 | NULL | 2
  added | TEXT | 1 | CURRENT_TIMESTAMP | 0
> the rows of schema_version
  1, 2, 3
> the server starts again: db.js runs migrate() on the same shop.db
  it starts
> the rows of schema_version
  1, 2, 3
> Ana saves the Teapot twice, straight into the table
  INSERT INTO wishlist_items (user_id, product_id) VALUES (1, 6) -> 1 row
  INSERT INTO wishlist_items (user_id, product_id) VALUES (1, 6) -> error: UNIQUE constraint failed: wishlist_items.user_id, wishlist_items.product_id

Your task: in server/db.js, below the cart's migration, add migration 3: create the table wishlist_items and record version 3.

Assignment
Follow the Coding Tutorial to write the wishlist's migration in server/db.js: an if for version 3, the CREATE TABLE inside it, and setVersion(3).

Hint
Copy the shape of the cart_items migration. Without setVersion(3), the second start runs the CREATE TABLE again and fails with "table wishlist_items already exists".