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

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

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

This article is related to following TypeORM error:

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