[PostgreSQL] Play with TypeORM 1

Intro

This time, I tried accessing Database by TypeORM. Because I wanted to manage Database tables by ORM, I created a table first. 

GitHub - typeorm/typeorm 

TypeORM - Amazing ORM for TypeScript and JavaScript (ES7, ES6, ES5).

Installation and creating a project

Installation

TypeORM needed "reflect-metadata" and database driver.

npm install --save typeorm reflect-metadata pg typescript tsc
npm install --save-dev @types/node

And I also installed "ts-node" to skip compiling.

npm install --save ts-node

Preparing

References

I could generate a TypeORM project by TypeORM command.

npx typeorm init --name gen-typeorm-sample --database postgres

I refered Most of all settings from it.

Adding files and folders

First, I added tsconfig.json.

npx tsc --init

And I edited like below.

tsconfig.json

{
  "compilerOptions": {
    /* Basic Options */
    "incremental": true,
    "target": "es5",
    "module": "commonjs",
    "lib": [
      "es5",
      "es6"
    ],
    "sourceMap": true,
    "outDir": "./build",
    /* Strict Type-Checking Options */
    "strict": true,
    "noImplicitAny": true,
    "strictNullChecks": true,
    "alwaysStrict": true,

    /* Additional Checks */
    "noUnusedLocals": true,
    
    /* Module Resolution Options */
    "moduleResolution": "node",
    "esModuleInterop": true,
    
    /* Experimental Options */
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true,

    /* Advanced Options */
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true
  }
}

And I added some folders.

typeorm-sample(my project name)
L node_modules
L src
    L entity
    L migration
L package-lock.json
L package.json
L tsconfig.json

After that, I added a TypeORM config file.

ormconfig.json

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

Though I created tables by ORM, I had to create empty database first. Or I would get exception by database was not found.

Add an Entity class

I added an Entity class to create table.

sample-user.ts

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity("SampleUser")
export class SampleUser {
    @PrimaryGeneratedColumn()
    id: number = -1;

    @Column()
    firstName: string = '';

    @Column()
    lastName: string = '';

    @Column()
    age: number = -1;
}

Table name

Because the table name was come from the entity class file name. So by default, when I created the table, it was named "sample_user". Because I wanted to name "SampleUser", I added the table name to @Entity().

Creating tables

When I accessed a database table, if it didn't exist, it would be created automatically. 

[2020-06-05 Update] When I connected the database, if it didn't exist, it would be created automatically.

index.ts

import "reflect-metadata";
import {createConnection} from "typeorm";
import { SampleUser } from "./entity/sample-user";

// The table would be created.
createConnection().then(async connection => {

    const user = new SampleUser();
    user.firstName = "Timber";
    user.lastName = "Saw";
    user.age = 25;
    
    await connection.manager.save(user);
    
    const users = await connection.manager.find(User);
    console.log("Loaded users: ", users);
    
}).catch(error => console.log(error));

After execution, the table would be created.

Primary key(auto increment)

I could use "@PrimaryGeneratedColumn()" to set auto increment ID. The datatype of database was "serial". An important thing was I couldn't set any custom values to auto increment values. In this sample, I couldn't change "id" value. When I added new "SampleUser", the "id" value what I set was ignored. When I tried updating, if I changed "id" value, it would be treated as new one.

NOT NULL

By default, the column data type was set as "NOT NULL". So if I wanted to set as nullable value, I had to add a propety into @Column().

sample-user.ts

@Entity("SampleUser")
export class SampleUser {
...
    @Column({ nullable: true })
    age: number = -1;
}

I could know other ColumnOptions here. typeorm/entities.md at master · typeorm/typeorm · GitHub And I could set column datatype through the ColumnOption. typeorm/entities.md at master · typeorm/typeorm · GitHub

Use transaction

How could I use transaction? There might be some ways to use it and I did like below.

index.ts

import "reflect-metadata";
import {createConnection } from "typeorm";
import { SampleUser } from "./entity/sample-user";

createConnection().then(async connection => {
    const queryRunner = connection.createQueryRunner();
    await queryRunner.startTransaction();
    try{
        const secondUser = new SampleUser();    
        secondUser.firstName = 'Hello2';
        secondUser.lastName = 'World2';
        secondUser.age = 36;
        await queryRunner.manager.save(secondUser);
        queryRunner.commitTransaction();
    }catch(error) {
        await queryRunner.rollbackTransaction();
    }
    finally {
        await queryRunner.release();
    }
}).catch(error => console.log(error));

One important thing was when I used transaction through QueryRunner, I couldn't use "connection.manager" to save data. The transaction only effected "queryRunner.manager". https://typeorm.io/#/transactions