In this Project, you will write an API that can be used to manage Cars stored in a Relational Database and You will also write some SQL statements in the queries.sql
file.
Build a RESTful API for an "cars" resource. The client for this API is a car dealer. Write the statements mentioned below as a SQL Query using the w3schools website.
The critical information for each car is the vin, make, model, and mileage. They also track transmission type (manual, automatic...) and status of the title (clean, salvage...), but this information is not always immediately known. Write the "up" and "down" functions inside the data/migrations/01-make_cars_table.js
migration file to satisfy the following schema:
field | data type | metadata |
---|---|---|
id | unsigned integer | primary key, auto-increments, generated by database |
vin | string | required, unique |
make | string | required |
model | string | required |
mileage | numeric | required |
title | string | optional |
transmission | string | optional |
-
Write the following db access functions inside
api/cars/cars-model.js
using Knex:getAll
resolves to an array of car records (or an empty array)getById
resolves to a car record by the given idcreate
resolves to the newly created car record
-
Write CRUD for the
cars
resource insideapi/cars/cars-router.js
:[GET] /api/cars
returns an array of cars sorted by id (or an empty array if there aren't any).[GET] /api/cars/:id
returns a car by the given id.[POST] /api/cars
returns the created car.
-
Manually test your endpoints with a REST client like
Insomnia
orPostman
to check they are working as expected.
Visit SQL Try Editor at W3Schools.com using Chrome and build the queries below. Once they work copy them to the queries.sql
file at the root of the project.
- Find all customers with postal code 1010. Returns 3 records.
- Find the phone number for the supplier with the id 11. Should be (010) 9984510.
- List first 10 orders placed, sorted descending by the order date. The order with date 1997-02-12 should be at the top.
- Find all customers that live in London, Madrid, or Brazil. Returns 18 records.
- Add a customer record for "Yusuf Ali", the contact name is "Yusuf Ali" the address is "123 Somali Street" in "Somalia", postal code "111" and the country is "Somalia".
- Update Yusuf Ali record so that the postal code changes to "11122".
Clicking the Restore Database
button in the page will repopulate the database with the original data and discard all changes you have made.
- Add seed data to the database using
knex seeds
- Add
[PUT]
and[DELETE]
operations to your API. - Write a schema file for a
sales
table. This table should track information on the sale of each car. You may wish to researchforeign keys
in order to link each sale to the entry incars
which sold.