TypeORM - Entity

Creating a field type to store amount using Typeorm and NestJS

I am experimenting with Postgres, TypeORM and NestJS. I was trying to create a decimal field that will store currency amounts with precision of 10 and upto 2 decimal places.

My amount: 8000.50 should be supported by this field.

So, I created the TypeORM entity as shown below and ran the npm run start:dev.

import { Column, Entity, PrimaryGeneratedColumn, CreateDateColumn } from 'typeorm'

@Entity({ name: 'transactions' })
export class TransactionsEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ precision: 10, scale: 2 })
  amount: number;

  @Column({ type: 'varchar', length: 300 })
  description: string;

  @Column({ default: true })
  isActive: string;

  @CreateDateColumn({ type: 'timestamptz', default: () => 'CURRENT_TIMESTAMP' })
  createdAt: Date;

  @Column({ type: 'varchar', length: 300 })
  createdBy: string;
}

as soon as I ran “npm run start:dev” I got error as shown below:

query failed: ALTER TABLE "transactions" ADD "amount" integer(10,2) NOT NULL
error: { error: syntax error at or near "("
    at Parser.parseErrorMessage (/Users/samundra/personal/www/whoowesme/backend/node_modules/pg-protocol/dist/parser.js:278:15)
    at Parser.handlePacket (/Users/samundra/personal/www/whoowesme/backend/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/samundra/personal/www/whoowesme/backend/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.stream.on (/Users/samundra/personal/www/whoowesme/backend/node_modules/pg-protocol/dist/index.js:10:42)
    at Socket.emit (events.js:182:13)
    at addChunk (_stream_readable.js:283:12)
    at readableAddChunk (_stream_readable.js:264:11)
    at Socket.Readable.push (_stream_readable.js:219:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
  length: 90,
  name: 'error',
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '48',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1149',
  routine: 'scanner_yyerror' }
[Nest] 8769   - 10/13/2020, 2:35:46 AM   [TypeOrmModule] Unable to connect to the database. Retrying (2)... +3045ms
QueryFailedError: syntax error at or near "("
    at new QueryFailedError (/Users/samundra/personal/www/whoowesme/backend/node_modules/typeorm/error/QueryFailedError.js:11:28)
    at Query.callback (/Users/samundra/personal/www/whoowesme/backend/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:187:38)
    at Query.handleError (/Users/samundra/personal/www/whoowesme/backend/node_modules/pg/lib/query.js:139:19)
    at Client._handleErrorMessage (/Users/samundra/personal/www/whoowesme/backend/node_modules/pg/lib/client.js:326:17)
    at Connection.emit (events.js:182:13)
    at parse (/Users/samundra/personal/www/whoowesme/backend/node_modules/pg/lib/connection.js:109:12)
    at Parser.parse (/Users/samundra/personal/www/whoowesme/backend/node_modules/pg-protocol/dist/parser.js:40:17)
    at Socket.stream.on (/Users/samundra/personal/www/whoowesme/backend/node_modules/pg-protocol/dist/index.js:10:42)
    at Socket.emit (events.js:182:13)
    at addChunk (_stream_readable.js:283:12)

Here, the error error: { error: syntax error at or near “(” always showed up no matter what I did. But the error message Unable to connect to the database. Retrying (2) is mis-leading. The root cause is failed query and not the database connection itself. Clearly, it said that my query was wrong. Then I copied my “ALTER query”

ALTER TABLE “transactions” ADD “amount” integer(10,2) NOT NULL

and ran it directly against DB and got exact same error. By the way, I am using DBeaver to run these query.

Alter table error
Alter table error

Now, I was pretty much confident that integer(10, 2) was not working on Postgres database. So, I switched to use numeric(10,2) and it worked.

Alter Table - Working
Alter Table – Working

Now, all I had to do was use numeric type with my Entity. TypeORM allows us to specify custom type for the column so I had to do was specify Column({ type: numeric, decimal: 10, scale: 2 }). After change, my TypeORM entity is:

import { Column, Entity, PrimaryGeneratedColumn, CreateDateColumn } from 'typeorm'

@Entity({ name: 'transactions' })
export class TransactionsEntity {
  @PrimaryGeneratedColumn()
  id: number;

  // Updated and added 'numeric'
  @Column({ type: 'numeric', precision: 10, scale: 2 })
  amount: number;

  @Column({ type: 'varchar', length: 300 })
  description: string;

  @Column({ default: true })
  isActive: string;

  @CreateDateColumn({ type: 'timestamptz', default: () => 'CURRENT_TIMESTAMP' })
  createdAt: Date;

  @Column({ type: 'varchar', length: 300 })
  createdBy: string;
}

Notice that I have update decorator for amount field with

@Column({ type: ‘numeric’, precision: 10, scale: 2 })

Screenshot:

A quick screenshot on how I found that numeric field is supported by Column() decorator. Thanks to Typescript typehints it was matter of click click and looking through the self-documented type definitions. This is where type definitions really shine bright and self-documents its usage.

Typescript-beauty
Typescript-beauty

 

This article is related to following TypeORM error:

  • { error: syntax error at or near “(“
  • [TypeOrmModule] Unable to connect to the database. Retrying (2)

 

3 Replies to “Creating a field type to store amount using Typeorm and NestJS”

  1. Hi Samundra Shrestha. I have the same problem. In what format does your data was stored in Postgres? My problem is when the user inputs a whole number let’s say 88, Postgres store the data with additional decimal whatever the scale is .e.g. @Column({ name: ‘startHours’ , type: ‘decimal’, precision: 15, scale: 3, nullable: true }). It would store 88.000 in the Postgres.

    What I want to achieve is let user inputs whole number without the extra decimal and let user input up to 3 decimal place.

    1. Hi Mike Bernal. I think that is always the case if you specify scale in your data type. You may be able to use float, but you’ll have to validate the user input to allow only 3 decimal places in the application level instead

Leave a Reply to Junerey Cancel reply

Your email address will not be published. Required fields are marked *