Server-Side Functionality with a Database

Hierarchical Resources and APIs

Learning Objectives

  • You know how to implement an API for hierarchical resources.

In the previous chapter, we discussed how hierarchical resources could be modeled in RESTful APIs. Here, we implement that design in practice with books and their chapters.

Database migration

First, we need to create a migration file to add chapters to the database. The migration file will create a new table for chapters, which will have a foreign key reference to the books table. This way, each chapter can be associated with a specific book.

Create a new file called V3__book_chapters.sql (note two underscores in the name) in the database-migrations folder and add the following SQL commands to it.

CREATE TABLE book_chapters (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
  chapter_number INTEGER NOT NULL CHECK (chapter_number > 0),
  title TEXT NOT NULL CHECK (length(title) > 0),
  content TEXT
);

Then, run the database migration. With this, we have created a new table called book_chapters that has a foreign key reference to the books table. Each chapter has a book_id that references the book it belongs to, a chapter_number to indicate the order of the chapter, a title, and optional content.

Hierarchical Resources and Server-Side APIs

Next, we will create a server-side API that allows us to interact with the hierarchical resources. The API will allow us to create, read, update, and delete chapters associated with books. The structure of the API will be as follows:

  • GET /api/books/{bookId}/chapters returns a list of chapters for a specific book.
  • GET /api/books/{bookId}/chapters/{chapterId} returns a single chapter for a specific book.
  • POST /api/books/{bookId}/chapters creates a new chapter for a specific book.
  • PUT /api/books/{bookId}/chapters/{chapterId} updates a chapter for a specific book.
  • DELETE /api/books/{bookId}/chapters/{chapterId} deletes a chapter for a specific book.

We could expand the existing bookController.js and bookRepository.js (that were worked on in the chapter CRUD Pattern, Repository Pattern, and Layered Architecture), but to keep the code organized, we will create a new controller and repository for chapters.

Repository

First, create a file called chapterRepository.js in the server folder and add the following code to it.

import postgres from "postgres";


const sql = postgres();


const create = async (bookId, chapter) => {
  const result = await sql`INSERT INTO book_chapters
    (book_id, chapter_number, title, content)
    VALUES (${bookId}, ${chapter.chapter_number}, ${chapter.title}, ${chapter.content})
    RETURNING *;`;


  return result[0];
};


const readAll = async (bookId) => {
  return await sql`SELECT * FROM book_chapters
    WHERE book_id = ${bookId}
    ORDER BY chapter_number;`;
};


const readOne = async (bookId, chapterId) => {
  const result = await sql`SELECT * FROM book_chapters
    WHERE book_id = ${bookId} AND id = ${chapterId};`;
  return result[0];
};


const update = async (bookId, chapterId, chapter) => {
  const result = await sql`UPDATE book_chapters SET
      chapter_number = ${chapter.chapter_number},
      title = ${chapter.title},
      content = ${chapter.content}
    WHERE book_id = ${bookId} AND id = ${chapterId}
    RETURNING *;`;


  return result[0];
};


const deleteOne = async (bookId, chapterId) => {
  const result = await sql`DELETE FROM book_chapters
    WHERE book_id = ${bookId} AND id = ${chapterId}
    RETURNING *;
  `;


  return result[0];
};


export { create, deleteOne, readAll, readOne, update };

As you might notice, it is highly similar to the bookRepository.js file, but it operates on the book_chapters table instead of the books table — it is still just CRUD operations after all. The key change is that we pass both bookId and chapterId to the functions that operate on chapters, as chapters are associated with books. We order the chapters by their chapter_number when reading all chapters for a book to make sure that they’re always returned in the correct order.

Task Repository

0 / 25 points

In this assignment, you will extend the functionality of a todo application by implementing a repository for managing tasks associated with each todo item.

The assignment template comes with a file called taskRepository.js. Assume that there is a database available, the configuration for the database is passed as environment variables, and the database contains the following todos table that we set up when working on the walking skeleton.

CREATE TABLE todos (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Furthermore, assume that there is a table called todo_tasks that is defined as follows:

CREATE TABLE todo_tasks (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    todo_id INTEGER NOT NULL REFERENCES todos(id) ON DELETE CASCADE,
    description TEXT NOT NULL CHECK (length(description) > 0),
    is_done BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

If you wish, you can create a migration file for the todo_tasks table to your walking skeleton and run the migration to create the table in the database.

Add the following functions to the taskRepository.js file:

  • create(todoId, task): Creates a new task for a given todo identified by its id. The task object has a property description. Once the task is created, the created task object is returned (including the id and created_at timestamp).
  • deleteById(id): Deletes a task by its id. If the task existed, the deleted task object is returned; otherwise, return undefined.
  • findAll(todoId): Returns all tasks for a given todo id from the database as an array of objects.
  • findById(id): Returns a single task by its id. If no task exists with the given id, return undefined.
  • updateById(id, task): Updates a task by its id based on the todo object. The task object has the properties description and is_done. If the task exists, the updated task object is returned; otherwise, return undefined.

Export the functions from the taskRepository.js file so that they can be used in other parts of an application.

Controller

Next, create a file called chapterController.js in the server folder and add the following code to it. In the following code, we assume that the path variables will be called bookId and chapterId.

Note again the similarity to the bookController.js file.

import * as chapterRepository from "./chapterRepository.js";


const create = async (c) => {
  const bookId = Number(c.req.param("bookId"));
  if (!Number.isInteger(bookId)) {
    return c.json({ error: "Invalid book id" }, 400);
  }


  const chapter = await c.req.json();


  if (!chapter.chapter_number ||
    !chapter.title) {
    return c.json({ error: "Missing required fields" }, 400);
  }


  const newChapter = await chapterRepository.create(bookId, chapter);
  return c.json(newChapter, 201);
};


const readAll = async (c) => {
  const bookId = Number(c.req.param("bookId"));
  if (!Number.isInteger(bookId)) {
    return c.json({ error: "Invalid book id" }, 400);
  }


  const chapters = await chapterRepository.readAll(bookId);
  return c.json(chapters);
};


const readOne = async (c) => {
  const bookId = Number(c.req.param("bookId"));
  if (!Number.isInteger(bookId)) {
    return c.json({ error: "Invalid book id" }, 400);
  }


  const chapterId = Number(c.req.param("chapterId"));
  if (!Number.isInteger(chapterId)) {
    return c.json({ error: "Invalid chapter id" }, 400);
  }


  const chapter = await chapterRepository.readOne(bookId, chapterId);


  if (!chapter) {
    return c.json({ error: "Chapter not found" }, 404);
  }


  return c.json(chapter);
};


const update = async (c) => {
  const bookId = Number(c.req.param("bookId"));
  if (!Number.isInteger(bookId)) {
    return c.json({ error: "Invalid book id" }, 400);
  }


  const chapterId = Number(c.req.param("chapterId"));
  if (!Number.isInteger(chapterId)) {
    return c.json({ error: "Invalid chapter id" }, 400);
  }


  const chapter = await c.req.json();


  if (!chapter.chapter_number ||
    !chapter.title) {
    return c.json({ error: "Missing required fields" }, 400);
  }


  const updatedChapter = await chapterRepository.update(
    bookId,
    chapterId,
    chapter,
  );


  if (!updatedChapter) {
    return c.json({ error: "Chapter not found" }, 404);
  }


  return c.json(updatedChapter);
};


const deleteOne = async (c) => {
  const bookId = Number(c.req.param("bookId"));
  if (!Number.isInteger(bookId)) {
    return c.json({ error: "Invalid book id" }, 400);
  }


  const chapterId = Number(c.req.param("chapterId"));
  if (!Number.isInteger(chapterId)) {
    return c.json({ error: "Invalid chapter id" }, 400);
  }


  const deletedChapter = await chapterRepository.deleteOne(bookId, chapterId);


  if (!deletedChapter) {
    return c.json({ error: "Chapter not found" }, 404);
  }


  return c.json(deletedChapter);
};


export { create, deleteOne, readAll, readOne, update };

Wiring routes

Finally, we need to wire the controller to the routes. Open the app.js file, and modify it to match the following.

import { Hono } from "@hono/hono";
import { cors } from "@hono/hono/cors";
import * as bookController from "./bookController.js";
import * as chapterController from "./chapterController.js";


const app = new Hono();


app.use("/*", cors());


app.post("/api/books", bookController.create);
app.get("/api/books", bookController.readAll);
app.get("/api/books/:bookId", bookController.readOne);
app.put("/api/books/:bookId", bookController.update);
app.delete("/api/books/:bookId", bookController.deleteOne);


app.post("/api/books/:bookId/chapters", chapterController.create);
app.get("/api/books/:bookId/chapters", chapterController.readAll);
app.get("/api/books/:bookId/chapters/:chapterId", chapterController.readOne);
app.put("/api/books/:bookId/chapters/:chapterId", chapterController.update);
app.delete("/api/books/:bookId/chapters/:chapterId", chapterController.deleteOne);


export default app;

Now, our application has a server-side API that allows us to interact with hierarchical resources, specifically books and their chapters. The API supports creating, reading, updating, and deleting chapters associated with books.

With the API in place, we can test it with curl. Below, we first check the details of a book, then add two chapters to it, and finally check the chapters of the book.

Terminal window
$ curl localhost:8000/api/books/1
{"id":1,"title":"HTML for Hamsters","description":"Teaching HTML to small rodents.","published_at":"2017-02-01T00:00:00.000Z","page_count":156}
$ curl -X POST -H "Content-Type: application/json" -d '{"chapter_number": 1, "title": "From Wheel to Webpage", "content": "First HTML page."}' http://localhost:8000/api/books/1/chapters
{"id":1,"book_id":1,"chapter_number":1,"title":"From Wheel to Webpage","content":"First HTML page."}
$ curl -X POST -H "Content-Type: application/json" -d '{"chapter_number": 2, "title": "The Great Nesting Instinct", "content": "HTML and nested elements."}' http://localhost:8000/api/books/1/chapters
{"id":2,"book_id":1,"chapter_number":2,"title":"The Great Nesting Instinct","content":"HTML and nested elements."}
$ curl http://localhost:8000/api/books/1/chapters
[{"id":1,"book_id":1,"chapter_number":1,"title":"From Wheel to Webpage","content":"First HTML page."},{"id":2,"book_id":1,"chapter_number":2,"title":"The Great Nesting Instinct","content":"HTML and nested elements."}]

Task Controller and Repository

0 / 35 points

The assignment template comes with a solution to the earlier exercise Task Repository and three other files: (1) app.js, (2) taskController.js, and (3) app-run.js.

First, implement the following functions to the taskController.js file and export them so that they can be used in other parts of the application. For database interactions, use the functions from taskRepository.js:

  • create(c) - Creates a new task for a given todo.

    • Expects a route parameter :todoId.
    • The request body must be a JSON document with the following structure: 
      { "description": "string", "is_done": false }
      The is_done field is optional and defaults to false.
    • If the task was successfully created, the response should include the newly created task with its id, todo_id, and created_at timestamp. The status code should be 201 Created.
    • If the description is missing, respond with 400 Bad Request and the following JSON body: 
      { "error": "Missing required fields" }
    • If the todo id is not a valid integer, respond with: 
      { "error": "Invalid todo id" }

  • readAll(c) - Returns all tasks for a given todo.

    • Expects a route parameter :todoId.
    • The response should be a JSON array of tasks. The status code should be 200 OK.
    • If the todo id is not a valid integer, respond with: 
      { "error": "Invalid todo id" }

  • readOne(c) - Returns a single task by its id.

    • Expects a route parameter :taskId.
    • The status code should be 200 OK if the task is found. If no task exists with the given id, respond with 404 Not Found and the following JSON body: 
      { "error": "Task not found" }
    • If the id is not a valid integer, respond with 400 Bad Request and the following JSON body: 
      { "error": "Invalid task id" }

  • update(c) - Updates a task by its id.

    • Expects a route parameter :taskId.
    • The request body must be a JSON document with the following structure: 
      { "description": "string", "is_done": true }
    • If the task was successfully updated, the response should include the updated task with its id and created_at timestamp. The status code should be 200 OK.
    • If no task exists with the given id, respond with 404 Not Found and the following JSON body: 
      { "error": "Task not found" }
    • If the id is not a valid integer, respond with 400 Bad Request and the following JSON body: 
      { "error": "Invalid task id" }
    • If the request body does not have the expected fields, respond with 400 Bad Request and the following JSON body: 
      { "error": "Missing required fields" }
    • Note! When checking if is_done is present in the request body, remember that it can be either true or false — that is, checking for truthiness is not enough.

  • deleteOne(c) - Deletes a task by its id using taskRepository.js.

    • Expects a route parameter :taskId.
    • If the task was successfully deleted, the response should include the deleted task with its id and created_at timestamp. The status code should be 200 OK.
    • If no task exists with the given id, respond with 404 Not Found and the following JSON body: 
      { "error": "Task not found" }
    • If the id is not a valid integer, respond with 400 Bad Request and the following JSON body: 
      { "error": "Invalid task id" }

After implementing the controller functions, modify the app.js so that it routes the HTTP requests to the appropriate controller functions. The routes for tasks should be as follows:

  • POST /api/todos/:todoId/tasks -> create
  • GET /api/todos/:todoId/tasks -> readAll
  • GET /api/todos/:todoId/tasks/:taskId -> readOne
  • PUT /api/todos/:todoId/tasks/:taskId -> update
  • DELETE /api/todos/:todoId/tasks/:taskId -> deleteOne

Hint! The functionality you created in the chapter “Interacting with a Database” might be useful when implementing the controller functions.

Summary

To summarize:

  • Hierarchical resources allow modeling relationships between entities, such as books and their chapters.
  • A database schema can capture these relationships using foreign keys, e.g. chapters referencing their parent book.
  • Server-side APIs can expose hierarchical resources by nesting endpoints under their parent resource.

Todos and Tasks

0 / 20 points

Finally, combine the work you did in the earlier exercises focusing on Todo and Task functionality and return the application here as a zip. The expected files in the zip are as follows:

.
├── app.js
├── app-run.js
├── taskController.js
├── taskRepository.js
├── todoController.js
└── todoRepository.js

As a summary, the todo controller functionality should be the same as in the exercise “Todo Controller and Repository”, while the task controller functionality should be the same as in the exercise “Task Controller and Repository”. The routes in app.js should jointly be as follows:

  • POST /api/todos -> todoController.create
  • GET /api/todos -> todoController.readAll
  • GET /api/todos/:todoId -> todoController.readOne
  • PUT /api/todos/:todoId -> todoController.update
  • DELETE /api/todos/:todoId -> todoController.deleteOne
  • POST /api/todos/:todoId/tasks -> taskController.create
  • GET /api/todos/:todoId/tasks -> taskController.readAll
  • GET /api/todos/:todoId/tasks/:taskId -> taskController.readOne
  • PUT /api/todos/:todoId/tasks/:taskId -> taskController.update
  • DELETE /api/todos/:todoId/tasks/:taskId -> taskController.deleteOne
← RESTful API Design
Overarching Project →