Interacting with a Database

Databases and PostgreSQL

Databases are systems that store data in a structured way, allowing for efficient retrieval and manipulation of that data. In this course, we use PostgreSQL, which is one of the most popular open-source relational database management systems.

When setting up the walking skeleton, we added PostgreSQL database and migrations to it. At that point, we learned how to access the database through the command-line interface and how to add database migration files.

Here, we very briefly recap accessing the database through the command line and adding migration files, after which we move on to building an API that allows interacting with the database.

Accessing the database through the command line

When the database is running in a container, we can access it using Docker’s exec command. If the container is called postgresql_database, we can access the PostgreSQL command-line interface using the following command.

docker exec -it postgresql_database psql -U username database

The above command opens a connection to the container postgresql_database, connecting to a database called database as the user username. In addition to the regular SQL commands, the following commands are useful:

• \dt lists all tables in the database.
• \d table_name describes the table called table_name.
• \q quits the command-line interface.

After setting up the walking skeleton, when we run the command \dt in the database, we should see two tables in the database. The first table is a table called flyway_schema_history that is used to keep track of the database migrations. The second table is a table called todos, which is created by the database migration file added to the project in the walking skeleton.

database=# \dt
                 List of relations
 Schema |         Name          | Type  |  Owner
--------+-----------------------+-------+----------
 public | flyway_schema_history | table | username
 public | todos                 | table | username
(2 rows)

Adding a database migration file

To alter the database schema, we need to add a database migration file. The migration file is a SQL file that contains the SQL commands needed to alter the database schema. The migration files are stored in the database-migrations folder of the walking skeleton.

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

CREATE TABLE books (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL CHECK (length(title) > 0),
  description TEXT,
  published_at DATE,
  page_count INTEGER CHECK (page_count > 0)
);

Then, stop and restart the walking skeleton to apply the migration. The migration file is automatically detected by the database migration tool, and the SQL commands in the file are executed against the database.

$ docker compose up --build
...
database-migrations-1  | Successfully validated 2 migrations (execution time 00:00.119s)
database-migrations-1  | Current version of schema "public": 1
database-migrations-1  | Migrating schema "public" to version "2 - books"
database-migrations-1  | Successfully applied 1 migration to schema "public", now at version v2 (execution time 00:00.040s)

After the migration, you should see a new table called books in the database.

$ docker exec -it postgresql_database psql -U username database
psql (17.5 (Debian 17.5-1.pgdg120+1))
Type "help" for help.

database=# \dt
                 List of relations
 Schema |         Name          | Type  |  Owner
--------+-----------------------+-------+----------
 public | books                 | table | username
 public | flyway_schema_history | table | username
 public | todos                 | table | username
(3 rows)

database=# \q

Postgres.js and connecting to the database

We use the Postgres.js library to connect to the PostgreSQL database from the server-side application. The library is already included in the walking skeleton, so we only need to import it and create a connection to the database.

If you did not go through the step accessing database from Deno server-side application in setting up the walking skeleton, do it now — the key steps included adding the Postgres.js dependency to deno.json, setting up environment variables, and importing the library in the application.

Importing the library and creating a client

When the environment variables are set up, we can create a connection to the database in the app.js file of the server-side application as follows. The first line imports the Postgres.js library, and the second line creates a client that uses the environment variables.

import postgres from "postgres";

const sql = postgres();

The sql variable is now a client.

Connecting to the database and executing SQL commands

We do not explicitly connect to the database in the application code. Instead, when we execute the first SQL command using the client, the client automatically connects to the database using the connection details provided in the environment variables.

For example, the following application would connect to the database and execute a SQL command to retrieve all books from the database.

import postgres from "postgres";

const sql = postgres();

const result = await sql`SELECT * FROM books`;
console.log(result);

The sql tag is a tagged template literal that executes SQL commands in a safe way. We’ll see examples of unsafe ways later in the course, when looking into web application security.

If the connection fails, ensure deno.json maps “postgres” to the correct version of the Postgres.js library, and that the environment variables are set up correctly.

An API for books

Let’s now start working towards an API that allows interacting with the books table in the database. First, we create an API endpoint that retrieves all books from the database. The endpoint will be a GET request to the path /api/books, and it will return a JSON-formatted list of books.

Retrieving all books

The SQL command for reading all rows from a table is SELECT * FROM table_name. For a table called books, the command would be SELECT * FROM books.

Modify the app.js file of the server-side application to match the following.

import { Hono } from "@hono/hono";
import { cors } from "@hono/hono/cors";
import postgres from "postgres";

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

const sql = postgres();

app.get("/api/books", async (c) => {
  const result = await sql`SELECT * FROM books`;
  return c.json(result);
});

export default app;

In the above, we add a new API endpoint that listens to GET requests to the path /api/books. The endpoint queries the database for all books and returns the result as a JSON-formatted list.

The cors middleware is used to enable Cross-Origin Resource Sharing (CORS) for the API endpoint. We’ll learn a bit more about CORS later in the course, when looking into client-server interaction.

When testing the API endpoint, you’ll receive an empty list, as we have not yet added any books to the database.

$ curl http://localhost:8000/api/books
[]

To see if the API can indeed retrieve data from the database, we can add some books to the database using the command-line interface.

$ docker exec -it postgresql_database psql -U username database
psql (17.5 (Debian 17.5-1.pgdg120+1))
Type "help" for help.

database=# INSERT INTO books (title, description, published_at, page_count)
VALUES
  ('HTML for Hamsters', 'Teaching HTML to small rodents.', '2017-02-01', 156),
  ('CSS: Cannot Style Sandwiches', 'Discovering that CSS has nothing to do with bread.', '2019-10-01', 204),
  ('JavaScript and the Fifty Shades of Errors', 'Epic quest to get a single line of code to run.', '2021-09-01', 321);
INSERT 0 3
database=# \q

Now, when we query the API endpoint again, we see that it returns the books we just added to the database. The output below has been formatted for clarity.

$ curl http://localhost:8000/api/books
[
  {"id":1,"title":"HTML for Hamsters","description":"Teaching HTML to small rodents.","published_at":"2017-02-01T00:00:00.000Z","page_count":156},
  {"id":2,"title":"CSS: Cannot Style Sandwiches","description":"Discovering that CSS has nothing to do with bread.","published_at":"2019-10-01T00:00:00.000Z","page_count":204},
  {"id":3,"title":"JavaScript and the Fifty Shades of Errors","description":"Epic quest to get a single line of code to run.","published_at":"2021-09-01T00:00:00.000Z","page_count":321}
]

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

Reading a single book

The SQL command for retrieving rows from a table where the value of a column matches a value is as follows:

SELECT * FROM table_name WHERE column = value;

For example, if we would want to retrieve a book with the ID of 1 from the books table, the SQL command would be:

SELECT * FROM books WHERE id = 1;

To create an API endpoint that allows retrieving a single book, we need to use a path parameter to specify the ID of the book to be retrieved. Let’s use /api/books/:bookId, where :bookId is the ID of the book to be retrieved.

Modify the app.js file of the server-side application to include the following route.

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

  const result = await sql`SELECT *
    FROM books
    WHERE id = ${id}`;
  if (result.length === 0) {
    return c.json({ error: "Book not found" }, 404);
  }
  return c.json(result[0]);
});

The result[0] is used to return the first element of the result array, as the sql command always returns an array, even if there is only one row in the result.

Now, we can test the API endpoint by sending a GET request with the ID of the book we want to retrieve. For example, to retrieve the book with ID 1, we can use the following command.

$ curl http://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}

If we try to retrieve a book that does not exist, we will receive a 404 error with an error message.

$ curl http://localhost:8000/api/books/999
{"error":"Book not found"}

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

{ "id": 1, "name": "Write a TODO app", "created_at": "2025-01-01T12:34:56.000Z" }

{ "error": "Invalid todo id" }

{ "error": "Todo not found" }

Adding a book

The SQL command for adding a row to a table first lists the column names, which is followed by the values that will be set to those columns. The structure is as follows:

INSERT INTO table_name (column1, column2, ...)
  VALUES (value1, value2, ...);

For example, if we would want to add a book to the books table, the SQL command would be:

INSERT INTO books (title, description, published_at, page_count)
  VALUES ('Book Title', 'Book Description', '2023-10-01', 123);

To create an API endpoint that allows adding a book, we need to use a POST request to the path /api/books. The request body will contain the book details in JSON format. We’ll use the following format for the JSON document, keeping it in line with the structure of the books table.

{
  "title": "Book Title",
  "description": "Book Description",
  "published_at": "2023-10-01",
  "page_count": 123
}

Modify the app.js file of the server-side application to include the following route.

app.post("/api/books", async (c) => {
  const book = await c.req.json();

  if (!book.title ||
    !book.description ||
    !book.published_at ||
    !book.page_count) {
    return c.json({ error: "Missing required fields" }, 400);
  }

  const result = await sql`INSERT INTO books
    (title, description, published_at, page_count)
    VALUES (${book.title}, ${book.description}, ${book.published_at}, ${book.page_count})
    RETURNING *;`;

  return c.json(result[0], 201);
});

The RETURNING * is a PostgreSQL-specific feature that allows us to return the inserted row. The result variable will contain an array with the inserted book, and we return the first element of the array as a JSON response. Furthermore, we use the status code 201 Created to indicate that a new resource has been created.

Now, we can test the API endpoint by sending a POST request with a JSON-formatted request body. We can use curl to do this, as follows:

$ curl -X POST http://localhost:8000/api/books \
  -H "Content-Type: application/json" \
  -d '{"title": "New Book", "description": "A new book description.", "published_at": "2023-10-01", "page_count": 123}'
{"id":4,"title":"New Book","description":"A new book description.","published_at":"2023-10-01T00:00:00.000Z","page_count":123}

We can also now use the API endpoint for retrieving a single book to verify that the book was added successfully.

$ curl http://localhost:8000/api/books/4
{"id":4,"title":"New Book","description":"A new book description.","published_at":"2023-10-01T00:00:00.000Z","page_count":123}

Note that we do not send the id in the request body when adding a book, as the id column is an auto-incrementing primary key, and its value is automatically generated by the database.

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

{ "name": "string" }

{ "error": "Missing required fields" }

Deleting a book

The SQL command for deleting a row from a table based on the value of a column is as follows:

DELETE FROM table_name WHERE column = value;

As an example, if we would want to delete a book with the ID of 1 from the books table, the SQL command would be:

DELETE FROM books WHERE id = 1;

To create an API endpoint that allows deleting a book, we need to use a DELETE request to the path /api/books/:bookId, where :bookId is the ID of the book to be deleted.

While you could model this as a POST to a custom action instead of using DELETE, using the correct HTTP method makes the API more intuitive.

Modify the app.js file of the server-side application to include the following route.

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

  const result = await sql`DELETE FROM books
    WHERE id = ${id}
    RETURNING *;`;
  if (result.length === 0) {
    return c.json({ error: "Book not found" }, 404);
  }
  return c.json(result[0]);
});

Now, DELETE requests to /api/books/:bookId will delete the book with the specified ID from the database. Like when adding a book, we use the RETURNING * clause to return the deleted book. In addition, we check if the book exists before deleting it, and return an error if it does not.

Now, we can test the API endpoint by sending a DELETE request with the ID of the book we want to delete.

$ curl -X DELETE http://localhost:8000/api/books/4
{"id":4,"title":"New Book","description":"A new book description.","published_at":"2023-10-01T00:00:00.000Z","page_count":123}

Once the book has been deleted, attempting to delete it again will return an error, as the book no longer exists in the database.

$ curl -X DELETE http://localhost:8000/api/books/4
{"error":"Book not found"}

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

{ "id": 2, "name": "Another todo", "created_at": "2025-01-01T13:00:00.000Z" }

{ "error": "Invalid todo id" }

{ "error": "Todo not found" }

Updating a book

The SQL command for updating a row in a table based on the value of a column is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE column = value;

For example, if we would want to update the title and description of a book with the ID of 1 in the books table, the SQL command would be:

UPDATE books
SET title = 'Updated Book Title', description = 'Updated Description'
WHERE id = 1;

As the last step, to create an API endpoint that allows updating a book, we need to use a PUT request to the path /api/books/:bookId, where :bookId is the ID of the book to be updated. The request body will contain the updated book details in JSON format, similar to the one used for adding a book.

Modify the app.js file of the server-side application to include the following route.

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

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

  if (!book.title ||
    !book.description ||
    !book.published_at ||
    !book.page_count) {
    return c.json({ error: "Missing required fields" }, 400);
  }

  const result = await sql`
    UPDATE books SET
      title = ${book.title},
      description = ${book.description},
      published_at = ${book.published_at},
      page_count = ${book.page_count}
    WHERE id = ${id}
    RETURNING *;`;

  if (result.length === 0) {
    return c.json({ error: "Book not found" }, 404);
  }
  return c.json(result[0]);
});

Now, PUT requests to /api/books/:bookId will update the book with the specified ID in the database. Like when adding and deleting a book, we use the RETURNING * clause to return the updated book. In addition, we check if the book exists before updating it, and return an error if it does not.

Let’s try the new API endpoint out by first adding a new book and then updating it using the API endpoint. First, we add a book.

$ curl -X POST http://localhost:8000/api/books \
  -H "Content-Type: application/json" \
  -d '{"title": "Book to Update", "description": "Description.", "published_at": "2023-10-01", "page_count": 123}'
{"id":5,"title":"Book to Update","description":"Description.","published_at":"2023-10-01T00:00:00.000Z","page_count":123}

Then, we update the book with the ID of 5 using the following command.

$ curl -X PUT http://localhost:8000/api/books/5 \
  -H "Content-Type: application/json" \
  -d '{"title": "Updated Book", "description": "Updated Description.", "published_at": "2023-10-01", "page_count": 456}'
{"id":5,"title":"Updated Book","description":"Updated Description.","published_at":"2023-10-01T00:00:00.000Z","page_count":456}

Finally, we can retrieve the updated book to verify that the update was successful.

$ curl http://localhost:8000/api/books/5
{"id":5,"title":"Updated Book","description":"Updated Description.","published_at":"2023-10-01T00:00:00.000Z","page_count":456}

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

{ "name": "string", "created_at": "2025-01-01" }

{ "error": "Todo not found" }

{ "error": "Missing required fields" }

{ "error": "Invalid todo id" }

Summary

In summary:

• Databases such as PostgreSQL store and organize data in tables. Database migration files are used to control the way how the database schema evolves over time.
• Server-side applications connect to databases using client libraries. In our case, we use Postgres.js, which allows executing SQL queries with standard SQL commands.
• APIs can be created to allow interacting with the data stored in databases.
• Common operations for interacting with data in databases include creating, reading, updating, and deleting data.