Declarative Schema Management with Kubernetes

# 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.

- `krew` is the package manager for `kubectl` plugins (ref. https://github.com/kubernetes-sigs/krew)

```bash
❯ 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.

```yaml
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

```bash
❯ 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.

```bash
❯ 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

```yaml
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

```bash
❯ 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

```bash
❯ 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

```yaml
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

```bash
❯ 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).

```bash
❯ 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

```bash
❯ 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.

```bash
❯ 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.

```bash
❯ 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