Database

To store user data persistently, we need a database. The database recommended in this guide is PostgreSQL.

To simplify our infrastructure as code setup we will use a third-party to host our database. In this case our infrastructure is very flexible since it doesn't store any persistent data. We can easily swap out our infrastrucutre without having to worry about backups and restoration of our persistent data.

The third-party database host recommended by this guide is Supabase. In addition to providing a database, Supabase also provides authentication. Outsourcing authentication to a third-party saves us time which we can then use to instead focus on our own domain.

Supabase comes in multiple pricing tiers. A free version is also available. The tier recommended by this guide is "Pro" at $25 per month. This tier includes backups of our database, which is not the case with the free tier.

Database Creation

Start by creating an account on Supabase and subscribing to the "Pro" plan. As part of subscribing to the Pro plan you should also create an "Organization" in Supabase. Now we can create a new project. Go to your "All projects" dashboard and click on "New project":

For the "Project name" enter "Test". For the "Compute Size" choose "MICRO". Now enter a password for your database. Save this password in a secure place, e.g. your password manager. For the region select your preferred database location. In the case of Epic Fantasy Forge, the database is located in "North EU (Stockholm)". Now click "Create new project".

You should now have two projects in Supabase, one for the test environment and one for the production environment:

Backups

If you are on the Pro plan of Supabase, then by default backups are created on a daily basis and stored for seven days. You can view and restore your backups by navigating to one of your projects dashboard (e.g. the test environment project), clicking on the "Database" icon on the left sidebar, choosing "Backups" under the "PLATFORM" section and selecting the "Scheduled backups" tab:

Custom Domain

For our production environment, we will configure a custom domain in Supabase. There is no need for a custom domain for our test environment. Start by navigating to your production project's dashboard, click on the "Project Settings" icon on the left sidebar, click on "General" in the "PROJECT SETTINGS" section and click on "Enable add on" for the "Custom Domains":

Select "Custom Domain" and click "Confirm":

On the "Project Settings" "Add Ons" section, click on "Change custom domain" under the "Custom domain" section:

Enter a value for your custom domain, e.g. "database.epicfantasyforge.com" in the case of Epic Fantasy Forge. Don't use your root domain as the custom domain for Supabase since you want to keep the root domain for your actual website. Use a subdomain such as "database" instead. For the "Add" button to work you need to first create the CNAME record in Cloudflare, which is covered below.

In Cloudflare, go to your domain's dashboard, open the "DNS" accordion and select "Records". Then click on "Add record" and choose "CNAME" for the record type. For the "Name" field enter your desired subdomain, e.g. "database" and in the "Target" field copy & paste the value presented in Supabase for the CNAME record. Make sure the "Proxy status" checkbox is not selected. Choose "1 min" for the TTL. Then click "Save". For more information on how to add DNS records in Cloudflare, see the Custom Domain section on the User Facing Documentation page of this guide.

After some delay, the "Add" button in Supabase should work for adding your custom domain. Now Supabase instructs you to add a TXT record to verify your custom domain:

In Cloudflare, add another DNS record for your domain by clicking on "Add record". Select "TXT" for the record type. For the "Name", copy & paste the matching value from Supabase. For the "Content" field, copy & paste the matching value from Supabase. Then click "Save":

After some delay, the "Verify" button in Supabase should work. Once it does, click the "Activate" button to activate your custom domain:

Confirm the activation by clicking "Activate":

Your custom domain should now be active in Supabase:

Connecting to the Database

To connect to our databases, we will store two database connection strings in our CI as CI variables. We will then use those variables in our deployment jobs in the CI.

To find your Supabase connection strings, click on "Connect" on your project's dashboard:

You can then find your connection string under the "Connection String" tab under the "Session pooler" section:

To mask this connection string, we must first convert it to base64. Save your connection string to a temporary file on your development machine, e.g. in "~/supabase_connection_string_test". Now we must replace the "postgresql://" part at the beginning of the connection string with "ecto://" in the saved file. Additionally replace the "[YOUR-PASSWORD]" part of the connection string with the password you set for the test environment database earlier. Now run the below command to get a base64 representation of your connection string:

base64 supabase_connection_string_test | tr -d '\n' | cat

Copy the value outputted to the command line from the above command and create a new CI variable with the "Key" set to "DATABASE_URL_TEST" and populating the "Value" field with the value you just copied from the command line:

For more information how to create CI variables CI Variables section on the Technical Documentation page in this guide. Remember to select "Masked and hidden" for the "Visibility" field.

Now repeat this process for the production database URL, setting the "Key" to "DATABASE_URL_PRODUCTION". Finally you should have two additional CI variables for the database URLs.

For enhanced security, we will reject any non-encrypted connections to our database. To configure this, on your Supabase project dashboard, click on the "Project Settings" icon on the left sidebar, click "Database" under the "CONFIGURATION" section and enable the toggle switch for the "Enfore SSL on incoming connections" item in the "SSL Configuration" section:

Deployment of Test Environment

As we now have databases for the test environment and production environments, we can finally deploy our Phoenix project to the test and production environments.

First we need to generate a secret. Go to the root directory of your Phoenix project and run:

mix phx.gen.secret

Copy the value printed to the console from the above command and store this secret in a CI variable with key "SECRET_KEY_BASE_TEST":

We will run our Phoenix app inside a Docker container in our test and production environments. Run the below command to generate a Dockerfile for your Phoenix project:

mix phx.gen.release --docker

There should now be a Dockerfile in the root directory of your Phoenix project. Modify the auto-generated Dockerfile to download the Supabase CA certificate. We need to also install "wget" as one of the additional dependencies:

Dockerfile

# install build dependencies
RUN apt-get update -y && apt-get install -y build-essential git wget \
    && apt-get clean && rm -f /var/lib/apt/lists/*_*

# download Supabase CA certificate
RUN wget https://supabase-downloads.s3-ap-southeast-1.amazonaws.com/prod/ssl/prod-ca-2021.crt -O /usr/local/share/ca-certificates/supabase_ca_cert.crt

Near the end of the Dockerfile, add a step to copy the Supabase CA certificate into the Docker image. Place the new step just after the last "COPY" step which copies the final release from the build stage:

Dockerfile

# Copy the Supabase CA certificate
COPY --from=builder /usr/local/share/ca-certificates/supabase_ca_cert.crt /usr/local/share/ca-certificates/supabase_ca_cert.crt

We will now add a new stage to our CI pipeline to build this Docker image everytime we merge to the main branch. Modifiy your .gitlab-ci.yml as per the below. Add the "docker-test-environment" stage after the "provision" stage in the CI pipeline.

.gitlab-ci.yml

stages:
  - docker-test-environment

include:
  - local: "ci/docker-test-environment.yml"

In the "ci" directory of your Git repository, add a file named "docker-test-environment.yml" and add the below content:

docker-test-environment.yml

docker-test-environment:
  image: docker:latest
  rules:
    - if: $CI_COMMIT_BRANCH == "main"
  script:
    - cd web
    - echo "$CI_JOB_TOKEN" | docker login $CI_REGISTRY -u $CI_REGISTRY_USER --password-stdin
    - docker build --pull -t $CI_REGISTRY_IMAGE:test-environment .
    - docker push $CI_REGISTRY_IMAGE:test-environment
  services:
    - docker:dind
  stage: docker-test-environment

Before we create the test environment deployment stage, we should first make some changes to our configuration in "config/runtime.exs". First, enable TLS for the database connection:

runtime.exs

  config :epic_fantasy_forge, EpicFantasyForge.Repo,
    ssl: [verify: :verify_peer, cacertfile: "/usr/local/share/ca-certificates/supabase_ca_cert.crt"],
    url: database_url,
    pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
    socket_options: maybe_ipv6

Set the default host to your domain name and hardcode the port to 80:

runtime.exs

  host = System.get_env("PHX_HOST") || "epicfantasyforge.com"
  port = 80

Now we can create the test environment deployment stage in the CI pipeline. Modify your .gitlab-ci.yml as per the below. Add the "deploy-test-environment" stage after the "docker-test-environment" stage in the CI pipeline.

.gitlab-ci.yml

stages:
  - deploy-test-environment

include:
  - local: "ci/deploy-test-environment.yml"

In the "ci" directory of your Git repository, add a file named "deploy-test-environment.yml" and add the below content:

deploy-test-environment.yml

deploy-test-environment:
  before_script:
    - cat $CI_PRIVATE_KEY | base64 -d > ~/ci_private_key
    - chmod og= ~/ci_private_key
    - export CI_PRIVATE_KEY=~/ci_private_key
    - export DATABASE_URL=$(echo $DATABASE_URL_TEST | base64 -d)
    - dnf install -y openssh-clients
  environment:
    name: Test
    url: https://test.epicfantasyforge.com
  image: fedora:latest
  rules:
    - if: $CI_COMMIT_BRANCH == "main"
  script:
    - ssh -i $CI_PRIVATE_KEY -o StrictHostKeyChecking=no deployer@$TEST_ENVIRONMENT_IP "echo \"$CI_JOB_TOKEN\" | docker login $CI_REGISTRY -u $CI_REGISTRY_USER --password-stdin"
    - ssh -i $CI_PRIVATE_KEY -o StrictHostKeyChecking=no deployer@$TEST_ENVIRONMENT_IP "docker pull $CI_REGISTRY_IMAGE:test-environment"
    - ssh -i $CI_PRIVATE_KEY -o StrictHostKeyChecking=no deployer@$TEST_ENVIRONMENT_IP "docker container rm -f test-environment"
    - ssh -i $CI_PRIVATE_KEY -o StrictHostKeyChecking=no deployer@$TEST_ENVIRONMENT_IP "docker run -d --publish 80:80 --name test-environment --env SECRET_KEY_BASE=$SECRET_KEY_BASE_TEST --env DATABASE_URL=$DATABASE_URL --env PHX_HOST=test.epicfantasyforge.com $CI_REGISTRY_IMAGE:test-environment"
  stage: deploy-test-environment

The docker-test-environment and deploy-test-environment stages will only execute when we merge to the main branch.

Deployment of Production Environment

First, generate a secret for your production environment. Go to the root directory of your Phoenix project and run:

mix phx.gen.secret

Copy the value printed to the console from the above command and store this secret in a CI variable with key "SECRET_KEY_BASE_PRODUCTION":

We will now add a new stage to our CI pipeline to build this Docker image every 4 weeks. Modifiy your .gitlab-ci.yml as per the below. Add the "docker-production-environment" stage after the "docker-test-environment" stage in the CI pipeline.

.gitlab-ci.yml

stages:
  - docker-production-environment

include:
  - local: "ci/docker-production-environment.yml"

In the "ci" directory of your Git repository, add a file named "docker-production-environment.yml" and add the below content:

docker-production-environment.yml

docker-production-environment:
  image: docker:latest
  rules:
    - if: $RELEASE == "Web"
  script:
    - cd web
    - echo "$CI_JOB_TOKEN" | docker login $CI_REGISTRY -u $CI_REGISTRY_USER --password-stdin
    - docker build --pull -t $CI_REGISTRY_IMAGE:production-environment .
    - docker push $CI_REGISTRY_IMAGE:production-environment
  services:
    - docker:dind
  stage: docker-production-environment

Now we can create the production environment deployment stage in the CI pipeline. Modify your .gitlab-ci.yml as per the below. Add the "deploy-production-environment" stage after the "deploy-test-environment" stage in the CI pipeline.

.gitlab-ci.yml

stages:
  - deploy-production-environment

include:
  - local: "ci/deploy-production-environment.yml"

In the "ci" directory of your Git repository, add a file named "deploy-production-environment.yml" and add the below content:

deploy-production-environment.yml

deploy-production-environment:
  before_script:
    - cat $CI_PRIVATE_KEY | base64 -d > ~/ci_private_key
    - chmod og= ~/ci_private_key
    - export CI_PRIVATE_KEY=~/ci_private_key
    - export DATABASE_URL=$(echo $DATABASE_URL_TEST | base64 -d)
    - dnf install -y openssh-clients
  environment:
    name: Production
    url: https://epicfantasyforge.com
  image: fedora:latest
  rules:
    - if: $RELEASE == "Web"
  script:
    - ssh -i $CI_PRIVATE_KEY -o StrictHostKeyChecking=no deployer@$PRODUCTION_ENVIRONMENT_IP "echo \"$CI_JOB_TOKEN\" | docker login $CI_REGISTRY -u $CI_REGISTRY_USER --password-stdin"
    - ssh -i $CI_PRIVATE_KEY -o StrictHostKeyChecking=no deployer@$PRODUCTION_ENVIRONMENT_IP "docker pull $CI_REGISTRY_IMAGE:production-environment"
    - ssh -i $CI_PRIVATE_KEY -o StrictHostKeyChecking=no deployer@$PRODUCTION_ENVIRONMENT_IP "docker container rm -f production-environment"
    - ssh -i $CI_PRIVATE_KEY -o StrictHostKeyChecking=no deployer@$PRODUCTION_ENVIRONMENT_IP "docker run -d --publish 80:80 --name production-environment --env SECRET_KEY_BASE=$SECRET_KEY_BASE_PRODUCTION --env DATABASE_URL=$DATABASE_URL --env PHX_HOST=epicfantasyforge.com $CI_REGISTRY_IMAGE:production-environment"
  stage: deploy-production-environment

The docker-production-environment and deploy-production-environment stages will only run when the RELEASE environment variable is set to "Web". We will now create a pipeline schedule in GitLab where the pipeline is triggered every 4 weeks with the release environment variable set to "Web".

In GitLab, open the "Build" accordion on the left sidebar and select "Pipeline schedules". Click on "Create a new pipeline schedule":

For the "Description" field enter "Release - Web". Set the "Interval Pattern" to "Custom" and enter "0 0 * * SUN%4" as the value. For "Select target branch or tag" choose the "main" branch. Add a variable with key "RELEASE" and value "Web". Ensure the "Activated" checkbox is ticked. Then click on "Save changes":

Your pipeline should now be scheduled to trigger every 4 weeks on Sunday at 00:00: