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"
}
}
また、synchronize
を false
にしています。synchronize
が true
だと、アプリを実行するたびにEntityとDBの同期が行われます。
今回、DBのスキーマ変更は migration
で管理したいので false
にしています。
migration
migrationファイル生成
typeorm migration:generate
を実行すると Entity
と DBのスキーマ
を比較して自動でマイグレーションファイルを生成してくれます。
今回、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.json
の logging
を true
にすると、以下のように実行された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}]