Entityからmigrationを作成(型指定, リレーション定義)
TypeORMのmigration利用方法について取り上げます。まず、Entityからmigrationを生成して、migrationを実行する一連の流れ確認をします。その後、「型指定の方法」「リレーション定義の方法」を確認します。
migration作成 & 実行の流れ
Entityを作成
( typeorm entity:create )
$ npx typeorm entity:create --name Test
Entity /typeorm_sample/src/entity/Test.ts has been created successfully.
以下Entityが作成されました。
$ cat src/entity/Test.ts
import {Entity} from "typeorm";
@Entity()
export class Test {
}
Entityを編集
idプロパティ nameプロパティ を追加してみます。
import {
Column,
Entity,
PrimaryGeneratedColumn,
} from 'typeorm';
@Entity()
export class Test {
@PrimaryGeneratedColumn()
readonly id?: number
@Column({length: '100', unique: true})
name: string
}
マイグレーションを作成
( typeorm migration:generate )
Entityをもとにマイグレーションを作成します。
$ npx ts-node ./node_modules/.bin/typeorm migration:generate --name test
Migration /typeorm_sample/src/migration/1574464157770-test.ts has been generated successfully.
以下、マイグレーションが生成されました。
$ ls src/migration
1574464157770-test.ts
import {MigrationInterface, QueryRunner} from "typeorm";
export class test1574464157770 implements MigrationInterface {
name = 'test1574464157770'
public async up(queryRunner: QueryRunner): Promise<any> {
await queryRunner.query("CREATE TABLE `test` (`id` int NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, UNIQUE INDEX `IDX_bb6f40984281fc016e98cab260` (`name`), PRIMARY KEY (`id`)) ENGINE=InnoDB", undefined);
}
public async down(queryRunner: QueryRunner): Promise<any> {
await queryRunner.query("DROP INDEX `IDX_bb6f40984281fc016e98cab260` ON `test`", undefined);
await queryRunner.query("DROP TABLE `test`", undefined);
}
}
マイグレーションを実行
( typeorm migration:run )
$ 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 `test` (`id` int NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, UNIQUE INDEX `IDX_bb6f40984281fc016e98cab260` (`name`), PRIMARY KEY (`id`)) ENGINE=InnoDB
query: INSERT INTO `test`.`migrations`(`timestamp`, `name`) VALUES (?, ?) -- PARAMETERS: [1574464157770,"test1574464157770"]
Migration test1574464157770 has been executed successfully.
query: COMMIT
作成されたテーブルを確認
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| migrations |
| test |
+----------------+
2 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM `test`;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | UNI | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
最後の変更を元に戻す
( typeorm migration:revert )
もし、最後に実行されたマイグレーションによる変更を元に戻したい場合、以下コマンドを実行します。
typeorm migration:revert
実行してみます。
$ npx ts-node ./node_modules/.bin/typeorm migration:revert
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'test' AND `TABLE_NAME` = 'migrations'
query: SELECT * FROM `test`.`migrations` `migrations` ORDER BY id DESC
1 migrations are already loaded in the database.
test1574464157770 is the last executed migration. It was executed on Sat Nov 23 2019 08:09:17 GMT+0900 (Japan Standard Time).
Now reverting it...
query: START TRANSACTION
query: DROP INDEX `IDX_bb6f40984281fc016e98cab260` ON `test`
query: DROP TABLE `test`
query: DELETE FROM `test`.`migrations` WHERE `timestamp` = ? AND `name` = ? -- PARAMETERS: [1574464157770,"test1574464157770"]
Migration test1574464157770 has been reverted successfully.
query: COMMIT
カラムの型指定について確認
型指定の方法を確認します。
Entityでの型指定
以下処理ですが、 https://github.com/typeorm/typeorm/tree/master/sample/sample11-all-types-entity を参考に実装しています。一通りの型指定方法を確認できます。
import {
Column,
Entity,
PrimaryGeneratedColumn,
CreateDateColumn,
UpdateDateColumn
} from 'typeorm'
export enum SampleEnum {
ONE = 'one',
TWO = 'two'
}
@Entity()
export class Test {
@PrimaryGeneratedColumn()
id: number
@Column()
name: string
@Column('text')
text: string
@Column({length: '32', unique: true})
shortTextColumn: string
@Column()
numberColumn: number
@Column('integer')
integerColumn: number
@Column('int')
intColumn: number
@Column('smallint')
smallintColumn: number
@Column('bigint')
bigintColumn: number
@Column('float')
floatColumn: number
@Column('double')
doubleColumn: number
@Column('decimal')
decimalColumn: number
@Column()
date: Date
@Column('date')
dateColumn: Date
@Column('time')
timeColumn: Date
@Column('boolean')
isBooleanColumn: boolean
@Column('boolean')
isSecondBooleanColumn: boolean
@Column('json')
jsonColumn: any
@Column('enum', {enum: SampleEnum})
enum: SampleEnum
@CreateDateColumn()
createdDate: Date
@UpdateDateColumn()
updatedDate: Date
}
migrationによって生成されたテーブル
上記Entityをもとにmigrationを生成します。以下、migration実行後によって生成されたテーブルです。
mysql> SHOW COLUMNS FROM `test`;
+-----------------------+-------------------+------+-----+----------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-------------------+------+-----+----------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| text | text | NO | | NULL | |
| shortTextColumn | varchar(32) | NO | UNI | NULL | |
| numberColumn | int(11) | NO | | NULL | |
| integerColumn | int(11) | NO | | NULL | |
| intColumn | int(11) | NO | | NULL | |
| smallintColumn | smallint(6) | NO | | NULL | |
| bigintColumn | bigint(20) | NO | | NULL | |
| floatColumn | float | NO | | NULL | |
| doubleColumn | double | NO | | NULL | |
| decimalColumn | decimal(10,0) | NO | | NULL | |
| date | datetime | NO | | NULL | |
| dateColumn | date | NO | | NULL | |
| timeColumn | time | NO | | NULL | |
| isBooleanColumn | tinyint(4) | NO | | NULL | |
| isSecondBooleanColumn | tinyint(4) | NO | | NULL | |
| jsonColumn | json | NO | | NULL | |
| enum | enum('one','two') | NO | | NULL | |
| createdDate | datetime(6) | NO | | CURRENT_TIMESTAMP(6) | |
| updatedDate | datetime(6) | NO | | CURRENT_TIMESTAMP(6) | |
+-----------------------+-------------------+------+-----+----------------------+----------------+
21 rows in set (0.01 sec)
リレーションについて確認
今回作成するテーブルのER図
このようなリレーションを持つテーブルを構築していきます。
利用するDecorator
- リレーション種別に合わせたDecoratorでリレーションを定義します。
- 1 対 1 (
@OneToOne) - 1 対 多 (
@OneToMany@ManyToOne) - 多 対 多 (
@ManyToMany)
- 1 対 1 (
@JoinColumnは外部キーを所有するテーブルに指定します(＠OneToOne@ManyToOneで利用)。@JoinTableは@ManyToManyでどちらかのEntityに指定します。
コード(Entity)
Phone.ts
import {
Column,
Entity,
PrimaryGeneratedColumn,
OneToOne,
JoinColumn,
} from 'typeorm'
import { User } from './User'
@Entity()
export class Phone {
@PrimaryGeneratedColumn()
readonly id?: number
@Column({length: '50', unique: true})
tel: string
@OneToOne(type => User, {
cascade: true
})
@JoinColumn()
user?: User
constructor(tel: string) {
this.tel = tel
}
}
Post.ts
import {
Column,
Entity,
PrimaryGeneratedColumn,
ManyToOne,
JoinColumn,
} from 'typeorm'
import { User } from './User'
@Entity()
export class Post {
@PrimaryGeneratedColumn()
readonly id?: number
@Column({length: '50'})
body: string
@ManyToOne(type => User, {
cascade: true
})
@JoinColumn()
user: User
constructor(body: string, user: User) {
this.body = body
this.user = user
}
}
Role.ts
import {
Column,
Entity,
PrimaryGeneratedColumn,
ManyToMany,
JoinTable,
} from 'typeorm'
import { User } from './User'
@Entity()
export class Role {
@PrimaryGeneratedColumn()
readonly id?: number
@Column({length: '50'})
name: string
@ManyToMany(type => User, users => users.roles, {
cascade: true
})
@JoinTable()
users?: User[]
constructor(name: string) {
this.name = name
}
}
User.ts
import {
Column,
Entity,
PrimaryGeneratedColumn,
OneToOne,
OneToMany,
ManyToMany,
} from 'typeorm'
import { Phone } from './Phone'
import { Post } from './Post'
import { Role } from './Role'
@Entity()
export class User {
@PrimaryGeneratedColumn()
readonly id?: number
@Column({length: '100'})
name: string
@OneToOne(type => Phone, phone => phone.user)
phone?: Phone
@OneToMany(type => Post, posts => posts.user)
posts?: Post[]
@ManyToMany(type => Role, roles => roles.users)
roles: Role[]
constructor(name: string, roles: Role[]) {
this.name = name
this.roles = roles
}
}
生成されたマイグレーション
上記Entityをもとに、以下のマイグレーションが生成されました。
import {MigrationInterface, QueryRunner} from "typeorm";
export class relation1574473821425 implements MigrationInterface {
name = 'relation1574473821425'
public async up(queryRunner: QueryRunner): Promise<any> {
await queryRunner.query("CREATE TABLE `post` (`id` int NOT NULL AUTO_INCREMENT, `body` varchar(50) NOT NULL, `userId` int NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB", undefined);
await queryRunner.query("CREATE TABLE `role` (`id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB", undefined);
await queryRunner.query("CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB", undefined);
await queryRunner.query("CREATE TABLE `phone` (`id` int NOT NULL AUTO_INCREMENT, `tel` varchar(50) NOT NULL, `userId` int NULL, UNIQUE INDEX `IDX_5e97923da572b93d710c2e3ac2` (`tel`), UNIQUE INDEX `REL_260d7031e6bd9ed4fbcd2dd3ad` (`userId`), PRIMARY KEY (`id`)) ENGINE=InnoDB", undefined);
await queryRunner.query("CREATE TABLE `role_users_user` (`roleId` int NOT NULL, `userId` int NOT NULL, INDEX `IDX_ed6edac7184b013d4bd58d60e5` (`roleId`), INDEX `IDX_a88fcb405b56bf2e2646e9d479` (`userId`), PRIMARY KEY (`roleId`, `userId`)) ENGINE=InnoDB", undefined);
await queryRunner.query("ALTER TABLE `post` ADD CONSTRAINT `FK_5c1cf55c308037b5aca1038a131` FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION", undefined);
await queryRunner.query("ALTER TABLE `phone` ADD CONSTRAINT `FK_260d7031e6bd9ed4fbcd2dd3ad6` FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION", undefined);
await queryRunner.query("ALTER TABLE `role_users_user` ADD CONSTRAINT `FK_ed6edac7184b013d4bd58d60e54` FOREIGN KEY (`roleId`) REFERENCES `role`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
await queryRunner.query("ALTER TABLE `role_users_user` ADD CONSTRAINT `FK_a88fcb405b56bf2e2646e9d4797` FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
}
public async down(queryRunner: QueryRunner): Promise<any> {
await queryRunner.query("ALTER TABLE `role_users_user` DROP FOREIGN KEY `FK_a88fcb405b56bf2e2646e9d4797`", undefined);
await queryRunner.query("ALTER TABLE `role_users_user` DROP FOREIGN KEY `FK_ed6edac7184b013d4bd58d60e54`", undefined);
await queryRunner.query("ALTER TABLE `phone` DROP FOREIGN KEY `FK_260d7031e6bd9ed4fbcd2dd3ad6`", undefined);
await queryRunner.query("ALTER TABLE `post` DROP FOREIGN KEY `FK_5c1cf55c308037b5aca1038a131`", undefined);
await queryRunner.query("DROP INDEX `IDX_a88fcb405b56bf2e2646e9d479` ON `role_users_user`", undefined);
await queryRunner.query("DROP INDEX `IDX_ed6edac7184b013d4bd58d60e5` ON `role_users_user`", undefined);
await queryRunner.query("DROP TABLE `role_users_user`", undefined);
await queryRunner.query("DROP INDEX `REL_260d7031e6bd9ed4fbcd2dd3ad` ON `phone`", undefined);
await queryRunner.query("DROP INDEX `IDX_5e97923da572b93d710c2e3ac2` ON `phone`", undefined);
await queryRunner.query("DROP TABLE `phone`", undefined);
await queryRunner.query("DROP TABLE `user`", undefined);
await queryRunner.query("DROP TABLE `role`", undefined);
await queryRunner.query("DROP TABLE `post`", undefined);
}
}
生成されたテーブルを確認
上記マイグレーションを実行して生成されたテーブルです。
mysql> SHOW TABLES;
+-----------------+
| Tables_in_test |
+-----------------+
| migrations |
| phone |
| post |
| role |
| role_users_user |
| user |
+-----------------+
6 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM `user`;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM `phone`;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| tel | varchar(50) | NO | UNI | NULL | |
| userId | int(11) | YES | UNI | NULL | |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM `post`;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| body | varchar(50) | NO | | NULL | |
| userId | int(11) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM `role`;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM `role_users_user`;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| roleId | int(11) | NO | PRI | NULL | |
| userId | int(11) | NO | PRI | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
migrationsRunオプション
コネクションオプションの migrationsRun を true にすることで、アプリケーション起動時に自動でマイグレーションを実行させることもできるようです。
参考
https://typeorm.io/#/connection-options/common-connection-options
参考
- https://typeorm.io/#/migrations
- https://typeorm.io/#/relations
- https://typeorm.io/#/decorator-reference
- https://github.com/typeorm/typeorm/tree/master/sample/sample11-all-types-entity
- https://github.com/typeorm/typeorm/tree/master/sample/sample2-one-to-one
- https://github.com/typeorm/typeorm/tree/master/sample/sample3-many-to-one
- https://github.com/typeorm/typeorm/tree/master/sample/sample4-many-to-many
