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

hidetaka okamoto

ormconfig.jsonがないな・・・

hidetaka okamoto

いきなりnpxでやったほうがいいかも？

npx typeorm init --database sqlite3
Need to install the following packages:
  typeorm
Ok to proceed? (y) y

hidetaka okamoto

こっちも変わらんか。
バージョン上がって動き変わったとかかな。

% 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

hidetaka okamoto

やっぱ公式もみよう。

https://typeorm.io/

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設定書いてないからかな。

hidetaka okamoto

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: [],
})

hidetaka okamoto

これで動いた。

hidetaka okamoto

動かしてみる。

$ 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.

hidetaka okamoto

テーブルとレコードができている。

% 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

hidetaka okamoto

SQLite側からデータを入れてみる。

INSERT INTO user (firstName, lastName, age) VALUES ("John", "Due", 30);

ちゃんとデータが入ってる。

SELECT * FROM user;
1|Timber|Saw|25
2|John|Due|30

hidetaka okamoto

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は自動生成。他の３つは必須。
なのでこういうクエリはエラーになる。

sqlite> INSERT INTO user (firstName, lastName) VALUES ("John", "Due");
Error: NOT NULL constraint failed: user.age

hidetaka okamoto

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を新しく追加。

hidetaka okamoto

0.3系なので、現状ドキュメントやQiitaなどの記事があまりあてにならない・・・
0.3明記のものを探す。

hidetaka okamoto

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: [],
})

hidetaka okamoto

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"`);
    }

}

hidetaka okamoto

新しい設定でmigration用のテーブルを作成
現行テーブルのデータをINSERT
現行テーブルを削除
migration用のテーブルを現行テーブル名に変更

こんな感じかな。
変更箇所が２つだからか、このフローが２回動くようになってるっぽい。

hidetaka okamoto

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

hidetaka okamoto

前回の設定でエラーになったクエリをもう一度実行する。

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||

hidetaka okamoto

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でちゃんと１件しか取れないクエリを書いておきたいかな。

await AppDataSource.getRepository(User)
    .createQueryBuilder('user')
    .where('user.id = :id', {
        id: 1
    })
    .getOne()

getMany()でもいけるけど、１件だけなことが自明だから配列で欲しくないパターン用って印象。

hidetaka okamoto

結果はこんな感じ。

$ ts-node src/index.ts
User {
  id: 1,
  firstName: 'Timber',
  lastName: 'Saw',
  age: '25',
  address: null
}

hidetaka okamoto

せっかくなので、もう１つテーブルを作ってみる。

hidetaka okamoto

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: [],
})

hidetaka okamoto

せっかくなので、リレーションをつける。

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

参考

https://www.tutorialspoint.com/typeorm/typeorm_relations.htm#:~:text=TypeORM allows the entities to,target entity and vice versa.

hidetaka okamoto

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"`);
    }

}

hidetaka okamoto

実行

% 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.

hidetaka okamoto

なんかおかしい・・・？

sqlite> SELECT * FROM orders;
id|user_id|userIdId
1|6|6
2|6|6

hidetaka okamoto

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;

}

hidetaka okamoto

動かしてみるコード

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))