Practice using a SQLite database to power a Node/Express web app.
Make sure you have Git and Node (v18) installed.
- Use this template, clone your copy, cd into it
- Run
npm installto install all the dependencies - Run
npm run seedto seed the database with some example data - Run
npm run devto start the server.
This uses thenodemonlibrary to auto-restart the server when you save changes.
This app already has the routes and templates created. Your job is to fill out the model functions so the routes can access the data they need.
Note: you won't be able to load pages without errors until you've completed the challenges below.
Each challenge has associated unit tests. You can either run all the tests with npm test, or each individual challenge's tests with npm run test:1, npm run test:2 etc.
Make sure you read test failures carefully—the output can be noisy but the error message should provide useful information to help you.
This app includes a database for a fake business. It includes information on the business' customers, products, orders etc. It's helpful to know the structure of the database before working with it. You can either read database/schema.sql, or expand the sections below.
products
| column | type | constraints |
|---|---|---|
| id | integer | primary key autoincrement |
| product_name | text | not null |
| category_id | integer | references categories(category_id) |
| quantity_per_unit | text | |
| unit_price | numeric | default 0 |
| units_in_stock | integer | default 0 |
| units_on_order | integer | default 0 |
categories
| column | type | constraints |
|---|---|---|
| id | integer | primary key autoincrement |
| name | text | |
| description | text |
Write and export a new function named listProducts in model/products.js. This function should retrieve all products from the products DB table and return the id, name, quantity_per_unit, unit_price, units_in_stock and units_on_order columns.
This function is used in routes/products.js. Once you've finished it you should be able to see a table of all products if you visit the / route in your browser.
Write and export a new function named searchProducts in model/products. This function should take a search string, then return any products in the products table whose name contains that string. Each product should include the id and name columns. For example searchProducts("iscu") should return [{ id: 19, name: "Teatime Chocolate Biscuits" }].
This function is used in routes/search.js. Once you've finished it you should be able to visit /search in your browser and use the search form to browse the products.
Write and export a new function named getProduct in model/products. It should take an ID, then return the matching product from the productstable. It should return theidandname columns.
This function is used in routes/product.js. Once you've finished it you should be able to see a page for one specific product if you visit the /product/:id in your browser (e.g. /product/1, /product/34 etc).
Amend the query used in getProduct to also include columns called category_name and category_description. You will need to retrieve this info from the categories table.
Once complete you should see this additional info show up on the product pages in your browser (e.g. /product/1).
Amend the query used in listProducts to also calculate the stock_value—the result of multiplying unit_price by units_in_stock for each product. Try to do this without writing any JavaScript.
The currencies in the products table are just normal numbers, with an arbitrary number of decimal places. It would be more user-friendly to format these as currency values rounded to 2 decimal places. Use the built-in SQLite format function to format the unit_price and stock_value columns as 2-decimal-place GBP (e.g. £2.57).