Drizzle Team•6mo ago

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 ⚡•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 ⚡•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 ⚡•6mo ago

The data you want to insert can have a date and after you parse it it becomes a string.

ak4zhOP•6mo 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)
});

ak4zhOP•6mo 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...

ak4zhOP•6mo 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 ⚡•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

ak4zhOP•6mo 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.

ak4zhOP•6mo 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.

ak4zhOP•6mo 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 ⚡•6mo 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 😦

ak4zhOP•6mo 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 ⚡•6mo ago

With node-postgres, builtin jsonb should work as expected, is it?

ak4zhOP•6mo ago

Yes simply using inbuilt jsonb with node-postgres everything works fine.

rphlmr ⚡•6mo ago

I prefer postgres-js because it is 100% js. node-postgres use some C code and could not work in some platforms

ak4zhOP•6mo ago

Ahhh gotcha...

rphlmr ⚡•6mo 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"

Gaming

Programming

Infer date values in jsonb column as Date object