Custom Type interpreted as String

Hello everyone, I am having some issues with getting Drizzle to work with PostGIS, I am trying to have support for PostGIS's Point geometry. This is what I have setup

export type Point = {
    longitude: number;
    latitude: number;
};

export const pointDB = customType<
    {
        data: Point;
        driverData: string;
    }
>({
    dataType() {
        return 'GEOMETRY(POINT, 4326)';
    },
    toDriver(value: Point): string {
        return `SRID=4326;POINT(${value.longitude} ${value.latitude})`;
    },
    fromDriver(value: string): Point {
        const matches = value.match(/POINT\((?<longitude>[\d.-]+) (?<latitude>[\d.-]+)\)/);
        const { longitude, latitude } = matches.groups;

        return {
            longitude: parseFloat(longitude),
            latitude: parseFloat(latitude),
        };
    },
});

But the data returned stays a string

carriers: Array(1)0: 
id: "db34d18d-b4d0-42e2-8121-29d47ad18327"
location: "POINT(44.769368 20.4631)"

This is my query

const carriers = await db.select({
    id: activeCarriers.employeeId,
    location: sql<Point>`st_astext(active_carriers.location) as location`,
    })
    .from(activeCarriers)
return { carriers }

This is happening across packages in monorepo, not sure if that could cause the type inference to fail

at first sight you did everything right

also you should install only 1 drizzle-orm instance instead of multiple

to make it works properly

we have GH issue to handle multiple drizzle-orm instances as one in monorepo

but it's still in backlog

can you try it on empty project with single drizzle-orm instance, just to check if the reason with multiple instances

Will try it out now

Lucky to have found this thread! I tried to build a point type as well to support inserts - haven't tried yours out yet, but I wonder if this set up would support inserts as well.

I know realize I posted my thrad in the wrong channel, but there's a bit more context here: https://discord.com/channels/1043890932593987624/1056966312997429278/1104042792918990928

it should work for inserts and long as @Volks specified toDriver function implementation that is responsible for insert mappings

fromDriver responsible for select mappings

Oh yes, it does!

@Vicente It works for you?

Do you have a monorepo?

Do you mean whether my project uses a tool like lerna to manage a monorepo? If that is case, it doesn't.

so changing my toDriver to the one you showed us above, makes my insert successful. However, I'm working now on the returning() and select() scenarios - I'd like to parse the geometry to text at the type level instead of in my queries.

I'm still getting acquainted with the drizzle internals. For the returning() scenario, I still don't know where to go to intercept the query builder. I'd like to add that st_astext so the newly created records contain the parsed location instead of the binary piece representing the point. Not sure whether the current custom types API supports that either.

@Andrew Sherman Can you elaborate on the 1 drizzle-orm instance? I export the drizzle instance and all of the drizzle types/ functions from my @company/db package and use that. Is that also not possible?

Maybe it won't work for returning. I guess I'll need @Dan Kochetov for this case

it should

let me double check

oh woah, that was quick. Ty guys for chiming in. I'm struggling to see where to go. No rush, I'm still debugging/tracing things to understand how everything works first.

You just need to be sure that drizzle-orm is installed once and reused in other packages

so could you explain what your current issue is?

is the fromDriver function not working for your custom type?

Exactly. In this case:

 fromDriver(value: string): Point {
        const matches = value.match(/POINT\((?<longitude>[\d.-]+) (?<latitude>[\d.-]+)\)/);
        const { longitude, latitude } = matches.groups;

        return {
            longitude: parseFloat(longitude),
            latitude: parseFloat(latitude),
        };
    },

value is an actual binary blob representing the geometry - it is missing the set_astext(location) to convert it to the text representation

  console.log
    Query: insert into "locations" ("lonlat", "created_at", "updated_at") values ($1, $2, $3) returning "id", "lonlat", "created_at", "updated_at" -- params: ["POINT(-5.712635 36.054591)", "2023-05-06T12:27:51.203Z", "2023-05-06T12:27:51.203Z"]

      at r.write (node_modules/src/logger.ts:19:3)

  console.log
    010100000099F04BFDBCD916C0F6D37FD6FC064240

From my test script when outputting to stdout the contents of value.

what is that second value that you are logging?

Oh, yes - so that output happens when returning from an insert like the one below. I'm outputting the value of lonlatas it comes from pg:

    const station = {
      position: {
        latitude: 36.054591,
        longitude: -5.712635,
      },
      createdAt: new Date(),
      updatedAt: new Date(),
    };
    const stations = [station];

    const newlyCreated = await db
        .insert(locations)
        .values(stations)
        .returning();
    

I think I know what the problem is
If this is the code you are running:

const carriers = await db.select({
    id: activeCarriers.employeeId,
    location: sql<Point>`st_astext(active_carriers.location) as location`,
    })
    .from(activeCarriers)
return { carriers }

Then the location field won't be mapped according to your custom type logic, because Drizzle doesn't know that it's related to your custom type. To run the mapping, add the .mapWith(<column>) to the column.

so it'll look like this

sql<Point>`...`.mapWith(table.column)

where table.column is your custom type column

Does the sql<Type> not tell it what to map with?

this is the expected type
types have no effect in runtime

Yeah makes sense, will try it out a bit later and report back

same thing for returning

Thanks everyone

@Vicente I guess your problem is different though?

I think so. If I understand where to use the sql<Point>....mapWith(table.column) I'm not sure how that gets picked up by the returning()

This is only for cases when you're selecting custom SQL instead of just a column

Ideally I wouldn't want to perform the st_text parsing when querying things - I'd like that to happen transparently for these types

Can you do the sql trick in returning as well?
location: sql<Point>st_astext(active_carriers.location) as location,

```

I can try that out yes

Yes, it might work like this, but then you'll have to specify the expected type manually every time, which is a lot of boilerplate IMO

And it won't work as nice with joins, where the columns might become nullable automatically if they are joined

Can we create an issue to track this? Might be useful?

I think adding SQL wrapping for custom types might be a good idea

Yes, I'll create an issue

That's great news! It may be a too big for a first issue, but it'd be happy to work on it with some guidance.

mapWith works for me as well! Thanks, it's just a bit verbose. I would like to write a helper for this but I am unable to work with the string templating , as when I try to template with ${} it is translated as $1 and the placeholder $ arguments are not allowed in selects

export const selectPoint = (column: string, decoder: DriverValueMapper<any, any>) => {
  return sql<Point>`st_astext(${column}) as ${column}`.mapWith(decoder);
};

backend-1 | [18:26:33 UTC] INFO: Query: select "employee_id", st_astext($1) as $2 from "active_carriers" -- params: ["location", "location"]

Any ideas?

wrap it in sql.raw()

the part that you don't need escaped as a param

@Dan Kochetov How can I type the sql.raw to properly be a number and not SQL<Unknown>?

task_count: sql.raw(`count(${carrierTask.id}) filter (where ${carrierTask.isCurrent} = false)`),

sql<Point>`st_astext(${sql.raw(column)}) as ${column}`.mapWith(decoder)

I guess something like this

just choose a variable you don't want to translate to $1

Won't the sql make the insides of the ${} parametized? Eg it will translate to something like st_astext($1) as $2?

If you use .raw it won’t

raw is for the cases you won’t translate specific value to parametrized

I now understand the usage of raw... I was misusing it, thanks for the info

Any way to get fully qualified names to use with .raw eg

console.log(driverShifts.id._.tableName + " " + driverShifts.id._.name)<- something like this, so I don't have to hardcode the string table_name.column_name

The above example gives me error that I am accessing undefined field name

TypeError: Cannot read properties of undefined (reading 'tableName')

I am looking into writing utility function for this but this might be a common thing that I am missing

getTableConfig(driverShifts.id.table).name

using ._.tableName is way better though
I think I'll make it work in the future

It would be a great DX to be able to quickly get the fully qualified selector without having to hardcode it and lose on renames and other features