TypeScriptでMySQLを実行する

TypeScript勉強始めて1日目です。
[#YYTypeScript #1](https://yyts.connpass.com/event/144296)に参加するのにTypeScript全く触らないで行くのもどうかと思いTypeScriptデビューしました👏

サーバーサイドTypeScriptというワードを周りでよく聞くようになり、
TypeScriptでデータベース(MySQL)と接続ってどうやるんだろとふと疑問に思ったので試しにやってみます。

## Docker+Node+TypeScriptの環境を作る

サーバーサイドTypeScriptはDockerで構築していいのかな？

### docker-compose.yml

```yml
version: "3"
services:
  node:
    build: ./docker/node
    tty: true
    volumes:
      - .:/work
    working_dir: /work

  db:
    image: mysql:8.0
    volumes:
      - db-store:/var/lib/mysql
      - ./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf
    environment:
      - MYSQL_DATABASE=typescript
      - MYSQL_USER=docker
      - MYSQL_PASSWORD=secret
      - MYSQL_ROOT_PASSWORD=secret
      - TZ=Asia/Tokyo
    ports:
      - 13306:3306

volumes:
  db-store:
```

### ./docker/node/Dockerfile

```dockerfile
FROM node:12.8-alpine
LABEL maintainer "ucan"

RUN yarn global add typescript@3.6
```

`yarn tsc` と打ちたくなかったのでglobalにインストールしてます。
dockerで管理すればバージョンも揃えられるし良さそう。

### docker/mysql/my.cnf

```shell-session
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_bin
default-time-zone = SYSTEM
log_timestamps = SYSTEM

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4
```

### ビルド

```shell-session
$ docker-compose up -d --build
```

### 実行環境の確認とテーブル作成

```shell-session
$ docker-compose exec node ash
$ node --version
v12.8.1
$ yarn --version
1.17.3
$ tsc --version
Version 3.6.3

$ docker-compose exec db bash
$ mysql --version
mysql  Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)

$ mysql -uroot -p${MYSQL_PASSWORD} ${MYSQL_DATABASE}

CREATE TABLE sample (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  content varchar(255),
  PRIMARY KEY (`id`)
);

INSERT INTO sample (content) VALUES ('aaa');
INSERT INTO sample (content) VALUES ('bbb');
INSERT INTO sample (content) VALUES ('ccc');

SELECT * FROM sample;
+----+---------+
| id | content |
+----+---------+
|  1 | aaa     |
|  2 | bbb     |
|  3 | ccc     |
+----+---------+
```

```shell-session
$ yarn init
yarn init v1.17.3
question name (work): 
question version (1.0.0): 
question description: 
question entry point (index.js): 
question repository url: 
question author: 
question license (MIT): 
question private: 
```

#### 補足: シェルの違いについて

- `node` コンテナはベースイメージが `Alpine` なので `ash` シェル
  - [Almquist Shell](https://ja.wikipedia.org/wiki/Almquist_Shell)
- `db` コンテナはベースイメージが `Debian` なので `bash` シェル
  - [Bash Shell](https://ja.wikipedia.org/wiki/Bash)

## tsc init

```shell-session
$ tsc --init
```

`tsconfig.json` が生成されます。

### tsconfig.json

コマンドで生成されたjsonファイルです。今回は特に変更しません。

```json
{
  "compilerOptions": {
    /* Basic Options */
    // "incremental": true,                   /* Enable incremental compilation */
    "target": "es5",                          /* Specify ECMAScript target version: 'ES3' (default), 'ES5', 'ES2015', 'ES2016', 'ES2017', 'ES2018', 'ES2019' or 'ESNEXT'. */
    "module": "commonjs",                     /* Specify module code generation: 'none', 'commonjs', 'amd', 'system', 'umd', 'es2015', or 'ESNext'. */
    // "lib": [],                             /* Specify library files to be included in the compilation. */
    // "allowJs": true,                       /* Allow javascript files to be compiled. */
    // "checkJs": true,                       /* Report errors in .js files. */
    // "jsx": "preserve",                     /* Specify JSX code generation: 'preserve', 'react-native', or 'react'. */
    // "declaration": true,                   /* Generates corresponding '.d.ts' file. */
    // "declarationMap": true,                /* Generates a sourcemap for each corresponding '.d.ts' file. */
    // "sourceMap": true,                     /* Generates corresponding '.map' file. */
    // "outFile": "./",                       /* Concatenate and emit output to single file. */
    // "outDir": "./",                        /* Redirect output structure to the directory. */
    // "rootDir": "./",                       /* Specify the root directory of input files. Use to control the output directory structure with --outDir. */
    // "composite": true,                     /* Enable project compilation */
    // "tsBuildInfoFile": "./",               /* Specify file to store incremental compilation information */
    // "removeComments": true,                /* Do not emit comments to output. */
    // "noEmit": true,                        /* Do not emit outputs. */
    // "importHelpers": true,                 /* Import emit helpers from 'tslib'. */
    // "downlevelIteration": true,            /* Provide full support for iterables in 'for-of', spread, and destructuring when targeting 'ES5' or 'ES3'. */
    // "isolatedModules": true,               /* Transpile each file as a separate module (similar to 'ts.transpileModule'). */

    /* Strict Type-Checking Options */
    "strict": true,                           /* Enable all strict type-checking options. */
    // "noImplicitAny": true,                 /* Raise error on expressions and declarations with an implied 'any' type. */
    // "strictNullChecks": true,              /* Enable strict null checks. */
    // "strictFunctionTypes": true,           /* Enable strict checking of function types. */
    // "strictBindCallApply": true,           /* Enable strict 'bind', 'call', and 'apply' methods on functions. */
    // "strictPropertyInitialization": true,  /* Enable strict checking of property initialization in classes. */
    // "noImplicitThis": true,                /* Raise error on 'this' expressions with an implied 'any' type. */
    // "alwaysStrict": true,                  /* Parse in strict mode and emit "use strict" for each source file. */

    /* Additional Checks */
    // "noUnusedLocals": true,                /* Report errors on unused locals. */
    // "noUnusedParameters": true,            /* Report errors on unused parameters. */
    // "noImplicitReturns": true,             /* Report error when not all code paths in function return a value. */
    // "noFallthroughCasesInSwitch": true,    /* Report errors for fallthrough cases in switch statement. */

    /* Module Resolution Options */
    // "moduleResolution": "node",            /* Specify module resolution strategy: 'node' (Node.js) or 'classic' (TypeScript pre-1.6). */
    // "baseUrl": "./",                       /* Base directory to resolve non-absolute module names. */
    // "paths": {},                           /* A series of entries which re-map imports to lookup locations relative to the 'baseUrl'. */
    // "rootDirs": [],                        /* List of root folders whose combined content represents the structure of the project at runtime. */
    // "typeRoots": [],                       /* List of folders to include type definitions from. */
    // "types": [],                           /* Type declaration files to be included in compilation. */
    // "allowSyntheticDefaultImports": true,  /* Allow default imports from modules with no default export. This does not affect code emit, just typechecking. */
    "esModuleInterop": true                   /* Enables emit interoperability between CommonJS and ES Modules via creation of namespace objects for all imports. Implies 'allowSyntheticDefaultImports'. */
    // "preserveSymlinks": true,              /* Do not resolve the real path of symlinks. */
    // "allowUmdGlobalAccess": true,          /* Allow accessing UMD globals from modules. */

    /* Source Map Options */
    // "sourceRoot": "",                      /* Specify the location where debugger should locate TypeScript files instead of source locations. */
    // "mapRoot": "",                         /* Specify the location where debugger should locate map files instead of generated locations. */
    // "inlineSourceMap": true,               /* Emit a single file with source maps instead of having a separate file. */
    // "inlineSources": true,                 /* Emit the source alongside the sourcemaps within a single file; requires '--inlineSourceMap' or '--sourceMap' to be set. */

    /* Experimental Options */
    // "experimentalDecorators": true,        /* Enables experimental support for ES7 decorators. */
    // "emitDecoratorMetadata": true,         /* Enables experimental support for emitting type metadata for decorators. */
  }
}
```

## JavaScriptでMySQLを操作するライブラリ

- [https://github.com/mysqljs/mysql](https://github.com/mysqljs/mysql)
- [https://github.com/CodeFoodPixels/node-promise-mysql](https://github.com/CodeFoodPixels/node-promise-mysql)

調べたところ二つありましたが、 `node-promise-mysql` を選択しました。

## mysql ライブラリインストール

```
$ yarn add promise-mysql@4.1 @types/mysql@2.15
```

## MySQLを実行するコードを書く

### sql.ts

```typescript
import * as mysql from 'promise-mysql';

async function connection() {
  const connection = await mysql.createConnection({
    host: 'db',
    user: 'docker',
    password: 'secret',
    database: 'typescript',
    multipleStatements: true
  });

  return connection;
}

connection().then(connection => {
  // console.log(connection);
  const result = connection.query('SELECT * FROM sample');
  connection.end();

  return result;
}).then(function(rows) {
  console.log(rows);
});
```

### 実行

```shell-session
$ tsc sql.ts
$ node sql.js
[
  RowDataPacket { id: 1, content: 'aaa' },
  RowDataPacket { id: 2, content: 'bbb' },
  RowDataPacket { id: 3, content: 'ccc' }
]
```

TypeScriptからMySQLへ接続できました！
基本は参考記事を元にやってみました。
クラス化したいところでしたが、
環境作ったり時間が足りなかったので次の記事でやってみたいと思います。(続く...?)

### sql.js

トランスパイルされたsql.jsファイルはこんな風になってました。

```js
"use strict";
var __awaiter = (this && this.__awaiter) || function (thisArg, _arguments, P, generator) {
    function adopt(value) { return value instanceof P ? value : new P(function (resolve) { resolve(value); }); }
    return new (P || (P = Promise))(function (resolve, reject) {
        function fulfilled(value) { try { step(generator.next(value)); } catch (e) { reject(e); } }
        function rejected(value) { try { step(generator["throw"](value)); } catch (e) { reject(e); } }
        function step(result) { result.done ? resolve(result.value) : adopt(result.value).then(fulfilled, rejected); }
        step((generator = generator.apply(thisArg, _arguments || [])).next());
    });
};
var __generator = (this && this.__generator) || function (thisArg, body) {
    var _ = { label: 0, sent: function() { if (t[0] & 1) throw t[1]; return t[1]; }, trys: [], ops: [] }, f, y, t, g;
    return g = { next: verb(0), "throw": verb(1), "return": verb(2) }, typeof Symbol === "function" && (g[Symbol.iterator] = function() { return this; }), g;
    function verb(n) { return function (v) { return step([n, v]); }; }
    function step(op) {
        if (f) throw new TypeError("Generator is already executing.");
        while (_) try {
            if (f = 1, y && (t = op[0] & 2 ? y["return"] : op[0] ? y["throw"] || ((t = y["return"]) && t.call(y), 0) : y.next) && !(t = t.call(y, op[1])).done) return t;
            if (y = 0, t) op = [op[0] & 2, t.value];
            switch (op[0]) {
                case 0: case 1: t = op; break;
                case 4: _.label++; return { value: op[1], done: false };
                case 5: _.label++; y = op[1]; op = [0]; continue;
                case 7: op = _.ops.pop(); _.trys.pop(); continue;
                default:
                    if (!(t = _.trys, t = t.length > 0 && t[t.length - 1]) && (op[0] === 6 || op[0] === 2)) { _ = 0; continue; }
                    if (op[0] === 3 && (!t || (op[1] > t[0] && op[1] < t[3]))) { _.label = op[1]; break; }
                    if (op[0] === 6 && _.label < t[1]) { _.label = t[1]; t = op; break; }
                    if (t && _.label < t[2]) { _.label = t[2]; _.ops.push(op); break; }
                    if (t[2]) _.ops.pop();
                    _.trys.pop(); continue;
            }
            op = body.call(thisArg, _);
        } catch (e) { op = [6, e]; y = 0; } finally { f = t = 0; }
        if (op[0] & 5) throw op[1]; return { value: op[0] ? op[1] : void 0, done: true };
    }
};
exports.__esModule = true;
var mysql = require("promise-mysql");
function connection() {
    return __awaiter(this, void 0, void 0, function () {
        var connection;
        return __generator(this, function (_a) {
            switch (_a.label) {
                case 0: return [4 /*yield*/, mysql.createConnection({
                        host: 'db',
                        user: 'docker',
                        password: 'secret',
                        database: 'typescript',
                        multipleStatements: true
                    })];
                case 1:
                    connection = _a.sent();
                    return [2 /*return*/, connection];
            }
        });
    });
}
connection().then(function (connection) {
    // console.log(connection);
    var result = connection.query('SELECT * FROM sample');
    connection.end();
    return result;
}).then(function (rows) {
    console.log(rows);
});
```

## 参考記事

- [Node.jsでpromise-mysqlを扱ってみたかったので独自目線で纏めた](https://qiita.com/aya_akatsuki/items/7c01bfe33cc3e04f82eb)
- [node.jsでMySQLに接続する](https://blog.deezus.net/entry/2018/09/23/000000)