Databases and Data Validation

Databases and Deployment

---

Deployment with Deno KV

Deploying software that uses Deno KV is straightforward. As Deno KV is a part of the Deno ecosystem, there is no need to configure the key-value database. That is, when you deploy an application that uses Deno KV, the key-value database is automatically taken into use on the server.

You can use the deployctl deploy command that we previously used in Deploying Web Applications to deploy the application.

Loading Exercise...

Deployment with PostgreSQL

To deploy a server-side application that uses PostgreSQL, or some other external database that is not automatically configured by the deployment platform, the database must be acquired and configured separately.

There are online services for PostgreSQL, such as Supabase, which offer PostgreSQL as a service. You can also set up a PostgreSQL database on your own server, or use a cloud service like Amazon RDS or Google Cloud SQL.

We also offer a PostgreSQL database as a service for students in the course through a server at database.cs.aalto.fi. Taking a database into use requires a few steps. First, you need to make a request to get access to the database. After that, you will receive the connection details for the database, and you need to configure your application to connect to the database.

To request access to the PostgreSQL database, make a GET request to the address https://database.cs.aalto.fi/api/databases, with the header “Database-Token” set to your personal database token that is shown below.

Your personal database token:

Copy

Note that the token is unique to you, and you should not share it with others. For example, if the database token is 1234, you can make the request with the following command:

curl -H "Database-Token: 1234" https://database.cs.aalto.fi/api/databases

When you make a request to the server, the server responds with the credentials for the database. The connection details include the database name, the username, and password. You can use these details to connect to the database from your application.

As an example, a response from the server might look as follows.

curl -H "Database-Token: 1234" https://database.cs.aalto.fi/api/databases
{
  "database": "database1234",
  "username": "user1234",
  "password": "password1234"
}

Use from Deno

To use the database from Deno, create a new environment file, for example, production.env with the database credentials. You may copy the existing project.env as a template and modify it to match the credentials.

The key thing to remember is to change every environment variable that contains the database credentials to the new values. In addition, the host needs to be changed to database.cs.aalto.fi and the port to 54321. The production.env file might look as follows, where some of the lines are omitted.

PGPORT=54321
PGHOST=database.cs.aalto.fi
PGDATABASE=database1234
PGUSER=user1234
PGPASSWORD=password1234

## ...
FLYWAY_USER=user1234
FLYWAY_PASSWORD=password1234
FLYWAY_URL=jdbc:postgresql://database.cs.aalto.fi:54321/database1234

After you have created the production.env file, you can run your application with the following command.

docker compose --env-file production.env up --build

The command starts the application in a Docker container with the environment variables from the production.env file.

In addition, as we have used Flyway for database migrations, with the above production.env, you could run the migrations to the database. You can do this with the following Docker command. The command mounts the database migrations directory to the Docker container and reads the database credentials from the database.env file.

docker run --env-file production.env -v $(pwd)/database-migrations:/flyway/sql flyway/flyway migrate

In addition, to prevent creating unnecessary amounts of connections, modify the postgres function call in your application to the following, which sets the maximum number of database connections to 2 and the maximum lifetime of a connection to 10 seconds.

const sql = postgres({
  max: 2,
  max_lifetime: 10,
});

Deployment testing

As a minimum example for testing out the deployment, you can create a simple Deno application that allows querying a database. For such an application, we could have Hono and Postgres.js in the deno.json as follows.

{
  "imports": {
    "@hono/hono": "jsr:@hono/hono@4.6.5",
    "postgres": "https://deno.land/x/postgresjs@v3.4.4/mod.js"
  },
}

And then the concrete app.js would be as follows. Below, the application checks that the query does not do data manipulation, and then queries the database. The connection to the database is formed in the query function.

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

const BANNED_WORDS = [
  "delete", "update", "insert", "drop", "alter", "create",
  "truncate", "replace", "merge", "grant", "revoke",
  "transaction", "commit", "rollback", "savepoint", "lock",
  "execute", "call", "do", "set", "comment"
];

const query = async (query) => {
  // check that the query does not do data manipulation
  for (const word of BANNED_WORDS) {
    if (query.toLowerCase().includes(word)) {
      throw new Error(`You cannot ${word} data`);
    }
  }

  const sql = postgres({
    max: 2,
    host: "database.cs.aalto.fi",
    port: 54321,
    database: "TODO_DATABASE",
    username: "TODO_USERNAME",
    password: "TODO_PASSWORD",
  });
  return await sql.unsafe(query);
};

const app = new Hono();

app.get("/*", (c) => {
  return c.html(`
    <html>
      <head>
        <title>Hello, world!</title>
      </head>
      <body>
        <h1>Hello, world!</h1>
        <p>To use this, make a POST with a JSON document in the request body. The query property of the JSON document will be used to query a database.</p>
        <p>There are no tables though, so you can only do simple queries like "SELECT 1 + 1".</p>
      </body>
    </html>
    `);
});

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

Deno.serve(app.fetch);

The above can be deployed to Deno Deploy or to some other online hosting service. In reality, however, when using Deno Deploy, we would wish to have the database connection details as environment variables. This can be done by setting the environment variables in the Deno Deploy configuration. For more details, see the Deno Deploy documentation on environment variables.

The above application, with credentials, has been deployed to https://database-deployment-demo.deno.dev/. You may try the application, e.g., with the following curl command.

curl -X POST -d '{"query": "SELECT 1 + 1 AS sum"}' https://database-deployment-demo.deno.dev
[{"sum":2}]%

Loading Exercise...

Database API

The database provides a few other API endpoints for (1) querying the database, and (2) for deleting the database. The API endpoints work as follows.

curl -X POST -H "Database-Token: 1234" -H "Content-Type: application/json" -d '{"query": "CREATE TABLE test (id SERIAL PRIMARY KEY, name VARCHAR(50))"}' https://database.cs.aalto.fi/api/databases
[]%
curl -X POST -H "Database-Token: 1234" -d "{\"query\": \"INSERT INTO test (name) VALUES ('Alice')\"}" https://database.cs.aalto.fi/api/databases
[]%
curl -X POST -H "Database-Token: 1234" -d "{\"query\": \"SELECT * FROM test\"}" https://database.cs.aalto.fi/api/databases

To delete your database, you can make a DELETE request to the address https://database.cs.aalto.fi/api/databases, with the same header as before. For example, if the database token is 1234, you can make the request with the following command:

curl -X DELETE -H "Database-Token: 1234" https://database.cs.aalto.fi/api/databases
{"command":"delete","status":"ok"}%