TheLazy.Dev
Portfolio GitHub

Blog

typescript |typeorm

[TypeScript][PostgreSQL]Try TypeORM

masanori_msl

Masui Masanori

Posted on May 30, 2021

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

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

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

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

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.

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

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

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

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

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

}
Enter fullscreen mode Exit fullscreen mode

Run

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

npx typeorm migration:run
Enter fullscreen mode Exit fullscreen mode

So I change the command like below.

npx ts-node ./node_modules/typeorm/cli.js migration:run
Enter fullscreen mode Exit fullscreen mode

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

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

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

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);
    }
}
Enter fullscreen mode Exit fullscreen mode
💖 💪 🙅 🚩
masanori_msl
Masui Masanori

Posted on May 30, 2021

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

Sign up to receive the latest update from our blog.

Related

Handling TypeORM migrations in Electron apps
electron Handling TypeORM migrations in Electron apps

November 24, 2024

Sveltekit + TypeScript + TypeORM + ESM
sveltekit Sveltekit + TypeScript + TypeORM + ESM

October 21, 2024

Project Starter Kit: Neon Postgres Database + ExpressJs + TypeScript and TypeORM + CLI Tool
devchallenge Project Starter Kit: Neon Postgres Database + ExpressJs + TypeScript and TypeORM + CLI Tool

August 28, 2024

Desenvolvimento Full-Stack: Integrando Front-end e Back-end com Eficiência
fullstack Desenvolvimento Full-Stack: Integrando Front-end e Back-end com Eficiência

May 31, 2024

Migration - Module query with TypeORM version 0.3.x
typescript Migration - Module query with TypeORM version 0.3.x

April 3, 2024