Connect the Shop to a Database

An array inside your code forgets everything when the app stops. A database keeps data in a file instead. From now on the shop's products live in the file shop.db, in the products table, and Node's built-in node:sqlite module lets your app talk to it.

You open a connection to the file once, when the app starts. Then db.prepare(...) gets a SQL query ready, and .all() runs it and gives every row it found, as an array. TypeScript can't know what the database sends back, so you tell it the rows' shape with as and a type. Each row is an object: one property for each column you asked for, named like the column. For example, with a table books that has a title and a year:

type Book = { title: string; year: number };

const rows = db.prepare("SELECT title, year FROM books").all() as Book[];
// rows could be [{ title: "Dune", year: 1965 }, { title: "Emma", year: 1815 }]
const first = rows[0];
console.log(first.title, first.year);   // Dune 1965

An API answers with objects whose keys say what each value is, so you build one Product object from each row. A cell that is empty in the database (NULL) arrives in TypeScript as null, which is why the Product type says category: string | null, and JSON writes it as null too.

GET /           →  200  Welcome to the shop!
GET /products   →  200  [{"id": 1, "name": "Mug", "price": 12, "category": "kitchen"},
                         {"id": 2, "name": "Notebook", "price": 7, "category": "office"},
                         … 8 products in all …
                         {"id": 8, "name": "Gift card", "price": 50, "category": null}]

Your task: connect the app to shop.db and make GET /products read the products from the products table, turning each row into a Product with the keys id, name, price and category.

Assignment
Follow the Coding Tutorial to open the connection, run the query and turn its rows into the array of products.

Hint
Each row has one property for each column of your SELECT, named like the column: row.name is the value of the name column. Add as Product[] after .all() so TypeScript knows the rows' shape.