[TypeScript][PostgreSQL][TSyringe][Express]Try TypeORM 2

Intro

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

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

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

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

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

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

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

Inner join

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

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

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

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

Many-to-one / one-to-many relations - TypeORM

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.
But they are only for using in JavaScript|TypeScript world.

After all I decided using "Number" type.

book.ts

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

Blog

[TypeScript][PostgreSQL][TSyringe][Express]Try TypeORM 2

Masui Masanori

Intro

Environments

Foreign key

author.ts

genre.ts

book.ts

Not null constraint

book.ts

dataContext.ts

bookService.ts

Inner join

bookService.ts

index.ts

Result

Monetary Types in TypeORM?

book.ts

Join Our Newsletter. No Spam, Only the good stuff.

Related