Postgres point type support for insert queries

[lovis-ff]

I am using kysely with the PostgresDialect configuration as

const db = new Kysely<Database>({
    dialect: new PostgresDialect({
      pool,
    }),
    plugins: [new CamelCasePlugin()],
  });

I am also using postgres point type to store coordinates, which is of the format (x,y).

The cool thing is that the point type is transformed on queries to an object as {x, y} which is way better to work with.

When inserting however like

db.insertInto("points").values({
   location: {x: 4.4, y: 5.5}
}).execute();

I get an error like

"invalid input syntax for type point: \"{\"x\":4.4,\"y\":5.5}\"".

So I have to manually parse the datatype like

const location = {x: 4.4, y: 5.5};
// @ts-ignore
db.insertInto("points").values({
   location: `(${location.x},${location.y})`
}).execute();

which works correctly but introduces overhead and reduced typesafety.

Can we have the transformation also supported on insert or have a plugin at hand for this?

[koskimas]

You can make this type-safe by creating a simple helper like this

import { sql, RawBuilder } from 'kysely'

interface Point {
  x: number
  y: number
}

function point(pt: Point): RawBuilder<Point> {
  const point = `(${pt.x},${pt.y})`
  return sql<Point>`${point}`
}

db.insertInto("points").values({
   location: point(location)
}).execute();

Doing this in a plugin is possible, but it's extremely hard to make it work in all possible cases.

[koskimas]

Actually this plugin would probably work in this particular case:

export class PointPlugin implements KyselyPlugin {
  readonly #transformer = new PointTransformer()

  transformQuery(args: PluginTransformQueryArgs): RootOperationNode {
    return this.#transformer.transformNode(args.node)
  }

  transformResult(
    args: PluginTransformResultArgs
  ): Promise<QueryResult<UnknownRow>> {
    return Promise.resolve(args.result)
  }
}

class PointTransformer extends OperationNodeTransformer {
  protected transformValue(node: ValueNode): ValueNode {
    return {
      ...node,
      value: isPoint(node.value) ? mapPoint(node.value) : node.value
    }
  }

  protected transformPrimitiveValueList(
    node: PrimitiveValueListNode
  ): PrimitiveValueListNode {
    return {
      ...node,
      values: node.values.map(it => isPoint(it.value) ? mapPoint(it.value) : it.value)
    }
  }
}

function mapPoint(point: Point): string {
  return `(${point.x}, ${point.y})`
}

function isPoint(point: unknown): point is Point {
  return typeof point === 'object'
    && point
    && 'x' in point
    && 'y' in point
}

I didn't run the code, so there might be some bugs.

[lovis-ff]

Hi, thanks for this super fast solution.

The first proposed solution works fine and fixes the typing issues. The second proposed solution I could not bring to work unfortunately. All the values get mapped to undefined for me for some reason.

I would prefer to go with the second solution but I understand the problem with making it work in all cases and am not deep enough into the plugins to make it run by myself. If you know why all values get mapped to undefined now, I would be glad to bring the plugin approach to run.

Again, many thanks for the quick help and response on this one!

[koskimas]

I tried this and it works:

      interface Point {
        x: number
        y: number
      }

      class PointPlugin implements KyselyPlugin {
        readonly #transformer = new PointTransformer()

        transformQuery(args: PluginTransformQueryArgs): RootOperationNode {
          return this.#transformer.transformNode(args.node)
        }

        transformResult(
          args: PluginTransformResultArgs
        ): Promise<QueryResult<UnknownRow>> {
          return Promise.resolve(args.result)
        }
      }

      class PointTransformer extends OperationNodeTransformer {
        protected transformValue(node: ValueNode): ValueNode {
          return {
            ...node,
            value: isPoint(node.value) ? mapPoint(node.value) : node.value,
          }
        }

        protected transformPrimitiveValueList(
          node: PrimitiveValueListNode
        ): PrimitiveValueListNode {
          return {
            ...node,
            values: node.values.map((it) => (isPoint(it) ? mapPoint(it) : it)),
          }
        }
      }

      function mapPoint(point: Point): string {
        return `(${point.x}, ${point.y})`
      }

      function isPoint(point: unknown): point is Point {
        return (
          typeof point === 'object' && !!point && 'x' in point && 'y' in point
        )
      }

      const db = new Kysely<DB>({ dialect: ..., plugins: [new PointPlugin()]})

This should actually work in all situations for that I can think of.

[lovis-ff]

That works well. Thanks for the update!