Testing and Deployment

Deployment and Databases

---

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.

For the purposes of this course, we also offer a PostgreSQL database as a service for students 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"
}

Loading Exercise...

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 5 seconds.

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

Deployment testing

As a minimum example for testing out the deployment, let’s modify the “Hello world! application from the previous chapter to 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.8.12",
    "postgres": "npm:postgres@3.4.7"
  }
}

And then the concrete app.js could be as follows. Below, the application simply connects to the database and performs a calculation using PostgreSQL. The result is then returned as part of the JSON response.

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({
  max: 2,
  max_lifetime: 5,
});

app.get("/", (c) => c.json({ message: "Hello World!" }));

app.get("/database", async (c) => {
  const result = await sql`SELECT 1 + 1 AS sum`;
  return c.json({ result });
});

export default app;

Then, login to the dashboard of your server-side application in Deno Deploy, and navigate to the server-side application, and open up settings. The settings page allows adding environment variables to the application. Add the following environment variables, replacing the values with your own database credentials.

• PGHOST - database.cs.aalto.fi
• PGPORT - 54321
• PGDATABASE - your database name
• PGUSER - your database username
• PGPASSWORD - your database password

With the environment variables set, we can redeploy our “Hello world!” application, now with database access. After running the deno deploy --prod command, the application is available online.

$ curl https://hello-world.aalto-web-software-development.deno.net/database
{"result":[{"sum":2}]}%

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"}%

Loading Exercise...

Summary

In summary:

• Deploying a Deno application with PostgreSQL requires acquiring and configuring a database separately, either through services like Supabase, Amazon RDS, Google Cloud SQL, or the course’s database.cs.aalto.fi service.
• Environment variables must be added to the Deno Deploy dashboard settings before deploying the application with database access.