- Revision from last week
- Recap integration of cyf_hotels DB with NodeJS
- CRUD operations with NodeJS and PostgreSQL
By the end of this lesson trainees should be able to
- Create an API endpoint that can add data to a database
- Write code that effectively validates data before adding it to a database
- Be aware of the dangers of SQL injection attacks, and how to avoid them
- Create an API endpoint that can retrieve data from a database with multiple parameters
- Create an API endpoint that can update data in a database
- Create an API endpoint that can delete data in a database
For this class, we will use the tables and data from
cyf_hotels_exercise5.sql. To start from a clean state for your
cyf_hotels database, run
psql -d cyf_hotels -f cyf_hotels_exercise5.sql.
During the last class, we created a new NodeJS project called
cyf-hotels-api with a single API endpoint
/hotels to get the list of all hotels. In this class, we will add other endpoints with more functionalities to interact with the
"The acronym CRUD refers to all of the major functions that are implemented in relational database applications. Each letter in the acronym can map to a standard Structured Query Language (SQL) statement and Hypertext Transfer Protocol (HTTP) method [...]." - Wikipedia
In the following, we will add a new API endpoint to create a new hotel in the table
hotels of the
cyf_hotels database. As a reminder, here is an example of a SQL insert statement to add a new hotel:
As we create a new record in the database, we will add a new POST endpoint in the
cyf-hotels-api project from last class. Moreover, we need to be able to pass some parameters to this API endpoint such as the hotel name, the number of rooms and the postcode, so we can use this API to create a different hotel. These parameters can be sent in the body of the request. To access the parameters in the body of the request with Express.JS, we need add the following line in the
server.js file of the
This means the body will be automatically parsed from a string into a JSON object. We can finally add our new endpoint to create a new hotel:
What could go wrong with the code above? There is no validation of any user inputs which could result in errors, duplications or inconsistent data in the database! Here are few examples of things we can verify before creating the hotel in the database:
- Check that the number of rooms is a positive number
- Check that no hotel with the same name already exists in the database
Let's start by validating that the number of rooms is a positive number and if it doesn't, return an error.
Then we can validate the new hotel doesn't already exist in the database, thus preventing duplicate data.
- Follow the above steps to create a new POST endpoint
/hotelsto create a new hotel. Make sure to add validation for the number of rooms and the hotel name. Test your new API endpoint with Postman and check that the new hotel has been correctly created in your database.
- Add a new POST API endpoint to create a new customer in the
- Add validation to check that there is no other customer with the same name in the customers table before creating a new customer.
We already have one GET endpoint to load all the hotels in the database. However, we can improve this endpoint and add a couple of extra functionalities. First, we may want to order the list of hotels by name:
Another functionality which could be useful is to filter the hotel with a keyword to be able to search for a specific hotel name:
In some case, you would want to load only a specific hotel by id. Let's define a new GET endpoint to load one specific hotel:
- Add the GET endpoints
/hotels/:hotelIdmentioned above and try to use these endpoints with Postman.
- Add a new GET endpoint
/customersto load all customers ordered by name.
- Add a new GET endpoint
/customers/:customerIdto load one customer by ID.
- Add a new GET endpoint
/customers/:customerId/bookingsto load all the bookings of a specific customer. Returns the following information: check in date, number of nights, hotel name, hotel postcode.
We can now implement an endpoint to update a customer record in the database. For this, we will use a PUT endpoint.
What can go wrong in the code above? Again, there is no validation! We could set an empty email or even a string which is not following the format of an email. Remember, validating data is very important to make sure you don't end up with inconsistent data in your database!
- Add the PUT endpoint
/customers/:customerIdand verify you can update a customer email using Postman.
- Add validation for the email before updating the customer record in the database. If the email is empty, return an error message.
- Add the possibility to also update the address, the city, the postcode and the country of a customer. Be aware that if you want to update the city only for example, the other fields should not be changed!
To delete a record from the database, we will use a DELETE endpoint:
However, if you try to delete a customer which already has some bookings, the previous endpoint will fail. Do you know why? You cannot delete a customer whose ID is used as a foreign key in another table (in this case, in the
bookings table). Let's delete all the customer bookings first:
- Add the DELETE endpoint
/customers/:customerIdabove and verify you can delete a customer along their bookings with Postman.
- Add a new DELETE endpoint
/hotels/:hotelIdto delete a specific hotel.
Note: A hotel can only be deleted if it doesn't appear in any of the customers' bookings! Make sure you add the corresponding validation before you try to delete a hotel.
All of the coursework for this week can be found here.
Please spend two minutes reviewing this lesson to help us improve it for the future. This feedback will be shared with volunteers.