Databases and Data Validation

Interacting with a Database

---

A web application that allows interacting with a database consists of three parts: a client-side application, a server-side application, and a database. The client-side application provides an user interface and has the functionality to send requests to the server-side application and to display the responses to the user. The server-side application has API endpoints that the client-side application interacts with and that provide the functionality for communicating with the database. The database, typically, is a stand-alone software that can be interacted with.

Communication with databases is typically done using a library that provides database-specific functionality. Such libraries are also at times called database drivers.

To meaningfully proceed, you need a walking skeleton that has a Svelte client-side application, a Deno server-side application, and a PostgreSQL database. See the part Setting up a Walking Skeleton for an overview of setting up one.

Database and database library

We use PostgreSQL as the database and Postgres.js as the database driver. The chapter PostgreSQL Database and Migrations outlines the key steps needed to take the database into use.

As a starting point for this chapter, modify the app.js of your walking skeleton to contain the following code.

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

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

const sql = postgres();

export default app;

In the above, we create a Hono web application and add CORS middleware to it. We also create a PostgreSQL client that we can use to interact with the database.

When the Deno server interacts with the database server, in terms of client-server architecture, the database server is the server, while the Deno server is a client.

In addition to importing the “postgres” library, the following code is the key part of the above code. It creates a PostgreSQL client, using configuration from the environment variables. The environment variables are defined in the project.env file, as outlined in the chapter PostgreSQL Database and Migrations.

const sql = postgres();

Server-side application

Let’s modify the application so that it provides a single API endpoint that is sent a JSON-formatted document with the property query that contains the SQL query to be executed. The server-side application then executes the query and returns the result.

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

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

const sql = postgres();

app.post("/", async (c) => {
  const { query } = await c.req.json();
  const result = await sql.unsafe(query);
  return c.json(result);
});

export default app;

In the above, we add a new API endpoint that listens to POST requests to the root URL. The endpoint expects a JSON-formatted document in the request body. The following line is a shorthand for parsing the JSON document into a JavaScript object and extracting the property query from it.

const { query } = await c.req.json();

The query is then executed using the PostgreSQL client and the result is returned to the client.

const result = await sql.unsafe(query);
return c.json(result);

Above, we use the method unsafe of the PostgreSQL client to execute the query. The method is called unsafe because it allows executing any SQL query, including queries that modify the database. The method returns the result of the query as an array of objects.

This is the only chapter in this course that uses the unsafe method. In general, the unsafe method should only be used if you are fully certain about what you are doing, and there are no other ways to achieve what you want to do.

At the moment, you should be able to start the server and send a POST request to the root URL with a JSON-formatted document that contains the property query with a SQL query. The server should then execute the query and return the result.

As an example, the SQL query “SELECT 1 + 1 AS sum” should return a list with a single object. The object should have a property sum with the value 2.

curl -X POST -d '{"query": "SELECT 1 + 1 AS sum"}' localhost:8000
[{"sum":2}]%

In database terms, the query selects the sum of 1 and 1 and gives it the alias “sum”. The result is a single row with a column called “sum” that has the value 2.

Client-side application

On the client-side, create a file called SQLInterface.svelte and add it to the src/lib/components of the client. Place the following code in the file.

<script>
  import { PUBLIC_API_URL } from "$env/static/public";

  let query = $state("");
  let result = $state([]);

  const executeQuery = async () => {
    const response = await fetch(PUBLIC_API_URL, {
      method: "POST",
      body: JSON.stringify({ query }),
    });

    result = await response.json();
  };
</script>

<h1>SQL Interface</h1>

<h2>Write query here</h2>

<textarea bind:value={query}></textarea><br />
<button onclick={executeQuery}>Execute query</button>

<h2>Query results:</h2>

<p>{JSON.stringify(result)}</p>

In the above, we create a Svelte component that allows the user to write an SQL query in a text area and to execute the query by clicking a button. The result of the query is displayed below the level two heading “Query results” as a JSON-string.

Figure 1 below shows an example of using the SQL interface in the browser. In Figure 1, the user has typed in the the query “SELECT 1 + 2 AS sum” and clicked the button “Execute query”. The result of the query is displayed as a JSON-formatted string below the heading “Query results”.

Fig 1. — The SQL interface in action. The user has typed the query “SELECT 1 + 2 AS sum” and clicked the button “Execute query”. The result is displayed as a JSON-string.

Loading Exercise...

Concretely, the flow of the application is as follows. When the user types a query in the text area and clicks the button “Execute query”, the executeQuery function is called. The function sends a POST request to the server with the query in a JSON document in the request body. The server parses the JSON document into a JavaScript object and extracts the property query from it. The server then queries the database with the query and returns the result. Finally, the client shows the response from the server to the user.

The flow of the application is visualized in the sequence diagram below.

With the SQL interface in place, you may try to write different SQL queries and see how the application responds. Try going over at least the following queries.

1. Create a database table called “users” with columns “id” and “name”.

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);

2. Insert a row into the “users” table.

INSERT INTO users (name) VALUES ('Edgar F. Codd');

3. Select all rows from the “users” table.

SELECT * FROM users;

4. Insert another row into the “users” table.

INSERT INTO users (name) VALUES ('Donald D. Chamberlin');

5. Select all rows from the “users” table.

SELECT * FROM users;

6. Insert a third row into the “users” table.

INSERT INTO users (name) VALUES ('Raymond F. Boyce');

7. Select all rows from the “users” table.

SELECT * FROM users;

8. Update the name of the user with the id 1.

UPDATE users SET name = 'Edgar F. Codd (1923-2003)' WHERE id = 1;

9. Select all rows from the “users” table.

SELECT * FROM users;

10. Delete the user with the id 1.

DELETE FROM users WHERE id = 1;

11. Select all rows from the “users” table.

SELECT * FROM users;

12. Drop the “users” table.

DROP TABLE users;

The above queries contain much of the key functionality that is used when interacting with a relational database. The queries create a table, insert rows into the table, select rows from the table, update rows in the table, and delete rows from the table. Finally, the queries also drop the table, removing the table from the database.

Loading Exercise...

Command-line interface

Although above, we created a web application that allows executing SQL queries, databases are often interacted with using command-line interfaces. With the database container in the walking skelton, we can use the psql command inside the PostgreSQL container to interact with the database. If the container name is postgresql_database, then the command is as follows.

docker exec -it postgresql_database psql

The above command opens a connection to the database as a superuser. 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.

As an example, when we run the command \dt, 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)