It's possible use native data transformation features of the DB?

[alexojegu]

Is your feature request related to a problem? Please describe.
Working with spatial DB features it's possible save and load data of the DB in several formats, in my case I am interesting in use ST_GeomFromGeoJSON and ST_AsGeoJson. So that I can do something as the next and get the position data as GeoJSON:

@Entity({ tableName: "studio" })
export default class StudioEntity {
    @PrimaryKey()
    public id!: number;

    @Property({ columnType: "point srid 4326" })
    public position!: Geometry;

    @Property({ persist: false })
    public distance?: number;
}

interface Geometry {
    type: string;
    coordinates: number[];
}

public async findId(id: number | string): Promise<StudioEntity | null> {
    return this.studioRepository.findOne({ id: { $eq: id as number } });
}

Describe the solution you'd like
Some way to be able to use a function from the DB when saving or getting data from the DB.

Describe alternatives you've considered
Use a "custom type", but if I understand correctly, then I will need handle it manually using JS. Maybe can work to save data, using something as (I have to try it):

convertToDatabaseValue(value: any): any {
    return `ST_GeomFromGeoJSON('${JSON.stringify(value) || null}', 1, 4326)`
  }

But I can't think of what to do when load data in a query.

[B4nan]

So I guess we need something like this:

https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/cookbook/advanced-field-value-conversion-using-custom-mapping-types.html

[alexojegu]

Would it be possible to access Knex from custom types?

convertToDatabaseValue(value: any, knex: Knex): any {
    return knex.raw("ST_GeomFromGeoJSON(?, 1, 4326)", [JSON.stringify(geometry || null)]);
}

Or it might be better to modify the initialization of MikroORM so it can be integrated with tools as knex-postgis, for example:

@singleton()
export default class OrmClient {
    private mikroOrm: MikroORM<AbstractSqlDriver>;
    private postgis: knexPostgis;

    public constructor() {
        this.mikroOrm = new MikroORM({
            type: "mysql",
            clientUrl: process.env.DATABASE_URL,
            entities: ["dist/entities/*.js"],
            entitiesTs: ["src/entities/*.ts"],
            autoJoinOneToOneOwner: false,
            forceUtcTimezone: true,
        });
        this.postgis = knexPostgis(this.mikroOrm.getKnexQB());
    }

    public async initialize(): Promise<void> {
        await MikroORM.init(this.mikroOrm);
    }

    public get st(): knexPostgis {
        return this.postgis;
    }
}

export default PointType extends Type {
    private st: knexPostgis;

    public constructor() {
        super();

        this.st = container.resolve(OrmClient).st;
    }

    convertToDatabaseValue(value: any): any {
        return this.st.geomFromGeoJSON(value || null);
    }
}

[B4nan]

You can do this: https://github.com/mikro-orm/mikro-orm/blob/master/tests/issues/GH372.test.ts#L13

[ashervb]

@B4nan running into a similar issue myself. It's easy enough to implement convertToDatabaseValue but convertToJSValue for spatial types becomes more cumbersome to write without the ability to use the built-in db functions. To parse the WKB I need to make use of the wkx library as follows:

  convertToJSValue(value, platform) {
    if (typeof value === 'string') {
      const hexAry = wkb.match(/.{2}/g)
      const intAry = []
      for (const i in hexAry) {
        intAry.push(parseInt(hexAry[i], 16))
      }
      const buf = Buffer.from(intAry)
      const geom = wkx.Geometry.parse(buf)
      return {
        type: 'Point',
        coordinates: [geom.x, geom.y],
      }
    }
    return value
  }

vs just 'ST_AsGeoJSON("column")::json' (which works when used in an @Formula decorator actually).

[marines]

While I'm patiently waiting for native SQL selectors here's my not-so-cumbersome wkx implementation of a Point custom type:

import { Type } from '@mikro-orm/core';
import { Point } from 'geojson';
import wkx from 'wkx';

export class GeometryType extends Type<Point, string> {

    convertToDatabaseValue(value: Point): string {
        return wkx.Geometry.parseGeoJSON(value).toWkb().toString('hex');
    }

    convertToJSValue(value: string): Point {
        return wkx.Geometry.parse(Buffer.from(value, 'hex')).toGeoJSON() as Point;
    }

}

I'm using it for a geometry column type:

@Property({
      type: GeometryType,
      columnType: 'geometry',
})
geometry: Point;