This is the final part of our post about Node.js, Express, and Knex.js. Our goal is not to give a detailed tutorial on Knex.js, but to encourage readers to explore it further through its official documentation. We will create the endpoint to interact with our database using the code available here as a starting point.

Database Connection Configuration

The knexfile.js file, which we created in the previous post to run our migrations, can also be used for our application. Create the config/database.js file with the following content:

import knex from 'knex';
import config from '../../knexfile.js';

const environment = process.env.NODE_ENV || 'development';
const dbConfig = config[environment];
const db = knex(dbConfig);

export default db;

The code retrieves the appropriate database configuration from the knexfile.js based on the current environment and then creates the Knex instance. This db object will be used throughout the application to perform database operations.

Routing

Routing refers to how an application's endpoints manage client requests. A route essentially consists of:

Method: The HTTP request method.
Path: Together with the method, it defines the endpoints where requests can be made. Paths can be strings, string patterns, or regular expressions. The path supports dynamic route segments, which are identified by colons, known as route parameters.
Handler: The function that processes the request. We can provide multiple functions that act like middleware to handle a request.

app.method(path, handler)

To organize and group related routes for better code structure and maintainability, we will use Express routers. Let's start by defining all our handlers.

Add Task Handler

The task ID will be generated using the following package:

npm install uuid

Create the src/feature/todos/addTodo.js file with the following content:

import db from '../../config/database.js';
import { v7 as uuidv7 } from 'uuid';

export const addTodo = async (req, res) => {
  const todo = {
    id: uuidv7(),
    title: req.body.title,
    completed: false,
    created_at: new Date(),
  };
  await db('todos').insert(todo);
  res.status(201).json(todo);
};

This code defines a handler function called addTodo that creates a new todo item, stores it in the database, and returns it to the client.

Find Todo Handler

Create the src/feature/todos/findTodo.js file with the following content:

import db from '../../config/database.js';

export const ensureTodoFound = async (req, res, next) => {
  const todo = await db('todos').where('id', req.params.todoId).first();
  if (!todo) {
    return res.status(404).json({ error: 'Todo not found' });
  }
  req.todo = todo;
  next();
};

export const findTodo = async (req, res) => {
  res.status(200).json(req.todo);
};

This code defines two functions that work together to handle finding and retrieving individual todos from the database:

ensureTodoFound: This middleware ensures a todo exists before proceeding to the next handler.
findTodo: This is a handler that returns the todo found by the prior middleware.

The key benefit of this pattern is the reusability of the ensureTodoFound function, as we will see later.

List Todos Handler

Create a src/middlewares/paginationParam.js file with the following content:

const DEFAULT_PAGE_NUMBER = 1;
const DEFAULT_PAGE_SIZE = 10;

const toPositiveInt = (value, defaultValue) => {
  const num = Number(value);
  return Number.isInteger(num) && num > 0 ? num : defaultValue;
};

export const paginationParam = (req, res, next) => {
  const { pageNumber, pageSize } = req.query;
  req.pagination = {
    pageSize: toPositiveInt(pageSize, DEFAULT_PAGE_SIZE),
    pageNumber: toPositiveInt(pageNumber, DEFAULT_PAGE_NUMBER),
  };
  req.pagination.offset =
    (req.pagination.pageNumber - 1) * req.pagination.pageSize;
  next();
};

This middleware processes pagination query parameters and adds them to the request object. The idea behind creating this middleware is to reuse it across multiple list endpoints. Create a src/feature/todos/listTodos.js file with the following content:

import db from '../../config/database.js';

export const listTodos = async (req, res) => {
  const { completed, title } = req.query;
  let baseQuery = db('todos');
  if (completed !== undefined) {
    baseQuery = baseQuery.where('completed', completed === 'true');
  }

  if (title && title.trim()) {
    baseQuery = baseQuery.where('title', 'ilike', `${title.trim()}%`);
  }

  const [{ count: total }] = await baseQuery.clone().count('* as count');
  const items = await baseQuery
    .select('*')
    .orderBy('created_at', 'desc')
    .limit(req.pagination.pageSize)
    .offset(req.pagination.offset);

  const totalCount = parseInt(total);

  res.status(200).json({
    items,
    pageNumber: req.pagination.pageNumber,
    pageSize: req.pagination.pageSize,
    totalPages: Math.ceil(totalCount / req.pagination.pageSize),
    totalItems: totalCount,
  });
};

This handler retrieves a filtered and paginated list of todos.

Check and Uncheck Handlers

Create a src/feature/todos/checkTodos.js file with the following content:

import db from '../../config/database.js';

export const checkTodo = async (req, res) => {
  await db('todos').where('id', req.todo.id).update({
    completed: true,
  });

  const updatedTodo = await db('todos').where('id', req.todo.id).first();
  res.status(200).json(updatedTodo);
};

Create a src/feature/todos/uncheckTodos.js file with the following content:

import db from '../../config/database.js';

export const uncheckTodo = async (req, res) => {
  await db('todos').where('id', req.todo.id).update({
    completed: false,
  });

  const updatedTodo = await db('todos').where('id', req.todo.id).first();
  res.status(200).json(updatedTodo);
};

Both handlers update the completed column. The handler expects req.todo to exist, which means it's designed to work with the ensureTodoFound middleware we defined earlier.

Router

Create a src/feature/todos/routes.js file with the following content:

import express from 'express';
import { addTodo } from './addTodo.js';
import { findTodo, ensureTodoFound } from './findTodo.js';
import { checkTodo } from './checkTodo.js';
import { uncheckTodo } from './uncheckTodo.js';
import { listTodos } from './listTodos.js';
import { paginationParam } from '../../middlewares/paginationParam.js';

const router = express.Router();
router
  .post('/', addTodo)
  .get('/:todoId', ensureTodoFound, findTodo)
  .post('/:todoId/check', ensureTodoFound, checkTodo)
  .post('/:todoId/uncheck', ensureTodoFound, uncheckTodo)
  .get('/', paginationParam, listTodos);

export default router;

This code sets up the routing for our API. It defines all the endpoints and their corresponding handlers in a clear and organized manner. Update the src/server.js as follows:

import express from 'express';
import dotenv from 'dotenv';
import todosRoutes from './features/todos/routes.js';

dotenv.config();
const PORT = process.env.PORT || 3000;
const app = express();
app.use(express.json());
app.use('/api/todos', todosRoutes);
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

We add the express.json() middleware to enable JSON parsing for incoming requests and set up the todo routes under the /api/todos path prefix.

Testing

The REST Client extension enables us to test and interact with REST APIs directly within Visual Studio Code, eliminating the need for external tools such as Postman or curl. It allows us to:

Write HTTP requests in .http or .rest files using a simple syntax.
Send GET, POST, PUT, DELETE, and other HTTP requests.
View responses directly in VS Code.
Save and organize our API requests as files in our project.

Create a requests/addTodo.http file with the following content:

POST http://localhost:5000/api/todos HTTP/1.1
content-type: application/json

{
  "title": "task-{{$guid}}"
}

Create a requests/findTodo.http file with the following content:

# @name add
POST http://localhost:5000/api/todos HTTP/1.1
content-type: application/json
accept: application/json

{
  "title": "task-{{$guid}}"
}

###

# @name find
GET http://localhost:5000/api/todos/{{add.response.body.id}} HTTP/1.1

Create a requests/listTodo.http file with the following content:

# @name add
POST http://localhost:5000/api/todos HTTP/1.1
content-type: application/json
accept: application/json

{
  "title": "task-{{$guid}}"
}

###

# @name list
GET http://localhost:5000/api/todos?title={{add.response.body.title}}&completed=false&pageNumber=1&pageSize=10 HTTP/1.1

Create a requests/checkTodo.http file with the following content:

# @name add
POST http://localhost:5000/api/todos HTTP/1.1
content-type: application/json
accept: application/json

{
  "title": "task-{{$guid}}"
}

###

# @name check
POST http://localhost:5000/api/todos/{{add.response.body.id}}/check HTTP/1.1

Create a requests/checkTodo.http file with the following content:

# @name add
POST http://localhost:5000/api/todos HTTP/1.1
content-type: application/json
accept: application/json

{
  "title": "task-{{$guid}}"
}

###

# @name uncheck
POST http://localhost:5000/api/todos/{{add.response.body.id}}/uncheck HTTP/1.1

These files act as a manual testing tool for our endpoints and provide documentation on how to use our API.

In the upcoming articles, we will work on improving these endpoints. You can find all the code here. Thanks, and happy coding.

Node.js and Express: Knex.js (Part II)