PostgreSQL Geometry in TypeORM "point" is an unexpected word token

[kyle-helping-hand]

Describe the bug

I am trying to setup an in-memory database for automated testing, but I am unable to synchronize my TypeORM entities since @Column("geometry", { spatialFeatureType: "Point", srid: 4326 }) will cause the following error:

QueryFailedError: Jest: Got error running globalSetup - project-directory/src/test/jest.setup.ts, reason: 💔 Your query failed to parse.
This is most likely due to a SQL syntax error. However, you might also have hit a bug, or an unimplemented feature of pg-mem.
If this is the case, please file an issue at https://github.com/oguimbal/pg-mem along with a query that reproduces this syntax error.

👉 Failed query:

    CREATE TABLE "locations" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "name" character varying NOT NULL, "coordinates" geometry(Point,4326) NOT NULL, "created_at" TIMESTAMP NOT NULL DEFAULT now(), "updated_at" TIMESTAMP NOT NULL DEFAULT now(), CONSTRAINT "UQ_76c01886004f9bddd37ee33d821" UNIQUE ("coordinates"), CONSTRAINT "PK_7cc1c9e3853b94816c094825e74" PRIMARY KEY ("id"));

💀 Syntax error at line 1 col 132:

1  CREATE TABLE "locations" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "name" character varying NOT NULL, "coordinates" geometry(Point,4326) NOT NULL, "created_at" TIMESTAMP NOT NULL DEFAULT now(), "updated_at" TIMESTAMP NOT NULL DEFAULT now(), CONSTRAINT "UQ_76c01886004f9bddd37ee33d821" UNIQUE ("coordinates"), CONSTRAINT "PK_7cc1c9e3853b94816c094825e74" PRIMARY KEY ("id"));
                                                                                                                                      ^
Unexpected word token: "point". Instead, I was expecting to see one of the following:

    - A "int" token

To Reproduce

TypeORM Location Entity

import { Validate } from "class-validator";
import {
    BaseEntity,
    Column,
    CreateDateColumn,
    Entity,
    OneToMany,
    Point,
    PrimaryGeneratedColumn,
    Unique,
    UpdateDateColumn,
} from "typeorm";

import { ValidateString } from "../utils/validator";
import Event from "./event.model";
import SavedLocation from "./saved-location.model";

@Entity("locations")
@Unique(["coordinates"])
export default class Location extends BaseEntity {
    @PrimaryGeneratedColumn("uuid")
    id: string;

    @Column()
    @Validate(ValidateString)
    name: string;

    @Column("geometry", { spatialFeatureType: "Point", srid: 4326 })
    coordinates: Point;

    @OneToMany(() => SavedLocation, (savedLocation) => savedLocation.location, {
        onDelete: "CASCADE",
    })
    savedLocations?: SavedLocation[];

    @OneToMany(() => Event, (event) => event.location)
    events?: Event[];

    @CreateDateColumn({ name: "created_at" })
    createdAt: Date;

    @UpdateDateColumn({ name: "updated_at" })
    updatedAt: Date;
}

Jest Setup

// eslint-disable-next-line @typescript-eslint/no-var-requires
require("ts-node").register({
    transpileOnly: true,
    // // Additional options for pg-mem extension
    // typeCheck: true, // Perform type checking for improved compatibility
    // experimentalEmitDecoratorMetadata: true, // Required for TypeORM decorators
    // emitDecoratorMetadata: true, // Required for TypeORM decorators
});

import { DataSource } from "typeorm";
import { newDb, DataType, IMemoryDb } from "pg-mem";
import { v4 } from "uuid";

export class TestHelper {
    private static _instance: TestHelper;

    public static get instance(): TestHelper {
        if (!TestHelper._instance) {
            TestHelper._instance = new TestHelper();
        }

        return TestHelper._instance;
    }

    private _dataSource: DataSource;
    private _testDb: IMemoryDb;

    async setupTestDB() {
        const db = newDb({
            autoCreateForeignKeyIndices: true,
        });

        db.public.registerFunction({
            implementation: () => "current database",
            name: "current_database",
        });

        db.public.registerFunction({
            implementation: () => "version",
            name: "version",
        });

        db.registerExtension("uuid-ossp", (schema) => {
            schema.registerFunction({
                name: "uuid_generate_v4",
                returns: DataType.uuid,
                implementation: v4,
                impure: true,
            });
        });

        const ds: DataSource = await db.adapters.createTypeormDataSource({
            type: "postgres",
            entities: ["./src/models/**/*.model{.ts,.js}"],
        });

        await ds.initialize();
        await ds.synchronize();

        this._testDb = db;
        this._dataSource = ds;
    }

    get dataSource(): DataSource {
        return this._dataSource;
    }

    async teardownTestDB() {
        if (this._dataSource) {
            // TODO: Destroy testDB
            await this._dataSource.destroy();
        }
    }
}

const setup = async () => {
    await TestHelper.instance.setupTestDB();
};

export default setup;

pg-mem version

2.6.13

[joekrall]

I am also encountering this same error on 2.8.1

[abdul-mattee-pikessoft]

Any solutions for this? Still facing the same issue.

[maximemg]

Still having the issue.

CREATE ..... "geolocation" geography(Point,4326), .....

 Unexpected word token: "point". Instead, I was expecting to see one of the following:

    - A "int" token
    
    

Tried to implement the type like this but none worked

db.registerExtension('postgis', schema => {
  schema.registerEquivalentType({
    name: 'geography',
    equivalentTo: DataType.point,
    isValid: geo => geo !== null && geo !== undefined,
  });

  schema.registerEquivalentType({
    name: 'geography(Point,4326)',
    equivalentTo: DataType.point,
    isValid: geo => geo !== null && geo !== undefined,
  });

  schema.registerEquivalentType({
    name: 'point',
    equivalentTo: DataType.point,
    isValid: geo => geo !== null && geo !== undefined,
  });

  schema.registerEquivalentType({
    name: 'Point',
    equivalentTo: DataType.point,
    isValid: geo => geo !== null && geo !== undefined,
  });

  schema.registerEquivalentType({
    name: 'Point,4326',
    equivalentTo: DataType.point,
    isValid: geo => geo !== null && geo !== undefined,
  });

  schema.registerEquivalentType({
    name: '(Point, 4326)',
    equivalentTo: DataType.point,
    isValid: geo => geo !== null && geo !== undefined,
  });
});

db.public.registerEquivalentType({
  name: 'geography',
  equivalentTo: DataType.point,
  isValid: () => true,
});

db.public.registerEquivalentType({
  name: 'geography(Point,4326)',
  equivalentTo: DataType.point,
  isValid: () => true,
});

db.public.registerEquivalentType({
  name: 'point',
  equivalentTo: DataType.point,
  isValid: geo => geo !== null && geo !== undefined,
});

db.public.registerEquivalentType({
  name: 'Point',
  equivalentTo: DataType.point,
  isValid: geo => geo !== null && geo !== undefined,
});

db.public.registerEquivalentType({
  name: 'Point,4326',
  equivalentTo: DataType.point,
  isValid: geo => geo !== null && geo !== undefined,
});

db.public.registerEquivalentType({
  name: '(Point, 4326)',
  equivalentTo: DataType.point,
  isValid: geo => geo !== null && geo !== undefined,
});