Node.js Microservice Architecture Creating a Node.js DAO with Knex.js

We use Knex to build a data access module (DAO) to execute SQL queries to create, update, delete and retrieve todos from the database.

So far, we stored the todos in-memory in a variable todos in a file todo.storage.

import { TodoMap } from "./todo.type";

export const todos: TodoMap = {};

The data access module todo.dao acted against this variable todos.

import { v4 as uuid } from "uuid";
import { todos } from "./todo.storage";
import { Todo, TodoId } from "./todo.type";

export async function getTodo(id: TodoId): Promise<Todo | undefined> {
  return todos[id];
}

export async function getTodos(): Promise<Todo[]> {
  return Object.values(todos);
}

export async function createTodo(todo: Todo): Promise<Todo> {
  const id = uuid();
  const createdTodo = {
    ...todo,
    id,
  };
  todos[id] = createdTodo;
  return createdTodo;
}

export async function updateTodo(
  id: TodoId,
  todo: Todo
): Promise<Todo | "NotFound"> {
  if (!(id in todo)) {
    return "NotFound";
  }
  const updatedTodo = {
    ...todo,
    id,
  };
  todos[id] = updatedTodo;
  return updatedTodo;
}

export async function deleteTodo(id: TodoId): Promise<void> {
  delete todos[id];
}

Now, we exchange the implementation step-by-step to execute SQL queries against the database instead.

GET /todos 🔗

The endpoint GET /todos returns a list of all todos in the database. It utilizes the DAO function getTodos.

import { db } from "../../database/db";
import { Todo } from "./todo.type";

export function getTodos(): Promise<Todo[]> {
  return db.table<Todo>("todos").select("*");
}

The query uses the db connection pool that we created in the previous post. It chooses to act on the table with the name todos and selects every row and every column.

With httpie or curl, we can check the retrieval against the running application npm start and the running database docker-compose up -d.

Note Initialize the environment variable $TOKEN by running the script . ./local.sh.

http http://localhost:3000/todos Authorization:"Bearer $TOKEN"

HTTP/1.1 200 OK
Connection: keep-alive
Content-Length: 329
Content-Type: application/json; charset=utf-8
Date: Sun, 17 Apr 2022 17:01:27 GMT
ETag: W/"149-ffBiGJ6cbX5lQsi4xlpAB7ZE9T8"
Keep-Alive: timeout=5
X-Powered-By: Express

[
    {
        "assignee": "Naomi",
        "dueDate": "2025-01-05",
        "id": "142e8446-f5ba-4813-bfb5-f3192a37f1bf",
        "name": "Create Todo"
    },
    {
        "assignee": "Peter",
        "dueDate": "2029-06-11",
        "id": "bf6091cc-69f8-4a9e-93ed-0294c3a8ac2f",
        "name": "Delete Todo"
    },
    {
        "assignee": "Sheila",
        "dueDate": "2032-11-27",
        "id": "6e5bad4c-7f1c-4ab4-9361-c6bd697b4257",
        "name": "Change Todo"
    }
]

The query returns the three todos that are initialized with the local seeding ./database/seeds/todos.ts created in the previous post.

GET /todos/:id 🔗

The endpoint GET /todos/:id returns a single specific todo using the DAO function getTodo.

import { db } from "../../database/db";
import { Todo, TodoId } from "./todo.type";

export function getTodo(id: TodoId): Promise<Todo | undefined> {
  return db.table<Todo>("todos").where("id", id).first<Todo>();
}

From the table todos we select the rows with that specific ID. Since the ID is defined as a primary key in the migrations ./database/migrations, we know that there is only one row with that ID. Therefore, we define to retrieve the first value or undefined and explicitly type the result to be of the type Todo.

If we query for one of the existing todo IDs, we get the corresponding todo with all fields.

http http://localhost:3000/todos/6e5bad4c-7f1c-4ab4-9361-c6bd697b4257 Authorization:"Bearer $TOKEN"

HTTP/1.1 200 OK
Connection: keep-alive
Content-Length: 109
Content-Type: application/json; charset=utf-8
Date: Sun, 17 Apr 2022 17:02:27 GMT
ETag: W/"6d-Vx+CUC5e2HeHaKEJfis7cAgay/0"
Keep-Alive: timeout=5
X-Powered-By: Express

{
    "assignee": "Sheila",
    "dueDate": "2032-11-27",
    "id": "6e5bad4c-7f1c-4ab4-9361-c6bd697b4257",
    "name": "Change Todo"
}

Similarly, we can query for a non-existing ID. The controller ./controllers/todos/get-todo.controller translates the undefined from the DAO into a not found error 404.

http http://localhost:3000/todos/747c58f7-842e-4782-8897-9d81ad640f27 Authorization:"Bearer $TOKEN"

HTTP/1.1 404 Not Found
Connection: keep-alive
Content-Length: 9
Content-Type: text/plain; charset=utf-8
Date: Sun, 17 Apr 2022 17:36:33 GMT
ETag: W/"9-0gXL1ngzMqISxa6S1zx3F4wtLyg"
Keep-Alive: timeout=5
X-Powered-By: Express

Not Found

POST /todos 🔗

The endpoint POST /todos creates a new todo using the DAO function createTodo. When a user creates a todo, we need to ignore an incoming ID and generate a unique ID. The todo with this generated ID can then be both inserted into the database and returned to the user.

import { v4 as uuid } from "uuid";
import { db } from "../../database/db";
import { Todo } from "./todo.type";

export async function createTodo(todo: Todo): Promise<Todo> {
  const todoWithId = {
    ...todo,
    id: uuid(),
  };
  await db.table<Todo>("todos").insert(todoWithId);
  return todoWithId;
}

On a request to the endpoint POST /todos, we can observe the todo with the ID being returned.

http POST http://localhost:3000/todos Authorization:"Bearer $TOKEN" name=New assignee=Francis dueDate=2045-12-03

HTTP/1.1 200 OK
Connection: keep-alive
Content-Length: 102
Content-Type: application/json; charset=utf-8
Date: Sun, 17 Apr 2022 17:04:23 GMT
ETag: W/"66-i4v1yOpk5ZXd9In2M+wglJR27Tg"
Keep-Alive: timeout=5
X-Powered-By: Express

{
    "assignee": "Francis",
    "dueDate": "2045-12-03",
    "id": "7a071521-62f7-4fce-b99f-9bf7c5c21716",
    "name": "New"
}

PUT /todos/:id 🔗

On a request to the endpoint PUT /todos/:id, an existing todo is supposed to be updated using the DAO function updateTodo. The relevant todo is identified with the ID from the route. An additional ID that might be passed in the body is ignored.

import { db } from "../../database/db";
import { Todo, TodoId } from "./todo.type";

export async function updateTodo(
  id: TodoId,
  todo: Todo
): Promise<Todo | "NotFound"> {
  const todoWithId = {
    ...todo,
    id,
  };
  const changedRowCount = await db
    .table<Todo>("todos")
    .where("id", id)
    .update(todoWithId);
  return changedRowCount === 0 ? "NotFound" : todoWithId;
}

If a user tries to update a non-existing todo, then we try the update and inspect if a row was changed.

We can check the endpoint with an existing ID and update the due date. The endpoint returns the todo with the updated field.

http PUT http://localhost:3000/todos/7a071521-62f7-4fce-b99f-9bf7c5c21716 Authorization:"Bearer $TOKEN" name=New assignee=Francis dueDate=2046-12-03

HTTP/1.1 200 OK
Connection: keep-alive
Content-Length: 102
Content-Type: application/json; charset=utf-8
Date: Sun, 17 Apr 2022 17:12:47 GMT
ETag: W/"66-Yt1SoQRe6m7HzcEOLvlGf/vVo/s"
Keep-Alive: timeout=5
X-Powered-By: Express

{
    "assignee": "Francis",
    "dueDate": "2046-12-03",
    "id": "7a071521-62f7-4fce-b99f-9bf7c5c21716",
    "name": "New"
}

For a non-existing ID, the API returns a not found error 404.

http PUT http://localhost:3000/todos/747c58f7-842e-4782-8897-9d81ad640f27 Authorization:"Bearer $TOKEN" name=New assignee=Francis dueDate=2046-12-03

HTTP/1.1 404 Not Found
Connection: keep-alive
Content-Length: 9
Content-Type: text/plain; charset=utf-8
Date: Sun, 17 Apr 2022 18:06:34 GMT
ETag: W/"9-0gXL1ngzMqISxa6S1zx3F4wtLyg"
Keep-Alive: timeout=5
X-Powered-By: Express

Not Found

DELETE /todos/:id 🔗

Last but not least, the endpoint DELETE /todos/:id deletes an existing todo using the DAO function deleteTodo.

import { db } from "../../database/db";
import { Todo, TodoId } from "./todo.type";

export async function deleteTodo(id: TodoId): Promise<"Ok" | "NotFound"> {
  const deletedRowCount = await db
    .table<Todo>("todos")
    .where("id", id)
    .delete();
  return deletedRowCount === 0 ? "NotFound" : "Ok";
}

If a todo with this ID does not exist, then the DAO reports it to be not found. The API still returns a success 204, since the user intent of the absence of the todo with this ID is achieved.

We can verify the deletion with httpie or curl.

http DELETE http://localhost:3000/todos/7a071521-62f7-4fce-b99f-9bf7c5c21716 Authorization:"Bearer $TOKEN"

HTTP/1.1 204 No Content
Connection: keep-alive
Date: Sun, 17 Apr 2022 17:13:32 GMT
ETag: W/"a-bAsFyilMr4Ra1hIU5PyoyFRunpI"
Keep-Alive: timeout=5
X-Powered-By: Express

Conclusion 🔗

The data access module todo.dao provides five functions to retrieve, create, update and delete todos in the database.

Become a GitHub sponsor to access the code of this post as a GitHub repo.