K
Kysely•2y ago
Dylel

Kysely Geometry Parsing Plugin - Convert driver values to/from geojson

0 I've currently written a rough prototype for parsing geometry. Most orms/query builders allow you to provide a mapValueToDb & mapValueFromDb based on a column type. However, kyseley doesn't really seem to have that. I found transformQueryResponse inside a plugin example, so I've written one of those, of which I had to loop over every value (nested too) that could be a buffer and then parse it to geometry. So I've used this to handle parsing from DB to client, however transforming it back to the DB without knowing the column type was going to be near impossible. So to get the value back into the db driver format, i've used a raw query builder response template (aslo from docs) to make the value to a geometry using an sql function. I've made a repo: repo: https://github.com/dylel/kysely-mssql-geometry FYI, i'm using a locally installed kyseley from a PR to support SQL Server as thats the DB i'm using. you'll need to build this if you want to actually run anything the udt.ts wasn't created by me, that came from node-mssql as converting from microsoft's udt format is quite complicated Is there a better way I could have done this? seems rather chaotic way to convert values to/from the DB driver. I've got it working in this format plugin is called: geometry-handler-plugin 
async function main() {
  console.log("running query");
  const results = await db
    .selectFrom("test")
    .select(["test.name", "test.geom"])
    .execute();

  console.log("results", JSON.stringify(results, null, 2));

  // test insert
  const insertResult = await db
    .insertInto("test")
    .values({
      name: "test",
      geom: toGeom({
        type: "Polygon",
        coordinates: [
          [
            [0, 0],
            [0, 1],
            [1, 1],
            [1, 0],
            [0, 0],
          ],
        ],
      }),
    })
    .execute();
  console.log("insertResult", insertResult);
}

main();
async function main() {
  console.log("running query");
  const results = await db
    .selectFrom("test")
    .select(["test.name", "test.geom"])
    .execute();

  console.log("results", JSON.stringify(results, null, 2));

  // test insert
  const insertResult = await db
    .insertInto("test")
    .values({
      name: "test",
      geom: toGeom({
        type: "Polygon",
        coordinates: [
          [
            [0, 0],
            [0, 1],
            [1, 1],
            [1, 0],
            [0, 0],
          ],
        ],
      }),
    })
    .execute();
  console.log("insertResult", insertResult);
}

main();
GitHub
GitHub - dylel/kysely-mssql-geometry
Contribute to dylel/kysely-mssql-geometry development by creating an account on GitHub.
2 Replies
koskimas
koskimas•2y ago
Most orms/query builders allow you to provide a mapValueToDb & mapValueFromDb based on a column type. However, kyseley doesn't really seem to have that.
Kysely is not an ORM. The mapping problem is much much easier in ORMs that only allow a small amount of operations that they have fully abstracted. They are in full control of their whole API easily. Kysely on the other hand allows you to build any SQL query, no matter how complex. Figuring out the type of the values in arbitrary queries is a really hard problem. It'd be doable if we spent a huge amount of time on it, but it would still break any time you use raw sql in a query. Kysely leaves the mapping to the db driver as they usually provide a way to do it (since they usually have the needed metadata returned by the DB in their hands). The mssql driver probably would've offered a way to do the transformation easily.
Dylel
DylelOP•2y ago
Yeah i realise that kyseley isn't an orm. I was basically just trying to figure out where this logic should actually live, as i just want to use kyseley without having to invoc functions all the time, more of a set and forget. Thanks for the clarification that this should primarily be a driver concern, unfortunately igal couldn't use node-mssql, and used tedious + tarn for pooling and I don't think tedious has way to hook into the conversion but i'll look into that some more

Did you find this page helpful?