Databases and Data Validation

Repository and CRUD Pattern

---

Repository Pattern

The repository pattern is a design pattern that provides an abstraction between the data (e.g., the database) and the application logic. The pattern helps encapsulate the logic required to retrieve the data, and to transform it if needed, allowing the rest of the application to interact with the data in a consistent way without worrying about specific data access mechanisms.

When applying the repository pattern in a server-side web application, an additional “layer” is added to the application. The layer is responsible for handling the data access logic, and the rest of the application interacts with the data through the layer. The flow is visualized in Figure 1.

Fig 1. — Repository pattern creates an additional layer on top of the database, providing an abstraction through which the database is used.

Concretely, this would mean creating a new file for accessing the database, and exposing functions from the file that allow the rest of the application to interact with the data.

It is be up to the programmer to ensure that the database is accessed only through the repository file, and not through other parts of the application.

Let’s look at this first in a visit statistics application that does not use a database. In such an application, the repository file would contain a data structure that holds the data, and functions for interacting with the data. The rest of the application would use these functions to interact with the data.

First, create a file called statisticsRepository.js, and place it in the same folder with app.js, on the server-side of the application. Place the following code to the file.

const statistics = { visitCount: 0 };

const getVisitCount = () => statistics.visitCount;

const incrementVisitCount = () => {
  statistics.visitCount++;
};

export { getVisitCount, incrementVisitCount };

The above file contains a data structure statistics that holds the data, and two functions getVisitCount and incrementVisitCount for interacting with the data. The rest of the application can now use these functions to interact with the data.

A server-side application that uses the repository file could look as follows.

import { Hono } from "jsr:@hono/hono@4.6.5";
import { cors } from "jsr:@hono/hono@4.6.5/cors";
import * as statisticsRepository from "./statisticsRepository.js";

const app = new Hono();
app.use("/*", cors());

app.get("/", (c) => {
  const visitCount = statisticsRepository.getVisitCount();
  return c.json({ visitCount });
}

app.post("/", (c) => {
  statisticsRepository.incrementVisitCount();
  return c.json({ message: "OK" });
});

Deno.serve(app.fetch);

Now, if we would need to adjust how the data is stored, we would only need to adjust the statisticsRepository.js file, while the app.js file would remain the same.

Loading Exercise...

CRUD Pattern and Postgres.js

When briefly discussing the CRUD Pattern in the last part, we mentioned that the term CRUD refers to create, read, update, and delete operations that are often used when managing data. We also outlined the following API for a book management application.

app.post("/books", (c) => {
  // Create a new book item
});

app.get("/books", (c) => {
  // Retrieve all book items
});

app.put("/books/:id", (c) => {
  // Update a book item with the given id
});

app.delete("/books/:id", (c) => {
  // Delete a book item with the given id
});

When combining the repository pattern with the CRUD pattern, the repository file would contain functions for creating, reading, updating, and deleting data, which — when not yet implemented — would be empty functions as follows.

import postgres from "postgres";

const sql = postgres();

const create = async (book) => {
  // Create a new book item
};

const readAll = async () => {
  // Retrieve all book items
};

const readOne = async (id) => {
  // Retrieve a book item with the given id
};

const update = async (id, book) => {
  // Update a book item with the given id
};

const remove = async (id) => {
  // Delete a book item with the given id
};

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

Create a new file called bookRepository.js and place it in the same folder with app.js. Place the above code to the file.

Database schema

For a simple book management application, the database schema could look as follows. The schema contains a table books with columns id, title, and year.

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title TEXT,
  year INT
);

For the next examples, you wish to create the table in your local PostgreSQL database, either using the SQL interface created earlier in this part, or by logging into the database through the container.

Creating a new book

Adding a row to the books table can be done with the following SQL query.

INSERT INTO books (title, year) VALUES ('The Hobbit', 1937);

When adding rows to the table, we do not provide a value for the id column, as the id column is defined using SERIAL PRIMARY KEY. This means that PostgreSQL will automatically generate a unique value for the id column.

Don’t be a SERIAL killer by trying to specify values for an id column. 🥁🤦

When using the Postgres.js library, values are inserted into the table using the sql object. The sql object is a tagged template literal that can be used to execute SQL queries.

Concretely, the create function in the bookRepository.js would use the sql object to execute the SQL query as follows.

const create = async (book) => {
  const result = await sql`INSERT INTO books (title, year)
    VALUES (${book.title}, ${book.year})
    RETURNING *`;
  return result[0];
};

The query inserts a new book to the database, using the properties title and year of the book object, and returns the inserted row.

Now, if we modify the app.js to use functions from bookRepository.js, we can create a new book item by sending a POST request to the /books endpoint with JSON data.

// other imports

// importing all functions from bookRepository.js as bookRepository
import * as bookRepository from "./bookRepository.js";

// other functionality

app.post("/books", async (c) => {
  const book = await c.req.json();
  const newBook = await bookRepository.create(book);
  return c.json(newBook);
});

// other functionality

With the above changes, we can test the functionality by sending a POST request to the /books endpoint with JSON data.

curl -X POST -d '{"title": "The Hobbit", "year": 1937}' localhost:8000/books
{"id":1,"title":"The Hobbit","year":1937}%

The query returns a JSON object with the id, title, and year of the newly created book item.

Reading all books

Reading all books from the database can be done with the following SQL query.

SELECT * FROM books;

The same query can be given to the sql object, which returns all the books from the database as a list of objects.

const readAll = async () => {
  return await sql`SELECT * FROM books`;
};

With the readAll function in place, we can modify app.js to process GET requests to the /books endpoint. Below, whenever a GET request is made to the /books endpoint, the application returns a list of all books from the database.

app.get("/books", async (c) => {
  const books = await bookRepository.readAll();
  return c.json(books);
});

Again, with the change in place, we can test the functionality by sending a GET request to the /books endpoint.

curl localhost:8000/books
[{"id":1,"title":"The Hobbit","year":1937}]%

Reading one book

Next, let’s implement the functionality for reading a single book item. Reading a single book item from the database can be done with the following SQL query. Below, the id of the book item is 1.

SELECT * FROM books WHERE id = 1;

The same query can be given to the sql object. When using the sql object, the query is given as a tagged template literal, and the value for the id is provided as a parameter. Below, the function readOne in the bookRepository.js would read and return a single book item from the database based on the id that is given as a parameter.

const readOne = async (id) => {
  const result = await sql`SELECT * FROM books WHERE id = ${id}`;
  return result[0];
};

With the readOne function in place, we can modify app.js to process GET requests to the /books/:id endpoint. Below, whenever a GET request is made to the /books/:id endpoint, the application returns a single book item from the database based on the id that is given as a path variable.

app.get("/books/:id", async (c) => {
  const id = c.req.param("id");
  const book = await bookRepository.readOne(id);
  return c.json(book);
});

With the change in place, we can test the functionality by sending a GET request to the /books/:id endpoint.

curl localhost:8000/books/1
{"id":1,"title":"The Hobbit","year":1937}%

Updating a book

Let’s next add the functionality for updating a book item. Updating a book item in the database can be done with the following SQL query. Below, we would find the book with the id 1 and change its name to “The Lord of the Rings” and the year to 1954.

UPDATE books SET title = 'The Lord of the Rings', year = 1954 WHERE id = 1;

The same query would be given to the sql object, but we would provide the book information and the id as parameters. Below, the function update in the bookRepository.js would update a single book item in the database based on the id and the book object.

const update = async (id, book) => {
  const result = await sql`UPDATE books
    SET title = ${book.title}, year = ${book.year}
    WHERE id = ${id}
    RETURNING *`;
  return result[0];
};

With the update function in place, we would modify app.js to process PUT requests to the /books/:id endpoint. Below, whenever a PUT request is made to the /books/:id endpoint, the application updates a single book item in the database based on the id that is given as a path variable.

app.put("/books/:id", async (c) => {
  const id = c.req.param("id");
  const book = await c.req.json();
  const updatedBook = await bookRepository.update(id, book);
  return c.json(updatedBook);
});

Now, we can test the functionality by sending a PUT request to the /books/:id endpoint with JSON data.

curl -X PUT -d '{"title": "The Lord of the Rings", "year": 1954}' localhost:8000/books/1
{"id":1,"title":"The Lord of the Rings","year":1954}%

After the change, the book item with the id 1 should have the title “The Lord of the Rings” and the year 1954.

curl localhost:8000/books/1
{"id":1,"title":"The Lord of the Rings","year":1954}%

Deleting a book

Finally, let’s add the functionality for deleting a book item. Deleting a book item from the database can be done with the following SQL query. Below, we would delete the book item with the id 1.

DELETE FROM books WHERE id = 1;

The same query would again be given to the sql object, but with the id as a parameter. Below, the function remove in the bookRepository.js would delete a single book item from the database based on the id that is given as a parameter, and return the deleted book item.

const remove = async (id) => {
  const result = await sql`DELETE FROM books WHERE id = ${id} RETURNING *`;
  return result[0];
};

With the remove function in place, we would modify app.js to process DELETE requests to the /books/:id endpoint. Below, whenever a DELETE request is made to the /books/:id endpoint, the application deletes a single book item from the database based on the id that is given as a path variable.

app.delete("/books/:id", async (c) => {
  const id = c.req.param("id");
  const deletedBook = await bookRepository.remove(id);
  return c.json(deletedBook);
});

Now, we can test the functionality by sending a DELETE request to the /books/:id endpoint.

curl -X DELETE localhost:8000/books/1
{"id":1,"title":"The Lord of the Rings","year":1954}%

After the change, the book would be deleted from the database, and it would not be in the list of books.

curl localhost:8000/books
[]%

Full CRUD functionality

As a whole, the full CRUD functionality using the repository pattern would look as follows. Below, the bookRepository.js file contains functions for creating, reading, updating, and deleting data from the database, and the app.js file uses these functions to interact with the data.

import postgres from "postgres";

const sql = postgres();

const create = async (book) => {
  const result = await sql`INSERT INTO books (title, year)
    VALUES (${book.title}, ${book.year})
    RETURNING *`;
  return result[0];
};

const readAll = async () => {
  return await sql`SELECT * FROM books`;
};

const readOne = async (id) => {
  const result = await sql`SELECT * FROM books WHERE id = ${id}`;
  return result[0];
};

const update = async (id, book) => {
  const result = await sql`UPDATE books
    SET title = ${book.title}, year = ${book.year}
    WHERE id = ${id}
    RETURNING *`;
  return result[0];
};

const remove = async (id) => {
  const result = await sql`DELETE FROM books WHERE id = ${id} RETURNING *`;
  return result[0];
};

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

Loading Exercise...