Drizzle Team•2y ago

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),
        };
    },
});

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)"

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 }

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

46 Replies

Andrii Sherman•2y ago

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

volksOP•2y ago

Will try it out now

Vicente•2y ago

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

Andrii Sherman•2y ago

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

Vicente•2y ago

Oh yes, it does!

volksOP•2y ago

@Vicente It works for you? Do you have a monorepo?

Vicente•2y ago

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.

volksOP•2y ago

@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?

Andrii Sherman•2y ago

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

Dan•2y ago

it should let me double check

Vicente•2y ago

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.

Andrii Sherman•2y ago

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

Dan•2y ago

so could you explain what your current issue is? is the fromDriver function not working for your custom type?

Vicente•2y ago

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),
        };
    },

 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

  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.

Dan•2y ago

what is that second value that you are logging?

Vicente•2y ago

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();

    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();

Dan•2y ago

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 }

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)

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

where table.column is your custom type column

volksOP•2y ago

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

Dan•2y ago

this is the expected type types have no effect in runtime

volksOP•2y ago

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

Dan•2y ago

same thing for returning

volksOP•2y ago

Thanks everyone

Dan•2y ago

@Vicente I guess your problem is different though?

Vicente•2y ago

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()

Dan•2y ago

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

Vicente•2y ago

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

volksOP•2y ago

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

Vicente•2y ago

I can try that out yes

Dan•2y ago

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

volksOP•2y ago

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

Dan•2y ago

I think adding SQL wrapping for custom types might be a good idea Yes, I'll create an issue

Vicente•2y ago

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.

Dan•2y ago

https://github.com/drizzle-team/drizzle-orm/issues/554

GitHub

Allow wrapping the column in custom SQL for custom types · Issue #5...

For example, to always select fn(column) instead of column

volksOP•2y ago

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);
};

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?

Dan•2y ago

wrap it in sql.raw() the part that you don't need escaped as a param

volksOP•2y ago

@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)`),

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

Andrii Sherman•2y ago

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

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

volksOP•2y ago

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

Andrii Sherman•2y ago

If you use .raw it won’t raw is for the cases you won’t translate specific value to parametrized

volksOP•2y ago

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

Dan•2y ago

getTableConfig(driverShifts.id.table).name using ._.tableName is way better though I think I'll make it work in the future

volksOP•2y ago

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

Dhan•15mo ago

Hi folks, the above info was really useful, though it was quite difficult for me to navigate. Heres a complete example in case someone else will find it useful.

import { sql, type DriverValueMapper, type SQL } from 'drizzle-orm';

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

export const pointType = customType<{ data: Point; driverData: string }>({
  dataType() {
    return 'geometry(Point,4326)';
  },
  toDriver(value: Point): string {
    return `SRID=4326;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
         Currently returning() is not supported and select must use a 
         custom select like so: db.select({ geo: selectPoint('geo', place.geo) })`,
        value,
      );
    }

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

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

// Schema
export const place = pgTable('place', {
  id: uuid('id')
  // drizzle-kit support for pointType is not 100% yet - edit the generated sql file like so:
  // "geometry(Point,4326)" -> geometry(Point,4326)
  geo: pointType('geo').notNull(),
});

// Usage
const selectedPlace = await db.select({ geo: selectPoint('geo', place.geo) }).from(place);

import { sql, type DriverValueMapper, type SQL } from 'drizzle-orm';

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

export const pointType = customType<{ data: Point; driverData: string }>({
  dataType() {
    return 'geometry(Point,4326)';
  },
  toDriver(value: Point): string {
    return `SRID=4326;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
         Currently returning() is not supported and select must use a 
         custom select like so: db.select({ geo: selectPoint('geo', place.geo) })`,
        value,
      );
    }

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

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

// Schema
export const place = pgTable('place', {
  id: uuid('id')
  // drizzle-kit support for pointType is not 100% yet - edit the generated sql file like so:
  // "geometry(Point,4326)" -> geometry(Point,4326)
  geo: pointType('geo').notNull(),
});

// Usage
const selectedPlace = await db.select({ geo: selectPoint('geo', place.geo) }).from(place);

I added SQL<Point> as the selectPoint return type, else selectedPlace would not be typed correctly. I hope this looks good, please let me know if I missed something.

iolyd•14mo ago

Since this thread helped me, I might as well share another version for anyone else taking a hit at this. Here's how I'm trying to work it out:

/**
 * Implements postgis point geometry type.
 */
export const point = <C extends { srid?: SRID; z?: boolean; m?: boolean }, N extends string>(
  name: N,
  config?: C
) => {
  const extraDimensions = `${config?.z ? 'Z' : ''}${config?.m ? 'M' : ''}`;
  return customType<{
    data: { type: typeof GEOMETRY_TYPES.Point; coordinates: Coordinate<C> };
    driverData: string;
    config: C;
  }>({
    dataType(config) {
      return `extensions.geometry(Point${extraDimensions},${config?.srid ?? SRIDS.WGS84})`;
    },
    toDriver(value) {
      const zd = config?.z ? `,${config.z}` : '';
      const md = config?.m ? `,${config.m}` : '';
      return `Point${extraDimensions}(${value.coordinates[0]},${value.coordinates[1]}${zd}${md})`;
    },
    fromDriver(value) {
      const matches = value.match(
        /POINT(?<z>Z?)(?<m>M?)\((?<coordinateString>(\d+(?:\.\d*)?,? *?)*)\)/
      );
      if (!matches?.groups) {
        throw new Error(`Point geometry value (${value}) does not match the expected pattern.`);
      }
      const { z, m, coordinateString } = matches.groups;
      if ((config?.z && !z) || (config?.m && !m)) {
        throw new Error(
          `Missing dimension(s) expected according to Point column config. Value has ${JSON.stringify(
            {
              z: !!config?.z,
              m: !!config?.m,
            }
          )} but column is supposed to be typed ${JSON.stringify({ z, m })}.`
        );
      }
      const coordinates = coordinateString
        .split(',')
        .map((d) => parseFloat(d.trim())) as Coordinate<C>;
      return { type: GEOMETRY_TYPES.Point, coordinates };
    },
  })(name, config);
};

/**
 * Implements postgis point geometry type.
 */
export const point = <C extends { srid?: SRID; z?: boolean; m?: boolean }, N extends string>(
  name: N,
  config?: C
) => {
  const extraDimensions = `${config?.z ? 'Z' : ''}${config?.m ? 'M' : ''}`;
  return customType<{
    data: { type: typeof GEOMETRY_TYPES.Point; coordinates: Coordinate<C> };
    driverData: string;
    config: C;
  }>({
    dataType(config) {
      return `extensions.geometry(Point${extraDimensions},${config?.srid ?? SRIDS.WGS84})`;
    },
    toDriver(value) {
      const zd = config?.z ? `,${config.z}` : '';
      const md = config?.m ? `,${config.m}` : '';
      return `Point${extraDimensions}(${value.coordinates[0]},${value.coordinates[1]}${zd}${md})`;
    },
    fromDriver(value) {
      const matches = value.match(
        /POINT(?<z>Z?)(?<m>M?)\((?<coordinateString>(\d+(?:\.\d*)?,? *?)*)\)/
      );
      if (!matches?.groups) {
        throw new Error(`Point geometry value (${value}) does not match the expected pattern.`);
      }
      const { z, m, coordinateString } = matches.groups;
      if ((config?.z && !z) || (config?.m && !m)) {
        throw new Error(
          `Missing dimension(s) expected according to Point column config. Value has ${JSON.stringify(
            {
              z: !!config?.z,
              m: !!config?.m,
            }
          )} but column is supposed to be typed ${JSON.stringify({ z, m })}.`
        );
      }
      const coordinates = coordinateString
        .split(',')
        .map((d) => parseFloat(d.trim())) as Coordinate<C>;
      return { type: GEOMETRY_TYPES.Point, coordinates };
    },
  })(name, config);
};

Where:

// constants.ts
/**
 * Common coordinate projection systems and their Spatial Reference System ID.
 */
export const SRIDS = {
  /**
   * Lat/Lon globe-based coordinate system. Uses degrees to represent spheroid position.
   */
  WGS84: 4326,
  /**
   * Lat/Lon flat-map coordinates in meters. Generally the default system used for web apps.
   */
  WebMercator: 3857,
} as const;

export type SRID = ValueOf<typeof SRIDS> | `${ValueOf<typeof SRIDS>}`;

export const GEOMETRY_TYPES = {
  Point: 'Point',
  LineString: 'LineString',
  Polygon: 'Polygon',
  MultiPoint: 'MultiPoint',
  MultiLineString: 'MultiLineString',
  MultiPolygon: 'MultiPolygon',
};

export type GeometryType = ValueOf<typeof GEOMETRY_TYPES>;

// constants.ts
/**
 * Common coordinate projection systems and their Spatial Reference System ID.
 */
export const SRIDS = {
  /**
   * Lat/Lon globe-based coordinate system. Uses degrees to represent spheroid position.
   */
  WGS84: 4326,
  /**
   * Lat/Lon flat-map coordinates in meters. Generally the default system used for web apps.
   */
  WebMercator: 3857,
} as const;

export type SRID = ValueOf<typeof SRIDS> | `${ValueOf<typeof SRIDS>}`;

export const GEOMETRY_TYPES = {
  Point: 'Point',
  LineString: 'LineString',
  Polygon: 'Polygon',
  MultiPoint: 'MultiPoint',
  MultiLineString: 'MultiLineString',
  MultiPolygon: 'MultiPolygon',
};

export type GeometryType = ValueOf<typeof GEOMETRY_TYPES>;

ItsWendell•14mo ago

https://discord.com/channels/1043890932593987624/1074717449997537420/1165366348759715961 I've implemeted two custom similar, experimental (but working) geometry columns that support GeoJSON + PostGIS (read only for now), and which has an option to select which type you want. 1. Manually parse PostGIS hex / wkx coming from / to the database driver - Experimental gist: https://gist.github.com/ItsWendell/38ebe96b34d00d9138ce23cc363d7009 2. Wrap the customType and SQL inject the public".ST_AsGeoJSON("${dbName}") as "${dbName} as a columnName. (This hardcodes the public schema in here though so this wouldn't work if you need this work on any other schemas too, but could be an config option in the column) - Experimental gist: https://gist.github.com/ItsWendell/65a107c0f8de00fdd6e4f3935789ce5a @iolyd I don't undestand how you expect the driver return text, since the default that it returns is hex, right?

TKR👑•10mo ago

Please help! This is such a pain in the ass and I need to launch! coordinateType.ts

import { sql } from "drizzle-orm";
import { customType } from "drizzle-orm/pg-core";

export interface Point {
  latitude: number;
  longitude: number;
}

export const point = customType<{
  data: Point;
  driverData: string;
  config: {
    srId: number;
  };
}>({
  toDriver({ latitude, longitude }) {
    return sql`ST_GeogFromText('POINT(${longitude} ${latitude})')`;
  },

  dataType(config) {
    return config?.srId
      ? `geography(Point, ${config?.srId})`
      : "geography(Point)";
  },
});

import { sql } from "drizzle-orm";
import { customType } from "drizzle-orm/pg-core";

export interface Point {
  latitude: number;
  longitude: number;
}

export const point = customType<{
  data: Point;
  driverData: string;
  config: {
    srId: number;
  };
}>({
  toDriver({ latitude, longitude }) {
    return sql`ST_GeogFromText('POINT(${longitude} ${latitude})')`;
  },

  dataType(config) {
    return config?.srId
      ? `geography(Point, ${config?.srId})`
      : "geography(Point)";
  },
});

then I use this in my schema.ts as follows:

export const myTable = pgTable("my_table", {
  //bunch of other columns
  coordinates: point("coordinates").notNull(),
  //more columns
})

export const myTable = pgTable("my_table", {
  //bunch of other columns
  coordinates: point("coordinates").notNull(),
  //more columns
})

and trying to make a simple request in my node application:

const newItem: dbListing = JSON.parse(event.body);
newItem.coordinates = { latitude: newItem.coordinates[1], longitude: newItem.coordinates[0] };
body = await db
          .insert(myTable)
          .values(newItem)
          .returning();

const newItem: dbListing = JSON.parse(event.body);
newItem.coordinates = { latitude: newItem.coordinates[1], longitude: newItem.coordinates[0] };
body = await db
          .insert(myTable)
          .values(newItem)
          .returning();

What am I missing here? I keep getting the error: PostgresError: could not determine data type of parameter $6\ (parameter 6 is the coordinates in my schema, but I have redacted my other columns to keep my example simple above) I am using PostGIS on a PostgreSQL engine Aurora db. I simply ran the following command to support this newly needed column:

ALTER TABLE my_table
ADD coordinates GEOGRAPHY(Point);

ALTER TABLE my_table
ADD coordinates GEOGRAPHY(Point);

any ideas on this? @Dan Kochetov ? For future readers. I solved by using sql.raw() as I think the regular sql template was causing trouble with how it passes values around (as part of injection protection). I am safe architecturally. Here is the final change I made to my toDriver definition if you need it. Above code stayed the same.

toDriver({ latitude, longitude }) {
    return sql.raw(`ST_GeographyFromText('POINT(${longitude} ${latitude})')`);
}

toDriver({ latitude, longitude }) {
    return sql.raw(`ST_GeographyFromText('POINT(${longitude} ${latitude})')`);
}

Gaming

Programming

Custom Type interpreted as String