Use the tables and data from your CYF_hotel database. If you need to start from a clean state for your database, run psql -d CYF_hotel -f build_hotel.sql.

Earlier, we created a new NodeJS project called CYF-hotels-api with a single API endpoint /customers to get the list of all customers. Now, we will add other endpoints with more functionalities to interact with the CYF_hotel database.

You should have a server.js file that looks something like this:

const express = require("express");
const app = express();
const { Pool } = require("pg");

const db = new Pool({
  user: "keith",
  host: "localhost",
  database: "CYF_hotel",
  password: "",
  port: 5432,
});

app.get("/customers", function (req, res) {
  db.query("SELECT * FROM customers")
    .then((result) => {
      res.status(200).json({ customers: result.rows });
    })
    .catch((err) => {
      console.log(err);
    });
});

app.listen(3000, function () {
  console.log("Server is listening on port 3000. Ready to accept requests!");
});

Before we move on, run the server.js file in node and check the endpoint using your browser.

Using Postman 👩🏽‍✈️👨🏾‍✈️Code along : Retrieving individual records

Using Postman to Test an API

You should have Postman already installed on your system. If not then please install it now.

Run Postman (it can be slow to start).

Check the method is GET, set the URL for your customers endpoint then click Send.

Returning all the customers is fine for a small table (even the 130+ customers) but if we have a large number of rows, thousands or millions, then that approach is very clumsy.

If we know the value of the id column for the customer we can retrieve just that one row (we know there is only one row because id is the Primary Key).

Create a new endpoint to get the customer by using the id value:

app.get("/customers/:id", function (req, res) {
  // TODO - add code here
});

Here the endpoint includes :id, which identifies an extra parameter in the URL that will provide the id value, as in http://localhost:3000/customers/3. We just need a way to get the value from the URL.

Express provides a simple way to get such parameters from the request:

const custId = parseInt(req.params.id);

Note that the id in req.params.id must match the name after the colon in the endpoint "customers/:id".

Next we need to query the customers table and provide the value of the id into a WHERE clause so that we retrieve only the one row that matches:

db.query("SELECT * FROM customers WHERE id = $1", [custId])
  .then((result) => {
    console.log(result);
  })
  .catch((err) => {
    console.log(err);
  });

In the above code notice that:

• The SELECT now uses a WHERE clause
• The value we are wanting to look for appears as $1 - this is a placeholder for the ‘id’ value
• The second parameter is the array [custId] that provides the id value

Let’s complete the endpoint to return the retrieved values:

app.get("/customers/:id", function (req, res) {
  const custId = parseInt(req.params.id);
  db.query("SELECT * FROM customers WHERE id = $1", [custId])
    .then((result) => {
      console.log(result.rows);
    })
    .catch((error) => {
      console.log(error);
    });
});

In db.query you can use placeholders $1, $2, … $9, $10, $11, … etc to mark the place where a parameter value should be used. The parameters to replace the placeholders are supplied in the second argument, the array of values. In this case there is only one value (but it must still be put into an array) so we have [custId] as the replacement value for the first placeholder, $1. If there is more than one placeholder there must be the same number of array elements and they must be in the order of the placeholder numbers.

String Placeholders

With String placeholders you don’t put apostrophes around the placeholder:

app.get("/customers/by_city/:city", (req, res) => {
  const cityName = req.params.city;
  db.query("SELECT * FROM customers WHERE city LIKE $1 || '%'", [cityName])
    .then(() => {})
    .catch((err) => {});
});

Use Placeholders to Avoid SQL Injection

What is SQL Injection

If an end user can enter unrestricted text into a data field on a form there is the possibility that they could put a nasty SQL command inside the data. For example, if the field is used to enter the customer name the user could type: J Doe'; delete from customers; select 'x' from customers as the name value. It looks a bit unlikely but any user with a little bit of SQL knowledge could eventually work out the format needed.

If the code used string concatenation to for the final SQL command it could be something like:

const myQuery = `UPDATE customers SET name = '${inputName}' WHERE id =  + ${ID}`;

Then myQuery would become: UPDATE customers SET name = 'J Doe'; DELETE FROM customers; SELECT 'x' FROM customers WHERE id = 123 (Note that semicolon can be used between SQL commands in this context)

By using placeholders instead of string concatenation we can prevent this kind of attack. This is VERY important in web-facing apps that use SQL.

💾 CRUD operations

🌱 Creating data

In the following, we will add a new API endpoint to create a new customer in the table customers of the CYF_hotel database. As a reminder, here is an example of a SQL insert statement to add a new customer:

INSERT INTO customers (name, email, phone, address, city, postcode, country)
  VALUES ('Fred Bloggs', 'fred@bloggs.org', '07123456789', '1 Low Brow',
          'Ashbottom', 'XY2 3ZA', 'UK');

Using body-parser for Form Data

When an endpoint can reasonably expect a large number of data values, for example, when we need to insert a new row into the customers table, then using parameters in the URL is not practical. Instead we can use a ‘middleware’ package such as body-parser, which can extract data from the body of the request.

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 data to this API endpoint such as the name, address, postcode, etcetera, so we can use this API to create different customers. The data can be sent in the body of the request. To access the parameters in the body of the request with Express.JS, we need to add the module body-parser to the CYF-hotels-api project:

At the terminal command line:

npm install --save body-parser

Then include it in the server.js:

const bodyParser = require("body-parser");
app.use(bodyParser.json());

📩 Inserting Rows Using Node.js

We can finally make an endpoint to create a new customer:

app.post("/customers", function (req, res) {
  const newName = req.body.name;
  const newEmail = req.body.email;
  const newPhone = req.body.phone;
  ...
  const newCountry = req.body.country;

  const query =
    `INSERT INTO customers (name, email, phone, address, city, postcode, country)
    VALUES ($1, $2, $3, $4, $5, $6, $7)`;

  db.query(query, [newName, newEmail, ..., newCountry])
    .then(() => {
      res.status(201).send("Created a new customer");
    })
    .catch(err => {
      console.log(err);
    })
});

📮 Using Postman to Test a POST

In Postman:

• Change the method to POST
• Ensure the URL address is your customers endpoint, for example: http://localhost:3000/customers
• Under the address in the Postman window select Body then select raw
• Ensure that JSON is selected at the right hand end
• Enter the data as JSON in the panel below these settings, for example:

{ "name": "Fred Bloggs", "email": "fred@bloggs.org", "phone": "07123456789" }

• Click send

You should see the response “Customer created.”

What Can We Do After an Insert?

• We must start checking for errors in the execution of the SQL. This applies to all commands, not just INSERT.
• If the table has an auto-incrementing primary key we can obtain the value for the new row and return it to the browser. This is often required for subsequent actions in the use case.
• The new data can be logged to the console (although this is not common practice).
• … and so forth …

🧪 Checking for Errors in SQL

The catch block in db.query always has an error parameter. If the passed SQL query throws an error then the callback function passed to the catch method is invoked with an error object generated by node postgres. A typical situation might be:

  db
    .query(...);
    .then(/* do things for success*/);
    .catch((err) => res.status(500).json({error: err}));

👩🏽‍🔬 Validating

What could go wrong with our code so far? 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 customer in the database:

1. Check that the phone number contains only digits, ‘+’, ‘-’, ‘(’ or ‘)’
2. Check that no customer with the same email already exists in the database
3. …

Let’s start by validating that phone number the consists of only digits, +, -, (, ) or space and if it doesn’t, return an error. This check uses a regular expression to replace all valid characters with a ‘0’ (zero) symbol then check the result is made up of only ‘0’ symbols.

if (
  newCustPhone.replace(/[+\-()0-9 ]/g, "0") != // replace all valid chars with 0
  "0".padEnd(newCustPhone.length, "0")
) {
  // compare with all zeros same length as phone
  return res
    .status(400)
    .send("The phone number must only contain 0-9, +, -, (, ) or space.");
}

Note: regular expressions may be new to you but they are very powerful and also quite complicated. We don’t have time to teach you the intricacies of “regex” here but you can find numerous resources on the web and in the MDN documentation.

As you have already learned, this validation can also be performed in the browser because it doesn’t involve any database interaction. This is a faster and less costly (in terms of network traffic) approach and should be used where practical. It makes user correction of mistakes quicker and more natural and can be done on a per item basis.

📨 Check the Customer’s Email

We can also validate the new customer’s email doesn’t already exist in the database, to prevent duplicate data.

app.post("/customers", function (req, res) {
  const newName = req.body.name;
  const newEmail = req.body.email;
  const newPhone = req.body.phone;
  // et cetera ...

  //
  // Validate the new customer's email address by querying the existing customers table
  // to return any rows that contain the same values
  //
db.query("SELECT 1 FROM customers WHERE email=$1", [newEmail])
  .then((result) => {
    if (result.rowCount > 0) {      // note the use of result.rowCount
      return Promise.reject({error: 'Customer already exists'})
    } else {
      return db.query(`INSERT INTO customers (name, email, phone, address, city, postcode, country)
        VALUES ($1, $2, $3, $4, $5, $6, $7)`, [newName, newEmail, ..., newCountry],
    }
  })
  .then(() => {
    res.status(201).send("Customer created")
  })
  .catch((error) => {
    // any errors or manual rejections will be intercepted here
    // handle error here
  })

Note:

• The SELECT does not choose any column values, just the literal 1. We are only interested in the number of rows found (it must be 0 for success).
• If a row is found, then a customer with this email exists so we call Promise.reject: this will then trigger the first catch block in the promise chain where we can handle the error.
• If a row is not found, then we perform the insertion query, taking care to return the call to db.query so we can access its resolve value in the next .then block
• This validation is NOT suitable for performing in the browser because it interacts with the database.

🗝️ Return the Primary Key Value for an Insert

If the table uses an auto-incrementing primary key then it is very useful to return the generated value after the INSERT. PostgreSQL provides a very simple mechanism to do this (as do most RDBMS’s) by appending the RETURNING clause to the INSERT, as below:

INSERT INTO customers (name, email, phone)
  VALUES ('Fred Bloggs', 'fred@bloggs.org', '07123456789')
  RETURNING id;

The endpoint now uses the query call shown below:

  db.query(`INSERT INTO customers (name, email, phone, address, city, postcode, country)
    VALUES ($1, $2, $3, $4, $5, $6, $7)
    RETURNING id`, [newName, newEmail, ..., newCountry])
    .then(result => {
        const newId = result.rows[0].id;
        console.log(`New Customer id = ${newId}`);
        res.status(200).json({lastId: newId});
    })
    .catch(err => {
      res.status(500).json({error: err});
    })

🆕 Updating data

We can now implement an endpoint to update a customer record in the database. For this, we will use a PUT endpoint.

app.put("/customers/:id", function (req, res) {
  const custId = req.params.id;
  const newEmail = req.body.email;
  const newPhone = req.body.phone;

  db.query("UPDATE customers SET email=$2, phone = $3 WHERE id=$1", [
    custId,
    newEmail,
    newPhone,
  ])
    .then(() => res.send(`Customer ${customerId} updated!`))
    .catch((err) => {
      console.error(err);
      res.status(500).json({ error: err });
    });
});

Note:

• This endpoint uses both a URL parameter (:id) to supply the customer id and data in the request body to provide the updated column values.
• The order of the placeholders does not have to correspond to their sequence in the SQL. In this case we’ve put the custId value first but it appears last in the SQL (in the WHERE clause).

We used URL parameters to query for a specific customer earlier and we used body data to insert a new customer.

What can go wrong in the code above? Again, there is no validation! The user could try to 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!

🗑️ Deleting data

To delete a record from the database, we will use a DELETE endpoint:

app.delete("/customers/:id", function (req, res) {
  const custId = req.params.id;

  db.query("DELETE FROM customers WHERE id=$1", [custId])
    .then(() => res.send(`Customer ${custId} deleted!`))
    .catch((err) => console.error(err));
});

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 reservations table). Let’s delete all the customer bookings first:

app.delete("/customers/:customerId", function (req, res) {
  const customerId = req.params.customerId;

  db.query("DELETE FROM reservations WHERE cust_id=$1", [customerId])
    .then(() => {
      return db.query("DELETE FROM customers WHERE id=$1", [customerId]);
    })
    .then(() => res.send(`Customer ${customerId} deleted!`))
    .catch((e) => console.error(e));
});

⚠️ Warning!

Optimistic Locking

The solution is known as “optimistic locking” but we just don’t have time in this course to cover it. So long as you are aware of the problem you can use the approach but add plenty of comments to make clear that the code is not yet safe.

Note also that optimistic locking requires changes not only to the code of your Node endpoints but also to the behaviour of the web pages that use and modify the data.