Deployment and databases (Render)

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 Deploying on a server (Render) and at Deployment with view templates (Render). That is, we assume that you already have an application that is running on Render and you know how to deploy and update it.

Creating a database server

Creating a database on render is done by clicking the New + button in the Render dashboard and choosing PostgreSQL. Figure 1 shows a situation where we are about to click PostgreSQL.

When you click on the button, you are shown a configuration page. For the present example, we've again created a new web service that is running at https://artos-unique-wsd-deployment-iii.onrender.com/ -- that is, we've created a web service on Render using the name artos-unique-wsd-deployment-iii.

Note that since the PostgreSQL database is available in the free tier for one month, the above deployment no longer works.

When creating a database, it is meaningful to associate the database with an application that the database relates to, and hence we enter artos-unique-wsd-deployment-iii-database as the database name. We leave the username and password empty, which leads to Render automatically generating them. We choose Frankfurt (EU Central) as the database location, and use 15 as the PostgreSQL database version. Further, we again choose the Free instance type.

The entered database configuration is shown in Figure 2.

Once you've filled in the details, click the Create Database button at the end of the page. This will lead to the database instance being created. Once finished, you see that the database instance is available, as shown in Figure 3.

Finding internal Database URL

When you scroll down the page that shows that the database has been created, you'll find an area called Connections. This links connection details to your application. In our case, the area looks like the one in Figure 4 (the actual Hostname has been hidden though).

Copy the value for the Internal Database URL and add it to a notepad. Next, we'll be adding it to the use of our web service.

Creating an environment variable group

Having copied the value for Internal Database URL, click on the Env Groups at the top. This opens up a page with a button New Environment Group as shown in Figure 5.

Environment groups allow sharing variable values across services. Click on the New Environment Group button. This opens up a page for configuring the environment group, asking for a name, and providing options for entering variable values.

We'll create a group called artos-unique-wsd-deployment-iii-env-group and add a new environment variable called DATABASE_URL to it. Paste the value of Internal Database URL that you previously copied as the value for DATABASE_URL. When done, the page should look similar to Figure 6 (with the exception that your group differs and the value for DATABASE_URL differs).

Once done, click Create Environment Group. Once done, you are shown the list of existing environment groups, as shown in Figure 7.

Next, we can add the environment variable group to our web service.

Adding environment variable group to web service

Next, open up the Render dashboard and find your deployment from the list of deployments. Click on the deployment, which shows a page corresponding to your application. The page should look similar to the one shown in Figure 8.

Now, click on Environment on the left hand side menu. This opens up the possibility of entering environment variables to the application. From the shown options, find Linked Environment Groups and select the environment group that you just created from the list of options. Once done, click the Link button.

Once you've linked the environment group to the application, the page should look similar to the Figure 9.

Now, when deploying the application, there is an environment variable called DATABASE_URL that provides access to the database.

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
├── deps.js
└── Dockerfile

That is, we are still missing database functionality.

Let's add the dependency for accessing the database into the deps.js. Modify the file deps.js to match the following and save the file.

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

Next, create a folder database and create a file called database.js to the folder. Copy the following contents to the file and save the file.

import { postgres } from "../deps.js";

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

export { sql };

In the above, we are checking for the existence of an environment variable DATABASE_URL. If it exists, we create the database driver using the value stored in the environment variable.

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

Now, the structure of the application is as follows.

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

3 directories, 6 files

Accessing the database

Modify the app.js and add a new path that returns the number of rows in a database table called addresses (it doesn't exist yet, we'll create it soon-ish..). Copy the following contents to the file app.js and save the file.

import { serve } from "./deps.js";
import { configure, renderFile } from "./deps.js";
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("Hello you!");
};

serve(handleRequest, { port: 7777 });

It's again time for git add, git commit, and git push again.

git add .
git commit -m "counting rows in database"
[main 616753d] counting rows in database
// ...
 3 files changed, 24 insertions(+), 1 deletion(-)
git push
Counting objects: 6, done.
// ...
To github.com:avihavai/wsd-online-deployment-iii.git
   ...  main -> main

Now, the application is up and running, or is it?

Build failed!

When we access our application in Render dashboard, we observe that the build failed. Inspecting the logs of the web service in Render, among the logs, we see the following error:

error: Uncaught PermissionDenied: Requires env access to "DATABASE_URL", run again with the --allow-env flag
if (Deno.env.get("DATABASE_URL")) {
             ^
    at Object.getEnv [as get] (deno:runtime/js/30_os.js:86:16)
    at file:///app/database/database.js:4:14

Fixing errors

The error highlights that we have not granted access to environment variables. Without rights to access environment variables, the application cannot see the database configuration that we provided to it. To fix this, let's adjust the Dockerfile to include the --allow-env flag in the run command. Modify the Dockerfile to match the following and save the file.

FROM denoland/deno:alpine-1.42.2

EXPOSE 7777

WORKDIR /app

COPY deps.js .

RUN deno cache deps.js

COPY . .

CMD [ "run", "--allow-env", "--allow-net", "--allow-read", "--watch", "--unstable", "app.js" ]

Now, it's time again for git add, git commit, and git push. This time, as we've modified just a single file, let's use the git add to add a specific file instead of all the files.

git add Dockerfile
git commit -m "allow env access"
[main ...] allow env access
 // ...
 1 file changed, 1 insertion(+), 1 deletion(-)
git push
Counting objects: 3, done.
// ...
To github.com:avihavai/wsd-online-deployment-iii.git
   ...  main -> main

When we push the changes to GitHub, Render again notices the change, and starts building the application. This time, the application builds correctly, and the latest version is deployed.

Oh noes, more errors!

However, when we access the path /addresses, we see errors. Through a browser, the error is simply Internal Server Error as shown in Figure 10.

We might guess the issue already, but there's no need to guess. When we access our web service in Render Dashboard and click on Logs on the left hand side menu of the web service, we can inspect the recent logs.

Looking at the logs, we see an error stating that the relation addresses does not exist.

PostgresError: relation "addresses" does not exist
  // ...
  at Server.handleRequest (file:///app/app.js:25:27)

The error effectively states that the database table addresses that we are trying to access does not exist.

Connecting to the database

Let's next connect to the database to remedy the issue. To connect to the database, you need a PostgreSQL client, which are briefly mentioned in Getting Started part of Working with Database I.

Here, we assume that you have psql at your disposal. To retrieve it, you can follow e.g. the tutorial How to install psql on Mac, Ubuntu, Debian, Windows at Timescale.com.

Open up your database from the dashboard and scroll down to the Connections area (shown in Figure 11).

Copy the PSQL Command to your clipboard and paste it to a terminal. This opens up a connection to the database.

(pasted PSQL Command)
psql (10.23 (Ubuntu 10.23-0ubuntu0.18.04.1), server 15.1)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
SSL connection (...)
Type "help" for help.

artos_unique_wsd_deployment_iii_database=>

When we type in \dt, we see that there are no tables in the database.

artos_unique_wsd_deployment_iii_database=> \dt
Did not find any relations.

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.

artos_unique_wsd_deployment_iii_database=> CREATE TABLE addresses (
artos_unique_wsd_deployment_iii_database(>   id SERIAL PRIMARY KEY,
artos_unique_wsd_deployment_iii_database(>   name TEXT NOT NULL,
artos_unique_wsd_deployment_iii_database(>   address TEXT NOT NULL
artos_unique_wsd_deployment_iii_database(> );
CREATE TABLE
artos_unique_wsd_deployment_iii_database=>

That is, the command that we type in is as follows:

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

Now, we can again check for the tables in the database using the command \dt.

artos_unique_wsd_deployment_iii_database=> \dt
           List of relations
            Schema |   Name    | Type  |                     Owner
--------+-----------+-------+-----------------------------------------------
 public | addresses | table | artos_unique_wsd_deployment_iii_database_user
(1 row)

artos_unique_wsd_deployment_iii_database=>

Now, the database table has been created. When we open up the path /addresses, we can see that the server no longer responds with an error, as shown in Figure 12.

Adding a row

Adding a row using the database terminal is straightforward. Let's add Donald who lives in Duckburg -- this is done with the following INSERT statement.

artos_unique_wsd_deployment_iii_database=> INSERT INTO addresses (name, address) VALUES ('Donald', 'Duckburg');
INSERT 0 1
artos_unique_wsd_deployment_iii_database=>

Now, when we again access the site, we can see that there is now 1 row in the database table.