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. 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:

const rows = db.prepare("SELECT title, year FROM books").all();
// 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 JavaScript as 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 an object 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.