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
Andrii Sherman14mo 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
volks
volks14mo ago
Will try it out now
Vicente
Vicente14mo 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
Andrii Sherman14mo 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
Vicente14mo ago
Oh yes, it does!
volks
volks14mo ago
@Vicente It works for you? Do you have a monorepo?
Vicente
Vicente14mo 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.
volks
volks14mo 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
Andrii Sherman14mo ago
Maybe it won't work for returning. I guess I'll need @Dan Kochetov for this case
bloberenober
bloberenober14mo ago
it should let me double check
Vicente
Vicente14mo 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
Andrii Sherman14mo ago
You just need to be sure that drizzle-orm is installed once and reused in other packages
bloberenober
bloberenober14mo ago
so could you explain what your current issue is? is the fromDriver function not working for your custom type?
Vicente
Vicente14mo 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.
bloberenober
bloberenober14mo ago
what is that second value that you are logging?
Vicente
Vicente14mo 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();

bloberenober
bloberenober14mo 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
volks
volks14mo ago
Does the sql<Type> not tell it what to map with?
bloberenober
bloberenober14mo ago
this is the expected type types have no effect in runtime
volks
volks14mo ago
Yeah makes sense, will try it out a bit later and report back
bloberenober
bloberenober14mo ago
same thing for returning
volks
volks14mo ago
Thanks everyone
bloberenober
bloberenober14mo ago
@Vicente I guess your problem is different though?
Vicente
Vicente14mo 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()
bloberenober
bloberenober14mo ago
This is only for cases when you're selecting custom SQL instead of just a column
Vicente
Vicente14mo 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
volks
volks14mo ago
Can you do the sql trick in returning as well? location: sql<Point>st_astext(active_carriers.location) as location,
```
Vicente
Vicente14mo ago
I can try that out yes
bloberenober
bloberenober14mo 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
volks
volks14mo ago
Can we create an issue to track this? Might be useful?
bloberenober
bloberenober14mo ago
I think adding SQL wrapping for custom types might be a good idea Yes, I'll create an issue
Vicente
Vicente14mo 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.
volks
volks14mo 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?
bloberenober
bloberenober14mo ago
wrap it in sql.raw() the part that you don't need escaped as a param
volks
volks14mo 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
Andrii Sherman14mo 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
volks
volks14mo 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
Andrii Sherman14mo ago
If you use .raw it won’t raw is for the cases you won’t translate specific value to parametrized
volks
volks14mo 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
bloberenober
bloberenober14mo ago
getTableConfig(driverShifts.id.table).name using ._.tableName is way better though I think I'll make it work in the future
volks
volks14mo 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
Dhan9mo 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
iolyd9mo 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
ItsWendell8mo 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👑
TKR👑4mo 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})')`);
}
Want results from more Discord servers?
Add your server
More Posts