[PostgreSQL] Play with TypeORM 2

Intro

In this post, I tried migration and setting foreign key.

[PostgreSQL] Play with TypeORM 1 GitHub - typeorm/typeorm

TypeORM - Amazing ORM for TypeScript and JavaScript (ES7, ES6, ES5).

Migration

To migrate tables, I created migration files by command.

npx typeorm migration:create -n AddUpdateDate

The file was created in src/migration.

1591186678545-AddUpdateDate.ts

import {MigrationInterface, QueryRunner} from "typeorm";
export class AddUpdateDate1591186678545 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
    }
    public async down(queryRunner: QueryRunner): Promise<void> {
    }
}

This time, I wanted to add "updateDate" column into "SampleUser" table.

1591186678545-AddUpdateDate.ts

import {MigrationInterface, QueryRunner} from "typeorm";
export class AddUpdateDate1591186678545 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
      await queryRunner.query(`ALTER TABLE "SampleUser" ADD COLUMN "updateDate" date DEFAULT current_timestamp NOT NULL`);
    }
    public async down(queryRunner: QueryRunner): Promise<void> {
      await queryRunner.query(`ALTER TABLE "SampleUser" DROP COLUMN "updateDate"`);
    }
}

One important thing was the column data type was nullable by default in SQL. So I should add "NOT NULL" into SQL or { nullable:true } into @Column() of Entity class. Or I would get error on runntime.

Failed execution

I could do "migration:run" command to execute the file.

npx typeorm migration:run

But I got an error.

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

SyntaxError: Cannot use import statement outside a module
    at wrapSafe (internal/modules/cjs/loader.js:1101:16)
    at Module._compile (internal/modules/cjs/loader.js:1149:27)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1205:10)
    at Module.load (internal/modules/cjs/loader.js:1034:32)
    at Function.Module._load (internal/modules/cjs/loader.js:923:14)
    at Module.require (internal/modules/cjs/loader.js:1074:19)
    at require (internal/modules/cjs/helpers.js:72:18)
    at Function.PlatformTools.load (C:\Users\example\Documents\workspace\gen-typeorm-sample\node_modules\typeorm\platform\PlatformTools.js:114:28)
    at C:\Users\example\Documents\workspace\gen-typeorm-sample\node_modules\typeorm\util\DirectoryExportedClassesLoader.js:39:69
    at Array.map (<anonymous>)

Resolve

It was because I used ts-node. I could do "migration:run" command like this.

npx ts-node ./node_modules/typeorm/cli.js migration:run

https://typeorm.io/#/migrations/running-and-reverting-migrations I also could reverting like this.

npx ts-node ./node_modules/typeorm/cli.js migration:revert

After I reverted, the last change was reverted.

"migration" table

When I did "migration:run", one table what was named "migration" was added.

If I did like below, the last "migration:run" was ignored.

1. I created table by migration file.
2. I dropped table directly( not by migration file).
3. I did "migration:run" again.

To do this, I had to "migration:revert" or delete last row from "migration" table.

When could I get new "id"?

For example, I wanted to add two rows(table A and B). B had referred A's id. So I had to add A first, and get A's id and put into B. When I could get A's id? Let's try!

index.ts

import "reflect-metadata";
import {createConnection } from "typeorm";
import { SampleUser } from "./entity/sample-user";

createConnection().then(async connection => {
   const queryRunner = connection.createQueryRunner();
   await queryRunner.startTransaction();
   try{
      const thirdUser = new SampleUser();
      thirdUser.firstName = 'Hello4';
      thirdUser.lastName = 'World4';
      thirdUser.age = 43;

      console.log("Before saving");
      console.log(thirdUser);

      await queryRunner.manager.save(thirdUser);
   
      console.log("Before committing");
      console.log(thirdUser);
      
      queryRunner.commitTransaction();
   
      console.log("After");
      console.log(thirdUser);
    }catch(error) {
        await queryRunner.rollbackTransaction();
    }
    finally {
        await queryRunner.release();
    }
}).catch(error => console.log(error));

result

Before saving
SampleUser { id: -1, firstName: 'Hello4', lastName: 'World4', age: 43 }
Before committing
SampleUser { id: 6, firstName: 'Hello4', lastName: 'World4', age: 43 }
After
SampleUser { id: 6, firstName: 'Hello4', lastName: 'World4', age: 43 }

When I did "queryRunner.manager.save", the new value was created.

Foreign key

How to set SampleUser "id" as Foreign key? I could use @ManyToOne(), @OneToMany(), @OneToOne().

sample-user.ts

import {Entity, PrimaryGeneratedColumn, Column, OneToMany, UpdateDateColumn} from "typeorm";
import { Post } from "./post";

@Entity("SampleUser")
export class SampleUser {
    @PrimaryGeneratedColumn()
    id: number = -1;

    @Column({ type: 'text' })
    firstName: string = '';

    @Column({ type: 'text' })
    lastName: string = '';

    @Column()
    age: number = -1;

    @UpdateDateColumn({ type: 'timestamptz' })
    updateDate: Date = new Date();

    @OneToMany(type => Post, post => post.user)
    posts: Post[]|null = null;

}

post.ts

import {Entity, PrimaryGeneratedColumn, Column, ManyToOne, UpdateDateColumn, OneToOne, JoinColumn} from "typeorm";
import { SampleUser } from "./sample-user";
import { Category } from "./category";

@Entity("Post")
export class Post {
    @PrimaryGeneratedColumn()
    id: number = -1;

    @ManyToOne(type => SampleUser, user => user.posts)
    user: SampleUser = new SampleUser();

    @OneToOne(() => Category)
    @JoinColumn([{
        name: 'categoryId',
        referencedColumnName: 'id'
    }])
    category: Category = new Category();

    @Column({ type: 'text' })
    title: string = '';

    @Column({ type: 'text' })
    article: string = '';

    @UpdateDateColumn({ type: 'timestamptz' })
    updateDate: Date = new Date();
}

category.ts

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

@Entity("Category")
export class Category {
    @PrimaryGeneratedColumn()
    id: number = -1;

    @Column({ type: 'text' })
    name: string = '';

    @UpdateDateColumn({ type: 'timestamptz' })
    updateDate: Date = new Date();
}

typeorm/many-to-one-one-to-many-relations.md at master · typeorm/typeorm · GitHub

Should I create table by synchronize: true or migration files?

Until now, I set "synchronize" true in ormconfig.json. So if some tables didn't exist, they would be created automatically. But I could write "CREATE TABLE" in migration files.

1591186678544-CreateSampleUserTable.ts

import {MigrationInterface, QueryRunner} from "typeorm";

export class AddCreateSampleUserTable1591186678544 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`CREATE TABLE "SampleUser" ( id serial PRIMARY KEY, "firstName" text NOT NULL, "lastName" text NOT NULL, "age" integer NOT NULL)`);
    }
    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DROP TABLE "SampleUser"`);
    }
}

What should I use? I couldn't decide now. But when I used master tables (I usually didn't change and just referred from other tables), because I wanted to set rows. So I preferred to use migration files.

1591356501422-AddCategories.ts

import {MigrationInterface, QueryRunner} from "typeorm";
import { Category } from "../entity/category";
export class AddCategories1591356501422 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
        const programming = new Category();
        programming.name = 'Programming';
        await queryRunner.manager.save(programming);
        const book = new Category();
        book.name = 'Book';
        await queryRunner.manager.save(book);
    }
    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DELETE FROM "Category"`);
    }
}