[TypeScript][PostgreSQL]Try TypeORM

Intro

This time, I will try accessing PostgreSQL from a TypeScript(Node.js) project.

Environments

• Node.js ver.16.2.0
• TypeScript ver.4.3.2
• ts-node ver.10.0.0
• typeorm ver.0.2.32
• pg ver.8.6.0
• reflect-metadata ver.0.1.13

Preparing

According to the documents, I installed TypeORM and added some files.

• TypeORM - Amazing ORM for TypeScript and JavaScript (ES7, ES6, ES5). Supports MySQL, PostgreSQL, MariaDB, SQLite, MS SQL Server, Oracle, WebSQL databases. Works in NodeJS, Browser, Ionic, Cordova and Electron platforms.

ormconfig.json

{
    "type": "postgres",
    "host": "localhost",
    "port": 5432,
    "username": "postgres",
    "password": "example",
    "database": "print_sample",
    "synchronize": true,
    "logging": true,
    "entities": [
       "ts/src/entity/**/*.ts"
    ],
    "migrations": [
       "src/migration/**/*.ts"
    ],
    "subscribers": [
       "ts/src/subscriber/**/*.ts"
    ],
    "cli": {
       "entitiesDir": "src/entity",
       "migrationsDir": "src/migration",
       "subscribersDir": "src/subscriber"
    }
 }

tsconfig.json

{
  "compilerOptions": {
    "target": "es5",
    "module": "commonjs",
    "lib": ["DOM", "ES5", "ES2015"],
    "sourceMap": true,
    "outDir": "./js",
    "strict": true,
    "noImplicitAny": true,"strictNullChecks": true,
    "strictFunctionTypes": true,
    "strictBindCallApply": true,
    "strictPropertyInitialization": true,
    "noImplicitThis": true,
    "alwaysStrict": true,
    "moduleResolution": "node",
    "esModuleInterop": true,
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true
  }
}

index.ts

import "reflect-metadata";
import {createConnection} from "typeorm";
import { ExecutionItem } from "./src/entity/executionItem";

function start() {
    createConnection().then(async connection => {
        // create a new record
        const sampleItem = new ExecutionItem();
        sampleItem.filePath = 'filepath.pdf';
        sampleItem.executionOrder = 1;
        sampleItem.printType = 2;
        sampleItem.lastUpdateDate = new Date();
        sampleItem.remarks = 'hello';
        await connection.manager.save(sampleItem);    
    }).catch(error => console.error(error));
}
start();

Migrations

DB first?

I couldn't find how to generate entity classes from existed database.

According to this issue, I may be able to use "typeorm-model-generator".
But it has already entered maintenance phase.

• Converting database to typeorm entities (Reverse engineering) · Issue #540 · typeorm/typeorm · GitHub

Code first

This is as same as Entity Framework Core.

1. Create or update entity classes
2. Create a migration file
3. run

1. Create or update entity classes

As same as Entity Framework Core, I can specify each tables and columns' names, data types, etc..

executionItem.ts

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";
@Entity('execution_item')
export class ExecutionItem {
    @PrimaryGeneratedColumn()
    id: number = -1;
    @Column({
        name: 'file_path',
        type: 'text',
        nullable: false
    })
    filePath: string = '';
    @Column({
        name: 'execution_order',
        type: 'integer',
        nullable: false,
    })
    executionOrder: number = 0;
    @Column({
        name: 'print_type',
        type: 'integer',
        nullable: false,
    })
    printType: number = 0;
    @Column({
        name: 'finished_time',
        nullable: true,
        type: 'timestamp with time zone'
    })
    finishedTime: Date|null = null;
    @Column({
        name: 'error_message',
        type: 'text',
        nullable: true
    })
    errorMessage: string|null = null;
    @Column({
        name: 'last_update_date',
        nullable: false,
        type: 'timestamp with time zone'
    })
    lastUpdateDate: Date = new Date();
}

One important thing is when I specify the data type explicitly, I must specify all columns.
Otherwise I will get an error.

DataTypeNotSupportedError: Data type "Object" in "ExecutionItem.errorMessage" is not supported by "postgres" database.
    at new DataTypeNotSupportedError (C:\Users\example\OneDrive\Documents\workspace\node-typeorm-sample\node_modules\typeorm\error\DataTypeNotSupportedError.js:8:28)
...

• Column options
• Column types for postgres

2. Create a migration file

I can create a migration file in two way.

"migration:create" generates an empty migration file.

npx typeorm migration:create -n CreateAddRemarks

1622389988549-CreateAddRemarks.ts

import {MigrationInterface, QueryRunner} from "typeorm";

export class CreateAddRemarks1622389988549 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
    }
}

Of cource it does nothing.
So I must write a migration file by myself.

"migration:generate" generate updating operations by differences from the last migration.

npx typeorm migration:generate -n GenerateAddRemarks

import {MigrationInterface, QueryRunner} from "typeorm";

export class GenerateAddRemarks1622382632575 implements MigrationInterface {
    name = 'GenerateAddRemarks1622382632575'

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "execution_item" ADD "remarks" text`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "execution_item" DROP COLUMN "remarks"`);
    }

}

Run

Because I use "ts-loader", I get an errror if I execute this command.

npx typeorm migration:run

So I change the command like below.

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

• # Running and reverting migrations
• Migrations

CRUD

Transactions

TypeORM has some way to create and use transactions.
Because it was easy to understand for me, I choosed using "QueryRunner".

print.service.ts

import { Connection } from "typeorm";
import { ExecutionItem } from "../entity/executionItem";

export async function create(connection: Connection): Promise<void> {
    const queryRunner = connection.createQueryRunner();
    await queryRunner.startTransaction();
    try
    {
...
        await queryRunner.manager.save(sampleItem);
        queryRunner.commitTransaction();
    } catch(error) {
        await queryRunner.rollbackTransaction();
        console.error(error);
    }
}

• Transactions

When I access Database, I must share single connection instance to avoid getting errors.

C:\Users\example\OneDrive\Documents\workspace\node-typeorm-sample\src\error\AlreadyHasActiveConnectionError.ts:8
        super();
        ^
AlreadyHasActiveConnectionError: Cannot create a new connection named "default", because connection with such name already exist and it now has an active connection session.
    at new AlreadyHasActiveConnectionError (C:\Users\example\OneDrive\Documents\workspace\node-typeorm-sample\src\error\AlreadyHasActiveConnectionError.ts:8:9)
    at ConnectionManager.create (C:\Users\example\OneDrive\Documents\workspace\node-typeorm-sample\src\connection\ConnectionManager.ts:57:23)
...

So I create connection first, and set it as every methods' arguments.

index.ts

import "reflect-metadata";
import {createConnection} from "typeorm";
import * as prints from './src/prints/print.service';

async function start() {
    const connection = await createConnection();
    const item01 = await prints.getItem(connection, 205);
    if(item01 == null) {
        await prints.create(connection);
    } else {
        item01.finishedTime = new Date();
        await prints.update(connection, item01);
    }
    await prints.deleteTarget(connection, 204);
    process.exit(0);
}
start();

Insert, Update and Delete

import { Connection } from "typeorm";
import { ExecutionItem } from "../entity/executionItem";

export async function create(connection: Connection): Promise<void> {
    const queryRunner = connection.createQueryRunner();
    await queryRunner.startTransaction();
    try
    {
        const sampleItem = new ExecutionItem();
        sampleItem.filePath = 'filepath.pdf';
        sampleItem.executionOrder = 1;
        sampleItem.printType = 2;
        sampleItem.lastUpdateDate = new Date();
        sampleItem.remarks = 'hello';
        await queryRunner.manager.save(sampleItem);
        queryRunner.commitTransaction();
    } catch(error) {
        await queryRunner.rollbackTransaction();
        console.error(error);
    }
}
export async function getItem(connection: Connection, id: number): Promise<ExecutionItem|null> {
    const result = await connection.getRepository(ExecutionItem)
        .createQueryBuilder('execution_item')
        .where('execution_item.id = :id', { id })
        .getOne();        
    if(result == null) {
        return null;
    }
    return result;
}
export async function update(connection: Connection, target: ExecutionItem) {
    const queryRunner = connection.createQueryRunner();
    await queryRunner.startTransaction();
    try
    {
        const updateTarget = await getItem(connection, target.id);
        if(updateTarget == null) {
            console.error('target was not found');
            return;
        }
        updateTarget.filePath = target.filePath;
        updateTarget.executionOrder = target.executionOrder;
        updateTarget.printType = target.printType;
        updateTarget.finishedTime = target.finishedTime;
        updateTarget.errorMessage = target.errorMessage;
        updateTarget.lastUpdateDate = new Date();
        updateTarget.remarks = target.remarks;
        await queryRunner.manager.save(updateTarget);
        queryRunner.commitTransaction();
    } catch(error) {
        await queryRunner.rollbackTransaction();
        console.error(error);
    }
}
export async function deleteTarget(connection: Connection, targetId: number) {
    const queryRunner = connection.createQueryRunner();
    await queryRunner.startTransaction();
    try
    {
        const target = await getItem(connection, targetId);
        if(target == null) {
            console.error('target was not found');
            return;
        }
        await queryRunner.manager.remove(target);
        queryRunner.commitTransaction();
    } catch(error) {
        await queryRunner.rollbackTransaction();
        console.error(error);
    }
}