Go to Qiita Advent Calendar 2024 Top
LoginSignup
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@kanata333(miyapana)

Declarative Schema Management with Kubernetes

Posted at

SchemaHero

SchemaHero is a Kubernetes operator for declarative database schema management.

Link to GitHub repository: https://github.com/schemahero/schemahero

Concepts

To be updated

Demo

Install SchemaHero

We can use krew to install shcemahero commands.

❯ kubectl krew install schemahero

❯ kubectl schemahero version
SchemaHero 0.12.1

❯ kubectl schemahero install
The SchemaHero operator has been installed to the cluster

❯ kubectl get pods -n schemahero-system
NAME           READY   STATUS    RESTARTS   AGE
schemahero-0   1/1     Running   0          20s

schemahero manages the following three custom resource definitions

  • migrations: history of creating/modifying/deleting tables
  • databases: to connect “actual” database in mysql pod?
  • tables: tables created in databases

Deploy MySQL

Deploy MySQL pod with the following YAML file.

apiVersion: apps/v1
kind: StatefulSet
metadata:
  labels:
    app: mysql
  name: mysql
  namespace: database
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  serviceName: mysql-headless
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - envFrom:
        - secretRef:
            name: mysql-secret
        image: mysql:5.7
        imagePullPolicy: IfNotPresent
        name: mysql
        ports:
        - containerPort: 3306
          protocol: TCP
  volumeClaimTemplates:
  - apiVersion: v1
    kind: PersistentVolumeClaim
    metadata:
      labels:
        app: mysql
      name: mysql-storage
    spec:
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 10Gi
      volumeMode: Filesystem
---
apiVersion: v1
items:
- apiVersion: v1
  data:
    MYSQL_DATABASE: dGVzdA==  # test
    MYSQL_PASSWORD: bXlzcWw=  # mysql
    MYSQL_ROOT_PASSWORD: cm9vdA==  # root
    MYSQL_USER: bXlzcWw=  # mysql
  kind: Secret
  metadata:
    name: mysql-secret
    namespace: database
  type: Opaque

Apply

❯ kubectl create ns database
namespace/database created

❯ kubectl apply -k k8s/mysql/overlays/database/
secret/mysql-secret created
service/mysql-headless created
statefulset.apps/mysql created

❯ kubectl get all,secret -n database
NAME          READY   STATUS    RESTARTS   AGE
pod/mysql-0   1/1     Running   0          21s

NAME                     TYPE        CLUSTER-IP   EXTERNAL-IP   PORT(S)    AGE
service/mysql-headless   ClusterIP   None         <none>        3306/TCP   23s

NAME                     READY   AGE
statefulset.apps/mysql   1/1     23s

NAME                         TYPE                                  DATA   AGE
secret/default-token-cthb6   kubernetes.io/service-account-token   3      8m12s
secret/mysql-secret          Opaque                                4      23s

Check if test database is created and tables are not created

❯ kubectl exec -it mysql-0 -n database -- mysql -uroot -p$(kubectl get secret -n database  mysql-secret -o yaml | grep MYSQL_ROOT_PASSWORD | sed 's/.*.: \(.*\)/\1/' | base64 --decode) test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> exit
Bye

Check migrations, databases, tables managed by SchemaHero

There are no resources.

❯ kubectl schemahero get migrations
No resources found.

❯ kubectl schemahero get databases
No reosurces found.

❯ kubectl schemahero get tables
No resources found.

Deploy database (connection)

Through database CRD, we can apply changes for test database. In YAML file of database (connection), we need to specify how to connect with the database in the format of username:password@tcp(host:port)/dbname?tls=false.

YAML file

apiVersion: databases.schemahero.io/v1alpha4
kind: Database
metadata:
  name: test
spec:
  connection:
    mysql:
      uri:
        value: mysql:mysql@tcp(mysql-0.mysql-headless.database.svc.cluster.local:3306)/test?tls=false

Apply the YAML file

❯ kubectl apply -f k8s/mysql/base/schema/test-db.yaml -n database
database.databases.schemahero.io/test created

❯ kubectl schemahero get databases
NAME  NAMESPACE  PENDING
test  database   0

❯ kubectl get all,secret -n database
NAME                    READY   STATUS    RESTARTS   AGE
pod/mysql-0             1/1     Running   0          2m49s
pod/test-controller-0   1/1     Running   0          11s

NAME                     TYPE        CLUSTER-IP   EXTERNAL-IP   PORT(S)    AGE
service/mysql-headless   ClusterIP   None         <none>        3306/TCP   2m51s

NAME                               READY   AGE
statefulset.apps/mysql             1/1     2m51s
statefulset.apps/test-controller   1/1     12s

NAME                                 TYPE                                  DATA   AGE
secret/default-token-cthb6           kubernetes.io/service-account-token   3      10m
secret/mysql-secret                  Opaque                                4      2m51s
secret/schemahero-test-token-nt7jl   kubernetes.io/service-account-token   3      12s

Check the log of test-controller-0 pod

❯ kubectl logs -n database test-controller-0
{"level":"info","msg":"Starting schemahero version [{Version:0.12.1 GitSHA:ee692c5 BuildTime:2021-03-26 21:51:56 +0000 UTC TimeFallback:}]"}

Deploy notes table

The YAML file

  • Specify target database
  • Specify table name
  • Describe schema of the table
apiVersion: schemas.schemahero.io/v1alpha4
kind: Table
metadata:
  name: notes
spec:
  database: test
  name: notes
  schema:
    mysql:
      primaryKey: [id]
      columns:
        - name: id
          type: int
          constraints:
            notNull: true
        - name: title
          type: varchar(50)
        - name: description
          type: varchar(100)
        - name: created_at
          type: date

Apply

❯ kubectl apply -f k8s/mysql/base/schema/notes-table.yaml -n database
table.schemas.schemahero.io/notes created

❯ kubectl schemahero get tables
NAME   DATABASE  PENDING
notes  test      1

❯ kubectl schemahero get migrations --all-namespaces
ID       DATABASE  TABLE  PLANNED  EXECUTED  APPROVED  REJECTED
7cbbee2  test      notes  1m19s

What’s PLAANED status?

→ The migration is just planned and isn’t executed yet. Let’s check it. (If we execute kubectl apply -f …, the table isn’t created).

❯ kubectl exec -it mysql-0 -n database -- mysql -uroot -p$(kubectl get secret -n database  mysql-secret -o yaml | grep MYSQL_ROOT_PASSWORD | sed 's/.*.: \(.*\)/\1/' | base64 --decode) test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> exit
Bye

Let’s see the migration. The DDL statement will be executed if we approve the migration

❯ kubectl schemahero describe migration 7cbbee2 -n database

Migration Name: 7cbbee2

Generated DDL Statement (generated at 2021-05-17T22:48:46+09:00):
  create table `notes` (`id` int (11) not null, `title` varchar (50), `description` varchar (100), `created_at` date, primary key (`id`))

To apply this migration:
  kubectl schemahero -n database approve migration 7cbbee2

To recalculate this migration against the current schema:
  kubectl schemahero -n database recalculate migration 7cbbee2

To deny and cancel this migration:
  kubectl schemahero -n database reject migration 7cbbee2

Approve the migration

We can guess that the query is executed and the table is created if the migration is approved.

❯ kubectl schemahero -n database approve migration 7cbbee2
Migration 7cbbee2 approved

❯ kubectl schemahero get migrations --all-namespaces
ID       DATABASE  TABLE  PLANNED  EXECUTED  APPROVED  REJECTED
7cbbee2  test      notes  8m6s     14s       14s

EXECUTED and APPROVED status are updated. Let’s see tables in test database.

❯ kubectl exec -it mysql-0 -n database -- mysql -uroot -p$(kubectl get secret -n database  mysql-secret -o yaml | grep MYSQL_ROOT_PASSWORD | sed 's/.*.: \(.*\)/\1/' | base64 --decode) test
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| notes          |
+----------------+
1 row in set (0.00 sec)

mysql> describe notes;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | NO   | PRI | NULL    |       |
| title       | varchar(50)  | YES  |     | NULL    |       |
| description | varchar(100) | YES  |     | NULL    |       |
| created_at  | date         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> exit
Bye

→ notes table is created with the desired schema!!!

Let’s see the log of test-controller-0 pod

The pod executed the query

❯ kubectl logs -n database test-controller-0
{"level":"info","msg":"Starting schemahero version [{Version:0.12.1 GitSHA:ee692c5 BuildTime:2021-03-26 21:51:56 +0000 UTC TimeFallback:}]"}
Executing query "create table `notes` (`id` int (11) not null, `title` varchar (50), `description` varchar (100), `created_at` date, primary key (`id`))"

❯ kubectl schemahero get tables
NAME   DATABASE  PENDING
notes  test      0

❯ kubectl schemahero get migrations --all-namespaces
ID       DATABASE  TABLE  PLANNED  EXECUTED  APPROVED  REJECTED
7cbbee2  test      notes  21m      13m       13m

Pros & Cons

Pros

  • We can manage databases and tables in RDS and Cassandra with Kubernetes
  • No need to grant strong permission to developers because pods will execute actual queries
  • We can know what queries are executed with kubectl schemahero get migrations --all-namespaces command
  • We can use Kubernetes secret that is used in database CRD
  • With Argo CD, we can know easily that the current schema is applied or not?

Cons

  • Only limited data types are supported
    • For example, datetime is not supported for now
  • kubectl apply -f ... is not enough to create tables
    • This command makes a plan of what query is executed
3
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?