error: error: syntax error at or near "NOT" typeorm

Question

Hi I'am create an app with NestJs an for the database I choise the Postgres, and the orm is the TypeOrm, I'am create my migration, the migration is an one file, and I already configured the package.json, but when I run with yarn typeorm migration:run, return this error :

query failed: CREATE TABLE "users" ("id" NOT NULL, "name" varchar NOT NULL, "email" varchar NOT NULL, "password" varchar NOT NULL, "biography" varchar NOT NULL, "links" varchar NOT NULL, "posts" int NOT NULL, "created_at" date NOT NULL DEFAULT now(), CONSTRAINT "UQ_97672ac88f789774dd47f7c8be3" UNIQUE ("email"), CONSTRAINT "posts" FOREIGN KEY ("posts") REFERENCES "posts" ("id") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "PK_a3ffb1c0c8416b9fc6f907b7433" PRIMARY KEY ("id"))
error: error: syntax error at or near "NOT"
    at Parser.parseErrorMessage (/home/luan/Área de Trabalho/Projetos/twinker/node_modules/pg-protocol/src/parser.ts:357:11)
    at Parser.handlePacket (/home/luan/Área de Trabalho/Projetos/twinker/node_modules/pg-protocol/src/parser.ts:186:21)
    at Parser.parse (/home/luan/Área de Trabalho/Projetos/twinker/node_modules/pg-protocol/src/parser.ts:101:30)
    at Socket.<anonymous> (/home/luan/Área de Trabalho/Projetos/twinker/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (events.js:315:20)
    at Socket.EventEmitter.emit (domain.js:467:12)
    at addChunk (internal/streams/readable.js:309:12)
    at readableAddChunk (internal/streams/readable.js:284:9)
    at Socket.Readable.push (internal/streams/readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
  length: 92,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '28',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1180',
  routine: 'scanner_yyerror'
}
query: ROLLBACK
Error during migration run:
QueryFailedError: syntax error at or near "NOT"
    at new QueryFailedError (/home/luan/Área de Trabalho/Projetos/twinker/src/error/QueryFailedError.ts:9:9)
    at PostgresQueryRunner.<anonymous> (/home/luan/Área de Trabalho/Projetos/twinker/src/driver/postgres/PostgresQueryRunner.ts:228:19)
    at step (/home/luan/Área de Trabalho/Projetos/twinker/node_modules/typeorm/node_modules/tslib/tslib.js:143:27)
    at Object.throw (/home/luan/Área de Trabalho/Projetos/twinker/node_modules/typeorm/node_modules/tslib/tslib.js:124:57)
    at rejected (/home/luan/Área de Trabalho/Projetos/twinker/node_modules/typeorm/node_modules/tslib/tslib.js:115:69)
    at processTicksAndRejections (internal/process/task_queues.js:93:5) {
  length: 92,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '28',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1180',
  routine: 'scanner_yyerror',
  query: 'CREATE TABLE "users" ("id" NOT NULL, "name" varchar NOT NULL, "email" varchar NOT NULL, "password" varchar NOT NULL, "biography" varchar NOT NULL, "links" varchar NOT NULL, "posts" int NOT NULL, "created_at" date NOT NULL DEFAULT now(), CONSTRAINT "UQ_97672ac88f789774dd47f7c8be3" UNIQUE ("email"), CONSTRAINT "posts" FOREIGN KEY ("posts") REFERENCES "posts" ("id") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "PK_a3ffb1c0c8416b9fc6f907b7433" PRIMARY KEY ("id"))',
  parameters: []
}

My migration code is too big so I put it here: https://pastebin.com/Ww2d0F16

What is this error? I'am search but no find anyware with this error, can you help my please?

The SQL generated is missing type for column id. "id" NOT NULL is not valid. Don't know much about migrations but this is in your code: type: 'id', Is this a valid type? Doesn't seem to translate to one. — Bjarni Ragnarsson
I think it's expecting a type after id? In my typeorm migrations, it looks like "id" character varying NOT NULL, ... — DemiPixel
I used console.log(queryRunner.getMemorySql()) to output the queries, then ran each one until I found the one that broke. — Glenn Bullock

Jboucly Jboucly · Accepted Answer · 2021-04-09T09:07:48

type: 'id' not existe on PosgresSQL !

You can try this =>

export class post1617813884949 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'users',
        columns: [
          {
            name: 'id',
            type: 'number', // Number if your primary key is number else varchar if your primary key is uuid
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment',
          },
          {
            name: 'name',
            type: 'varchar',
          },
          {
            name: 'email',
            type: 'varchar',
            isUnique: true,
          },
          {
            name: 'password',
            type: 'varchar',
          },
          {
            name: 'biography',
            type: 'varchar',
          },
          {
            name: 'links',
            type: 'varchar',
          },
          {
            name: 'posts',
            type: 'int',
          },
          {
            name: 'created_at',
            type: 'date',
            default: 'now()',
          },
        ],
        foreignKeys: [
          {
            name: 'posts',
            columnNames: ['posts'],
            referencedTableName: 'posts',
            referencedColumnNames: ['id'],
            onUpdate: 'CASCADE',
            onDelete: 'CASCADE',
          },
        ],
      }),
    );
    ...other_table
}

error: error: syntax error at or near "NOT" typeorm

1 Answers