🙌
typescript + express + mysql で簡易 webAPI サーバー作成
MySQL + Express + TypeScript で作成します。
いったん、エラーハンドリングは考えません。
promise-MySQL を使用しましたが、mysql2 でも問題ないと考えています。
ソースコードは以下になります。
全体構成
ディレクトリ構成は以下です。
.
├── 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