Dynamic Websites

By this stage you are familiar with the NodeJS language and how the Koa framework to deliver web pages. This worksheet will show you how to work with the SQLite database and how to use this knowledge to design and build a dynamic website, that is a website that gets its content from a data source (such as a relational database).

1 Building SQLite Databases

In the data persistence worksheet you worked with scripts that created a new database on launch and added the tables dynamically. In a typical scenario you would want to create the database .db file and populate it with some data before running the script. Since an SQLite database is just a single binary file with a .db file extension this process is very straightforward and you can either use the commandline shell tool or choose from a number of graphical tools to make this step as easy as possible.

1.1 Using the SQLite Shell Tools

Start by installing the tools on your computer:

On MacOS, install the Brew Package Manager then $ brew install sqlite.
On Ubuntu (including most online IDEs) it should be installed using the standard package manager:
1. $ sudo apt-get update
2. $ sudo apt-get install sqlite3 libsqlite3-dev
It a bit of a pain to install on Windows 10 but there is a good YouTube video that covers the process.

If you use the terminal/command prompt to navigate to the exercises/05_dynamic_website/ directory you will find a prebuilt database called bookshop.db. To open a database you use the sqlite3 bookshop.db command. Note: if the file you specify does not exist, a new database will be created. Open the bookshop.db database.

Notice that the prompt changes to sqlite>, this means you are interacting with the sqlite3 program. There are a number of special commands that include standard SQL statements as well as special sqlite commands (these start with a period/dot and are sometimes called dotcommands. Try the following commands (note: the up arrow recalls previous commands):

sqlite> .tables
sqlite> .schema books
sqlite> SELECT title, isbn FROM books;
sqlite> .headers on
sqlite> SELECT title, isbn FROM books;
sqlite> .mode column
sqlite> SELECT title, isbn FROM books;
sqlite> .exit

1.1.1 Executing Complex SQL Commands

The sqlite shell works well for simple SQL statements but it is difficult to enter more complex ones. A better solution is to create a .sql file containing the commands you want to run and then importing it. There is a books.sql file in the same directory as the database. If you open this you will see an SQL INSERT statement that will add a new book to the database:

$ sqlite3 bookshop.db
sqlite> .read books.sql
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT title, isbn FROM books;
sqlite> SELECT count(id) AS books FROM books;
sqlite> .exit

1.1.2 Test Your Understanding

Edit the books.sql file so it contains 3 INSERT statements to insert an additional 2 books into the database.
Run this SQL on your bookshop.db database to insert the two book records.
Check that there are now 14 books in the database.

1.2 Using a Graphical Tool

As you have seen it is possible to work with an SQLite database using the CLI however it is not simple and straightforward, especially when inserting complex records. In this section we will be learning how to use a graphical tool called DB Browser for SQLite which runs on MacOS, Linux and Windows. Note: since online IDEs don't not suport a traditional X11 Windows GUI you won't be able to install it.

Make sure you don't have the database open in the CLI tools!

If you are using an online IDE such as Goorm.io you will not be able to use this tool, instead you will need to complete the tasks using the shell tools (explained above). Here are some SQL statements to help you. You will also need to learn the data types supported by SQLite.

ALTER TABLE books ADD author TEXT;
DELETE FROM books WHERE id = 42;
INSERT INTO books(title, author) VALUES ("foo","bar");
UPDATE books SET publisher = 'foo', publshed = 2018 WHERE id = 1;

On MacOS and Windows 10 you can download the installer from the main website.
On Ubuntu Linux you need to add an additional repository and install from there:
1. sudo add-apt-repository ppa:linuxgndu/sqlitebrowser-testing
2. sudo apt-get update
3. sudo apt-get install sqlitebrowser

Install and open the software then use the Open Database button in the toolbar to open the bookshop.db database.

1.2.1 Modifying the Schema

Our first task is to add an author field to the database. Make sure the Database Structure tab is selected then right-click on the table name and choose Modify Table.

Use the Add Field button to add the author field which should have a type of text.

Notice that it builds the correct SQL statement underneath. This is useful to learn how to use the SQL language. When you are done, click on the OK button.

1.2.2 Browing and Editing the Data

Now we have modified the database schema we need to complete the task by adding the authors for the books in the table. You can find the list of authors in the books.csv file. Note: you have added two more books to the database so you will need to search for the authors data for these.

Open the Browse Data tab. Notice that there are two panes of information.

The left pane displays the records and fields currently in the selected table. You will have three additional records at this stage).
The right pane is for editing the currently selected database cell. if you make changes to a cell you should use the Apply button to save them.

Using the data in the books.csv file, add the authors to each of your book records.

1.2.3 Test Your Understanding

Add two more fields to the database to store the publisher and the year the book was published. Make sure you choose the most appropriate data type for each.
Use the data in the books.csv file to insert the correct data. You will need to find this data for the three additional books you have added to the table.

2 Building a Dynamic Web Page

Now you have a working database containing some useful data you need to display this in a web page. It uses the sqlite-async package which implements the database connectivity as functions returning promises (see the lecture slides for more explanation of these). By implementing promises, we can avoid using callbacks and this will simplify our code.

This project includes a manifest file called package.json. Open this and study the dependencies key which lists all the packages and their versions needed by the server. To install all dependencies type:

$ npm install

This will install all the modules defined in the manifest.

Now you should run the index.js script in the same exercises/05_dynamic_website/ directory we were using earlier. and access the root url.

Notice that there is a message in the terminal to let you know the script has connected to the database. Now view the web page, notice it displays the book titles from your database (including the ones you added).

The web page should display a numbered list of technical books.

This is known as a dynamic (or data-driven) website. By the end of this section you will have a clear understanding of how this can be created using NodeJS. Lets look at how this works. Open the index.js script.

The top of the script is where we import modules and configure the server:
1. On lines 5-9 we import all the module package dependencies, the packages listed in the manifest file package.json.
2. Lines 11-15 are where we create the koa server and configure its plugins.
3. Lines 17 and 18 are where we define any global constants.
The middle section of the script is where we define our routes. Each works in a similar manner:
1. The function takes a string to define the URL route and an anonymous function which has the async keyword in front of the ctx parameter, this denotes that the function is an async one that will return a promise.
2. The async function contains a try-catch block. If any of the steps in the try block throw an error, the program will immediately jump to the catch block and the thrown error will be available in the err parameter.
3. Some function calls such as the ones on lines 24-26 use the await keyword. This means they call asynchronous function that return promises. The await keyword extracts the return value from the promise.
4. The final step (such as that on line 28) is to use the Handlebars Template Engine to render the page. The first parameter specifies the name of the template (found in the views/ directory) and the second is the data to be passed to the template.

2.1 Templating

When we render the web page we need to combine some static elements such as the header and footer with dynamic data taken from a database. If you open the views/home.hbs template file you should see some static html elements but also some unfamiliar markup.

If you study line 28 in the index.js script you will see that the second parameter is a JavaScript object with two keys, one called title and the other books.
In the template you will see a placeholder inside the top level heading <h1>{{title}}</h1>. This tells the template engine to insert the data stored in the title key of the object passed to it, in this case the string Favourite Books.
Lower down the page you will see an li element inside a ul element. The books key contains an array of values (so more than one index). The markup around the li element tells the template to repeat the block for each index in the books array.
Each array index contains a title key (check the terminal output to see the overall structure) and the value in the title key is placed inside the li element.

When retrieving data using the SQLite package there are two key functions you should understand:

the db.get() function runs the query and returns the first record that matches. This means it returns a JavaScript object.
the db.all() function returns all the records that match. This means it returns a JavaScript array.

Take time to study the other functions which are detailed in the documentation.

2.1 Test Your Understanding

Convert the html list into a 1 column html table.
Modify the stylesheet to display the table clearly (you decide what style to apply).
Modify the html to retrieve the publication year.
display the publication year in a second column.
Add a third column that displays the text details for each book.

3 Adding Search Functionality

There are not many books in our database so displaying them all is not a problem however once we increase the number of books significantly we need to find a way to filter them. In this section we will be implementing a simple search functionality.

If you look directly under the first route you will see a second route commented out (lines 35-53). Comment out the route you have been using in section 2 and uncomment this one. Restart the script.
Notice the route uses a different template (newindex.handlebars) which contains an html form. This will display a search box and button.
Type in your search term sqlite and click on the search button, this reloads the page. You will see the search term sqlite remains in the text box and the page displays the books that match your search term.
1. Click in the address bar in your browser and look carefully at the URL.
2. It ends with the string ?q=sqlite.
3. Examine the attributes in the html form element:
  1. The action="/" attribute tells the form to send the data to the root URL.
  2. The method="get" attribute tells the form to pass the data in the URL.
4. Examine the html for the text box:
  1. The type="text" attribute tells the browser to display a textbox.
  2. The name="q" tells the form to submit the contents of the textbox as an object called q, this explains the string in the URL.
On line 60 the search string is passed to the page template. If you examine the html form you will see that this is used as the value attribute in the textbox, this is how the search string remains in the search box after the search.

3.1 Test Your Understanding

You have probably noticed that the search is case-sensitive. Use the SQLite upper() function to make the search case-insensitive (there is an sqlite command upper()).
The search ignores the author, publisher and year fields, change the SQL query to include these.
Insert a h2 element that displays the text xx results for "yyy" where xx contains the number of results found and yyy is the query. This should not be displayed at all if the search box is empty. HINT: you need to use an if block helper (there is one in the template you can adapt.

4 Passing Data Between Pages

At the moment we are only displaying some of the data for each book such as the title. If we tried to retrieve all the data for all the books this would take a lot longer to load and be very cluttered. The solution is to create another page to display all the data for a selected book.

Make sure the script is running and try accessing the /details/1 route. This displays detailed information on the first book in the database (the one with the primary key 1). What happens if you change this to /details/2? By passing the book id in the URL we can tell the page which book details to display. Open the index.js file and locate the /details/:id route that starts on line 65.

The route contains two segments:
1. The first segment must be the string details.
2. The second segment can be anything at all. This value can be accessed in the req.params object.
  1. Because the route defines :id, this is the object key it will be stored under, req.params.id.
3. This value is used to build the SQL statement (line 67) which is displayed in the terminal.
Now we call the all() function that is part of the database (db) object. This returns a JavaScript object containing the first matching record.
1. The callback runs after the query completes or an error occurs.
2. If no error occurs, the entire object is displayed in the terminal and passed to the html template.
If you open the details.handlebars template you can see the placeholder names match the database fields.

4.1 Linking the Pages

Now we have built the book details page we need to add hyperlinks to each book in the table. Clicking on the link will load the correct book details.

Locate the point where you are looping through the array of books and building the html list/table.
Wrap the book title in an html a element.
1. The href attribute should point to the details page and pass the id field for the book:
2. Here is a clue: <a href="/details/${book.id}">${book.title}</a>.
Restart the script and see if the links are displayed correctly.
If you click on one of these links does it load the correct book details?

4.2 Test Your Understanding

The descriptions in the database contain newline \n characters but these are ignored by the browser. use the String.replace() function to replace these with </p><p> so that the paragraphs are preserved.
1. By default handlebars will have url encoded the html elements you added. To avoid this, use three curly braces instead of 2 around the expression.
You have added additional fields to the database but these are not shown. Modify the script (and the html template) to display these missing fields.
The page title currently displays the text Bookshop. Change this to display the name of the book. To do this you will need to insert an additional data placeholder in the shared template file.
1. You should keep the default title on the other pages in the website. To do this you should use the handlebars conditional helper
Add a back button to return to the search page.
1. What has happened to the search filter when you go back to the previous page?
2. You will need to pass this to the details page and pass it back when the back button is clicked.
3. Add this to the URL you use to access the details page.
4. You will then need to add this to the link to return to the search results page.

5 Inserting Data

So far we our dynamic website has been working with data from the database but we have not been able to add new records to the database. In this section we will be building a form to let us add data.

To create a working form you need to have two routes:

The first (get) displays the form.
The second (post) inserts the data and then redirects back to another page.

There is already a working form. Access the /form route in your browser, this will display a simple html form where you can enter a book title, isbn and description. Try adding a book using this (there are some extra examples in the books.csv file). Notice that when you click on the add button you end up back at the list of books and the new one has been added to the end. Lets look under the bonnet to see how this has been achieved. Open the index.js script.

Towards the end of the script there is a /form route which sends the contents of the form.handlebars template to the browser. This is how we display the form.
In the form.handlebars file you will see that we have created a simple html form. Note:
1. The form element contains a couple of important attributes:
  1. The action="/add" attribute directs the form to send its data to the /add route.
  2. The method="post" attribute directs the form to send its data in the message body and not in the URL.
  3. Taken together it means that the data will be sent using the POST method to the /add route.
2. Each form element has a name attribute which defines the object key each piece of data can be found under.
In the index.js script you will see a route server.post('/add', callback), it is this that will be called when the form data has been submitted.
1. All the post data can be accessed through the req.body object and, on line 81 we print this to the terminal to check all the data is there.
2. Next we use this data to build an SQL statement to insert a new record into the database.
3. The db.run() function executes the SQL statement and the callback runs either on success or if there was an error.
4. Finally, as soon as the SQL statement has run we redirect the browser back to the main page which will display all the book records including our new one.

5.1 Test Your Understanding

Modify the form to add the additional fields of data you have added to the books table (author, publisher, year).
Modify the server.post('/add', callback) to insert this additional data into the database.
Add a link to the home page to take the user to the new book form.
Add a Cancel link on the form to return the user to the home page.
Style the table to improve its appearance and usability.

TEACHING-MATERIALS/05 Dynamic Websites.md