DT
Drizzle TeamMatan Nahmani

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.
Angelelz
Angelelz•207d 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 Nahmani
Matan Nahmani•207d 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
Angelelz•207d 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 Nahmani
Matan Nahmani•207d 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
Angelelz•207d 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
Angelelz•207d 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 Nahmani
Matan Nahmani•207d ago
yes i saw it, problem you can't do it with query so it's kinda annoying
Want results from more Discord servers?
Add your server
More Posts
inferring the column information from the imported schemais there anyway to get the column information from the imported schema information. When I dump the Drizzle tries to import mysql2/promise even though I want to use postgress.I got: `node_modules/drizzle-orm/mysql-core/db.d.ts:1:38 - error TS2307: Cannot find module 'mysql2Filter for articles with at least one commentI am trying to migrate to Drizzle from Prisma and I am having issue with being able to query for allHow to get a similar result of db.query but with extra aggregation and using selectI have the following drizzle query which works correctly but the results is not what I expect since Next 13, Next-Auth with Neon Postgres and Drizzle vs Prisma 5I am trying to set up Next-Auth with Neon Postgres and Drizzle, but failing miserably. I set up a rerror: column "x" is in a primary keyI'm getting the following error, when trying to push my schema to my remote db: error: column "x" ispostgres error: type "serial" does not existI want to migrate my my changes but i get this `error: type "serial" does not exist. ` This is myplanetscale cache errornot sure if the issue is from drizzle or trpc, but trying to query the db via the RSC api fails withMySql 8 - How to use onDuplicateKeyUpdate with array?as the title says, how do i use onDuplicateKeyUpdate with arrays? I can pass an array to .values() fPostgres json_aggHow can I create this equivalent using drizzle? ```sql select p.id, p.description, p.name, Performance difference between `.select()` and `.findMany()`Is there any performance difference between `.select()` and `.findMany()` when using in prepared sta"VALUES(Column)" abstraction ( quick )So, using MySql, I can use `[insert query here].onDuplicateKeyUpdate({ set: { name: sql'VALUES(name)Prepared statement returns undefinedI have the following server action: ``` export async function getUserByEmailAction(email: string) {Prepared Update statementsIs it possible to use preparad statements for updating data? I was able to use prepared statements wSchema to typeHey guys, how do you make a schema item as a static type ? for example, this thing: ```ts export cdb:push constraint is not found in the tableHi, I'm using planetscale and trying to push to my db but I get the error: errno: 3821, sql: 'ALTDoes schema declaration filepath have to be exactly as in docs?Hi there, I was just wondering: In the docs (https://orm.drizzle.team/docs/sql-schema-declaration) iSlow api request using a transactionHi I wondered if I can remove async out of the child transactions inside the transaction function, ttsc command fails when using sqliteHey, just started using the library, and I'm having issues running the `tsc` command. Here's the outDrizzle generates PG statement with parentheses on order by clauseHello! I'm trying to execute a simple query with Drizzle, but it generates an invalid SQL statement