【BTP】Node.jsとPostgreSQLでシンプルなアプリケーションを作成（2）CRUD処理編

## はじめに
この記事は、[Node.jsとPostgreSQLでシンプルなアプリケーションを作成](https://qiita.com/tami/items/267cf5a32ce0547eaa46)するシリーズの2回目です。
前回の記事では、Node.jsで作ったシンプルなアプリケーションがBTP上のPostgreSQLに接続できるところまでを確認しました。


## この記事のゴール

- Node.jsアプリケーションからPostgreSQLのテーブルに対してCRUD処理を行えるようにする

## ステップ

1. Read処理（全てのレコード）
1. Read処理（指定したidのレコード）
1. Create(Insert)処理
1. Update処理
1. Delete処理

### 1. Read処理（全てのレコード）
srvフォルダの配下にdb-op.jsというファイルを作成し、ここにCRUD処理を記述します。
プロジェクトの構成は以下のようになります。

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

```javascript:srv/db-op.js
'use strict'

function getAll(db,res) {
    const query = 'SELECT * FROM products'
    db.manyOrNone(query)
        .then((data) => {
            // success
            res.status(200).json(data)
        })
        .catch((error) => {
            // error
            res.status(500)
            res.end(`Error accessing DB: ${JSON.stringify(error)}`)
        })
}

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

function `getAll`はpg-promiseの[Database](http://vitaly-t.github.io/pg-promise/Database.html)オブジェクトを引数に取ります。Databaseの[manyOrNone](http://vitaly-t.github.io/pg-promise/Database.html#manyOrNone)というメソッドを使い、productsテーブルから全件を取得します。manyOrNoneは複数のレコードが返ってくることが想定されるときに使用します。

server.jsに処理を追加します。`/products`というパスが指定されたら、getAllメソッドを呼ぶようにします。

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

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

const dbConn = require('./db-conn')
const dbOp = require('./db-op') //追加

var _db = undefined
...

app.get('/products', function(req, res) {
    dbOp.getAll(_db, res)
})
```

Cloud Foundryにデプロイ後、`/products`のパスを指定するとすべてのレコードが取得できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/684564e6-7dea-e56a-3ef2-cc18b74f7a36.png)

###2. Read処理（指定したidのレコード）
URLで`/products/1`のようにidを指定した場合に、指定されたidのレコードを返す処理：`getOne`を追加します。

```javascript:srv/db-op.js
...

function getOne(db, res, id) {
    db.one({
        name: 'find-product',
        text: 'SELECT * FROM products WHERE id = $1',
        values : [id]
    })
        .then((product) => {
            res.status(200).json(product)
        })
        .catch((error) => {
            // error
            res.status(500)
            res.end(`Error accessing DB: ${JSON.stringify(error)}`)
        })
}

module.exports = {
    getAll: getAll,
    getOne: getOne
}
```

Databaseオブジェクトの[one](http://vitaly-t.github.io/pg-promise/Database.html#one)というメソッドを使い、productsテーブルから指定された1件のレコードを取得します。oneは1件の結果が必ず返ってくることが想定されるときに使用します。
oneに渡しているのは[PreparedStatement](https://vitaly-t.github.io/pg-promise/PreparedStatement.html)というオブジェクトです。ここでは以下のパラメータを指定しています。

|パラメータ  |意味  |
|---|---|
|name  |任意の名前。1つのセッション内でユニークである必要がある|
|text  |クエリストリングまたは[QueryFile](https://vitaly-t.github.io/pg-promise/QueryFile.html)オブジェクトを指定する。クエリストリングでは、`$1`, `$2`といった変数を使用することができる|
|values  |クエリストリングに入る変数を配列で指定する  |

server.jsに処理を追加します。`/products/:id`というパスが指定されたら、getOneメソッドを呼ぶようにします。

```javascript:srv/server.js
app.get('/products/:id', function(req, res) {
    dbOp.getOne(_db, res, req.params.id)
})
```

デプロイ後、`/products/1`を指定すると、指定したレコードのみが返ってきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/f67d89c1-5154-2892-4874-613c22749312.png)

存在しないidを指定した場合はエラーになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/7fab0769-72a5-dfc6-7353-bf41d5a80b73.png)

### 3. Create(Insert)処理
JSON形式で渡したデータをproductsテーブルに登録する処理：`insertOne`を追加します。

```javascript:srv/db-op.js
function insertOne(db, res, newData) {
    db.one({
        name: 'insert-product',
        text: 'INSERT INTO products(name, price) values($1, $2) RETURNING *',
        values : [newData.name, newData.price]
    })
        .then((product) => {
            res.status(201).json(product)
        })
        .catch((error) => {
            // error
            res.status(500)
            res.end(`Error accessing DB: ${JSON.stringify(error)}`)
        })    
}

module.exports = {
    getAll: getAll,
    getOne: getOne,
    insertOne: insertOne
}
```

insertOneで呼んでいるのは、getOneと同じDatabaseオブジェクトの[one](http://vitaly-t.github.io/pg-promise/Database.html#one)メソッドです。[RETURNING](https://www.postgresql.org/docs/9.5/dml-returning.html)というのは見慣れない構文ですが、これはINSERT、UPDATE、DELETEの結果更新されたレコードを取得するための命令です。

server.jsに処理を追加します。`/products`というパスでPOSTリクエストが来たら、insertOneメソッドを呼ぶようにします。

```javascript:srv/server.js
app.post('/products', function(req, res) {
    dbOp.insertOne(_db, res, req.body)
})
```

Postmanでテストします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/640b4686-da57-ed7f-7a54-470b5c526707.png)

この後`/products`を見ると、新しいレコードが追加されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/6245c7a4-e81b-3dbb-8f54-35f177e07950.png)


### 4. Update処理
productsテーブルを更新する処理：`modifyOne`を追加します。

```javascript:srv/db-op.js
function modifyOne(db, res, id, newData) {
    db.one({
        name: 'update-product',
        text: 'UPDATE products set name = $1, price = $2 WHERE id = $3 RETURNING *',
        values : [newData.name, newData.price, id]
    })
        .then((product) => {
            res.status(200).json(product)
        })
        .catch((error) => {
            // error
            res.status(500)
            res.end(`Error accessing DB: ${JSON.stringify(error)}`)
        })    
}

module.exports = {
    getAll: getAll,
    getOne: getOne,
    insertOne: insertOne,
    modifyOne: modifyOne
}
```

server.jsに処理を追加します。`/products/:id`というパスでPUTリクエストが来たら、modifyOneメソッドを呼ぶようにします。

```javascript:srv/server.js
app.put('/products/:id', function (req, res) {
    dbOp.modifyOne(_db, res, req.params.id, req.body)
})
```

Postmanから"banana"のレコードのpriceを110に変更しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/4388ab25-456b-2424-e428-95754ad05fa5.png)

### 5. Delete処理
指定されたレコードをproductsテーブルから削除する処理：`deleteOne`を追加します。

```javascript:srv/db-op.js
function deleteOne(db, res, id) {
    db.result({
        name: 'delete-product',
        text: 'DELETE FROM products WHERE id = $1',
        values: [id]
    })
    .then((product) => {
        res.status(200).end('OK')
    })
    .catch((error) => {
        // error
        res.status(500)
        res.end(`Error accessing DB: ${JSON.stringify(error)}`)
    })       
}

module.exports = {
    getAll: getAll,
    getOne: getOne,
    insertOne: insertOne,
    modifyOne: modifyOne,
    deleteOne: deleteOne
}
```

ここではDatabaseオブジェクトの[result](https://vitaly-t.github.io/pg-promise/Database.html#result)というメソッドを使っています。resultは結果が返ってくることを期待しない場合に使用します。CREATE, UPDATE処理ではRETURNINGで更新されたレコードを取得していましたが、ここでは特に結果を受け取らないのでresultメソッドを使用しています。

server.jsに処理を追加します。`/products/:id`というパスでDELETEリクエストが来たら、deleteOneメソッドを呼ぶようにします。

```javascript:srv/server.js
app.delete('/products/:id', function(req, res) {
    dbOp.deleteOne(_db, res, req.params.id)
})
```
削除に成功すると、'OK'が返ってきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/1808aacf-5fa4-962f-2b59-46869a05efcf.png)

一覧を見るとレコードが削除されたことが確認できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276021/066324f0-a12d-22a1-6b1e-7c40c4518423.png)

## 最終的なコード
最終的なコードは以下のようになります。

```javascript:srv/db-op.js
'use strict'

function getAll(db, res) {
    const query = 'SELECT * FROM products'
    db.manyOrNone(query)
        .then((data) => {
            // success
            res.status(200).json(data)
        })
        .catch((error) => {
            // error
            res.status(500)
            res.end(`Error accessing DB: ${JSON.stringify(error)}`)
        })
}

function getOne(db, res, id) {
    db.one({
        name: 'find-product',
        text: 'SELECT * FROM products WHERE id = $1',
        values : [id]
    })
        .then((product) => {
            res.status(200).json(product)
        })
        .catch((error) => {
            // error
            res.status(500)
            res.end(`Error accessing DB: ${JSON.stringify(error)}`)
        })
}

function insertOne(db, res, newData) {
    db.one({
        name: 'insert-product',
        text: 'INSERT INTO products(name, price) values($1, $2) RETURNING *',
        values : [newData.name, newData.price]
    })
        .then((product) => {
            res.status(201).json(product)
        })
        .catch((error) => {
            // error
            res.status(500)
            res.end(`Error accessing DB: ${JSON.stringify(error)}`)
        })    
}

function modifyOne(db, res, id, newData) {
    db.one({
        name: 'update-product',
        text: 'UPDATE products set name = $1, price = $2 WHERE id = $3 RETURNING *',
        values : [newData.name, newData.price, id]
    })
        .then((product) => {
            res.status(200).json(product)
        })
        .catch((error) => {
            // error
            res.status(500)
            res.end(`Error accessing DB: ${JSON.stringify(error)}`)
        })    
}

function deleteOne(db, res, id) {
    db.result({
        name: 'delete-product',
        text: 'DELETE FROM products WHERE id = $1',
        values: [id]
    })
    .then((product) => {
        res.status(200).end('OK')
    })
    .catch((error) => {
        // error
        res.status(500)
        res.end(`Error accessing DB: ${JSON.stringify(error)}`)
    })       
}

module.exports = {
    getAll: getAll,
    getOne: getOne,
    insertOne: insertOne,
    modifyOne: modifyOne,
    deleteOne: deleteOne
}
```

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

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

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

var _db = undefined
const app = express()

app.use(bodyParser.json())

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

app.get('/products', function(req, res) {
    dbOp.getAll(_db, res)
})

app.get('/products/:id', function(req, res) {
    dbOp.getOne(_db, res, req.params.id)
})

app.post('/products', function(req, res) {
    dbOp.insertOne(_db, res, req.body)
})

app.put('/products/:id', function (req, res) {
    dbOp.modifyOne(_db, res, req.params.id, req.body)
})

app.delete('/products/:id', function(req, res) {
    dbOp.deleteOne(_db, res, req.params.id)
})

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);

})
```

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

- Node.jsアプリケーションからPostgreSQLのテーブルに対してCRUD処理を行えるようにする

[次回](https://qiita.com/tami/items/fe616ade4eccd723ff36)はXSUAAサービスを使って認証を追加したいと思います。

## 参考

- [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)
- [PostgreSQL Tutorial](https://www.postgresqltutorial.com/)
- [PostgreSQLのドキュメント](https://www.postgresql.org/docs/)
- [pg-promiseのドキュメント](https://vitaly-t.github.io/pg-promise/index.html)