Change the Table: Migrations

The shop wants to give products a discount, and the table products has no column for it. A change to the structure of the tables is a migration. ALTER TABLE changes a table that already holds rows, and a DEFAULT fills the new column in for every one of them.

A migration must happen exactly once: the app starts again and again, and a database that already has the column must not get it twice. Some statements are safe to repeat, others are not:

db.exec("CREATE TABLE IF NOT EXISTS notes (text TEXT NOT NULL)");  // the 2nd time: does nothing
db.exec("ALTER TABLE customers ADD COLUMN phone TEXT");  // the 2nd time: error, duplicate column name

So the database keeps a small table of its own, schema_version, with one row per change made. When the app starts, it looks for the row of each change: if the row is missing, it makes the change and records it. Two check routes help: /check/schema shows the columns of products and the versions recorded, and /check/migrate-again runs migrate() once more, as the next start of the app would.

GET /products/1  →  200  {"id": 1, "name": "Mug", "price": 12, "category": "kitchen", "stock": 10, "discount": 0}
GET /check/schema  →  200  {"columns": ["id", "name", "price", "category", "stock", "discount"], "versions": [1]}
GET /check/migrate-again  →  200  {"columns": ["id", "name", "price", "category", "stock", "discount"], "versions": [1]}
POST /products  {"name": "Vase", "price": 18, "category": "home"}  →  201  {"id": 9}
GET /products/9  →  200  {"id": 9, "name": "Vase", "price": 18, "category": "home", "stock": 0, "discount": 0}
GET /products?sort=price&order=desc&per_page=2  →  200  {"products": [{"id": 8, "name": "Gift card", …, "discount": 0}, {"id": 3, "name": "Lamp", …, "discount": 0}], "total": 9, "next_page": 2}
GET /check/migrate-again  →  200  {"columns": [… the same 6 …], "versions": [1]}

The type Product at the top of your file doesn't list discount, and that's fine: a type is only what TypeScript knows while it checks your code, and res.json sends the whole row the database gives.

Your task: write a function migrate(), with the return type void, and call it when the app starts. It creates schema_version if needed and, only if version 1 isn't recorded yet, adds the column discount (a whole number, 0 by default) to products and records version 1.

Assignment
Follow the Coding Tutorial to write migrate(), call it at start-up, and add the discount column exactly once.

Hint
.get() answers undefined when no row matches: that is your sign that change 1 wasn't made yet. Record the version inside the same if as the ALTER TABLE.