Sample: Task API

Learning objectives

Observes the process related to designing and implementing an API.

Here, we look into the process of designing and implementing an API. The objective here is to create an API that can be used for storing and retrieving tasks. For each task, information on the task itself and the completion date, given that the task has been completed, is stored. In addition, tasks have an unique identifier.

Data definition

First, we define the format of the data that we wish to use. For this purpose, we create both a sample JSON document that corresponds to a single task as well as the database schema used for storing the tasks. Each task will be formatted as follows.

{
  "id": 1
  "task": "Build a Task API",
  "completed_on": null
}

The corresponding database table is as follows. The datatype TIMESTAMP WITH TIME ZONE corresponds to a timestamp field into which values are added in the UTC timezone.

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  task TEXT NOT NULL,
  completed_on TIMESTAMP WITH TIME ZONE
);

Create the above table to the same database that you have worked with previously. In addition, for testing purposes, add one row to the table. The following INSERT statement corresponds to adding a Build a Task API task.

INSERT INTO tasks (task) VALUES ('Build a Task API');

Defining API endpoints

Next, we define the API endpoints, i.e. the paths or routes that the API provides, and the actions related to the request methods on those endpoints. The API will provide the means to add a task, retrieve all tasks, retrieve a single task, set a task as completed, and to remove a task. We will use the following endpoints.

GET /tasks - returns a list containing all the tasks.
POST /tasks - creates and returns a new task. The request body must be a JSON document that contains an attribute task that will be used as the task name.
GET /tasks/{id} - returns the task with the id {id}.
POST /tasks/{id} - sets the completed_on date of the task to current date.
DELETE /tasks/{id} - deletes the task with the id {id}.

For creating the API, we start with the following baseline. The server uses the sql function that we have defined previously, as well as the errorMiddleware that can be used to log errors that occurred on the server.

import { Application, Router } from "https://deno.land/x/oak@v12.6.1/mod.ts";
import { sql } from "./database/database.js";
import { errorMiddleware } from "./middlewares/errorMiddleware.js";

const app = new Application();
app.use(errorMiddleware);

const router = new Router();
router.get("/", ({ response }) => {
  response.body = "Hello world!";
});

app.use(router.routes());

app.listen({ port: 7777 });

We will build the API functionality one by one.

Retrieving all the tasks

We first add the functionality needed to retrieve all the tasks in the database. Using the sql function, retrieving all the tasks in the database and returning them as JSON objects is straightforward. We will create a separate function getTasks that implements this. The function getTasks first makes the query to the database and then adds the query result to the response body.

const getTasks = async ({ response }) => {
  response.body = await sql`SELECT * FROM tasks`;
};

We also route GET requests to the path /tasks to the function defined above.

router.get("/tasks", getTasks);

Now, when we start the server, we can try out retrieving all the tasks from the database.

curl localhost:7777/tasks
[{"id":1,"task":"Build a Task API","completed_on":null}]%

The response contains the task that we added previously. As completed_on for the task is null, it has not yet been completed.

Adding a new task

Next, we will create the functionality for adding a new task. We expect that the request body has a JSON object that has an attribute task, which we then use when storing the task to the database. We will name the function that does this addTask.

Finally, at the end of the function, we respond with a status code 200, indicating that the request was accepted.

const addTask = async ({ request, response }) => {
  const body = request.body({ type: "json" });
  const document = await body.value;
  if (document.task) {
    await sql`INSERT INTO tasks (task) VALUES (${task})`;
  }
  response.status = 200;
};

We also route POST requests to the path /tasks to the function defined above.

router.post("/tasks", addTask);

Again, we try out the API from the command line. Adding an entry to the database can be done using a POST request that contains a JSON object that has the task as a variable. Here, we add a task called "Implement completing a task.".

curl -X POST -d '{"task":"Implement completing a task."}' localhost:7777/tasks
curl localhost:7777/tasks
[{"id":1,"task":"Build a Task API","completed_on":null},{"id":2,"task":"Implement completing a task.","completed_on":null}]%

Retrieving a single task

Let us next implement the functionality for retrieving a single task. In this case, we extract the identifier of the task that we wish to retrieve from the path -- retrieving a single task is done by a GET request to the path /tasks/:id, where :id contains the identifier of a particular task.

We create a function called getTask for retrieving a single task. The identifier of the task is retrieved from the request parameters (params in the context object). If a task is found from the database with the given identifier, it is returned in the response body. Otherwise, the API returns status code 404, which indicates that such a task was not found.

const getTask = async ({ params, response }) => {
  const tasks = await sql`SELECT * FROM tasks WHERE id = ${params.id}`;

  if (tasks && tasks.length > 0) {
    response.body = tasks[0];
  } else {
    response.status = 404;
  }
};

In the above example, when considering whether the reponse should be the 404 code or a JSON object, we check whether query returned a result and whether the result has rows. This way, we handle both situations where the identifier is faulty (the query produces no result) or where no rows matching the identifier is found (the rowCount in the result is zero).

Finally, we route GET requests to /tasks:id to the function getTask.

router.get("/tasks/:id", getTask);

Now, in addition to the previous functionality, the API also provides the opportunity for retrieving single tasks.

curl localhost:7777/tasks/1
{"id":1,"task":"Build a Task API","completed_on":null}%
curl localhost:7777/tasks/2
{"id":2,"task":"Implement completing a task.","completed_on":null}%
curl localhost:7777/tasks/3
curl localhost:7777/tasks
[{"id":1,"task":"Build a Task API","completed_on":null},{"id":2,"task":"Implement completing a task.","completed_on":null}]%

Completing a task

Let's next implement the functionality for completing a task. To complete a task, we set the value of completed_on to the current time. The completion time of a task is set when a POST request is made to the path related to a specific task, i.e. /tasks/:id where :id contains the identifier of a particular task.

Concretely setting the completion time is done using a SQL UPDATE query, and the current time is retrieved using the NOW() function. We will name the function completeTask -- the implementation is as follows.

const completeTask = async ({ params, response }) => {
  await sql`UPDATE tasks SET completed_on=NOW() WHERE id = ${params.id}`;
  response.status = 200;
};

Naturally, we also route POST requests to /tasks/:id to the function completeTask.

router.post("/tasks/:id", completeTask);

When trying out the API, we are finally able to complete a task. Here, we finish the "Implement completing a task." task.

curl localhost:7777/tasks/2
{"id":2,"task":"Implement completing a task.","completed_on":null}%
curl -X POST localhost:7777/tasks/2
curl localhost:7777/tasks/2
{"id":2,"task":"Implement completing a task.","completed_on":"(not null)"}%

Deleting a task

Finally, let us implement the functionality for deleting a task. We use the HTTP DELETE method for indicating a delete request, and -- similar to retrieving a single task and setting the completed date of a single task -- we retrieve the task to be deleted from the request URL.

Deleting content from the database is done using the SQL DELETE statement. The following function, deleteTask, shows the functionality needed to delete a task from the database based on an identifier.

const deleteTask = async ({ params, response }) => {
  await sql`DELETE FROM tasks WHERE id = ${params.id}`;
  response.status = 200;
};

We also route DELETE requests to /tasks/:id to the function deleteTask.

router.delete("/tasks/:id", deleteTask);

Now, we can also delete tasks. In the following example, we first retrieve the task "Build a Task API.", then set it as completed, then delete it, and finally retrieve all (remaining) tasks from the database.

curl localhost:7777/tasks/1
[{"id":1,"task":"Build a Task API","completed_on":null}]%
curl -X POST localhost:7777/tasks/1
curl localhost:7777/tasks/1
[{"id":1,"task":"Build a Task API","completed_on":"(not null)"}]%
curl -X DELETE localhost:7777/tasks/1
curl localhost:7777/tasks/1
curl localhost:7777/tasks
[{"id":2,"task":"Implement completing a task.","completed_on":"(not null)"}]%

API-functionality in larger projects

In more complex applications, API-related functionality would be placed within a folder called apis that is in the folder routes. For example, the functions responsible for the API above would be placed in a file called taskApi.js, which would look as follows.

import { sql } from "../../database/database.js";

const addTask = async ({ request, response }) => {
  const body = request.body({ type: "json" });
  const document = await body.value;
  if (document.task) {
    await sql`INSERT INTO tasks (task) VALUES (${task})`;
  }
  response.status = 200;
};

const completeTask = async ({ params, response }) => {
  await sql`UPDATE tasks SET completed_on=NOW() WHERE id = ${params.id}`;
  response.status = 200;
};

const deleteTask = async ({ params, response }) => {
  await sql`DELETE FROM tasks WHERE id = ${params.id}`;
  response.status = 200;
};

const getTask = async ({ params, response }) => {
  const tasks = await sql`SELECT * FROM tasks WHERE id = ${params.id}`;

  if (tasks && tasks.length > 0) {
    response.body = tasks[0];
  } else {
    response.status = 404;
  }
};

const getTasks = async ({ response }) => {
  response.body = await sql`SELECT * FROM tasks`;
};

export { addTask, completeTask, deleteTask, getTask, getTasks };

These would then be imported in the routes.js file that would map the functions to specific routes. API-specific routes often have an indicator in the path name that describes their functionality. Such an indicator could e.g. be /api/ -prefix in the path -- in this case, the routes.js could look as follows.

import { Router } from "https://deno.land/x/oak@v12.6.1/mod.ts";
import * as taskApi from "./apis/taskApi.js";

const router = new Router();

router.get("/api/tasks", taskApi.getTasks)
  .post("/api/tasks", taskApi.addTask)
  .get("/api/tasks/:id", taskApi.getTask)
  .post("/api/tasks/:id", taskApi.completeTask)
  .delete("/api/tasks/:id", taskApi.deleteTask);

export { router };

Naturally, it would be also possible to create a separate taskService.js, which would then encapsulate the database functionality; this way, controllers (given that the project would feature them) and apis could use the same functionality.