【入門】CLIでプロジェクト構築をして使い方を確認

CLIでTypeORMのプロジェクトを構築して、TypeORMの大まかな利用方法を確認します。「migrationの実行」「CRUDの動作確認」など行います。

CLIで新規プロジェクト構築

プロジェクト構築
( typeorm init )

新規プロジェクト用のフォルダを作成します。

$ mkdir sample_init
$ cd sample_init

TypeORMの CLI でプロジェクトを構築します。今回、DBは MySQL を利用します。

$ npx typeorm init --database mysql
Project created inside current directory.

フォルダ確認

CLI で生成されたファイルを確認します。

$ tree
.
├── README.md
├── ormconfig.json
├── package.json
├── src
│   ├── entity
│   │   └── User.ts
│   ├── index.ts
│   └── migration
└── tsconfig.json

3 directories, 6 files

package.json の記述は以下のようになっていました。

$ cat package.json 
{
   "name": "new-typeorm-project",
   "version": "0.0.1",
   "description": "Awesome project developed with TypeORM.",
   "devDependencies": {
      "ts-node": "3.3.0",
      "@types/node": "^8.0.29",
      "typescript": "3.3.3333"
   },
   "dependencies": {
      "typeorm": "0.2.20",
      "reflect-metadata": "^0.1.10",
      "mysql": "^2.14.1"
   },
   "scripts": {
      "start": "ts-node src/index.ts"
   }
}

npm install

パッケージのインストールは別途行う必要があります。

$ npm install

DockerでMySQL立ち上げ

DockerでMySQLを立ち上げます。

docker-compose.yml

version: '3'

services:

  db:
    image: mysql:5.7.27
    restart: always
    ports:
      - "13306:3306"
    volumes:
      - ./.data/db:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_USER: test
      MYSQL_PASSWORD: test

コンテナ起動

$ docker-compose up -d
Starting sample_init_db_1 ... done

コンテナが立ち上がりました。

$ docker-compose ps
      Name                   Command             State                 Ports               
-------------------------------------------------------------------------------------------
sample_init_db_1   docker-entrypoint.sh mysqld   Up      0.0.0.0:13306->3306/tcp, 33060/tcp

DB作成

MySQLに接続します。パスワードは docker-compose.yml にて設定しています。

$ mysql -uroot -p -h 127.0.0.1 --port 13306

DBを作成します。

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.02 sec)

DBの設定記述( ormconfig.json )

docker-compose.yml で設定したDB情報を指定します。

{
   "type": "mysql",
   "host": "localhost",
   "port": 13306,
   "username": "root",
   "password": "root",
   "database": "test",
   "synchronize": false,
   "logging": false,
   "entities": [
      "src/entity/**/*.ts"
   ],
   "migrations": [
      "src/migration/**/*.ts"
   ],
   "subscribers": [
      "src/subscriber/**/*.ts"
   ],
   "cli": {
      "entitiesDir": "src/entity",
      "migrationsDir": "src/migration",
      "subscribersDir": "src/subscriber"
   }
}

また、synchronizefalse にしています。synchronizetrue だと、アプリを実行するたびにEntityとDBの同期が行われます。

今回、DBのスキーマ変更は migration で管理したいので false にしています。

migration

migrationファイル生成

typeorm migration:generate を実行すると EntityDBのスキーマ を比較して自動でマイグレーションファイルを生成してくれます。

今回、CLIで生成したフォルダには、 src/entity/User.ts が既に作られているので User に対応するテーブルを生成するためのマイグレーションファイルが生成されます。

さっそく、実行してみます。( tsファイル を直接利用したいので、ts-node を利用します。)

$ npx ts-node ./node_modules/.bin/typeorm migration:generate -n user
Migration /tmp/sample_init/src/migration/1573090558959-user.ts has been generated successfully. 

1573090558959-user.ts が生成されました。中身は以下のようになります。

import {MigrationInterface, QueryRunner} from "typeorm";

export class user1573090558959 implements MigrationInterface {
    name = 'user1573090558959'

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT, `firstName` varchar(255) NOT NULL, `lastName` varchar(255) NOT NULL, `age` int NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB", undefined);
    }

    public async down(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("DROP TABLE `user`", undefined);
    }

}

なお、src/entity/User.ts の記述は以下のようになっていました。

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    firstName: string;

    @Column()
    lastName: string;

    @Column()
    age: number;

}

migration実行

typeorm migration:run でmigrationを実行できます。

$ npx ts-node ./node_modules/.bin/typeorm migration:run
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'test' AND `TABLE_NAME` = 'migrations'
query: CREATE TABLE `test`.`migrations` (`id` int NOT NULL AUTO_INCREMENT, `timestamp` bigint NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB
query: SELECT * FROM `test`.`migrations` `migrations` ORDER BY id DESC
0 migrations are already loaded in the database.
1 migrations were found in the source code.
1 migrations are new migrations that needs to be executed.
query: START TRANSACTION
query: CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT, `firstName` varchar(255) NOT NULL, `lastName` varchar(255) NOT NULL, `age` int NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB
query: INSERT INTO `test`.`migrations`(`timestamp`, `name`) VALUES (?, ?) -- PARAMETERS: [1573090558959,"user1573090558959"]
Migration user1573090558959 has been executed successfully.
query: COMMIT

userテーブル migrationsテーブル が生成されました。

mysql> SHOW COLUMNS FROM user;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| firstName | varchar(255) | NO   |     | NULL    |                |
| lastName  | varchar(255) | NO   |     | NULL    |                |
| age       | int(11)      | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> 
mysql> SHOW COLUMNS FROM migrations;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| timestamp | bigint(20)   | NO   |     | NULL    |                |
| name      | varchar(255) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

migrationsテーブル には、実行されたmigration情報が記録されています。

mysql> SELECT * FROM migrations;
+----+---------------+-------------------+
| id | timestamp     | name              |
+----+---------------+-------------------+
|  1 | 1573090558959 | user1573090558959 |
+----+---------------+-------------------+
1 row in set (0.00 sec)

CRUDの動作確認

Create(生成) Read(読み取り) Update(更新) Delete(削除) の動作確認をしてみます。

コード

src/index.ts に以下処理を記述します。

import { createConnection, getRepository, Repository } from "typeorm"
import { User } from "./entity/User"

const createUser = async (userRepository: Repository<User>) => {
  console.log("### Create ###")

  await userRepository.insert({
    firstName: "Taro",
    lastName: "Yamada",
    age: 25
  })

  await userRepository.save({
    firstName: "Saki",
    lastName: "Suzuki",
    age: 40
  })
}

const readUser = async (userRepository: Repository<User>) => {
  console.log("### Read ###")

  const users = await userRepository.find()
  console.log(`All Users: ${JSON.stringify(users)}`)

  const user = await userRepository.findOne({firstName: "Taro"})
  console.log(`Select User: ${JSON.stringify(user)}`)
}

const updateUser = async (userRepository: Repository<User>) => {
  console.log("### Update ###")

  await userRepository.update({lastName: "Suzuki"}, {age: 23})

  const userTaro = await userRepository.findOne({firstName: "Taro"})
  userTaro.age = 30
  await userRepository.save(userTaro)

  const users = await userRepository.find()
  console.log(`All Users: ${JSON.stringify(users)}`)
}

const deleteUser = async (userRepository: Repository<User>) => {
  console.log("### Delete ###")

  const userTaro = await userRepository.findOne({firstName: "Taro"})
  await userRepository.remove(userTaro)

  const users = await userRepository.find()
  console.log(`All Users: ${JSON.stringify(users)}`)
}

(async () => {
  const connection = await createConnection()

  const userRepository = getRepository(User)
  await createUser(userRepository)
  await readUser(userRepository)
  await updateUser(userRepository)
  await deleteUser(userRepository)

  await connection.close()
})()

実行結果

$ npx ts-node src/index.ts 
### Create ###
### Read ###
All Users: [{"id":1,"firstName":"Taro","lastName":"Yamada","age":25},{"id":2,"firstName":"Saki","lastName":"Suzuki","age":40}]
Select User: {"id":1,"firstName":"Taro","lastName":"Yamada","age":25}
### Update ###
All Users: [{"id":1,"firstName":"Taro","lastName":"Yamada","age":30},{"id":2,"firstName":"Saki","lastName":"Suzuki","age":23}]
### Delete ###
All Users: [{"id":2,"firstName":"Saki","lastName":"Suzuki","age":23}]

ormconfig.jsonloggingtrue にすると、以下のように実行されたSQLも表示されます。

$ npx ts-node src/index.ts 
### Create ###
query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `age`) VALUES (DEFAULT, ?, ?, ?) -- PARAMETERS: ["Taro","Yamada",25]
query: START TRANSACTION
query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `age`) VALUES (DEFAULT, ?, ?, ?) -- PARAMETERS: ["Saki","Suzuki",40]
query: COMMIT
### Read ###
query: SELECT `User`.`id` AS `User_id`, `User`.`firstName` AS `User_firstName`, `User`.`lastName` AS `User_lastName`, `User`.`age` AS `User_age` FROM `user` `User`
All Users: [{"id":1,"firstName":"Taro","lastName":"Yamada","age":25},{"id":2,"firstName":"Saki","lastName":"Suzuki","age":40}]
query: SELECT `User`.`id` AS `User_id`, `User`.`firstName` AS `User_firstName`, `User`.`lastName` AS `User_lastName`, `User`.`age` AS `User_age` FROM `user` `User` WHERE `User`.`firstName` = ? LIMIT 1 -- PARAMETERS: ["Taro"]
Select User: {"id":1,"firstName":"Taro","lastName":"Yamada","age":25}
### Update ###
query: UPDATE `user` SET `age` = ? WHERE `lastName` = ? -- PARAMETERS: [23,"Suzuki"]
query: SELECT `User`.`id` AS `User_id`, `User`.`firstName` AS `User_firstName`, `User`.`lastName` AS `User_lastName`, `User`.`age` AS `User_age` FROM `user` `User` WHERE `User`.`firstName` = ? LIMIT 1 -- PARAMETERS: ["Taro"]
query: SELECT `User`.`id` AS `User_id`, `User`.`firstName` AS `User_firstName`, `User`.`lastName` AS `User_lastName`, `User`.`age` AS `User_age` FROM `user` `User` WHERE `User`.`id` IN (?) -- PARAMETERS: [1]
query: START TRANSACTION
query: UPDATE `user` SET `age` = ? WHERE `id` IN (?) -- PARAMETERS: [30,1]
query: COMMIT
query: SELECT `User`.`id` AS `User_id`, `User`.`firstName` AS `User_firstName`, `User`.`lastName` AS `User_lastName`, `User`.`age` AS `User_age` FROM `user` `User`
All Users: [{"id":1,"firstName":"Taro","lastName":"Yamada","age":30},{"id":2,"firstName":"Saki","lastName":"Suzuki","age":23}]
### Delete ###
query: SELECT `User`.`id` AS `User_id`, `User`.`firstName` AS `User_firstName`, `User`.`lastName` AS `User_lastName`, `User`.`age` AS `User_age` FROM `user` `User` WHERE `User`.`firstName` = ? LIMIT 1 -- PARAMETERS: ["Taro"]
query: SELECT `User`.`id` AS `User_id`, `User`.`firstName` AS `User_firstName`, `User`.`lastName` AS `User_lastName`, `User`.`age` AS `User_age` FROM `user` `User` WHERE `User`.`id` IN (?) -- PARAMETERS: [1]
query: START TRANSACTION
query: DELETE FROM `user` WHERE `id` = ? -- PARAMETERS: [1]
query: COMMIT
query: SELECT `User`.`id` AS `User_id`, `User`.`firstName` AS `User_firstName`, `User`.`lastName` AS `User_lastName`, `User`.`age` AS `User_age` FROM `user` `User`
All Users: [{"id":2,"firstName":"Saki","lastName":"Suzuki","age":23}]

参考