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"
}
}
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.
Code first
This is as same as Entity Framework Core.
- Create or update entity classes
- Create a migration file
- 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)
...
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
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);
}
}
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);
}
}
Top comments (0)