🙌

typescript + express + mysql で簡易 webAPI サーバー作成

7 min read

MySQL + express + TypeScript で作成します。
一旦、エラーハンドリングは考えません。
promise-mysql を使用しましたが、mysql2 でも問題ないと思います。
ソースコードは以下になります。

https://github.com/Msksgm/express_mysql_typescript_api

全体構成

ディレクトリ構成は以下

.
├── movies-ddl.sql
├── package.json
├── src
│   ├── app.ts
│   └── config
│       └── index.ts
├── tsconfig.json
└── yarn.lock

環境構築

node.js

プロジェクトを初期化

yarn init -y

pakage を install

yarn add promise-mysql @types/mysql express
yarn add -D typescript ts-node @types/express @types/node

package.json に以下の記述を追加

{
...
  "main": "src/app.ts",
  "scripts": {
    "build": "tsc",
    "start": "nodemon"
  }
...
}

データベース

movie-ddl.sql を作成

drop database if exists MOVIE;

create database MOVIE CHARACTER SET UTF8;

CONNECT MOVIE;
drop user if exists 'app-user'@'%';
set global validate_password_policy=LOW;
create user 'app-user'@'%' identified by 'PaAsW0rD';
grant all privileges on MOVIE.* TO 'app-user'@'%';

create table if not exists MOVIE
(
    ID                 serial primary key,
    NAME               varchar(100)                      not null,
    CREATED_AT         timestamp(3)    default current_timestamp(3) not null,
    UPDATED_AT         timestamp(3)    default current_timestamp(3) not null
);

INSERT INTO MOVIE(NAME)
VALUES ('天気の子');
INSERT INTO MOVIE(NAME)
VALUES ('サマーウォーズ');
INSERT INTO MOVIE(NAME)
VALUES ('ジョゼと虎と魚たち');

MySQL で ddl を読み込む

mysql -u root -p
# パスワードを入力
root@localhost> source /PATH/TO/express_api/movies-ddl.sql

.env ファイル

.env ファイルを作成して、環境変数を読み込む

作成する.env ファイル

# express
PORT=4000

# DB
DB_HOST="localhost"
DB_PORT="3306"
DB_USER="app-user"
DB_PASSWORD="PaAsW0rD"
DB_DATABASE="MOVIE"

読み込みコマンド

export $(cat .env | grep -v ^# | xargs)

実装

config

src/config/index.ts

export default {
  /**
   * APIサーバーのPORT番号
   */
  port: parseInt(process.env.PORT, 10),

  /**
   * databaseの設定
   */
  db: {
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT, 10),
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
    multipleStatements: true,
  },
};

app

src/app.ts

import express from "express";
import * as mysql from "promise-mysql";
import config from "./config";

const app: express.Express = express();
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

app.listen(config.port, () => {
  console.log(`Start on port ${config.port}.`);
});

const connection = async () => {
  return await mysql.createConnection(config.db);
};

// movie一覧取得
app.get("/movie", (req: express.Request, res: express.Response) => {
  connection()
    .then((connection) => {
      const result = connection.query("SELECT * FROM MOVIE");
      connection.end();
      return result;
    })
    .then(function (rows) {
      res.send(rows);
    });
});

// movie単一取得
app.get("/movie/:movieId", (req: express.Request, res: express.Response) => {
  const movieId = req.params.movieId;
  connection()
    .then((connection) => {
      const result = connection.query("SELECT * FROM MOVIE WHERE ID = ?", [
        movieId,
      ]);
      connection.end();
      return result;
    })
    .then(function (rows) {
      res.send(rows);
    });
});

// movie追加処理
app.put("/movie", (req: express.Request, res: express.Response) => {
  const name = req.body.name;
  connection()
    .then((connection) => {
      const result = connection.query("INSERT INTO MOVIE (NAME) VALUES (?)", [
        name,
      ]);
      connection.end();
      return result;
    })
    .then(function (rows) {
      res.send(rows);
    });
});

// movie更新処理
app.patch("/movie/:movieId", (req: express.Request, res: express.Response) => {
  const movieId = req.params.movieId;
  const name = req.body.name;
  connection()
    .then((connection) => {
      const result = connection.query(
        "UPDATE MOVIE SET NAME = ? WHERE ID = ?",
        [name, movieId]
      );
      connection.end();
      return result;
    })
    .then(function (rows) {
      res.send(rows);
    });
});

// movie削除処理
app.delete("/movie/:movieId", (req: express.Request, res: express.Response) => {
  const movieId = req.params.movieId;
  connection()
    .then((connection) => {
      const result = connection.query("DELETE FROM MOVIE WHERE ID = ?", [
        movieId,
      ]);
      connection.end();
      return result;
    })
    .then(function (rows) {
      res.send(rows);
    });
});

動作確認

API サーバーを起動

yarn start

CURD を確認

READ

全権取得
curl -H "Content-Type: application/json" localhost:4000/movie/ | jq

[
  {
    "ID": 1,
    "NAME": "天気の子",
    "CREATED_AT": "2021-06-03T23:39:19.024Z",
    "UPDATED_AT": "2021-06-03T23:39:19.024Z"
  },
  {
    "ID": 2,
    "NAME": "サマーウォーズ",
    "CREATED_AT": "2021-06-03T23:39:19.026Z",
    "UPDATED_AT": "2021-06-03T23:39:19.026Z"
  },
  {
    "ID": 3,
    "NAME": "ジョゼと虎と魚たち",
    "CREATED_AT": "2021-06-03T23:39:19.026Z",
    "UPDATED_AT": "2021-06-03T23:39:19.026Z"
  }
]

単一取得
curl -H "Content-Type: application/json" localhost:4000/movie/1 | jq

[
  {
    "ID": 1,
    "NAME": "天気の子",
    "CREATED_AT": "2021-06-03T23:39:19.024Z",
    "UPDATED_AT": "2021-06-03T23:39:19.024Z"
  }
]

CREATE

作成
curl -X PUT -H "Content-Type: application/json" localhost:4000/movie -d ' {"name": "青の通り道"}'

{"fieldCount":0,"affectedRows":1,"insertId":4,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}

確認
curl -H "Content-Type: application/json" localhost:4000/movie/4 | jq

[
  {
    "ID": 4,
    "NAME": "青の通り道",
    "CREATED_AT": "2021-06-03T23:42:42.477Z",
    "UPDATED_AT": "2021-06-03T23:42:42.477Z"
  }
]

UPDATE

更新
curl -X PATCH -H "Content-Type: application/json" localhost:4000/movie/4 -d '{"name": "新聞記者"}'

{
  "fieldCount": 0,
  "affectedRows": 1,
  "insertId": 0,
  "serverStatus": 2,
  "warningCount": 0,
  "message": "(Rows matched: 1  Changed: 1  Warnings: 0",
  "protocol41": true,
  "changedRows": 1
}

確認
curl -H "Content-Type: application/json" localhost:4000/movie/4 | jq

[
  {
    "ID": 4,
    "NAME": "新聞記者",
    "CREATED_AT": "2021-06-03T23:42:42.477Z",
    "UPDATED_AT": "2021-06-03T23:42:42.477Z"
  }
]

DELETE

削除
curl -X DELETE -H "Content-Type: application/json" localhost:4000/movie/4 | jq

{
  "fieldCount": 0,
  "affectedRows": 1,
  "insertId": 0,
  "serverStatus": 2,
  "warningCount": 0,
  "message": "",
  "protocol41": true,
  "changedRows": 0
}

確認
curl -H "Content-Type: application/json" localhost:4000/movie/ | jq

[
  {
    "ID": 1,
    "NAME": "天気の子",
    "CREATED_AT": "2021-06-03T23:39:19.024Z",
    "UPDATED_AT": "2021-06-03T23:39:19.024Z"
  },
  {
    "ID": 2,
    "NAME": "サマーウォーズ",
    "CREATED_AT": "2021-06-03T23:39:19.026Z",
    "UPDATED_AT": "2021-06-03T23:39:19.026Z"
  },
  {
    "ID": 3,
    "NAME": "ジョゼと虎と魚たち",
    "CREATED_AT": "2021-06-03T23:39:19.026Z",
    "UPDATED_AT": "2021-06-03T23:39:19.026Z"
  }
]

Discussion

ログインするとコメントできます