DEV Community

loading...

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

Masui Masanori
Programmer, husband, father I love C#, TypeScript, etc.
・4 min read

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 = '';
}
Enter fullscreen mode Exit fullscreen mode

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 = '';
}
Enter fullscreen mode Exit fullscreen mode

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();
}
Enter fullscreen mode Exit fullscreen mode

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();
...
}
Enter fullscreen mode Exit fullscreen mode

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;
    } 
}
Enter fullscreen mode Exit fullscreen mode

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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();
    }
...
}
Enter fullscreen mode Exit fullscreen mode

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}`)
});
Enter fullscreen mode Exit fullscreen mode

Result

Alt Text

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;
}
Enter fullscreen mode Exit fullscreen mode

Discussion (0)