【CAP】CAPをBTPのPostgreSQLにデプロイ

## はじめに
SAP Cloud Application Programming Model（CAP）は、本番環境で使用するDBとして[SAP HANAのみをサポートしています](https://cap.cloud.sap/docs/guides/databases)。しかし、単にデータを格納するための箱として使うには、SAP HANAは少々オーバースペックではないか？（お値段も…）という気がします。

CAPのよいところは、CDSの定義だけで裏でテーブルやビューを作ってくれたり、サービスからのDBへの接続を開発者側が意識せずに行えるところでしょう。もしSAP HANA以外のDBを使おうとすると、CAPが使えないのでそのあたりを開発者側で管理する必要が出てきます。
そんな中、CAPをPostgreSQLでも使えるようにしようと有志によって2つのNode.jsモジュールが作られました。

- cds-pg
- cds-dbm

### cds-pg – The PostgreSQL adapter for SAP CDS
まず、2020年の8月に[Volker Buzek氏](https://people.sap.com/vobu)、 [Gregor Wolf氏](https://people.sap.com/gregor.wolf)によって[cds-pg](https://github.com/sapmentors/cds-pg)が作られました。cds-pgを使うと、CAPのプロジェクトをPostgreSQLに対応させることができます。なお、cds-pgは昨年行われた[Devtoverfest](https://blogs.sap.com/2020/11/09/devtoberfest-contest-voting/)のコンテストで優勝しています。
現状のステータスは[ready to be used](https://github.com/sapmentors/cds-pg#current-status)となっており、基本的な機能はそろったようです。（本番のプロジェクトで使うかどうかは判断が必要ですが）

### cds-dbm – Core Data Services – Database Migrations
続いて、2020年の11月に[Mike Zaschka氏](https://people.sap.com/mike_za)によって[cds-dbm](https://github.com/mikezaschka/cds-dbm)が作られました。cds-dbmは、CAPで作ったCDSをデータベースへデプロイするためのモジュールです。現在はPostgreSQLのみがサポートされていますが、将来的に他のデータベースにも対応できるようなデザインになっています。

cds-pgも一応デプロイ機能を持っていますが、削除 + 再登録方式のため[本番環境へのデプロイで使うことは推奨されていません](https://github.com/sapmentors/cds-pg#cds-deployment)。これに対しcds-dbmは既存のオブジェクトを消さずに差分をデプロイできる機能を備えています。

### 関連ブログ

- [PostgreSQL persistence adapter for CAP (Node.js)](https://blogs.sap.com/2020/08/20/postgresql-persistence-adapter-for-cap-node.js/)
- [Getting started with CAP on PostgreSQL (Node.js)](https://blogs.sap.com/2020/11/16/getting-started-with-cap-on-postgresql-node.js/)
- [Run and Deploy CAP with PostgreSQL on SAP BTP Cloud Foundry (Node.js)](https://blogs.sap.com/2020/11/30/run-and-deploy-cap-with-postgresql-on-sap-cloud-platform-cloud-foundry-node.js/)

### サンプルプロジェクト

- [cap-devtoberfest](https://github.com/mikezaschka/cap-devtoberfest/tree/main): CAP on PostgreSQLのシンプルなサンプル。ローカルのPostgreSQL、およびBTPのPostgreSQL用のブランチがある
- [pg-beershop](https://github.com/gregorwolf/pg-beershop): さまざまな環境（CF, Kyma, Microsoft Azure, Google Cloud Platform）へのデプロイ方法を紹介

## この記事でやってみること
この記事では、以下のブログを参考に新規のCAPプロジェクトを作ってBTPのPostgreSQLにデプロイしてみます。なお、私はPostgreSQLは触ったことがありません。
[Run and Deploy CAP with PostgreSQL on SAP BTP Cloud Foundry (Node.js)](https://blogs.sap.com/2020/11/30/run-and-deploy-cap-with-postgresql-on-sap-cloud-platform-cloud-foundry-node.js/)

### 前提条件
BTPのEntitlementsに**PostgreSQL, hyperscaler option**が必要です。私トライアルアカウントにはデフォルトで存在していました。なければ"Configure Entitlements"から追加しましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/96165fec-3726-65a4-9ae8-e2c1713953f1.png)

### 開発環境
開発環境は、VS Codeを使いました。SAP Business Application Studio(BAS)でもできます。
※CAPプロジェクトを作成するために必要な設定については[ドキュメント](https://cap.cloud.sap/docs/get-started/)をご参照ください。

## ステップ
1. CAPプロジェクトを作成
1. cds-pg, cds-dbmをDependencyに追加
1. package.jsonの設定
1. mta.yamlを作成
1. package.jsonにビルド用の設定を追加
1. mta.yamlの設定
1. Cloud Foundryにデプロイ

### 1. CAPプロジェクトを作成
以下のコマンドでCAPプロジェクトを生成します。

```
cds init cap-postgres-sample
```
サンプルファイルを追加します。

```
cds add samples
```
db/data-model.cdsとsrv/cat-service.cdsが追加されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/d156a485-bdfb-15a4-ae03-c8998e2460b8.png)
Booksエンティティに`@readonly`がついていますが、登録も試してみたいので`@readonly`は外します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/6760611a-30ee-3de1-e783-4b54a1688c62.png)

### 2. cds-pg, cds-dbmをDependencyに追加
以下のコマンドでDependencyを追加します。

```
npm i cds-pg
npm i cds-dbm
```

### 3. package.jsonの設定
package.jsonに以下の設定を追加します。

```json:package.json
  "cds": {
    "requires": {
      "db": {
        "kind": "database"
      },
      "database": {
        "impl": "cds-pg",
        "model": [
          "srv"
        ]
      }
    },
    "migrations": {
      "db": {
        "schema": {
          "default": "public",
          "clone": "_cdsdbm_clone",
          "reference": "_cdsdbm_ref"
        },
        "deploy": {
          "tmpFile": "tmp/_autodeploy.json",
          "undeployFile": "db/undeploy.json"
        }
      }
    }
  }
```

`cds.requres`セクションの設定内容は基本的に[通常のCAPプロジェクト](https://cap.cloud.sap/docs/node.js/cds-connect#cds-env-requires)と同じです。`cds.requres.db.kind`でdbに任意の名前を指定し、`cds.requres.db.kind.<任意の名前>`でそのサービスの設定をします。SAP HANAやSQLiteを使う場合との違いは、サービスの実装にcds-pgを使用するところです。

`cds.migrations`セクションはcds-dbmで使用します。

- `cds.migrations.db.schema`: [差分をデプロイ](https://github.com/mikezaschka/cds-dbm#automated-delta-deployments)する際に使用する設定
- `cds.migrations.deploy.undeployFile`: スキーマの中で不要になったテーブルやビューを記載するファイル

### 4. mta.yamlを作成
以下のコマンドでmta.yamlを作成します。

```
cds add mta
```

### 5. package.jsonにビルド用の設定を追加
package.jsonのcdsセクションに以下の設定を追加します。

```json:package.json
  "cds": {
    "build": {
      "tasks": [
        {
          "use": "node-cf",
          "src": "srv"
        },
        {
          "use": "postgres-cf",
          "src": "db",
          "options": {
            "deployCmd": "npx cds-dbm deploy --load-via delta --auto-undeploy"
          }
        }
      ]
    },
    "requires": {
    ...
    },
```
ここでは2つのビルドタスクを指定します。

- `node-cf`: `@sap/cds`によって提供される標準のビルドタスク
- `postgres-cf`: PostgreSQLにかかわるタスク

postgres-cfのオプションである`deployCmd`は、Cloud Foundryにデプロイする際に使用するコマンドです。ここで指定しなければデフォルトのコマンド`npx cds-dbm deploy --load-via delta`が使用されます。

※ビルド用の設定をしてからmta.yamlを生成すると以下のエラーになったので、先にmta.yamlを作っておく必要があるようです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/648d10ff-e491-f42d-58f1-8360157181b4.png)

#### ビルドしてみる
以下のコマンドでビルドを実行してみます。

```
npx cds-dbm build
```
すると、プロジェクト直下にgenというフォルダが作られます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/be836b5d-b24b-39e1-f698-40c65e11ce86.png)

genフォルダの中には以下のファイルがあります。

|ファイル  |説明  |
|---|---|
|csn.json  |データモデルを[CSN](https://cap.cloud.sap/docs/cds/csn)形式で定義したもの|
|package.json|必要なdependencyを記述したファイル|
|deploy.sh|環境変数の設定およびデプロイを実行するスクリプト|
|apt.yml|[CF apt-buildpack](https://github.com/cloudfoundry/apt-buildpack)を使用するための設定ファイル|
|manifest.ym|`cf deploy`の代わりに`cf push`コマンドを使ってデプロイする場合に使用するファイル|
|undeploy.json|PostgreSQLデータベースから削除するテーブルやビューを記載したファイル（対象がある場合のみ）|
|data/*csv|もとのdbフォルダからコピーされた.csvファイル。デプロイ時にテーブルにインポートされる|

### 6. mta.yamlの設定
ステップ4.で作成したmta.yamlファイルに以下の変更を加えます。

#### 6.1. ビルドコマンドを変更
cdsのbuildコマンドをcds-dbmのbuildコマンドに置き換えます。

```yaml
build-parameters:
  before-all:
   - builder: custom
     commands:
      - npm install --production
      # - npx -p @sap/cds-dk cds build --production　削除
      - npx cds-dbm build --production  #追加
```

#### 6.2. PostgreSQLサービスを定義
PostgreSQLサービスの定義をresourcesセクションに追加します。

```yaml
resources:
  - name: cap-postgres-sample-db
    parameters:
      path: ./pg-options.json
      service: postgresql-db
      service-plan: trial
      skip-service-updates:
        parameters: true
    type: org.cloudfoundry.managed-service   
```

プロジェクト直下に`pg-options.json`ファイルを作成します。このファイルにはPostgreSQLの設定パラメータを記載します。指定可能なパラメータについては[ドキュメント](https://help.sap.com/viewer/b3fe3621fa4a4ed28d7bbe3d6d88f036/Cloud/en-US/0630e03aa45d479eaf806c564dc2447a.html)をご参照ください。
ここでは、PostgreSQLのバージョンを指定しています。

```json:pg-options.json
{
    "engine_version": "11"
}
```

#### 6.3. postgres-deployerモジュールを作成
テーブルやビューをPostgreSQLにデプロイするためのモジュールを作成します。このモジュールはgenフォルダにある`deploy.sh`を実行します。デプロイ時に一時的に動くモジュールのため、`no-route`（ルートを作らない）および`no-start`（アプリケーションを開始しない）がtrueに設定されています。

```yaml
 - name:  cap-postgres-sample-db-deployer
   type: custom
   path: gen/db
   parameters:
     buildpacks: [https://github.com/cloudfoundry/apt-buildpack#v0.2.2, nodejs_buildpack] 
     no-route: true
     no-start: true
     disk-quota: 2GB
     memory: 512MB
     tasks:
     - name: deploy_to_postgresql
       command: chmod 755 deploy.sh && ./deploy.sh
       disk-quota: 2GB
       memory: 512MB      
   build-parameters:
     ignore: ["node_modules/"]
   requires:
     - name:  cap-postgres-sample-db    
```

#### 6.4. PostgreSQLサービスをサーバーモジュールに追加
最後に、PostgreSQLサービスをサーバーモジュール（~srv）の`requires`セクションに追加します。

```yaml
 # --------------------- SERVER MODULE ------------------------
 - name: cap-postgres-sample-srv
 # ------------------------------------------------------------
   type: nodejs
   path: gen/srv
   provides:
    - name: srv-api      # required by consumers of CAP services (e.g. approuter)
      properties:
        srv-url: ${default-url}
   requires:
    - name: cap-postgres-sample-db  #追加
```

### 7. Cloud Foundryにデプロイ
以下のコマンドでビルドします。

```
mbt build
```

以下のコマンドでCloud Foundryにデプロイします。

```
cf deploy mta_archives/cap-postgres-sample_1.0.0.mtar
```
初回のデプロイでは、PostgreSQLのサービスインスタンスの作成にかなり時間がかかりました。気長に待ちましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/bc77f088-1948-7c99-45f0-6f00813d38de.png)

#### トラブルシューティング
cap-postgres-sample-srvの開始時にエラーになってしまいました。以下は`cf logs cap-postgres-sample-srv --recent`で見たログです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/52b39a6f-30b1-285d-ee7f-072dff77073c.png)
[ドキュメント](https://cap.cloud.sap/docs/advanced/troubleshooting#node-version)を見たところ、@sap/cdsのバージョンとデプロイ先のNode.jsのバージョンが合っていないようです。対応として、package.jsonに以下の設定を追加しました。この後、正常にデプロイすることができました。

```json
  "engines": {
    "node": "14.16.0"
  }
```

## 動作確認
デプロイされたサービスを実行します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/91d0c8bc-c574-0bed-978e-437958a6b563.png)

### READ, CREATE
Booksエンティティを選択すると、データが取得できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/dbb66b00-3cf0-f559-5b36-0a3b3e548967.png)
Postmanからデータを登録してみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/62de7445-c252-5cb8-2589-321f5a195de4.png)
更新されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/13676a44-e227-2a60-89f7-0d4b125d9fa7.png)

### 差分更新
差分更新ができることを確認するため、新しいエンティティ(Authors)を追加してみます。

```sql:db/data-model.cds
namespace my.bookshop;

entity Books {
  key ID : Integer;
  title  : String;
  stock  : Integer;
  author : Association to Authors;
}

entity Authors {
  key ID : Integer;
  name   : String(111);
  dateOfBirth  : Date;
  dateOfDeath  : Date;
  placeOfBirth : String;
  placeOfDeath : String;
  books  : Association to many Books on books.author = $self;
}
```
```srv/cat-service.cds
using my.bookshop as my from '../db/data-model';

service CatalogService {
    entity Books as projection on my.Books;
    entity Authors as projection on my.Authors;
}
```
Authors用のcsvファイルを追加します。

```db/data/my.bookshop-Authors.csv
ID;name;dateOfBirth;placeOfBirth;dateOfDeath;placeOfDeath
101;Emily Brontë;1818-07-30;Thornton, Yorkshire;1848-12-19;Haworth, Yorkshire
107;Charlotte Brontë;1818-04-21;Thornton, Yorkshire;1855-03-31;Haworth, Yorkshire
150;Edgar Allen Poe;1809-01-19;Boston, Massachusetts;1849-10-07;Baltimore, Maryland
170;Richard Carpenter;1929-08-14;King’s Lynn, Norfolk;2012-02-26;Hertfordshire, England
```

Booksのデータにauthor_IDを追加します。

```db/data/my.bookshop-Books.csv
ID;title;stock;author_ID;
1;Wuthering Heights;100;101
2;Jane Eyre;500;107
```

ビルド、デプロイしてみると、Postmanから追加したレコードは消えず、既存のレコードにはauthor_IDが追加されていました。差分更新ができることが確認できました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/ea74da86-dc86-caf7-8c4e-d460ce5c0d89.png)

### テーブル/ビューの削除
テーブル、ビューを削除したときは、以下のような動きになると想定していました。

- ビルドしたときにgenフォルダに`undeploy.json`ができる
- デプロイすると、`undeploy.json`に指定されたテーブル、ビューが削除される

Authorsエンティティを削除して初期状態に戻してビルドしたところ、`undeploy.json`ファイルはありませんでした。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/49ad7ba5-3e62-14b5-93f6-f9d7492a5ba3.png)

しかし、デプロイしたあとでテーブル、ビューを確認すると、Authorsに関連するものは消えていました。

```
\dt
                   リレーション一覧
 スキーマ |         名前          |  タイプ  | 所有者
----------+-----------------------+----------+--------
 public   | databasechangelog     | テーブル | dbo
 public   | databasechangeloglock | テーブル | dbo
 public   | my_bookshop_books     | テーブル | dbo
(3 行)
```

```
\dv
                  リレーション一覧
 スキーマ |         名前         | タイプ |  所有者
----------+----------------------+--------+----------
 public   | catalogservice_books | ビュー | dbo
 public   | pg_stat_statements   | ビュー | rdsadmin
(2 行)
```
[ドキュメント](https://github.com/mikezaschka/cds-dbm#dropping-tablesviews)を見ると、デプロイコマンドに`--auto-undeploy`オプションをつけたときはCDSモデルで使用しなくなったテーブル/ビューを削除するとありました。他のプロジェクトでも同じデータベース（スキーマ）を使っている場合は、`undeploy.json`を使って削除対象をコントロールしたほうがいいとのことです。

つまり、他のプロジェクトとスキーマを共有している場合は

- `--auto-undeploy`オプションをつけない
- 削除したいテーブル、スキーマがある場合は他のプロジェクトで使用していないことを確認し、`undeploy.json`にマニュアルでエントリする

という対応になるのかな、と思いました（未検証）。

## PostgreSQLの中身を見てみる
PostgreSQLのインスタンスはできましたが、HANAのDatabase Explorer的なものは用意されていません。データベースの中身を見るにはコマンドラインを使うしかなさそうです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/aee47c23-9433-fdda-97c5-042b96960804.png)

[ドキュメント](https://help.sap.com/viewer/b3fe3621fa4a4ed28d7bbe3d6d88f036/Cloud/en-US/69036a6c0b3b4377a1ae6a1edd084988.html)を参考に、サービスインスタンスにSSHで接続してみます。

### 1. PostgreSQLのツールをインストール
Windowsの場合、こちらの記事が参考になります。
[PostgreSQL を Windows にインストールするには](https://qiita.com/tom-sato/items/037b8f8cb4b326710f71)
インストール後にPostgreSQLのbinフォルダをPATHに追加しておきました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/43a92e78-4077-403d-3f5e-60b6d1d3edbb.png)


### 2. CAPサービスでSSHを有効化
PostgreSQLのサービスインスタンスに接続するために、ホストとなるアプリケーションが必要なようです。今回はCAPサービスを使います。

```
cf enable-ssh cap-postgres-sample-srv
cf restart cap-postgres-sample-srv //sshを有効化した後はサービスの再起動が必要
```

### 3. PostgreSQLのサービスインスタンスのサービスキーを登録
以下のコマンドでサービスキーを登録します。BTPのコックピットからもできます。

```
cf create-service-key cap-postgres-sample-db sk1
```

サービスキーの中身を確認します。ここに表示される情報をSSHの接続とデータベースへのログインに使用します。

```
cf service-key cap-postgres-sample-db sk1

{
 "dbname": "XpcvTMpmdPia",
 "hostname": "postgres-0d5d7ec2-1978-4166-a150-fed3dcff15a5.ce4jcviyvogb.eu-central-1.rds.amazonaws.com",
 "password": "xxxxxxxxxxxx",
 "port": "2284",
 "sslcert": "-----BEGIN CERTIFICATE-----xxx-----END CERTIFICATE-----",
 "sslrootcert": "-----BEGIN CERTIFICATE-----xxx-----END CERTIFICATE-----",
 "uri": "postgres://64bb923de564:a699c51d71e2@postgres-0d5d7ec2-1978-4166-a150-fed3dcff15a5.ce4jcviyvogb.eu-central-1.rds.amazonaws.com:2284/XpcvTMpmdPia",
 "username": "xxxxxxxxxxxx"
}
```
### 4. CAPサービスにSSHで接続
以下のコマンドを実行します。最初の`63306`は任意のローカルポートです。`<hostname>`および`<port>`はサービスキーに記載されたものを使用します。

```
cf ssh -L 63306:<hostname>:<port> cap-postgres-sample-srv
```
以下のエラーが出た場合は、アプリケーションを再起動します。
>"Error opening SSH connection: ssh: handshake failed: ssh: unable to authenticate, attempted methods [none password], no supported methods remain"

```
cf restart cap-postgres-sample-srv
```

### 5. psqlクライアントを使ってデータベースにアクセス
新しいターミナルウインドウを立ち上げて以下のコマンドを実行します。`<dbname>`および`<username>`はサービスキーに記載されたものを使用します。次にパスワードを聞かれるので、サービスキーに記載されたパスワードを指定します。

```
psql -d <dbname> -U <username> -p 63306 -h localhost
```
以下のようにデータベースが指定された状態になりました。ここからは、コマンドを使ってテーブルやビューを見ることができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/ebb0063d-af70-4589-744c-57c9575a4875.png)

参考：[17 Practical psql Commands That You Don’t Want To Miss](https://www.postgresqltutorial.com/psql-commands/)

#### テーブル一覧を表示
```
\dt
```
```
                   リレーション一覧
 スキーマ |         名前          |  タイプ  | 所有者
----------+-----------------------+----------+--------
 public   | databasechangelog     | テーブル | dbo
 public   | databasechangeloglock | テーブル | dbo
 public   | my_bookshop_authors   | テーブル | dbo
 public   | my_bookshop_books     | テーブル | dbo
(4 行)
```

#### テーブル定義を表示
```
\d my_bookshop_books
```

```
                     テーブル"public.my_bookshop_books"
    列     |         タイプ          | 照合順序 | Null 値を許容 | デフォルト
-----------+-------------------------+----------+---------------+------------
 id        | integer                 |          | not null      |
 title     | character varying(5000) |          |               |
 stock     | integer                 |          |               |
 author_id | integer                 |          |               |
インデックス:
    "my_bookshop_books_pkey" PRIMARY KEY, btree (id)
```


#### ビュー一覧を表示

```
\dv
```

```
                   リレーション一覧
 スキーマ |          名前          | タイプ |  所有者
----------+------------------------+--------+----------
 public   | catalogservice_authors | ビュー | dbo
 public   | catalogservice_books   | ビュー | dbo
 public   | pg_stat_statements     | ビュー | rdsadmin
(3 行)
```

#### テーブルからデータを取得

```
SELECT * FROM my_bookshop_books;
```
```
 id |       title       | stock | author_id
----+-------------------+-------+-----------
  3 | Postman           |   100 |
  1 | Wuthering Heights |   100 |       101
  2 | Jane Eyre         |   500 |       107
(3 行)
```

## おわりに
去年CAPでPostgreSQLを使えるようになったと聞いたときには、「PostgreSQLを使ったこともないし、難しそうと」思っていましたが、やってみるとかなりスムーズに行けることがわかりました。それはコミュニティの人達がcds-pgとcds-dbmを改善し続けてくれたおかげだと思います。今後、CAP + PostgreSQLという組み合わせがプロジェクトにおいても選択肢になってくるのではないでしょうか。