【BTP】Node.jsとPostgreSQLでシンプルなアプリケーションを作成（1）準備編

## はじめに
先日、以下の記事を書きました。
[【CAP】CAPをBTPのPostgreSQLにデプロイ](https://qiita.com/tami/items/a7a6b019cf8e82efcf8e)

上記の記事で紹介した[cds-pg](https://github.com/sapmentors/cds-pg)、[cds-dbm](https://github.com/mikezaschka/cds-dbm)を使うと、CAPで作成したCDSモデルをPostgreSQLにデプロイして使うことができます。
CAPはテーブル、ビューの登録やCRUD処理を自動的に行ってくれるので、開発者がこれらの作業を意識する必要がありません。一方で、「もしCAPを使わなかったら何をする必要があるのか？」についても知っておきたいと思いました。

以下のサンプルプロジェクト（チュートリアル）があったので、これを参考にシンプルなNode.jsアプリを作成して手順を確認してみます。
[SAP-samples/cloud-cf-helloworld-nodejs](https://github.com/SAP-samples/cloud-cf-helloworld-nodejs/tree/master)

＜シリーズの目次＞

- 【BTP】Node.jsとPostgreSQLでシンプルなアプリケーションを作成（1）準備編
- [【BTP】Node.jsとPostgreSQLでシンプルなアプリケーションを作成（2）CRUD処理編](https://qiita.com/tami/items/f1a49acf3ae01f14c79e)
- [【BTP】Node.jsとPostgreSQLでシンプルなアプリケーションを作成（3）Approuter編](https://qiita.com/tami/items/fe616ade4eccd723ff36)
- [【BTP】Node.jsとPostgreSQLでシンプルなアプリケーションを作成（4）UI編](https://qiita.com/tami/items/56651f04d9eb9c3db2c4)

ソースコードは[GitHub](https://github.com/miyasuta/node-postgres-sample)に格納しています。記事と合わせてご参照ください。


## この記事のゴール
今回は準備編として、以下の状態を作ります。

- Node.jsアプリケーションとBTPのPostgreSQLのサービスインスタンスがバインドされている
- PostgreSQLにアプリケーションで使うテーブルが登録されている
- Node.jsアプリケーションからBTPのPostgreSQLに接続できる

## ステップ
1. Node.jsアプリケーションとPostgreSQLのサービスインスタンスをバインド
2. PostgreSQLにテーブルを登録
2. Node.jsアプリケーションからPostgreSQLに接続

### 1. Node.jsアプリケーションとPostgreSQLのサービスインスタンスをバインド
#### 1.1. PostgreSQLのサービスインスタンスを登録
以下のコマンドでサービスインタンスを登録します。

```
cf create-service postgresql-db trial <サービスインスタンス名>
```

トライアル環境だとPostgreSQLのサービスインスタンスを1つしか作れないので、既存のサービスインスタンスを流用してもよいです。私は登録済のサービスインスタンス`cap-posgre-sample-db`を使用しました。

#### 1.2. Node.jsプロジェクトの作成
新規プロジェクト用のフォルダを作成し、その下にsrvフォルダを作成します。

```
node-postgres-sample
　└ srv
```

srvフォルダに移動し、package.jsonを作成します。

```
cd node-postgres-sample/srv
npm init --yes
```
#### 1.3. Dependencyをインストール
以下4つのパッケージをインストールします。

```
npm i express body-parser pg-promise @sap/xsenv
```
- [pg-promise](https://github.com/vitaly-t/pg-promise)はPostgreSQLに接続したり、クエリを実行したりするのに使用します。
- [@sap/xsenv](https://www.npmjs.com/package/@sap/xsenv)はアプリケーションの環境変数にアクセスするのに使用します。

package.jsonに`start`スクリプトを追加し、以下の状態とします。

```json:srv/package.json
{
  "name": "node-postgres-sample",
  "version": "1.0.0",
  "description": "",
  "scripts": {
    "start": "node server.js"
  },
  "dependencies": {
    "@sap/xsenv": "^3.1.0",
    "body-parser": "^1.19.0",
    "express": "^4.17.1",
    "pg-promise": "^10.10.2"
  }
}
```

#### 1.4. server.jsを作成
server.jsファイルを作成します。現段階では、まだデータベースとの接続はしていません。

```javascript:srv/server.js
'use strict';

const express = require('express')
const bodyParser = require('body-parser')

var _db = undefined
const app = express()

app.use(bodyParser.json())

app.get('/', function (req, res) {
    res.send("Hello!")
})

var PORT = process.env.PORT || 8088
var server = app.listen(PORT, function() {
    const host = server.address().address
    const port = server.address().port
    console.log(`Example app listening at http://${host}:${port}`)    
})
```

`npm start`を実行し、`http://localhost:8088/`を開くと以下の画面が表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/2ea361a8-dd3b-7aa0-e271-060f4f83e1ca.png)

#### 1.5. mta.yamlを作成
プロジェクト直下にmta.yamlファイルを作成します。

```yaml:mta.yaml
_schema-version: '3.1'
ID: node-postgres-sample
version: 1.0.0

modules:
 - name: node-postgres-sample-srv
   type: nodejs
   path: srv
   provides:
    - name: srv-api
      properties:
        srv-url: ${default-url}
   build-parameters:
     ignore: ["node_modules/"]        
   requires:
    - name: cap-posgre-sample-db
    
resources:
  - name: cap-posgre-sample-db
    parameters:
      service: postgresql-db
      service-plan: trial
      skip-service-updates:
        parameters: true
    type: org.cloudfoundry.existing-service   #登録済サービスインスタンスのため、existing-serviceとする
```
この段階で、プロジェクトの構成は以下のようになっています。

```
node-postgres-sample
　└ srv
    　|- package.json
    　└  server.js
　└ mta.yaml
```

#### 1.6. Cloud Foundryにデプロイ
プロジェクトのルートで以下のコマンドを実行してCloud Foundryにデプロイします。

```
mbt build
cf deploy mta_archives/node-postgres-sample_1.0.0.mtar
```

### 2. PostgreSQLにテーブルを登録
PostgreSQLにアプリケーションで使うテーブルを登録します。このために、PostgreSQLにSSHを使って接続し、コマンドラインを使って操作を行います。ツールのインストール方法、およびPostgreSQLへの接続方法については、[こちら](https://qiita.com/tami/items/a7a6b019cf8e82efcf8e#postgresql%E3%81%AE%E4%B8%AD%E8%BA%AB%E3%82%92%E8%A6%8B%E3%81%A6%E3%81%BF%E3%82%8B)の記事をご参照ください。

※PostgreSQLのサービスインスタンスに接続するため、ホストとなるアプリケーションが必要となります。そのためにステップ1を先に実施しました。今回、ホストとなるアプリケーションは`node-postgres-sample-srv`となります。

#### 2.1. テーブルを登録
以下のSQLを実行し、`products`テーブルを登録します。

```
CREATE TABLE products (
id serial PRIMARY KEY,
name varchar(100),
price integer
);
```

#### 2.2. テーブルにデータを登録
以下のSQLを実行し、テーブルにデータを登録します。
idは[serial](https://www.postgresqltutorial.com/postgresql-serial/)のため自動採番されるので、指定する必要がありません。

```
INSERT INTO products(name, price) VALUES ('banana', 100);
```
レコードが登録されたことを確認します。

```
SELECT * FROM products;
 id |  name  | price
----+--------+-------
  1 | banana |   100
(1 行)
```

### 3. Node.jsアプリケーションからPostgreSQLに接続
srvフォルダの配下にdb-conn.jsというファイルを作成します。ここにはデータベースへの接続を行うための処理を記述します。
プロジェクトの構成は以下のようになります。

```
node-postgres-sample
　└ srv
    　|- package.json
    　|- db-conn.js
    　└  server.js
　└ mta.yaml
```

```javascript:srv/db-conn.js
'use strict'
const xsenv = require('@sap/xsenv')

function getConfig () {
    var config = {}
    if (process.env.VCAP_SERVICES) {
        config = {
            connectionString: xsenv.cfServiceCredentials('cap-posgre-sample-db').uri,
            ssl: { rejectUnauthorized: false }
        }
    } else {
        console.log('running locally is not supported')
    }
    return config;
}
        
function getDB (cb) {
    let pgp = require('pg-promise')({
        // Initialization Options
    })
    var db = pgp(getConfig())
    let sql = `SELECT id FROM products WHERE id = 1;`
    db.query(sql)
    .then((result) => {
        console.log('database initialized', result)
        cb(null, db)
        return
    })
    .catch((err) => {
        console.log(err)
        cb(err, null)
        return
    })
}

module.exports  = {
    getDB: getDB
}
```

`server.js`も変更します。起動時にデータベースへの接続をチェックするようにします。

```javascript:srv/server.js
'use strict';

const express = require('express')
const bodyParser = require('body-parser')

const dbConn = require('./db-conn')

var _db = undefined
const app = express()

app.use(bodyParser.json())

app.get("/", function (req, res) {
    res.send("Hello!")
})

function setDBCallback(error, db) {
    if (error !== null) {
        console.log('error when fetching the DB connection ' + JSON.stringify(error))
        return
    }
    _db = db;
}

var PORT = process.env.PORT || 8088
var server = app.listen(PORT, function() {
    const host = server.address().address
    const port = server.address().port
    console.log(`Example app listening at http://${server}:${port}`)

    dbConn.getDB(setDBCallback);
})
```

以上でビルド、デプロイを行います。

※デプロイするときに`cf logs node-postgres-sample-srv`でアプリケーションのログを確認します。以下のようにエラーなく起動すれば、データベースへの接続はうまくいっています。

```
   2021-05-27T15:27:58.72+0900 [CELL/0] OUT Downloaded droplet (22.9M)
   2021-05-27T15:27:58.72+0900 [CELL/0] OUT Starting health monitoring of container
   2021-05-27T15:27:59.71+0900 [APP/PROC/WEB/0] OUT > node-postgres-sample@1.0.0 start /home/vcap/app
   2021-05-27T15:27:59.71+0900 [APP/PROC/WEB/0] OUT > node server.js
   2021-05-27T15:27:59.95+0900 [APP/PROC/WEB/0] OUT Example app listening at http://[object Object]:8080
   2021-05-27T15:28:00.09+0900 [APP/PROC/WEB/0] OUT database initialized [ { id: 1 } ]
   2021-05-27T15:28:01.59+0900 [CELL/0] OUT Container became healthy
```

以下の行で、データが取れていることが確認できます。

```
 2021-05-27T15:28:00.09+0900 [APP/PROC/WEB/0] OUT database initialized [ { id: 1 } ]
```

#### （参考）接続に関する試行錯誤
[参考にしたソース](https://github.com/SAP-samples/cloud-cf-helloworld-nodejs/blob/2_REST_persist_DB_postgres/db-conn.js)では、データベースへ接続する処理は以下のようになっていました。

```javascript
function returnUriToDB() {
    var uri = '';
    if (process.env.VCAP_SERVICES) {
        // running in cloud
        uri = xsenv.cfServiceCredentials('sapcpcfhw-db').uri;
    } else {
        console.log('running locally is not supported');
    }
    return uri;
}


function getDB(cb) {
    let pgp = require('pg-promise')({
        // Initialization Options
    });
    var db = pgp(returnUriToDB());
    ...
}
```
しかし、アプリケーションを起動する際に`error: no pg_hba.conf entry for host "xx.xx.xx.xx" user "xxxx", database "xxxx", SSL off`というエラーとなってしまいました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/b67bc91d-dd1f-5da2-d947-4a1f81d200b3.png)
調べたところ、本来SSLで接続すべきところがSSLになっていないため起こるエラーのようでした。
[No pg_hba.conf entry for host in SAP CF PostgreSQL Hyperscaler](https://answers.sap.com/questions/13330640/no-pg-hbaconf-entry-for-host-in-sap-cf-postgresql.html)

[こちら](https://stackoverflow.com/questions/22301722/ssl-for-postgresql-connection-nodejs)のQAを参考に接続用のURLに`?ssl=true`を追加したところ、エラーの内容が変わりました。

```
uri = xsenv.cfServiceCredentials('cap-posgre-sample-db').uri + `?ssl=true`
```

今度は'self signed certificate'を使っていることによるエラーのようです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/2568c9b3-979c-1a32-f806-7a3cae3b3cc3.png)
対応として、[こちら](https://www.javaniceday.com/post/pg-promise-self-signed-certificate-error-in-postgres)を参考に以下のconfigオブジェクトを作って渡すようにしました。本番環境ではセキュリティ的によくなさそうですが、まずは接続できたのでよしとしました。

```javascript
        config = {
            connectionString: xsenv.cfServiceCredentials('cap-posgre-sample-db').uri,
            ssl: { rejectUnauthorized: false }
        }
```

## まとめ
この記事では、以下を実施しました。

- Node.jsアプリケーションとPostgreSQLのサービスインスタンスをバインド
- PostgreSQLにテーブルを登録
- Node.jsアプリケーションからPostgreSQLに接続

CAPを使わない場合、データベースに接続するために何をしなければならないかがわかった回でした。[次回](https://qiita.com/tami/items/f1a49acf3ae01f14c79e)はテーブルに対するCRUD処理を実装したいと思います。

## 参考

- [SAP-samples/cloud-cf-helloworld-nodejs/Chapter 2: Node.js RESTful API persists data in PostgreSQL DB](https://github.com/SAP-samples/cloud-cf-helloworld-nodejs/tree/2_REST_persist_DB_postgres)