sqlite + TypeORM(0.3.x) challenge
まず参考にする記事
Setup
$ mkdir demo && cd demo
$ npm init -y
$ npm i -D typescript ts-node
$ npm i typeorm splite3
$ npx typeorm init --database sqlite3
Directories
% tree -I node_modules
.
├── README.md
├── package-lock.json
├── package.json
├── src
│ ├── data-source.ts
│ ├── entity
│ │ └── User.ts
│ ├── index.ts
│ └── migration
├── tsconfig.json
└── yarn.lock
3 directories, 8 files
ormconfig.jsonがないな・・・
いきなりnpx
でやったほうがいいかも?
npx typeorm init --database sqlite3
Need to install the following packages:
typeorm
Ok to proceed? (y) y
こっちも変わらんか。
バージョン上がって動き変わったとかかな。
% tree -I node_modules
.
├── README.md
├── package-lock.json
├── package.json
├── src
│ ├── data-source.ts
│ ├── entity
│ │ └── User.ts
│ ├── index.ts
│ └── migration
└── tsconfig.json
3 directories, 7 files
やっぱ公式もみよう。
DB設定系はsrc/data-source.ts
に書くようになった?
import "reflect-metadata"
import { DataSource } from "typeorm"
import { User } from "./entity/User"
export const AppDataSource = new DataSource({
synchronize: true,
logging: false,
entities: [User],
migrations: [],
subscribers: [],
})
これがエラーを吐いてる。
Argument of type '{ synchronize: true; logging: false; entities: (typeof User)[]; migrations: undefined[]; subscribers: undefined[]; }' is not assignable to parameter of type 'DataSourceOptions'.
Property 'type' is missing in type '{ synchronize: true; logging: false; entities: (typeof User)[]; migrations: undefined[]; subscribers: undefined[]; }' but required in type 'SpannerConnectionOptions'.ts(2345)
DB設定書いてないからかな。
SQLite3のデータベース作る
作ってなかったので作る。
sqlite3 first-db.sqlite3
これでfirst-db.sqlite3
ファイルができるので、src/data-source.ts
に指定する。
import "reflect-metadata"
import { DataSource } from "typeorm"
import { User } from "./entity/User"
export const AppDataSource = new DataSource({
+ type: 'sqlite',
+ database: 'first-db.sqlite3',
synchronize: true,
logging: false,
entities: [User],
migrations: [],
subscribers: [],
})
これで動いた。
動かしてみる。
$ ts-node src/index.ts
Inserting a new user into the database...
Saved a new user with id: 1
Loading users from the database...
Loaded users: [ User { id: 1, firstName: 'Timber', lastName: 'Saw', age: 25 } ]
Here you can setup and run express / fastify / any other framework.
✨ Done in 2.37s.
テーブルとレコードができている。
% sqlite3 first-db.sqlite3
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
sqlite> .tables
user
sqlite> select * from user;
1|Timber|Saw|25
SQLite側からデータを入れてみる。
INSERT INTO user (firstName, lastName, age) VALUES ("John", "Due", 30);
ちゃんとデータが入ってる。
SELECT * FROM user;
1|Timber|Saw|25
2|John|Due|30
typeorm init
した時にあるデモのEntity。
import { Entity, PrimaryGeneratedColumn, Column } from "typeorm"
@Entity()
export class User {
@PrimaryGeneratedColumn()
id: number
@Column()
firstName: string
@Column()
lastName: string
@Column()
age: number
}
id
は自動生成。他の3つは必須。
なのでこういうクエリはエラーになる。
sqlite> INSERT INTO user (firstName, lastName) VALUES ("John", "Due");
Error: NOT NULL constraint failed: user.age
DBの設定を変えてみる。
import { Entity, PrimaryGeneratedColumn, Column } from "typeorm"
@Entity()
export class User {
@PrimaryGeneratedColumn()
id: number
@Column()
firstName: string
@Column()
lastName: string
- @Column()
+ @Column({
+ nullable: true,
+ type: 'varchar'
+ })
- age: number
+ age: number | null
+ @Column({
+ nullable: true,
+ type: 'varchar'
+ })
address: number | null
}
-
age
をオプションに変更。 -
address
を新しく追加。
0.3系なので、現状ドキュメントやQiitaなどの記事があまりあてにならない・・・
0.3明記のものを探す。
DataSource
にmigration
の設定を追加。
export const AppDataSource = new DataSource({
type: 'sqlite',
database: 'first-db.sqlite3',
synchronize: true,
logging: false,
entities: [User],
- migrations: [],
+ migrations: ['src/migration/*.ts'],
subscribers: [],
})
Migration fileを生成
$ yarn typeorm-ts-node-commonjs migration:generate src/migration/UserMigration -d src/data-source.ts
// or
$ npx typeorm-ts-node-commonjs migration:generate src/migration/UserMigration -d src/data-source.ts
生成されるファイル
import { MigrationInterface, QueryRunner } from "typeorm";
export class UserMigration1658723284422 implements MigrationInterface {
name = 'UserMigration1658723284422'
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`CREATE TABLE "temporary_user" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "firstName" varchar NOT NULL, "lastName" varchar NOT NULL, "age" integer NOT NULL, "address" varchar)`);
await queryRunner.query(`INSERT INTO "temporary_user"("id", "firstName", "lastName", "age") SELECT "id", "firstName", "lastName", "age" FROM "user"`);
await queryRunner.query(`DROP TABLE "user"`);
await queryRunner.query(`ALTER TABLE "temporary_user" RENAME TO "user"`);
await queryRunner.query(`CREATE TABLE "temporary_user" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "firstName" varchar NOT NULL, "lastName" varchar NOT NULL, "age" varchar, "address" varchar)`);
await queryRunner.query(`INSERT INTO "temporary_user"("id", "firstName", "lastName", "age", "address") SELECT "id", "firstName", "lastName", "age", "address" FROM "user"`);
await queryRunner.query(`DROP TABLE "user"`);
await queryRunner.query(`ALTER TABLE "temporary_user" RENAME TO "user"`);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`ALTER TABLE "user" RENAME TO "temporary_user"`);
await queryRunner.query(`CREATE TABLE "user" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "firstName" varchar NOT NULL, "lastName" varchar NOT NULL, "age" integer NOT NULL, "address" varchar)`);
await queryRunner.query(`INSERT INTO "user"("id", "firstName", "lastName", "age", "address") SELECT "id", "firstName", "lastName", "age", "address" FROM "temporary_user"`);
await queryRunner.query(`DROP TABLE "temporary_user"`);
await queryRunner.query(`ALTER TABLE "user" RENAME TO "temporary_user"`);
await queryRunner.query(`CREATE TABLE "user" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "firstName" varchar NOT NULL, "lastName" varchar NOT NULL, "age" integer NOT NULL)`);
await queryRunner.query(`INSERT INTO "user"("id", "firstName", "lastName", "age") SELECT "id", "firstName", "lastName", "age" FROM "temporary_user"`);
await queryRunner.query(`DROP TABLE "temporary_user"`);
}
}
- 新しい設定でmigration用のテーブルを作成
- 現行テーブルのデータをINSERT
- 現行テーブルを削除
- migration用のテーブルを現行テーブル名に変更
こんな感じかな。
変更箇所が2つだからか、このフローが2回動くようになってるっぽい。
migration実行
% yarn typeorm-ts-node-commonjs migration:run -d src/data-source.ts
yarn run v1.22.15
warning package.json: No license field
$ /Users/okamotohidetaka/sandbox/sql/practice-typeorm/node_modules/.bin/typeorm-ts-node-commonjs migration:run -d src/data-source.ts
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'migrations'
query: SELECT * FROM "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 must be executed.
query: BEGIN TRANSACTION
query: CREATE TABLE "temporary_user" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "firstName" varchar NOT NULL, "lastName" varchar NOT NULL, "age" integer NOT NULL, "address" varchar)
query: INSERT INTO "temporary_user"("id", "firstName", "lastName", "age") SELECT "id", "firstName", "lastName", "age" FROM "user"
query: DROP TABLE "user"
query: ALTER TABLE "temporary_user" RENAME TO "user"
query: CREATE TABLE "temporary_user" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "firstName" varchar NOT NULL, "lastName" varchar NOT NULL, "age" varchar, "address" varchar)
query: INSERT INTO "temporary_user"("id", "firstName", "lastName", "age", "address") SELECT "id", "firstName", "lastName", "age", "address" FROM "user"
query: DROP TABLE "user"
query: ALTER TABLE "temporary_user" RENAME TO "user"
query: INSERT INTO "migrations"("timestamp", "name") VALUES (1658723284422, ?) -- PARAMETERS: ["UserMigration1658723284422"]
Migration UserMigration1658723284422 has been executed successfully.
query: COMMIT
前回の設定でエラーになったクエリをもう一度実行する。
sqlite> INSERT INTO user (firstName, lastName) VALUES ("John", "Due");
sqlite>
エラーが出なくなった。
SELECTすると、ちゃんとデータがでてくる。
sqlite> SELECT * FROM user;
1|Timber|Saw|25|
2|Timber|Saw|25|
3|John|Due|30|
4|John|Due||
TypeORM側でクエリをうってみる。
全取得
import { AppDataSource } from "./data-source"
import { User } from "./entity/User"
AppDataSource.initialize().then(async () => {
const result = await AppDataSource.getRepository(User)
.createQueryBuilder('user')
.getMany()
console.log(result)
}).catch(error => console.log(error))
IS NOT NULL
await AppDataSource.getRepository(User)
.createQueryBuilder('user')
.where('age IS NOT NULL')
.getMany()
1件だけ取りたい
await AppDataSource.getRepository(User)
.createQueryBuilder('user')
.getOne()
「OrderByで並び替えて先頭だけとる」みたいなケースでもなければ、WHEREでちゃんと1件しか取れないクエリを書いておきたいかな。
await AppDataSource.getRepository(User)
.createQueryBuilder('user')
.where('user.id = :id', {
id: 1
})
.getOne()
getMany()
でもいけるけど、1件だけなことが自明だから配列で欲しくないパターン用って印象。
結果はこんな感じ。
$ ts-node src/index.ts
User {
id: 1,
firstName: 'Timber',
lastName: 'Saw',
age: '25',
address: null
}
せっかくなので、もう1つテーブルを作ってみる。
npx typeorm entity:create src/entity/Orders
できたファイル
import { Entity } from "typeorm"
@Entity()
export class Orders {
}
data-source.ts
に登録。
import "reflect-metadata"
import { DataSource } from "typeorm"
+ import { Orders } from "./entity/Orders"
import { User } from "./entity/User"
export const AppDataSource = new DataSource({
type: 'sqlite',
database: 'first-db.sqlite3',
synchronize: true,
logging: false,
- entities: [User],
+ entities: [User, Orders],
migrations: ['src/migration/*.ts'],
subscribers: [],
})
せっかくなので、リレーションをつける。
Order.ts
import { Column, Entity, ManyToOne, PrimaryGeneratedColumn } from "typeorm"
import { User } from "./User";
@Entity()
export class Orders {
@PrimaryGeneratedColumn()
id: number
@ManyToOne(
() => User,
user => user.id,
{
cascade: true
}
)
@Column({
type: 'integer'
})
user_id: number;
}
User.ts
- import { Entity, PrimaryGeneratedColumn, Column } from "typeorm"
+ import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from "typeorm"
+ import { Orders } from "./Orders"
@Entity()
export class User {
@PrimaryGeneratedColumn()
+ @OneToMany(() => Orders, order => order.user_id)
id: number
参考
migration
npx typeorm-ts-node-commonjs migration:generate src/migration/OrderMigration -d src/data-source.ts
できたファイル
import { MigrationInterface, QueryRunner } from "typeorm";
export class OrderMigration1658729410085 implements MigrationInterface {
name = 'OrderMigration1658729410085'
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`CREATE TABLE "orders" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "user_id" integer NOT NULL, "userIdId" integer)`);
await queryRunner.query(`CREATE TABLE "temporary_orders" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "user_id" integer NOT NULL, "userIdId" integer, CONSTRAINT "FK_916c66b74d50fe7cad01e3e5895" FOREIGN KEY ("userIdId") REFERENCES "user" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION)`);
await queryRunner.query(`INSERT INTO "temporary_orders"("id", "user_id", "userIdId") SELECT "id", "user_id", "userIdId" FROM "orders"`);
await queryRunner.query(`DROP TABLE "orders"`);
await queryRunner.query(`ALTER TABLE "temporary_orders" RENAME TO "orders"`);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`ALTER TABLE "orders" RENAME TO "temporary_orders"`);
await queryRunner.query(`CREATE TABLE "orders" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "user_id" integer NOT NULL, "userIdId" integer)`);
await queryRunner.query(`INSERT INTO "orders"("id", "user_id", "userIdId") SELECT "id", "user_id", "userIdId" FROM "temporary_orders"`);
await queryRunner.query(`DROP TABLE "temporary_orders"`);
await queryRunner.query(`DROP TABLE "orders"`);
}
}
実行
% npx typeorm-ts-node-commonjs migration:run -d src/data-source.ts
$ /Users/sandbox/sql/practice-typeorm/node_modules/.bin/typeorm-ts-node-commonjs migration:run -d src/data-source.ts
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'migrations'
query: SELECT * FROM "migrations" "migrations" ORDER BY "id" DESC
1 migrations are already loaded in the database.
1 migrations were found in the source code.
UserMigration1658723284422 is the last executed migration. It was executed on Mon Jul 25 2022 13:28:04 GMT+0900 (日本標準時).
1 migrations are new migrations must be executed.
query: BEGIN TRANSACTION
query: CREATE TABLE "orders" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "user_id" integer NOT NULL, "userIdId" integer)
query: CREATE TABLE "temporary_orders" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "user_id" integer NOT NULL, "userIdId" integer, CONSTRAINT "FK_916c66b74d50fe7cad01e3e5895" FOREIGN KEY ("userIdId") REFERENCES "user" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION)
query: INSERT INTO "temporary_orders"("id", "user_id", "userIdId") SELECT "id", "user_id", "userIdId" FROM "orders"
query: DROP TABLE "orders"
query: ALTER TABLE "temporary_orders" RENAME TO "orders"
query: INSERT INTO "migrations"("timestamp", "name") VALUES (1658729410085, ?) -- PARAMETERS: ["OrderMigration1658729410085"]
Migration OrderMigration1658729410085 has been executed successfully.
query: COMMIT
✨ Done in 1.81s.
なんかおかしい・・・?
sqlite> SELECT * FROM orders;
id|user_id|userIdId
1|6|6
2|6|6
xxxId
にTypeORMが勝手に設定するっぽい・・・?
カラム名から_id
を消した。
import { Column, Entity, JoinTable, ManyToOne, PrimaryGeneratedColumn } from "typeorm"
import { User } from "./User";
@Entity()
export class Orders {
@PrimaryGeneratedColumn()
id: number
@ManyToOne(
() => User,
user => user.id
)
@JoinTable()
user: User;
@Column({
type: 'varchar'
})
name: string;
}
動かしてみるコード
import { AppDataSource } from "./data-source"
import { Orders } from "./entity/Orders"
import { User } from "./entity/User"
AppDataSource.initialize().then(async () => {
const user = new User()
user.firstName = "Timber"
user.lastName = "Saw"
user.age = 25
const userRecord = await AppDataSource.manager.save(user)
const order1 = new Orders()
order1.user = userRecord
order1.name = "First item"
await AppDataSource.manager.save(order1)
const order2 = new Orders()
order2.user = userRecord
order2.name = "Second item"
await AppDataSource.manager.save(order2)
const users = await AppDataSource
.getRepository(Orders)
.createQueryBuilder()
.getMany()
console.log("Loaded users: ", users)
}).catch(error => console.log(error))