[TypeScript][PostgreSQL][TSyringe][Express]Try TypeORM 2
Tags: typescript, typeorm, express, tsyringe
Published: 2021-07-03 00:49:17

## Intro
* [[TypeScript][PostgreSQL]Try TypeORM](https://dev.to/masanori_msl/typescript-postgresql-try-typeorm-28en)
* [[TypeScript] Try DI with TSyringe](https://dev.to/masanori_msl/typescript-try-di-with-tsyringe-p4n)

This time, I will try relationship by using TypeORM.

### Environments
* Node.js ver.16.3.0
* TypeScript ver.4.3.4
* Express ver.4.17.1
* pg ver.8.6.0
* TSyringe ver.4.5.0
* TypeORM ver.0.2.34
* ts-node ver.10.0.0

## Foreign key
I can add foreign keys like below.

#### author.ts
```ts
import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity('author')
export class Author {
    @PrimaryGeneratedColumn()
    id: number = -1;
    @Column({
        name: 'name',
        type: 'text',
        nullable: false
    })
    name: string = '';
}
```

#### genre.ts
```ts
import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity('genre')
export class Genre {
    @PrimaryGeneratedColumn()
    id: number = -1;
    @Column({
        name: 'name',
        type: 'text',
        nullable: false
    })
    name: string = '';
}
```

#### book.ts
```ts
import {Entity, PrimaryGeneratedColumn, Column, ManyToOne, JoinColumn} from "typeorm";
import { Author } from "./author";
import { Genre } from "./genre";
@Entity('book')
export class Book {
    @PrimaryGeneratedColumn()
    id: number = -1;
    @Column({
        name: 'name',
        type: 'text',
        nullable: false
    })
    name: string = '';
    @ManyToOne(type => Author)
    @JoinColumn({ name: 'authorId', referencedColumnName: 'id' })
    author: Author = new Author();

    @ManyToOne(type => Genre)
    @JoinColumn({ name: 'genreId', referencedColumnName: 'id' })
    genre: Genre = new Genre();
    @Column({
        name: 'last_update_date',
        nullable: false,
        type: 'timestamp with time zone'
    })
    lastUpdateDate: Date = new Date();
}
```

### Not null constraint
One problem is I couldn't add not null constraints into "authorId" and "genreId".

So I add properties explicitly.

#### book.ts
```ts
...
export class Book {
...
    @Column({
        name: 'authorId',
        type: 'integer',
        nullable: false
    })
    authorId: number = 0;
    
    @ManyToOne(type => Author)
    @JoinColumn({ name: 'authorId', referencedColumnName: 'id' })
    author: Author = new Author();
    
    @Column({
        name: 'genreId',
        type: 'integer',
        nullable: false
    })
    genreId: number = 0;
    
    @ManyToOne(type => Genre)
    @JoinColumn({ name: 'genreId', referencedColumnName: 'id' })
    genre: Genre = new Genre();
...
}
```

Now I can add rows like below.

#### dataContext.ts
```ts
import "reflect-metadata";
import { singleton } from "tsyringe";
import { Connection, createConnection } from "typeorm";

// I must not connect two or more times.
@singleton()
export class DataContext {
    private connection: Connection|null = null;
    public async getConnection(): Promise<Connection> {
        if(this.connection != null) {
            return this.connection;
        }
        this.connection = await createConnection();
        return this.connection;
    } 
}
```

#### bookService.ts
```ts
import { autoInjectable } from "tsyringe";
import { Connection, QueryRunner } from "typeorm";
import { DataContext } from "../data/dataContext";
import { Author } from "../entities/author";
import { Book } from "../entities/book";
import { Genre } from "../entities/genre";

@autoInjectable()
export class BookService {
    public constructor(private context: DataContext) {
    }
    public async createSeedData() {
        const connection = await this.context.getConnection();
        const queryRunner = connection.createQueryRunner();
        await queryRunner.startTransaction();
        try {
            const authors = await this.createAuthors(connection, queryRunner);
            const genres = await this.createGenres(connection, queryRunner);
            const result = await this.createBooks(connection, queryRunner, authors, genres);

            if(result === true) {
                await queryRunner.commitTransaction();
            }
        } catch (err) {
            console.error(err);
            await queryRunner.rollbackTransaction();
        }
    }
    private async createAuthors(connection: Connection, queryRunner: QueryRunner): Promise<readonly Author[]> {
        const items = await connection.getRepository(Author)
            .createQueryBuilder('author')
            .getMany();
        if(items.length > 0) {
            return items;
        }
        const newItem = new Author();
        newItem.name = 'David Flanagan';
        await queryRunner.manager.save(newItem);
        return [newItem];
    }
    private async createGenres(connection: Connection, queryRunner: QueryRunner): Promise<readonly Genre[]> {
        const items = await connection.getRepository(Genre)
            .createQueryBuilder('genre')
            .getMany();
        if(items.length > 0) {
            return items;
        }
        const programming = new Genre();
        programming.name = 'Programming';
        await queryRunner.manager.save(programming);
        const manga = new Genre();
        manga.name = 'Manga';
        await queryRunner.manager.save(manga);
        const cooking = new Genre();
        cooking.name = 'Cooking';
        await queryRunner.manager.save(cooking);
        
        return [programming, manga, cooking];
    }
    private async createBooks(connection: Connection, queryRunner: QueryRunner,
            authors: readonly Author[], genres: readonly Genre[]): Promise<boolean> {
        const items = await connection.getRepository(Book)
            .createQueryBuilder('book')
            .getMany();
        if(items.length > 0) {
            return false;
        }
        const author = authors[0];
        const genre = genres.find(g => g.name === 'Programming');
        const newItem = new Book();
        newItem.name = 'Javascript: The Definitive Guide';
        newItem.price = 6318;
        newItem.author = author;
        newItem.genre = genre ?? genres[0];

        await queryRunner.manager.save(newItem);
        return true;
    }
}
```

* [Relations - TypeORM](https://typeorm.io/#/relations)
* [Many-to-one / one-to-many relations - TypeORM](https://typeorm.io/#/many-to-one-one-to-many-relations)

### Inner join
In Entity Framework Core, when I get "Book" data, I can set "Genre" and "Author" instances by foreign keys.

```cs
var books = await context.Books
    .Include(b => b.Genre)
    .Include(b => b.Author)
    .ToListAsync();
```

How about TypeORM?

Because it doesn't set automatically, so I use "innerJoinAndSelect".

#### bookService.ts
```ts
...
export class BookService {
...    
    public async getBooks(): Promise<readonly Book[]> {
        const connection = await this.context.getConnection();
        return await connection.getRepository(Book)
            .createQueryBuilder('book')
            .innerJoinAndSelect('book.genre', 'genre')
            .innerJoinAndSelect('book.author', 'author')
            .getMany();
    }
...
}
```

#### index.ts
```ts
import "reflect-metadata";
import express from 'express';
import { container } from 'tsyringe';
import { BookService } from "./books/bookService";

const port = 3000;
const app = express();
app.use(express.json());
app.use(express.raw());
app.use(express.static('clients/public'));

app.get('/books', async (req, res) => {
    const books = container.resolve(BookService);
    res.json(await books.getBooks());
});
app.listen(port, () => {
    console.log(`Example app listening at http://localhost:${port}`)
});
```

#### Result
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lfyiil07g7crnmi6irwc.png)

* [Many-to-one / one-to-many relations - TypeORM](https://typeorm.io/#/many-to-one-one-to-many-relations)

## Monetary Types in TypeORM?
I want to add "price" column into "book" table.
But what type shall I use for it?

In C#, I usually use "decimal".
But in JavaScript and TypeScript don't have the type.
And "BigInt" can only handle integers.

There are some libraries for handling monetary types like [Dinero.js](https://github.com/dinerojs/dinero.js).
But they are only for using in JavaScript|TypeScript world.

After all I decided using "Number" type.

#### book.ts
```ts
export class Book {
...
    @Column({
        name: 'price',
        type: 'money',
        nullable: false
    })
    price: number = 0;
}
```

* [Javascript: The Definitive Guide](https://www.oreilly.com/library/view/javascript-the-definitive/9781491952016/)
* [BigInt - MDN](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/BigInt)