Database 2: More SQL and integration with NodeJS
Teaching this lesson?
Read the Mentors Notes here
What will we learn today?
- Revision from last week
- More SQL
- Changing the definition of a table
- Dropping a table
- Updating a row
- Deleting a row
- Join tables
- Other useful operations
- Integration with NodeJS
- Introduction to node-postgres
- Loading data from a database with a GET endpoint
- Homework
Learning Objectives
- Add and remove columns in a pre-existing table using PostgreSQL using
ALTER
- Rename tables and columns in a pre-existing table using PostgreSQL using
DELETE
- Update rows in a pre-existing table using PostgreSQL using
UPDATE
- Combine tables together using PostgreSQL using
INNER JOIN
- Connect a PostgreSQL database to a NodeJS application
- Retrieve data from a PostgreSQL database in a NodeJS application
More SQL
For the following, use the file cyf_hotels_exercise5.sql
from the previous class to reinitialise your database with psql -d cyf_hotels -f cyf_hotels_exercise5.sql
.
Changing the definition of a table
Sometimes, you may need to change the definition of a table you created before without deleting it. Such changes include renaming a table, adding/removing a column, changing the name of a column, changing the type of a column etc... The general syntax to perform these operations is:
ALTER TABLE table_name action;
For example, to add a new column to the existing customers
table:
ALTER TABLE customers ADD COLUMN date_of_birth DATE;
To delete an existing column from the customers
table:
ALTER TABLE customers DROP COLUMN date_of_birth;
To rename the table customers
into clients
:
ALTER TABLE customers RENAME TO clients;
For more examples, you can consult the following tutorial: Postgres alter table.
Exercise 1
- Add a column
date_of_birth
of typeDATE
in thecustomers
table. - Rename the column
date_of_birth
tobirthdate
in thecustomers
table. - Delete the column
birthdate
from thecustomers
table
Dropping a table
To delete the table customers
:
DROP TABLE customers;
Exercise 2:
- Create a new table
test
- Drop the table
test
Updating a row
The general construction to update a row is:
UPDATE table SET column1 = value1, column2 = value2 WHERE condition;
For example, to update the name and country of the customers with ID 3:
UPDATE customers SET name='Bob Marley', country='Jamaica' WHERE id=3;
Exercise 3
- Update the postcode of the hotel named
Elder Lake Hotel
toL10XYZ
- Update the number of rooms of
Cozy Hotel
to25
- For the customer named
Nadia Sethuraman
, update her address to2 Blue Street
, her city toGlasgow
and her postcode toG11ABC
in one query - Update all the bookings of customer with ID
1
for the hotel with ID1
to5
nights in one query
Deleting a row
The syntax to delete a row is:
DELETE FROM table WHERE condition;
For example, to delete the booking with ID 4:
DELETE FROM bookings WHERE id=4;
NOTE: If you don't supply a WHERE
clause with DELETE
or UPDATE
the command will be applied to all the rows in the table which is rarely what you want.
Exercise 4
- Delete the booking of customer ID
8
for the date2020-01-03
- Delete all the bookings of customer ID
6
- Delete the customer with ID
6
Joining tables
Sometimes, you will need to retrieve data which are spread in different tables in a single response. For this purpose, you will need to join tables together. The general syntax is:
SELECT A.column1, B.column2 FROM A INNER JOIN B ON A.b_id=B.id;
For example, to load all the bookings along with customer data:
SELECT * FROM customers INNER JOIN bookings ON customers.id=bookings.customer_id;
To load all the bookings along with customer data and hotel data:
SELECT * FROM bookings
INNER JOIN customers ON customers.id=bookings.customer_id
INNER JOIN hotels ON hotels.id=bookings.hotel_id;
To load the booking checkin dates for customer ID 1
along with the customer name and the hotel name:
SELECT bookings.checkin_date,customers.name,hotels.name FROM bookings
INNER JOIN customers ON customers.id=bookings.customer_id
INNER JOIN hotels ON hotels.id=bookings.hotel_id
WHERE customers.id=1;
Exercise 5
- Try and understand each of the queries above in your
psql
prompt - Retrieve all the bookings along with customer data for bookings starting in 2020
- Retrieve the customer names, booking start dates and number of nights for all customers who booked the hotel name
Jade Peaks Hotel
- Retrieve all the booking start dates with customer names and hotel names for all bookings for more than 5 nights
Other useful operations
Ordering the result:
SELECT * FROM table ORDER BY column;
This will sort the returned rows in the ascending order for "column". To sort them in descending order, use:
SELECT * FROM table ORDER BY column DESC;
Limiting the number of results returned:
SELECT * FROM table LIMIT 10;
Returning all customers whose ID is 1, 2, 3 or 4:
SELECT * FROM customers WHERE id IN (1,2,3,4);
Query by pattern matching, for example retrieve all customers whose name starts with Bob:
SELECT * FROM customers WHERE name LIKE 'Bob%';
You can combine different operations together, for example, if you want to retrieve all the booking start dates with the customer names and hotel names for customer names starting with the letter M
ordered by hotel name with a limit of 3 results:
SELECT bookings.checkin_date,customers.name,hotels.name FROM bookings
INNER JOIN customers ON customers.id=bookings.customer_id
INNER JOIN hotels ON hotels.id=bookings.hotel_id
WHERE customers.name LIKE 'M%'
ORDER BY hotels.name
LIMIT 3;
Exercise 6
- Retrieve all customers whose name starts with the letter
S
- Retrieve all hotels which have the word
Hotel
in their name - Retrieve the booking start date, customer name, hotel name for the top 5 bookings ordered by number of nights in descending order
Integration with NodeJS
Introduction to node-postgres
"node-postgres is a collection of node.js modules for interfacing with your PostgreSQL database." - https://node-postgres.com/
In the following, we will use node-postgres to...
- Connect to a database
- Send SQL query to the database and get results
Loading data from a database with a GET endpoint
Let's build a brand new NodeJS application with a single GET endpoint to load the list of hotels that you already have in the hotels
table of the cyf_hotels
database.
First, create a new NodeJS application that we will call cyf-hotels-api (enter server.js
when asking about the entry point):
mkdir cyf-hotels-api && cd cyf-hotels-api && npm init
As before, we will use the Express library to build our API, and the node-postgres library to connect with our database:
npm install --save express
npm install --save pg
Create a server.js
file, import express, initialise the server and start listening for requests:
const express = require("express");
const app = express();
app.listen(3000, function() {
console.log("Server is listening on port 3000. Ready to accept requests!");
});
Import pg library and create a new GET endpoint to load the list of hotels:
const { Pool } = require('pg');
const pool = new Pool({
user: 'postgres',
host: 'localhost',
database: 'cyf_hotels',
password: '',
port: 5432
});
app.get("/hotels", function(req, res) {
pool.query('SELECT * FROM hotels', (error, result) => {
res.json(result.rows);
});
});
In the code above:
- We first import the
Pool
class from the pg library, which is used to connect to a database - We create a new pool where we specify the credentials to connect to the cyf_hotels database
- We then create a new /hotels endpoint where we use the method
query()
to send a SQL query to load all the hotels from the tablehotels
and return the results withresult.rows
. You can write any valid SQL query that you learned in thequery()
method!
Start your server with node server.js
and try to reach the /hotels
endpoint to see the list of hotels currently available in your hotels
table of your cyf_hotels
database. You can try to create/update/delete hotels to verify that your API always returns what is stored in your database.
Homework
All of the homework can be found in this repository.
Submission
Create a new branch from Master
to start working on this weeks homework. It should be called [YOUR_NAME]/Week2
.
When you have completed the homework create a pull request back to the CodeYourFuture/Databases-Homework
repository so your teach can feedback on it.
Tasks
You should complete all of the tasks in Week 2 of the Database Homework repository.