Infer date values in jsonb column as Date object

I used $types<>() to provide the type for my jsonb column but the returned value is date string not a js Date object.
type InvoiceSchema = {
invoiceNumber: string;
invoiceDate: Date;
items: {
name: string;
}[];
value?: number;
}
export const myTable = pgTable('my_table', {
invoices: jsonb("invoices").$type<InvoiceSchema[]>(),
})
type InvoiceSchema = {
invoiceNumber: string;
invoiceDate: Date;
items: {
name: string;
}[];
value?: number;
}
export const myTable = pgTable('my_table', {
invoices: jsonb("invoices").$type<InvoiceSchema[]>(),
})
Output Data
{
invoices : [
{
invoiceNumber: '3432423',
invoiceDate: '2024-07-13T00:00:00.000Z',
value: null,
items: [ [Object] ]
}
]
}
{
invoices : [
{
invoiceNumber: '3432423',
invoiceDate: '2024-07-13T00:00:00.000Z',
value: null,
items: [ [Object] ]
}
]
}
17 Replies
rphlmr ⚡
rphlmr ⚡6mo ago
Json can only have serializable data. This is why your date is transformed to a string. I would advise to also use a string for your type.
rphlmr ⚡
rphlmr ⚡6mo ago
You could automate the mapping with a zod schema and an override + a transform on the invoiceDate: https://orm.drizzle.team/docs/zod
Drizzle ORM - drizzle-zod
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
rphlmr ⚡
rphlmr ⚡6mo ago
The data you want to insert can have a date and after you parse it it becomes a string.
ak4zh
ak4zhOP6mo ago
I already have this invoice schema with zod, I alsi use superform for forms so I have a dateProxy as well in place. Will try the transform thing and report back Currently I get this error in frontend Error: Invalid ISO 8601 date string:
const proxyInvoiceDate = dateProxy(form, `invoices[${index}].invoiceDate`, { format: 'date', taint: false })

export const biltyInvoiceSchema = z.object({
invoiceNumber: z.string(),
invoiceDate: z.date(),
value: z.number(),
materials: z.array(invoiceMaterialSchema)
});
const proxyInvoiceDate = dateProxy(form, `invoices[${index}].invoiceDate`, { format: 'date', taint: false })

export const biltyInvoiceSchema = z.object({
invoiceNumber: z.string(),
invoiceDate: z.date(),
value: z.number(),
materials: z.array(invoiceMaterialSchema)
});
ak4zh
ak4zhOP6mo ago
Thanks, this seems to do the trick:
const invoiceSchema = {
invoiceDate: z.string().or( z.date() ).transform( arg => new Date( arg ) )
}
const invoiceSchema = {
invoiceDate: z.string().or( z.date() ).transform( arg => new Date( arg ) )
}
Source: https://github.com/colinhacks/zod/discussions/879#discussioncomment-2036276
GitHub
Parsing a date · colinhacks zod · Discussion #879
Very surprised that I could not find anything on this. If I have a date schema const schema = z.date() and I pass it a date string schema.parse('2022-01-12T00:00:00.000Z') I would expect to...
ak4zh
ak4zhOP6mo ago
Turns out this does not work. For now I am manually doing new Date(dateColumnFromJsonb) everytime I select the rows. Still looking for an convenient solution which I can configure in drizzle so it does the conversion automatically may be using custom column type Am I doing this right? Will this slow my queries a lot as it's using JSON.stringify and JSON.parse ?
const invoicesJsonb = <TData>(name: string) =>
customType<{ data: TData; driverData: string }>({
dataType() {
return 'jsonb';
},
toDriver(value: TData): string {
return JSON.stringify(value);
},
fromDriver(value) {
let result: any = value;
if (typeof value === "string") {
try {
result = JSON.parse(result);
result = zodInvoicesSchema.parse(result);
} catch {
return result;
}
}
return result;
}
})(name);
const invoicesJsonb = <TData>(name: string) =>
customType<{ data: TData; driverData: string }>({
dataType() {
return 'jsonb';
},
toDriver(value: TData): string {
return JSON.stringify(value);
},
fromDriver(value) {
let result: any = value;
if (typeof value === "string") {
try {
result = JSON.parse(result);
result = zodInvoicesSchema.parse(result);
} catch {
return result;
}
}
return result;
}
})(name);
rphlmr ⚡
rphlmr ⚡6mo ago
Looks good (haven’t tested) if it works. To know how much it impacts your performance, you have to benchmark before and after, but it should not be noticeable. If it is, just keep dates as string in both side and let the consumer of this request (your frontend for example) doing the mapping to a Date object. Nb: I am also using a custom jsonb with JSON parse in my apps without noticing any slowdown
ak4zh
ak4zhOP5mo ago
I checked the official implementation of drizzle jsonb column already uses the JSON.parse and JSON.stringify so I should not have any performance drop vs using official jsonb. All good, thank you so much. It seems something is wrong with this method: Ona another project I use inbuild jsonb column and the data is stored as actual json/array. But this the above method I just realised the whole json is stored as a JSON.stringified version in the database.
ak4zh
ak4zhOP5mo ago
When using the latest version of drizzle: Dbeaver shows it as string. Drizzle studio show it as json. Another project using jsonb drizzle-orm 0.29.1 DBeaver ans Drizzle both shows jsonb col as json value.
No description
No description
ak4zh
ak4zhOP5mo ago
Also the column that is showing as json by studio but string by dbeaver when I try to run son jsonb based query I am getting error:
SELECT id,
JSONB_ARRAY_LENGTH(bills.invoices)
FROM bills;
SELECT id,
JSONB_ARRAY_LENGTH(bills.invoices)
FROM bills;
SQL Error [22023]: ERROR: cannot get array length of a scalar

Error position:
SQL Error [22023]: ERROR: cannot get array length of a scalar

Error position:
Figured out it was due to different postgres package: postgres.js results in data stored as string node-postgres stores the data correctly
rphlmr ⚡
rphlmr ⚡5mo ago
hey yes I was about to mention that "my" jsonb fixes an issue for postgres.js but the butilin jsonb should be ok for other drivers I use postgres.js and with the builtin jsonb all my json is converted to string too 😦
ak4zh
ak4zhOP5mo ago
Ohhh shit... Now I switched to node-postgres instead. I thought it would be just an easy switch changing the import and creating the client. But I see the return type of queries are different between both the adapters. EDIT: This is for raw sql queries node-postgres returns .rows postgres-js returns rows directly. So I guess I have to change all my queries as well. Seems like a lot of work. Wish this jsonb issue was documented in bold letters in the docs haha... Just curious why do you use postgres-js adapter in your project if it has this issue?
rphlmr ⚡
rphlmr ⚡5mo ago
With node-postgres, builtin jsonb should work as expected, is it?
ak4zh
ak4zhOP5mo ago
Yes simply using inbuilt jsonb with node-postgres everything works fine.
rphlmr ⚡
rphlmr ⚡5mo ago
I prefer postgres-js because it is 100% js. node-postgres use some C code and could not work in some platforms
ak4zh
ak4zhOP5mo ago
Ahhh gotcha...
rphlmr ⚡
rphlmr ⚡5mo ago
I wish a system that could update the doc when an issue is confirmed by the team. Something like a banner with "there is some pending issues, check them here"
Want results from more Discord servers?
Add your server