An application with a database

An API and a managed PostgreSQL, joined by a secret, running as two copies, with a backup you have restored once.

Forty minutes, some of it waiting. By the end you will have an API running as two copies, a PostgreSQL instance of your own behind it on a private network, the connection string stored where nobody can read it back, and a backup you have actually restored.

Do this with something you do not mind breaking.

Before you start

  • An organization with some credit on it.
  • The CLI, installed and signed in. See Installing the CLI.
  • A repository your organization has connected, holding an API that listens on a port and reads the address of its database from one environment variable. This tutorial calls the variable DATABASE_URL, the application shop-api and the database shop-db. Replace acme/shop-api with your repository.

1. Order the database

In the console, open Database instances and press New instance. Name it shop-db, choose the Region, leave Network on the one offered (it is your organization's own), choose Single node under Topology, pick the smallest size, and press Deploy.

You should see shop-db in the list as provisioning, then running. A first start can take several minutes. The page updates by itself.

Do not use Mini-databases for this. A mini-database is reached over a public address only. An instance joins your private network, and that is how the application is going to reach it.

Note the name of the network. The application has to be on the same one.

2. Read the connection string

Open the instance with Manage. On the Overview tab, under Connection, press Show connection details.

You should see one line starting with postgresql://, and below it the host, the port, the database and the user. That line is the whole credential: your admin role, its password, and the private address.

Before you leave the page, press Test connections under Connectivity. The private endpoint should say accepting. If it does not, wait half a minute and test again: an instance is marked running slightly before it accepts connections.

That line contains a password. Do not paste it into a chat, a ticket or a commit.

3. Create the application, with the string as a secret

First put the string in a shell variable, without it appearing on screen or in your shell history. In bash or zsh:

read -rs DATABASE_URL

Paste the line, press Enter. Then create the application:

isogrid apps create --name shop-api \
  --url https://github.com/acme/shop-api \
  --replicas 2 --public \
  --secret-env DATABASE_URL="$DATABASE_URL" \
  --wait

--secret-env stores the value as a secret and hands it to the application as an environment variable. Do not use --env for it: an ordinary variable is readable by anyone who can see the application. If your code reads secrets from files instead, use --secret, which delivers the same value as the file /run/secrets/DATABASE_URL.

--replicas 2 runs two copies, so a deployment no longer leaves a gap.

You should see Created shop-api in …, on network …, then Build #1 succeeded., then shop-api is running. and its address.

Check the network on that first line. If it is not the one from step 1, the application starts and never finds the database. Name the right one with --network; isogrid networks list shows yours.

4. Check it

isogrid apps status shop-api
isogrid apps secrets shop-api

The first should print shop-api running 2/2 running. The second prints DATABASE_URL and nothing else: the name, never the value. No command and no page gives the value back.

Now open the address and call something that reads from the database.

If copies keep stopping, status lists them with the error, and this shows what the application printed:

isogrid apps logs shop-api --search 'level:error' --since 15m

In order of likelihood: the application is on another network than the database; the code reads a variable with a different name; or each copy opens more connections than the database accepts. Two copies means two pools. See Databases.

5. Take a backup

Backups are already on for an instance: daily, at 03:00 UTC, kept until you say otherwise. Do not wait for the night to find out whether they work. Open the Backups tab and press Back up now.

You should see "Taking the backup… it appears here when it is done.", then a row for each database on the instance with its size and the status succeeded, and Download and Restore beside it.

Under Backup schedule you can change the hour and Keep for (days). Left empty, every backup is kept.

This is not what the copies of a database give you. Replication repeats a mistaken DELETE faithfully. A backup is the only thing that predates it.

6. Restore it

A backup you have never restored is a hope, not a backup. Press Restore on the row. You are asked to name a new database, and offered the original name followed by _restored. Accept it.

You should see "Restoring into …. It will appear once complete."

A restore never overwrites. It goes beside your data, into a database that must not exist yet, so nothing the application is using changes.

To check it, open the Query tab, type the new name in the Database field, and count the rows of a table you know:

SELECT count(*) FROM orders;

You should get the number the table had when the backup was taken.

7. Point the application at the restored data

Only on the day you really need to go back. The connection string is the one from step 2 with the database name at the end replaced by the new one:

read -rs RESTORED_URL
isogrid apps update shop-api --secret-env DATABASE_URL="$RESTORED_URL"
isogrid apps deploy shop-api --wait

You should see Updated shop-api: secrets., then shop-api is running.

Storing a secret does not restart anything. The second command is what makes the copies pick it up: it redeploys the build the application already has, without building again.

What to read next