Using Databases

Most web applications use a database for storing data. In the Web Software Development course, we've practiced using PostgreSQL, which is a relational database. If you haven't taken the Web Software Development. Databases are connected to using a library -- our choice here is Postgres.js, which provides a convenient approach for connecting to a database.

Using Postgres.js deno client, we can adjust our JSON item API to use a database with relative ease. Let's assume that we have a PostgreSQL database with the following schema for the items, and that the PostgreSQL credentials are injected to the environment where we run our server.

CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

Now, instead of adding to a list of items, we perform SQL queries to retrieve and to store the data. The following outlines a simple example that uses a database table called items.

import postgres from "https://deno.land/x/postgresjs@v3.4.4/mod.js";

const sql = postgres({});

const handleGetRoot = async (request) => {
  return new Response("Hello world at root!");
};

const handleGetItem = async (request, urlPatternResult) => {
  const id = urlPatternResult.pathname.groups.id;
  const items = await sql`SELECT * FROM items WHERE id = ${id}`;

  // assuming that there's always an item that matches the id
  return Response.json(items[0]);
};

const handleGetItems = async (request) => {
  const items = await sql`SELECT * FROM items`;
  return Response.json(items);
};

const handlePostItems = async (request) => {
  // assuming that the request has a json object and that
  // the json object has a property name
  const item = await request.json();

  await sql`INSERT INTO items (name) VALUES (${item.name})`;
  return new Response("OK", { status: 200 });
};

As you notice, there are plenty of places where things can go wrong, however. For example, the above code assumes that we always receive a result when asking for an item with a specific identifier.