Deployment and databases (Fly.io)

So far, the applications that we have deployed online have not used a database. This means that any data stored on the server disappears whenever the server is restarted. Let's look into deploying an application that uses a database.

Here, we again build on top of the earlier materials at Online Deployment I and at Online Deployment II. That is, we assume that we already have an account at Fly.io and that we already have an application that is running on a Fly.io server.

Logging in

Let's again start by logging into the application.

ls
app.js  Dockerfile  fly.toml  views/
flyctl auth login
Opening https://fly.io/app/auth/cli/(hash) ...

Waiting for session... Done
successfully logged in as (email)

Creating a database server

Creating a database server is done using the command flyctl postgres create. Similar to flyway launch, the command asks for a few options: the name of the application, the region, and the database details.

For simplicity, we use the same name of the application that we previously created, but add a suffix -database to the name. If, for example, the application name was aged-cherry-5206, the app name for the database server would be aged-cherry-5206-database. Further, we use the same region as previously (Amsterdam, Netherlands), and select the development configuration.

flyctl postgres create
? Choose an app name (leave blank to generate one): aged-cherry-5206-database
automatically selected personal organization: (name)
? Select regions: Amsterdam, Netherlands (ams)
? Select configuration: Development - Single node, 1x shared CPU, 256MB RAM, 1GB disk
Creating postgres cluster aged-cherry-5206-database in organization personal
(waiting)

Postgres cluster aged-cherry-5206-database created
  Username:    postgres
  Password:    (password)
  Hostname:    aged-cherry-5206-database.internal
  Proxy port:  5432
  Postgres port:  5433
Save your credentials in a secure place -- you won't be able to see them again!

In addition, the output includes a link to the next steps and a database url that can be used to access the database from within an application. The url is, for example, postgres://postgres:(password)@aged-cherry-5206-database.internal:5432.

Attaching the database to the application

Now that we have a database server in place, we need to add it to our application. In principle, we could directly use the database url from earlier, but hardcoding such data to applications is a poor idea. To accommodate for this, Fly.io provides functionality for storing secrets, which will then be passed to the application as environment variables.

To attach our database to our application, we use the flyctl postgres attach command that is given the name of an application (led by --app) and the name of the database server. For example, if the name of our application is aged-cherry-5206 and the name of the database server is aged-cherry-5206-database, then we run the command flyctl postgres attach --app aged-cherry-5206 aged-cherry-5206-database.

flyctl postgres attach --app aged-cherry-5206 aged-cherry-5206-database

Postgres cluster aged-cherry-5206-database is now attached to aged-cherry-5206
The following secret was added to aged-cherry-5206:
  DATABASE_URL=postgres://(database-url-used-in-the-app)

Now, the virtual machine that runs our application has an environmental variable DATABASE_URL that contains the database address.

Note that attaching the database server to the application also creates a new user and a new database to the server. The name of the user and the name of the database will be the application name but with underscores instead of dashes -- in the above example aged_cherry_5206.

Connecting to the database

The database server can be connected to using flyctl postgres connect command, which is followed by -a and the name of the database server, and -d and the name of the database. The command opens up a psql shell, allowing us to interact with the database.

For example, if the database server name is aged-cherry-5206-database and the database name is aged_cherry_5206, the command to use is flyctl postgres connect -a aged-cherry-5206-database -d aged_cherry_5206.

flyctl postgres connect -a aged-cherry-5206-database -d aged_cherry_5206
Connecting to aged-cherry-5206-database.internal... complete
psql (14.4 (Debian 14.4-1.pgdg110+1))
Type "help" for help.

aged_cherry_5206=# \dt
Did not find any relations.
aged_cherry_5206=# \q

Adding a table

Through the shell, you can also create tables to the database and interact with the database as one normally would. As an example, the addresses table that we previously worked with can be created to the database as follows.

flyctl postgres connect -a aged-cherry-5206-database -d aged_cherry_5206
Connecting to aged-cherry-5206-database.internal... complete
psql (14.4 (Debian 14.4-1.pgdg110+1))
Type "help" for help.

aged_cherry_5206=# CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL
);
CREATE TABLE
aged_cherry_5206=# \dt
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | addresses | table | postgres
(1 row)

aged_cherry_5206=# \q

Note that although the owner of the table is postgres, we can still use it from our application.

Adding functionality for accessing database

Presently, the folder structure of our application is as follows.

tree --dirsfirst
.
├── views
│   ├── layouts
│   │   └── layout.eta
│   └── count.eta
├── app.js
├── Dockerfile
└── fly.toml

That is, we are still missing database functionality.

Let's add a folder database and copy the database.js file that we worked with when learning to work with datahases into the folder. The contents of the database.js should be as follows.

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

const sql = postgres({});

export { sql };

Presently, the database configuration assumes that the configuration comes from the environment. Database urls such as the one stored in DATABASE_URL need to be used in a different way, however, and we need to modify our database.js to accommodate for this.

The key change is to look for an environmental variable DATABASE_URL and use that as a part of creating the pool if it exists, while otherwise still resorting to the normal configuration.

Change the following row:

const sql = postgres({});

To the following:

let sql;
if (Deno.env.get("DATABASE_URL")) {
  sql = postgres(Deno.env.get("DATABASE_URL"));
} else {
  sql = postgres({});
}

Now, the database configuration uses a database url from the environment if it exists, but otherwise relies on the prior configuration. This way, we can continue working on our applications also locally, while maintaining the possibility to deploy the application online.

The structure of the application is presently as follows.

tree --dirsfirst
.
├── database
│   └── database.js
├── views
│   ├── layouts
│   │   └── layout.eta
│   └── count.eta
├── app.js
├── Dockerfile
└── fly.toml

Accessing the database

For simplicity, let's adjust the app.js so that it can be used to make a database request. Assuming that we have previously created the database table addresses to the database, we can add a query to the application that retrieves the number of rows in the addresses table. With sql, this would look like the following.

const rows = await sql`SELECT COUNT(*) as count FROM addresses`;
let rowCount = -42;
if (rows.length > 0) {
  rowCount = rows[0].count;
}

The -42 is used as a number that should never appear if the query is successful.

Let's modify the app.js and add a new path that returns the number of rows. The full app.js after modifications looks as follows.

import { serve } from "https://deno.land/std@0.222.1/http/server.ts";
import { configure, renderFile } from "https://deno.land/x/eta@v2.2.0/mod.ts";
import { sql } from "./database/database.js";

configure({
  views: `${Deno.cwd()}/views/`,
});

const responseDetails = {
  headers: { "Content-Type": "text/html;charset=UTF-8" },
};

const data = {
  count: 0,
};

const handleRequest = async (request) => {
  const url = new URL(request.url);
  if (url.pathname === "/count") {
    data.count++;
    return new Response(await renderFile("count.eta", data), responseDetails);
  }

  if (url.pathname === "/addresses") {
    const rows = await sql`SELECT COUNT(*) as count FROM addresses`;
    let rowCount = -42;
    if (rows.length > 0) {
      rowCount = rows[0].count;
    }

    return new Response(`Total rows: ${rowCount}`);
  }

  return new Response(`Meaning of life: 42`);
};

serve(handleRequest, { port: 7777 });

Now, when we deploy the application using flyctl deploy and wait for a while, the application is running and able to access the database.