Drizzle Team•15mo ago

is there a way to wrap a column select with ST_AsText or any other sql statement?

I've created a custom column type but the column return by default a binary data, instead i want to wrap the select automatically with ST_AsText to human readable text.

7 Replies

Angelelz•15mo ago

Yes, you can use the magic sql operator: https://orm.drizzle.team/docs/sql#sql-select

Magical sql operator 🪄 - DrizzleORM

Drizzle ORM | %s

Matan NahmaniOP•15mo ago

@Angelelz you mind giving me a hint over here? this is my custom type: i'm trying the default select of this field to be ST_AsText(<colum-type>) as PointText

export type Point = {
  lat: number;
  lng: number;
};

export const point = customType<{ data: Point; driverData: string }>({
  dataType() {
    return "POINT";
  },
  toDriver(value: Point) {
    return sql`POINT(${value.lng}, ${value.lat})`;
  },
  fromDriver(value: string) {
    const matches = value.match(/POINT\((?<lng>[\d.-]+) (?<lat>[\d.-]+)\)/);
    const { lat, lng } = matches?.groups ?? {};

    if (!matches) {
      console.warn(
        `Could not parse point value in function pointType.fromDriver`,
        value,
      );
    }

    return { lat: parseFloat(String(lat)), lng: parseFloat(String(lng)) };
  },
});

export type Point = {
  lat: number;
  lng: number;
};

export const point = customType<{ data: Point; driverData: string }>({
  dataType() {
    return "POINT";
  },
  toDriver(value: Point) {
    return sql`POINT(${value.lng}, ${value.lat})`;
  },
  fromDriver(value: string) {
    const matches = value.match(/POINT\((?<lng>[\d.-]+) (?<lat>[\d.-]+)\)/);
    const { lat, lng } = matches?.groups ?? {};

    if (!matches) {
      console.warn(
        `Could not parse point value in function pointType.fromDriver`,
        value,
      );
    }

    return { lat: parseFloat(String(lat)), lng: parseFloat(String(lng)) };
  },
});

Angelelz•15mo ago

I think this is what you're looking for:

...
  toDriver(value: Point) {
    return sql`ST_AsText(POINT(${value.lng}, ${value.lat}))`;
  },
...

...
  toDriver(value: Point) {
    return sql`ST_AsText(POINT(${value.lng}, ${value.lat}))`;
  },
...

I think, I'm just not familiar with this data type

Matan NahmaniOP•15mo ago

@Angelelz i gave it a try but I think toDriver only happen on executions (insert,update,etc) for query it just uses the default select (select: <column-name>) here is my try:

export type Point = {
  lat: number;
  lng: number;
};

export const point = customType<{ data: Point; driverData: string }>({
  dataType() {
    return "POINT";
  },
  toDriver(value: Point) {
    // return sql`POINT(${value.lng}, ${value.lat})`;
    return sql`ST_AsText(POINT(${value.lng}, ${value.lat}))`;
  },
  fromDriver(value: string) {
    const matches = value.match(/POINT\((?<lng>[\d.-]+) (?<lat>[\d.-]+)\)/);
    const { lat, lng } = matches?.groups ?? {};

    if (!matches) {
      console.warn(
        `Could not parse point value in function pointType.fromDriver`,
        value,
      );
    }

    return { lat: parseFloat(String(lat)), lng: parseFloat(String(lng)) };
  },
});

const allRooms = async () => {
  const rooms = await db.select().from(room).execute();
  console.log(db.select().from(room).toSQL());
  console.log(rooms);
};
void allRooms();
>>
Query: select `id`, `name`, `image`, `location`, `point`, `code`, `membersLimit`, `currentMembers`, `isAiResponding`, `lastProcessedAt`, `creatorId`, `createdAt` from `room`
Could not parse point value in function pointType.fromDriver kç÷¥áRÀ?á16¨ZD@
Could not parse point value in function pointType.fromDriver Ð|KULRÀ)ÞV$[D@
Could not parse point value in function pointType.fromDriver B#åIRÀydË@ü[D@
{
  sql: "select `id`, `name`, `image`, `location`, `point`, `code`, `membersLimit`, `currentMembers`, `isAiResponding`, `lastProcessedAt`, `creatorId`, `createdAt` from `room`",
  params: []
}
[
  {
    id: "8DTNT_tuFtNhbTIze4f7f",
    name: "Demo Room",
    image: null,
    location: "Some location",
    point: {
      lat: NaN,
      lng: NaN
    },
   ...
]

export type Point = {
  lat: number;
  lng: number;
};

export const point = customType<{ data: Point; driverData: string }>({
  dataType() {
    return "POINT";
  },
  toDriver(value: Point) {
    // return sql`POINT(${value.lng}, ${value.lat})`;
    return sql`ST_AsText(POINT(${value.lng}, ${value.lat}))`;
  },
  fromDriver(value: string) {
    const matches = value.match(/POINT\((?<lng>[\d.-]+) (?<lat>[\d.-]+)\)/);
    const { lat, lng } = matches?.groups ?? {};

    if (!matches) {
      console.warn(
        `Could not parse point value in function pointType.fromDriver`,
        value,
      );
    }

    return { lat: parseFloat(String(lat)), lng: parseFloat(String(lng)) };
  },
});

const allRooms = async () => {
  const rooms = await db.select().from(room).execute();
  console.log(db.select().from(room).toSQL());
  console.log(rooms);
};
void allRooms();
>>
Query: select `id`, `name`, `image`, `location`, `point`, `code`, `membersLimit`, `currentMembers`, `isAiResponding`, `lastProcessedAt`, `creatorId`, `createdAt` from `room`
Could not parse point value in function pointType.fromDriver kç÷¥áRÀ?á16¨ZD@
Could not parse point value in function pointType.fromDriver Ð|KULRÀ)ÞV$[D@
Could not parse point value in function pointType.fromDriver B#åIRÀydË@ü[D@
{
  sql: "select `id`, `name`, `image`, `location`, `point`, `code`, `membersLimit`, `currentMembers`, `isAiResponding`, `lastProcessedAt`, `creatorId`, `createdAt` from `room`",
  params: []
}
[
  {
    id: "8DTNT_tuFtNhbTIze4f7f",
    name: "Demo Room",
    image: null,
    location: "Some location",
    point: {
      lat: NaN,
      lng: NaN
    },
   ...
]

Angelelz•15mo ago

You're absolutely right, my bad. There's an open issue for this feature: https://github.com/drizzle-team/drizzle-orm/issues/1083

GitHub

[FEATURE]: Default select for custom types · Issue #1083 · drizzle-...

Describe what you want When creating a custom type that doesn't have a straightforward select like a postgis geometry, for example. export type Point = { lat: number; lng: number; }; export con...

Angelelz•15mo ago

This is not implemented yet. So you're going to have to do:

db.select({point: sql`ST_AsText(...)`})

db.select({point: sql`ST_AsText(...)`})

On every select

Matan NahmaniOP•15mo ago

yes i saw it, problem you can't do it with query so it's kinda annoying

Gaming

Programming

is there a way to wrap a column select with ST_AsText or any other sql statement?