CLOVER🍀

That was when it all began.

Express+SequelizeでMySQLと連携するREST APIを作る

前にExpressを使って、簡単なREST APIを作ってみたので、今度はそこからデータベースにアクセスするコードを書いてみようかなと。

この前、Promise-mysqlを使ってMySQLにアクセスするエントリを書いたのですが

Promise-mysqlで、Node.jsからMySQLにアクセスする - CLOVER

せっかくなら、ORMっぽいものも試してみようかなと思って探したところ、Sequelizeというものが良さそうな気がします。

Manual | Sequelize | The node.js ORM for PostgreSQL, MySQL, SQLite and MSSQL

今回は、ExpressとこのSequelizeを使って、データベースにアクセスするREST APIを書いてみましょう。

環境

今回の環境は、こちら。

$ node -v
v8.11.3

$ npm -v
5.6.0

お題と準備

今回は、書籍をテーマにデータの登録や取得をする、簡単なREST APIを作ってみます。

こちらをExpressとSequelizeを使って作成し、データベースはMySQLを使用します。

Expressと、JSONを扱うのでbody-parserをインストール。

$ npm i --save express body-parser

body-parser

Sequelizeは、Getting startedのインストール方法に習い、Sequelize本体とMySQL用のライブラリをインストール。

$ npm i --save sequelize mysql2

Getting started / Installation

mysql2というのは、mysql(node-mysql)と互換性のあり、高速なドライバらしいです。

Node MySQL 2

あと、Sequelizeでトランザクションに関するコードを簡単に書くために、Continuation-Local Storageを追加。

$ npm i --save continuation-local-storage

Continuation-Local Storage

ここまでで、依存関係はこんな感じです。

  "dependencies": {
    "body-parser": "^1.18.3",
    "continuation-local-storage": "^3.2.1",
    "express": "^4.16.3",
    "mysql2": "^1.6.1",
    "sequelize": "^4.38.0"
  },

あと、テストコードも書くことにします。JestとRequest-Promiseを使うことにしましょう。

$ npm i --save-dev jest request request-promise

テストに関する設定、依存関係はこちら。

  "devDependencies": {
    "jest": "^23.5.0",
    "request": "^2.88.0",
    "request-promise": "^4.2.2"
  },
  "jest": {
    "testEnvironment": "node"
  }

MySQLは8.0.12を使い、設定ファイルには以下のように以前のMySQLの認証方法を設定しておきます。

default_authentication_plugin = mysql_native_password

テーブル定義は、こちらで。

create table book(isbn varchar(15), title varchar(100), price int, primary key(isbn));

とりあえずいろいろ突っ込んでいるのですが、順次補足していきます。

データベースの接続定義とモデルを書く

では、最初にSequelizeを使ったデータベースの接続定義とモデルを書きます。

こちらと

Getting started / Setting up a connection

こちらに習って書いていきましょう。

Getting started / Your first model

これらの定義は、こちら。このソースコードは、REST APIを書いたサーバー側からrequireして使うものとします。
src/database.js

const Sequelize = require('sequelize');

const cls = require('continuation-local-storage');
const namespace = cls.createNamespace('my-namespace');
Sequelize.useCLS(namespace);

const sequelize = new Sequelize('practice', 'kazuhira', 'password', {
    host: '172.17.0.2',
    dialect: 'mysql',

    pool: {
        max: 10,
        min: 3,
        acquire: 30000,
        idle: 10000
    },

    // sequelize deprecated String based operators are now deprecated. Please use Symbol based operators for better security, read more at http://docs.sequelizejs.com/manual/tutorial/querying.html
    operatorsAliases: false
});

const models = {
    Book:  sequelize.define(
          'book',
          {
              isbn : {
                  type: Sequelize.STRING,
                  primaryKey: true
              },
              title: Sequelize.STRING,
              price: Sequelize.INTEGER
          },
          {
              freezeTableName: true,
              timestamps: false
          }
    )
};

module.exports = {
    sequelize: sequelize,
    models: models
};

Continuation-Local Storageを使ったコードがありますが、ここはいったん置いておきます。

データベースの接続(プール含む)は、Getting started通りです。

const sequelize = new Sequelize('practice', 'kazuhira', 'password', {
    host: '172.17.0.2',
    dialect: 'mysql',

    pool: {
        max: 10,
        min: 3,
        acquire: 30000,
        idle: 10000
    },

    // sequelize deprecated String based operators are now deprecated. Please use Symbol based operators for better security, read more at http://docs.sequelizejs.com/manual/tutorial/querying.html
    operatorsAliases: false
});

operatorsAliasesをfalseにしておかないと、警告されます…。

モデルは、Sequelize#defineで定義します。

    Book:  sequelize.define(
          'book',
          {
              isbn : {
                  type: Sequelize.STRING,
                  primaryKey: true
              },
              title: Sequelize.STRING,
              price: Sequelize.INTEGER
          },
          {
              freezeTableName: true,
              timestamps: false
          }
    )

モデル定義については、こちらも参照。

Tutorial | Sequelize | The node.js ORM for PostgreSQL, MySQL, SQLite and MSSQL

データ型の定義については、こちらを参照してください。

DataTypes

isbnにprimaryKey属性をtrueにしているのは、SequelizeはデフォルトでidをPrimary Keyとして求めるようですが、これを変更しています。

Working with legacy tables / Primary keys

オプションとして設定しているfreezeTableNameは、モデルに渡した名前を実テーブルにマッピングする際に、複数形に変換してしまうのを
抑制するためです。また、デフォルトでcreatedAtおよびupdatedAtカラムがあることがモデルの前提になっているので、これをオフに
するためにtimestampsをfalseにしています。

Model definition / Configuration

いきなり、イレギュラーコースな定義です…。

最後に、他のファイルから使うので、これらの定義をmodule.exports。

module.exports = {
    sequelize: sequelize,
    models: models
};

ここまでで、データベースへの接続定義とモデルの定義が終わりました。

REST APIを書く

では、定義したモデルなどを使った、REST APIを書いてみます。
src/server.js

const express = require('express');
const bodyParser = require('body-parser');
const database = require('./database.js');

const app = express();
const sequelize = database.sequelize;
const Book = database.models.Book;

app.use(bodyParser.json());

app.put('/book/:isbn', (req, res) => {
    const isbn = req.params['isbn'];
    const book = req.body;

    if (isbn === book.isbn) {
        sequelize.transaction(async t => {
            const b = await Book.findById(isbn);

            if (b !== null) {
                await Book.update(book, { where: { isbn: isbn } });
                return Promise.resolve(res.status(201).send(book));
            } else {
                const created = await await Book.create(book);
                return Promise.resolve(res.status(201).send(created));
            }
        });
    } else {
        res.sendStatus(400);
    }
});

app.delete('/book/:isbn', (req, res) => {
    const isbn = req.params['isbn'];

    sequelize.transaction(async t => {
        const b = await  Book.findById(isbn);

        if (b !== null) {
            await Book.destroy({ where: { isbn: isbn } });
            return Promise.resolve(res.sendStatus(204));
        } else {
            return Promise.resolve(res.sendStatus(204));
        }
    });
});

app.get('/book/:isbn', (req, res) => {
    const isbn = req.params['isbn'];

    sequelize.transaction(async t => {
        const b = await Book.findById(isbn);

        if (b !== null) {
            return Promise.resolve(res.send(b));
        } else {
            return Promise.resolve(res.sendStatus(404));
        }
    });
});

app.get('/book', (req, res) => {
    sequelize.transaction(async t => {
        const books =  await Book.findAll({ order: [['price', 'ASC']] });
        return Promise.resolve(res.send(books));
    });
});

app.listen(3000);

データの登録は、こちら。「/book/{isbn}」で受け付け、データがまだなければinsert、あればupdateするようにしています。

app.put('/book/:isbn', (req, res) => {
    const isbn = req.params['isbn'];
    const book = req.body;

    if (isbn === book.isbn) {
        sequelize.transaction(async t => {
            const b = await Book.findById(isbn);

            if (b !== null) {
                await Book.update(book, { where: { isbn: isbn } });
                return Promise.resolve(res.status(201).send(book));
            } else {
                const created = await await Book.create(book);
                return Promise.resolve(res.status(201).send(created));
            }
        });
    } else {
        res.sendStatus(400);
    }
});

Model#createやModel#updateを使用。

クエリについては、こちらと

Tutorial | Sequelize | The node.js ORM for PostgreSQL, MySQL, SQLite and MSSQL

Modelに関するメソッドを見ればよいでしょう。

Model | Sequelize | The node.js ORM for PostgreSQL, MySQL, SQLite and MSSQL API Document

更新系については、こちら。

Instances / Creating persistent instances

Instances / Updating / Saving / Persisting an instance

まだ登場してませんが、deleteも。

Instances / Destroying / Deleting persistent instances

トランザクションについてですが、本来はこんな感じのコードを書かないといけないみたいです。

Tutorial | Sequelize | The node.js ORM for PostgreSQL, MySQL, SQLite and MSSQL

return sequelize.transaction(function (t) {

  // chain all your queries here. make sure you return them.
  return User.create({
    firstName: 'Abraham',
    lastName: 'Lincoln'
  }, {transaction: t}).then(function (user) {
    return user.setShooter({
      firstName: 'John',
      lastName: 'Boothe'
    }, {transaction: t});
  });

}).then(function (result) {
  // Transaction has been committed
  // result is whatever the result of the promise chain returned to the transaction callback
}).catch(function (err) {
  // Transaction has been rolled back
  // err is whatever rejected the promise chain returned to the transaction callback
});

各メソッド(クエリ)のオプションに、transactionを渡し続けると…。

で、これをもうちょっと楽にする方法が、Continuation-Local Storageを使ったものみたいです。

Transactions / Automatically pass transactions to all queries

Continuation-Local Storageを使ってSequelizeと連携させると、各メソッド(クエリ)にトランザクションを自動的に渡すことができます。

この設定が、先程のデータベース接続とモデル定義で行っていた、こちらなわけです。

const cls = require('continuation-local-storage');
const namespace = cls.createNamespace('my-namespace');
Sequelize.useCLS(namespace);

namespaceの名称は、お好みで。

削除は、Model#destroyになっただけですね。

app.delete('/book/:isbn', (req, res) => {
    const isbn = req.params['isbn'];

    sequelize.transaction(async t => {
        const b = await  Book.findById(isbn);

        if (b !== null) {
            await Book.destroy({ where: { isbn: isbn } });
            return Promise.resolve(res.sendStatus(204));
        } else {
            return Promise.resolve(res.sendStatus(204));
        }
    });
});

検索は、Model#findByIdによる1件取得と

app.get('/book/:isbn', (req, res) => {
    const isbn = req.params['isbn'];

    sequelize.transaction(async t => {
        const b = await Book.findById(isbn);

        if (b !== null) {
            return Promise.resolve(res.send(b));
        } else {
            return Promise.resolve(res.sendStatus(404));
        }
    });
});

ソートを付けた、全件取得で。

app.get('/book', (req, res) => {
    sequelize.transaction(async t => {
        const books =  await Book.findAll({ order: [['price', 'ASC']] });
        return Promise.resolve(res.send(books));
    });
});

確認

それでは、動作確認してみましょう。package.jsonのscripts要素の「start」を、次のように設定。

  "scripts": {
    "start": "node src/server.js"
  },

起動。

$ npm start

データの登録。

$ curl -i -XPUT -H 'Content-Type: application/json' localhost:3000/book/978-4774182179 -d '{"isbn": "978-4774182179", "title": "[改訂新版]Spring入門 ――Javaフレームワーク ・より良い設計とアーキテクチャ", "price": 4104}'
HTTP/1.1 201 Created
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 157
ETag: W/"9d-0VwV5UrgpTA9ixU4s0V0WJvMiik"
Date: Thu, 16 Aug 2018 13:38:01 GMT
Connection: keep-alive

{"isbn":"978-4774182179","title":"[改訂新版]Spring入門 ――Javaフレームワーク ・より良い設計とアーキテクチャ","price":4104}


$ curl -i -XPUT -H 'Content-Type: application/json' localhost:3000/book/978-4798142470 -d '{"isbn": "978-4798142470", "title": "Spring徹底入門 Spring FrameworkによるJavaアプリケーション開発", "price": 4320}'
HTTP/1.1 201 Created
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 127
ETag: W/"7f-E5mRawCP+mh3s+Na+pMa+f8SCmE"
Date: Thu, 16 Aug 2018 13:38:57 GMT
Connection: keep-alive

{"isbn":"978-4798142470","title":"Spring徹底入門 Spring FrameworkによるJavaアプリケーション開発","price":4320}


$ curl -i -XPUT -H 'Content-Type: application/json' localhost:3000/book/978-4777519699 -d '{"isbn": "978-4777519699", "title": "はじめてのSpring Boot―スプリング・フレームワークで簡単Javaアプリ開発", "price": 2700}'
HTTP/1.1 201 Created
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 145
ETag: W/"91-0lRzOvi1yCFs9rLiEGXA09IUmmA"
Date: Thu, 16 Aug 2018 13:39:21 GMT
Connection: keep-alive

{"isbn":"978-4777519699","title":"はじめてのSpring Boot―スプリング・フレームワークで簡単Javaアプリ開発","price":2700}

1件取得。

$ curl -i localhost:3000/book/978-4774182179
HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 157
ETag: W/"9d-0VwV5UrgpTA9ixU4s0V0WJvMiik"
Date: Thu, 16 Aug 2018 13:39:52 GMT
Connection: keep-alive

{"isbn":"978-4774182179","title":"[改訂新版]Spring入門 ――Javaフレームワーク ・より良い設計とアーキテクチャ","price":4104}


$ curl -i localhost:3000/book/978-4798142470
HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 127
ETag: W/"7f-E5mRawCP+mh3s+Na+pMa+f8SCmE"
Date: Thu, 16 Aug 2018 13:39:52 GMT
Connection: keep-alive

{"isbn":"978-4798142470","title":"Spring徹底入門 Spring FrameworkによるJavaアプリケーション開発","price":4320}


$ curl -i localhost:3000/book/978-4777519699
HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 145
ETag: W/"91-0lRzOvi1yCFs9rLiEGXA09IUmmA"
Date: Thu, 16 Aug 2018 13:39:52 GMT
Connection: keep-alive

{"isbn":"978-4777519699","title":"はじめてのSpring Boot―スプリング・フレームワークで簡単Javaアプリ開発","price":2700}

全件取得。

$ curl -i localhost:3000/book
HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 433
ETag: W/"1b1-19FBNctXGkPIzxjaJ8Gsp8TkOk8"
Date: Thu, 16 Aug 2018 13:40:47 GMT
Connection: keep-alive

[{"isbn":"978-4777519699","title":"はじめてのSpring Boot―スプリング・フレームワークで簡単Javaアプリ開発","price":2700},{"isbn":"978-4774182179","title":"[改訂新版]Spring入門 ――Javaフレームワーク ・より良い設計とアーキテクチャ","price":4104},{"isbn":"978-4798142470","title":"Spring徹底入門 Spring FrameworkによるJavaアプリケーション開発","price":4320}]

削除。

$ curl -i -XDELETE localhost:3000/book/978-4774182179
HTTP/1.1 204 No Content
X-Powered-By: Express
ETag: W/"a-bAsFyilMr4Ra1hIU5PyoyFRunpI"
Date: Thu, 16 Aug 2018 13:41:08 GMT
Connection: keep-alive


$ curl -i -XDELETE localhost:3000/book/978-4798142470
HTTP/1.1 204 No Content
X-Powered-By: Express
ETag: W/"a-bAsFyilMr4Ra1hIU5PyoyFRunpI"
Date: Thu, 16 Aug 2018 13:41:08 GMT
Connection: keep-alive


$ curl -i -XDELETE localhost:3000/book/978-4777519699
HTTP/1.1 204 No Content
X-Powered-By: Express
ETag: W/"a-bAsFyilMr4Ra1hIU5PyoyFRunpI"
Date: Thu, 16 Aug 2018 13:41:09 GMT
Connection: keep-alive

削除確認。

$ curl -i localhost:3000/book
HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 2
ETag: W/"2-l9Fw4VUO7kr8CvBlt4zaMCqXZ0w"
Date: Thu, 16 Aug 2018 13:41:50 GMT
Connection: keep-alive

[]

OKそうです。

テストを書く

それでは、最後にテストコードで確認してみましょう。

テストは、JestとRequest-Promiseで行います。また、サーバーは起動済みとします。

$ npm start

テストコードの下準備をして

const rq = require('request-promise');

const uriBase = 'http://localhost:3000';

あらかじめデータを用意。

const books = {
    introSpring: {
        isbn: '978-4774182179',
        title: '[改訂新版]Spring入門 ――Javaフレームワーク ・より良い設計とアーキテクチャ',
        price: 4104
    },
    springReference: {
        isbn: '978-4798142470',
        title: 'Spring徹底入門 Spring FrameworkによるJavaアプリケーション開発',
        price: 4320
    },
    springBoot: {
        isbn: '978-4777519699',
        title: 'はじめてのSpring Boot―スプリング・フレームワークで簡単Javaアプリ開発',
        price: 2700
    }
};

データ登録。

test('register all data', async () => {
    ['introSpring', 'springReference', 'springBoot'].forEach(async bookName => {
        const response = await rq({
            uri: `${uriBase}/book/${books[bookName]['isbn']}`,
            method: 'PUT',
            body: books[bookName],
            json: true
        });

        expect(response).toEqual(books[bookName]);
    });
});

全件取得。

test('find all', async () => {
    const response = await rq({
        uri: `${uriBase}/book`,
        method: 'GET',
        json: true
    });

    expect(response).toEqual([books['springBoot'], books['introSpring'], books['springReference']]);
});

1件取得。

test('find one', async () => {
    const response = await rq({
        uri: `${uriBase}/book/978-4774182179`,
        method: 'GET',
        json: true
    });

    expect(response).toEqual(books['introSpring']);
});

1件削除。

test('delete one', async() => {
    await rq({
        uri: `${uriBase}/book/978-4774182179`,
        method: 'DELETE',
        json: true
    });

    const response = await rq({
        uri: `${uriBase}/book`,
            method: 'GET',
            json: true
    });

    expect(response).toEqual([books['springBoot'], books['springReference']]);

    ['introSpring', 'springReference', 'springBoot'].forEach(async bookName => {
        await rq({
            uri: `${uriBase}/book/${books[bookName]['isbn']}`,
            method: 'PUT',
            body: books[bookName],
            json: true
        });
    });
});

テストコードについては、ちょっと不満があるのですが、今回はこんなところで…。

なお、package.jsonのscripts要素は、こんな感じにしていました。

  "scripts": {
    "start": "node src/server.js",
    "test": "jest --detectOpenHandles"
  },

実行は、「npm test」で。

async/awaitを使ってテストを書くの、難しい…。