Question: How to model spatial data in Kysely?

[ghost]

Hi!

I am currently working on an application that stores geodata in a MySQL database using the spatial data types. I can use the sql literal to perform all the needed operations but I wonder how this is best modelled in the type definitions in TypeScript?

Thanks for the help!

[koskimas]

Which dialect are you using? The following only applies if you use the built-in MysqlDialect.

I'm not familiar with the spatial data types in MySQL or how they work with the mysql2 driver, but whatever gets returned by default, you can override it using the typeCast option. Something like this:

interface Point {
  lat: number
  long: number
}

function parsePoint(point: string): Point {
  // Parse the data returned by the DB. I don't know what that is.
}

const db = new Kysely<DB>({
  dialect: new MysqlDialect({
    pool: createPool({
      ...yourConfig,

      typeCast(field, next) {
        // Don't know if the type is called `POINT` but you get the gist
        if (field.type === 'POINT') {
          return parsePoint(field.string())
        } else {
          return next();
        }
      }
    })
  })
})

Then you could have a helper that creates a point to be used as an input:

function point(p: Point): Expression<Point> {
  // Don't know if this is the correct SQL, but you get the gist
  return sql<Point>`POINT(${p.lat}, ${p.long})`
}

point could be used in inserts etc.

db.insertInto('some_table')
  .values({
    some_point: point(somePoint)
  })

You'd type your points using the Point interface in the table interface:

type DB = {
  some_table: {
    some_point: Point
  }
}

[ghost]

Thanks for the suggestion! that's amazing! I am using MysqlDialect so that's perfect.

Maybe this would be nice to add to the documentation?

[ghost]

is there a way in Kysely to intercept the building of the query if a certain field is returned? In the case of spatial data, MySQL indeed does return x and y for "point"-shaped data but I'd like to essentially wrap it automatically in a MySQL function so the DB returns GeoJSON.

So concretely, how can I make it so that if I do select(['geo_column', 'some_other_column']) the query executed is SELECT ST_AsGeoJson(geo_column), some_other_column FROM ... ?

[igalklebanov]

Hey @matthiasfeist-foodfacts 👋

Check this out. The underlying driver, mysql2, supports custom result transformation.

[ghost]

So that's nothing I can do in Kysely directly? I'm asking because I might have a problem where for part of the code I'll be using mySQL and for another part the Planetscale HTTP driver – and I assume the Planetscale driver doesn't allow for these things?

[igalklebanov]

Check this out. Planetscale's serverless driver also supports this.

If the kysely-planetscale community dialect doesn't expose this, worth submitting an issue there.

[igalklebanov]

For drivers that don't support custom result transformations, we're thinking about a lightweight opt-in solution.

[ghost]

amazing :) thanks! And thanks for all the great work that you're doing! Kysely rocks! 😀