Working with database

In this chapter, we will deploy a database, configure our application to use it, and set up automatic DB migrations and initializations.

The application described in this chapter is not intended for use in production environments as-is. Note that successful completion of this entire guide is required to create a production-ready application.

Preparing the environment

If you haven’t prepared your environment during previous steps, please, do it using the instructions provided in the “Preparing the environment” chapter.

If your environment has stopped working or instructions in this chapter don’t work, please, refer to these hints:

docker run hello-world

Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
b8dfde127a29: Pull complete
Digest: sha256:9f6ad537c5132bcce57f7a0a20e317228d382c3cd61edae14650eec68b2b345c
Status: Downloaded newer image for hello-world:latest

Hello from Docker!
This message shows that your installation appears to be working correctly.

docker run hello-world

Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
b8dfde127a29: Pull complete
Digest: sha256:9f6ad537c5132bcce57f7a0a20e317228d382c3cd61edae14650eec68b2b345c
Status: Downloaded newer image for hello-world:latest

Hello from Docker!
This message shows that your installation appears to be working correctly.

sudo systemctl restart docker

sudo systemctl status docker

● docker.service - Docker Application Container Engine
     Loaded: loaded (/lib/systemd/system/docker.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2021-06-24 13:05:17 MSK; 13s ago
TriggeredBy: ● docker.socket
       Docs: https://docs.docker.com
   Main PID: 2013888 (dockerd)
      Tasks: 36
     Memory: 100.3M
     CGroup: /system.slice/docker.service
             └─2013888 /usr/bin/dockerd -H fd:// --containerd=/run/containerd/containerd.sock

dockerd[2013888]: time="2021-06-24T13:05:16.936197880+03:00" level=warning msg="Your kernel does not support CPU realtime scheduler"
dockerd[2013888]: time="2021-06-24T13:05:16.936219851+03:00" level=warning msg="Your kernel does not support cgroup blkio weight"
dockerd[2013888]: time="2021-06-24T13:05:16.936224976+03:00" level=warning msg="Your kernel does not support cgroup blkio weight_device"
dockerd[2013888]: time="2021-06-24T13:05:16.936311001+03:00" level=info msg="Loading containers: start."
dockerd[2013888]: time="2021-06-24T13:05:17.119938367+03:00" level=info msg="Loading containers: done."
dockerd[2013888]: time="2021-06-24T13:05:17.134054120+03:00" level=info msg="Daemon has completed initialization"
systemd[1]: Started Docker Application Container Engine.
dockerd[2013888]: time="2021-06-24T13:05:17.148493957+03:00" level=info msg="API listen on /run/docker.sock"

docker run hello-world

Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
b8dfde127a29: Pull complete
Digest: sha256:9f6ad537c5132bcce57f7a0a20e317228d382c3cd61edae14650eec68b2b345c
Status: Downloaded newer image for hello-world:latest

Hello from Docker!
This message shows that your installation appears to be working correctly.

minikube start

kubectl config set-context minikube --namespace=werf-guide-app

😄  minikube v1.20.0 on Ubuntu 20.04
✨  Using the docker driver based on existing profile
👍  Starting control plane node minikube in cluster minikube
🚜  Pulling base image ...
🎉  minikube 1.21.0 is available! Download it: https://github.com/kubernetes/minikube/releases/tag/v1.21.0
💡  To disable this notice, run: 'minikube config set WantUpdateNotification false'

🔄  Restarting existing docker container for "minikube" ...
🐳  Preparing Kubernetes v1.20.2 on Docker 20.10.6 ...
🔎  Verifying Kubernetes components...
    ▪ Using image gcr.io/google_containers/kube-registry-proxy:0.4
    ▪ Using image k8s.gcr.io/ingress-nginx/controller:v0.44.0
    ▪ Using image registry:2.7.1
    ▪ Using image docker.io/jettech/kube-webhook-certgen:v1.5.1
    ▪ Using image docker.io/jettech/kube-webhook-certgen:v1.5.1
    ▪ Using image gcr.io/k8s-minikube/storage-provisioner:v5
🔎  Verifying registry addon...
🔎  Verifying ingress addon...
🌟  Enabled addons: storage-provisioner, registry, default-storageclass, ingress
🏄  Done! kubectl is now configured to use "minikube" cluster and "werf-guide-app" namespace by default

Restarting existing docker container for "minikube"

minikube tunnel --cleanup=true

minikube start --namespace werf-guide-app

kubectl config set-context minikube --namespace=werf-guide-app

😄  minikube v1.20.0 on Ubuntu 20.04
✨  Using the docker driver based on existing profile
👍  Starting control plane node minikube in cluster minikube
🚜  Pulling base image ...
🎉  minikube 1.21.0 is available! Download it: https://github.com/kubernetes/minikube/releases/tag/v1.21.0
💡  To disable this notice, run: 'minikube config set WantUpdateNotification false'

🔄  Restarting existing docker container for "minikube" ...
🐳  Preparing Kubernetes v1.20.2 on Docker 20.10.6 ...
🔎  Verifying Kubernetes components...
    ▪ Using image gcr.io/google_containers/kube-registry-proxy:0.4
    ▪ Using image k8s.gcr.io/ingress-nginx/controller:v0.44.0
    ▪ Using image registry:2.7.1
    ▪ Using image docker.io/jettech/kube-webhook-certgen:v1.5.1
    ▪ Using image docker.io/jettech/kube-webhook-certgen:v1.5.1
    ▪ Using image gcr.io/k8s-minikube/storage-provisioner:v5
🔎  Verifying registry addon...
🔎  Verifying ingress addon...
🌟  Enabled addons: storage-provisioner, registry, default-storageclass, ingress
🏄  Done! kubectl is now configured to use "minikube" cluster and "werf-guide-app" namespace by default

Restarting existing docker container for "minikube"

kubectl create namespace werf-guide-app
kubectl create secret docker-registry registrysecret \
  --docker-server='https://index.docker.io/v1/' \
  --docker-username='<Docker Hub username>' \
  --docker-password='<Docker Hub password>'

namespace/werf-guide-app created
secret/registrysecret created

Preparing the repository

cd ~/werf-guide/app

# To see what changes we will make later in this chapter, let's replace all the application files
# in the repository with new, modified files containing the changes described below.
git rm -r .
cp -Recurse -Force ~/werf-guide/guides/examples/rails/040_db/* .
git add .
git commit -m WIP

# Enter the command below to show the files we are going to change.
git show --stat
# Enter the command below to show the changes that will be made.
git show

cd ~/werf-guide/app

# To see what changes we will make later in this chapter, let's replace all the application files
# in the repository with new, modified files containing the changes described below.
git rm -r .
cp -rf ~/werf-guide/guides/examples/rails/040_db/. .
git add .
git commit -m WIP

# Enter the command below to show files we are going to change.
git show --stat
# Enter the command below to show the changes that will be made.
git show

# Clone the example repository to ~/werf-guide/guides (if you have not cloned it yet).
if (-not (Test-Path ~/werf-guide/guides)) {
  git clone https://github.com/werf/website $env:HOMEPATH/werf-guide/guides
}

# Copy the (unchanged) application files to ~/werf-guide/app.
rm -Recurse -Force ~/werf-guide/app
cp -Recurse -Force ~/werf-guide/guides/examples/rails/030_assets ~/werf-guide/app

# Make the ~/werf-guide/app directory a git repository.
cd ~/werf-guide/app
git init
git add .
git commit -m initial

# To see what changes we will make later in this chapter, let's replace all the application files
# in the repository with new, modified files containing the changes described below.
git rm -r .
cp -Recurse -Force ~/werf-guide/guides/examples/rails/040_db/* .
git add .
git commit -m WIP

# Enter the command below to show the files we are going to change.
git show --stat
# Enter the command below to show the changes that will be made.
git show

# Clone the example repository to ~/werf-guide/guides (if you have not cloned it yet).
test -e ~/werf-guide/guides || git clone https://github.com/werf/website ~/werf-guide/guides

# Copy the (unchanged) application files to ~/werf-guide/app.
rm -rf ~/werf-guide/app
cp -rf ~/werf-guide/guides/examples/rails/030_assets ~/werf-guide/app

# Make the ~/werf-guide/app directory a git repository.
cd ~/werf-guide/app
git init
git add .
git commit -m initial

# To see what changes we will make later in this chapter, let's replace all the application files
# in the repository with new, modified files containing the changes described below.
git rm -r .
cp -rf ~/werf-guide/guides/examples/rails/040_db/. .
git add .
git commit -m WIP

# Enter the command below to show files we are going to change.
git show --stat
# Enter the command below to show the changes that will be made.
git show

Making our application stateful

At this point, our application does not use a database and does not store any data (i.e., it is stateless). To make it stateful, we need to enable Active Record and configure the application to work with MySQL (we will use the database to store the application state).

To get the Active Record and MySQL configuration, our skeleton Rails application was now generated using the following command (note that the --database mysql flag was added to the arguments while --skip-active-record was omitted):

rails new --database mysql \
  --skip-action-cable --skip-action-mailbox --skip-action-mailer --skip-action-text \
  --skip-active-job --skip-active-storage \
  --skip-javascript --skip-jbuilder --skip-turbolinks \
  --skip-keeps --skip-listen --skip-bootsnap --skip-spring --skip-sprockets \
  --skip-test --skip-system-test .

Please Note! The commands in the current section are for illustrative purposes only. They show how a basic application was generated. Only the commands in the “Testing the application/database” section are intended to be executed.

The following changes have been made to our application:

1. Adding mysql2 to Gemfile.
2. Enabling Active Record in config/application.rb.
3. Generating a config/database.yml configuration file..

Adding /remember and /say endpoints to the application

Let’s add two new endpoints to our application. The /remember endpoint will store the data to the database while the /say endpoint will retrieve the data from it.

Below are our listings for a new controller and model:

class TalkerController < ActionController::API
  def say
    begin
      talker = Talker.find(0)
      render plain: talker.answer + ", " + talker.name + "!\n"
    rescue ActiveRecord::RecordNotFound => e
      render plain: "I have nothing to say.\n"
    end
  end

  def remember
    unless params.has_key?(:answer)
      render plain: "You forgot the answer :(\n" and return
    end
    unless params.has_key?(:name)
      render plain: "You forgot the name :(\n" and return
    end

    begin
      talker = Talker.find(0)
    rescue ActiveRecord::RecordNotFound => e
      talker = Talker.new
    end
    talker.update(id: 0, answer: params[:answer], name: params[:name])

    render plain: "Got it.\n"
  end
end

class Talker < ActiveRecord::Base
end

Let’s add new paths to the route list:

Rails.application.routes.draw do
  get '/remember', to: 'talker#remember'
  get '/say', to: 'talker#say'

  get '/image', to: 'application#image'
  get '/ping', to: 'application#ping'
end

Now it’s time to define two basic migrations:

class CreateTalkers < ActiveRecord::Migration[6.1]
  def change
    create_table :talkers do |t|
      t.text :answer
    end
  end
end

class AddNameToTalkers < ActiveRecord::Migration[6.1]
  def change
    add_column :talkers, :name, :string
  end
end

… and schema.rb generated based on those migrations:

# This file is auto-generated from the current state of the database.

ActiveRecord::Schema.define(version: 2021_08_17_164348) do

  create_table "talkers", charset: "utf8", force: :cascade do |t|
    t.text "answer"
    t.string "name"
  end

end

New endpoints, /remember and /say, are ready.

Deploying a MySQL database and connecting to it

In real life, a database can be a part of the Kubernetes infrastructure or run outside of it. Outside of Kubernetes, you can deploy and maintain a database yourself or use a managed solution like Amazon RDS. For illustrative purposes, let’s deploy a MySQL database inside the Kubernetes cluster using the following basic StatefulSet:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  serviceName: mysql
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: mysql
        image: mysql:9.1
        ports:
        - containerPort: 3306
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: password
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
  volumeClaimTemplates:
  - metadata:
      name: mysql-data
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 100Mi

---
apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  selector:
    app: mysql
  ports:
  - port: 3306

Note that you can also use a database deployed differently. In this case, you will not need the above StatefulSet, while all further steps remain unchanged.

Now let’s configure our application to use the new database:

production:
  adapter: mysql2
  pool: 5
  host: mysql
  port: 3306
  username: root
  password: password
  database: werf-guide-app
  encoding: utf8

Great, the database and the application are ready to be deployed.

Initializing and migrating the database

There are several ways to initialize and migrate a database when deploying applications to Kubernetes. We will use one straightforward yet efficient method. It involves database migration (and initialization, if needed) by a dedicated Job simultaneously with deploying the application and the database.

We require the following conditions to be met to deploy resources in the desired order:

1. Before starting, the job to initialize/migrate the database must make sure the database is available and ready.
2. Before starting, applications must wait until the database is available and ready and all migrations are complete.

This way, all K8s resources are created at the same time but being run in the following order:

1. The database starts.
2. The initialization/migration Job runs.
3. The applications start.

Let’s now add a Job to perform database migrations/initializations:

apiVersion: batch/v1
kind: Job
metadata:
  # The release revision in the Job name will cause the Job to be recreated every time.
  # This way, we can get around the fact that the Job is immutable.
  name: "setup-and-migrate-db-rev{{ .Release.Revision }}"
spec:
  backoffLimit: 0
  template:
    spec:
      restartPolicy: Never
      imagePullSecrets:
      - name: registrysecret
      containers:
      - name: setup-and-migrate-db
        image: {{ .Values.werf.image.backend }}
        command:
        - sh
        - -euc
        - |
          is_mysql_available() {
            tries=$1
            i=0
            while [ $i -lt $tries ]; do
              mysqladmin -h mysql -P 3306 -u root -p=password ping || return 1
              i=$((i+1))
              sleep 1
            done
          }

          # Waiting for `mysqladmin ping` to run 10 times in a row.
          until is_mysql_available 10; do
            sleep 1
          done

          # Performing the initial configuration (if necessary) or migrations.
          bundle exec rails db:prepare
        env:
        - name: RAILS_ENV
          value: production

Now let’s add an init container to the application’s Deployment. It will wait until the database is ready/initialized and the migrations are completed:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: werf-guide-app
spec:
  replicas: 1
  selector:
    matchLabels:
      app: werf-guide-app
  template:
    metadata:
      labels:
        app: werf-guide-app
    spec:
      imagePullSecrets:
      - name: registrysecret
      initContainers:
      - name: wait-db-readiness
        image: {{ .Values.werf.image.backend }}
        command:
        - sh
        - -euc
        - |
          # Waiting until the database is available and migrations are completed.
          until bundle exec rails db:migrate:status; do
            sleep 1
          done
        env:
        - name: RAILS_ENV
          value: production
      containers:
      - name: backend
        image: {{ .Values.werf.image.backend }}
        command: ["bundle", "exec", "rails", "server"]
        ports:
        - containerPort: 3000
        env:
        - name: RAILS_ENV
          value: production
      - name: frontend
        image: {{ .Values.werf.image.frontend }}
        ports:
        - containerPort: 80

Testing the application/database

Let’s deploy the application:

werf converge --repo <DOCKER HUB USERNAME>/werf-guide-app

You should see the following output:

...
┌ ⛵ image backend
│ ┌ Building stage backend/dockerfile
│ │ backend/dockerfile  Sending build context to Docker daemon  329.2kB
│ │ backend/dockerfile  Step 1/25 : FROM ruby:2.7 as base
│ │ backend/dockerfile   ---> 1faa5f2f8ca3
...
│ │ backend/dockerfile  Step 25/25 : LABEL werf-version=v1.2.12+fix2
│ │ backend/dockerfile   ---> Running in be5cd3407213
│ │ backend/dockerfile  Removing intermediate container be5cd3407213
│ │ backend/dockerfile   ---> 8a86b0b0ad77
│ │ backend/dockerfile  Successfully built 8a86b0b0ad77
│ │ backend/dockerfile  Successfully tagged 90e9d19d-0e9a-463e-904a-c6ce4ed9ed51:latest
│ │ ┌ Store stage into <DOCKER HUB USERNAME>/werf-guide-app
│ │ └ Store stage into <DOCKER HUB USERNAME>/werf-guide-app (17.26 seconds)
│ ├ Info
│ │      name: <DOCKER HUB USERNAME>/werf-guide-app:0eab31032c0afad388f642f8bcf05fdef43adfa68b9ddd21c1ca5e6d-1629288112709
│ │        id: 8a86b0b0ad77
│ │   created: 2022-08-18 15:01:52 +0000 UTC
│ │      size: 364.7 MiB
│ └ Building stage backend/dockerfile (82.09 seconds)
└ ⛵ image backend (87.87 seconds)

┌ ⛵ image frontend
│ ┌ Building stage frontend/dockerfile
│ │ frontend/dockerfile  Sending build context to Docker daemon  329.2kB
│ │ frontend/dockerfile  Step 1/29 : FROM ruby:2.7 as base
│ │ frontend/dockerfile   ---> 1faa5f2f8ca3
...
│ │ frontend/dockerfile  Step 29/29 : LABEL werf-version=v1.2.12+fix2
│ │ frontend/dockerfile   ---> Running in bb3c5dad93fd
│ │ frontend/dockerfile  Removing intermediate container bb3c5dad93fd
│ │ frontend/dockerfile   ---> 4e5dad5c8103
│ │ frontend/dockerfile  Successfully built 4e5dad5c8103
│ │ frontend/dockerfile  Successfully tagged 798eca35-72b4-4bcd-8b1c-9192845bf5c4:latest
│ │ ┌ Store stage into <DOCKER HUB USERNAME>/werf-guide-app
│ │ └ Store stage into <DOCKER HUB USERNAME>/werf-guide-app (9.93 seconds)
│ ├ Info
│ │      name: <DOCKER HUB USERNAME>/werf-guide-app:b6e90d80c9419ca3e1cb93fd3baf0f46632f05c2490881585e9f6598-1629288112504
│ │        id: 4e5dad5c8103
│ │   created: 2022-08-18 15:01:52 +0000 UTC
│ │      size: 9.4 MiB
│ └ Building stage frontend/dockerfile (74.28 seconds)
└ ⛵ image frontend (81.25 seconds)
...
│ ┌ job/setup-and-migrate-db-rev5 po/setup-and-migrate-db-rev5-kdb4t container/setup-and-migrate-db logs
│ │ mysqladmin: connect to server at 'mysql' failed
│ │ error: 'Access denied for user 'root'@'172.17.0.1' (using password: YES)'
...
│ ┌ deploy/werf-guide-app po/werf-guide-app-78f77cf6c4-8fzw6 container/wait-db-readiness logs
│ │
│ │ database: werf-guide-app
│ │
│ │  Status   Migration ID    Migration Name
│ │ --------------------------------------------------
│ │    up     20210817162438  Create talkers
│ │    up     20210817164348  Add name to talkers
│ │
│ └ deploy/werf-guide-app po/werf-guide-app-78f77cf6c4-8fzw6 container/wait-db-readiness logs
...
│ ┌ Status progress
│ │  DEPLOYMENT      REPLICAS  AVAILABLE  UP-TO-DATE
│ │  werf-guide-app  1/1       1          1
│ │  │    POD                         READY  RESTARTS  STATUS
│ │  ├──  guide-app-78f77cf6c4-8fzw6  2/2    0         Running
│ │  └──  guide-app-f98f5ccd9-69xwm   2/2    0         Terminating
│ │  STATEFULSET  REPLICAS  READY  UP-TO-DATE
│ │  mysql        1/1       1      1
│ │  JOB                        ACTIVE  DURATION  SUCCEEDED/FAILED
│ │  setup-and-migrate-db-rev5  0       19s       0->1/0
│ │  │    POD                        READY  RESTARTS  STATUS
│ │  └──  and-migrate-db-rev5-kdb4t  0/1    0         Running  ->  Completed
│ └  Status                     progress
└ Waiting for release resources to become ready (18.57 seconds)

Release "werf-guide-app" has been upgraded. Happy Helming!
NAME: werf-guide-app
LAST DEPLOYED: Wed Aug 18 15:02:13 2022
NAMESPACE: werf-guide-app
STATUS: deployed
REVISION: 5
TEST SUITE: None

Don’t worry if the process seems to be stuck at this point and many errors appear in the messages. This happens due to checking the MySQL status; you just need to wait a bit when it’s done (usually, it takes no more than 1-2 minutes).

Now let’s try to access the /say endpoint that retrieves the data from the database:

curl http://werf-guide-app.test/say

Since the database is still empty, it should return the following message:

I have nothing to say.

Let’s save some data to the database using /remember:

curl "http://werf-guide-app.test/remember?answer=Love+you&name=sweetie"

The database must respond with the following:

Got it.

Let’s try to retrieve the data from the database using the /say endpoint once again:

curl http://werf-guide-app.test/say

If successful, you will see the following output:

Love you, sweetie!

You can also make sure that the data is in the database by directly querying the table contents:

kubectl exec -it statefulset/mysql -- mysql -ppassword -e "SELECT * from talkers" werf-guide-app

You should see the following output:

+----+----------+---------+
| id | answer   | name    |
+----+----------+---------+
|  0 | Love you | sweetie |
+----+----------+---------+

Done!

In this chapter, we turned our application into a stateful one by connecting it to the corresponding database. We deployed the database to the Kubernetes cluster, initialized it, and performed necessary DB migrations. Note that the above approach should work well with any relational database.

As usual, you can see all the changes made in this chapter by running the commands provided at the beginning.