ak4zh Comments - Answer Overflow

ak4zh

Posts Comments

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

Ahhh gotcha...

25 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

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

25 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

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?

25 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

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.

25 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

Figured out it was due to different postgres package: postgres.js results in data stored as string node-postgres stores the data correctly

25 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

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:

25 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

25 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

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.

25 replies

DTDrizzle Team

•Created by ak4zh on 7/15/2024 in #help

Column name different in drizzle magic sql vs drizzle-orm

Ohh ok, primarily because I was finding it easier to just do raw sql. Detail here: https://discord.com/channels/1043890932593987624/1056966312997429278/1262240863560667138 I use a lot of raw sql query in my other project and was not aware about this behaviour as my column names were in same case.

4 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

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.

25 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

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

25 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

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

25 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

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

25 replies

DTDrizzle Team

•Created by ak4zh on 7/13/2024 in #help

Infer date values in jsonb column as Date object

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

25 replies

DTDrizzle Team

•Created by Meexa on 12/7/2023 in #help

How to do "onConflictDoUpdate" when inserting an array of values?

I have a similar situation with composite unique index.

itemAutionIndex: uniqueIndex('item_auction_idx').on(items.itemId, items.auctionHouseId)

itemAutionIndex: uniqueIndex('item_auction_idx').on(items.itemId, items.auctionHouseId)

How can I use the index as the target? I tried the above format:

await db
  .insert(items)
  .values(
    chunk.map((item) => ({
      auctionHouseId: Number(auctionHouseId),
      itemId: item.itemId,
      // ...
    })),
  )
  .onConflictDoUpdate({
    target: [items.itemId, items.auctionHouseId],
    set: {
      yourColumnName: sql`excluded.yourColumnName`
    },
  });

await db
  .insert(items)
  .values(
    chunk.map((item) => ({
      auctionHouseId: Number(auctionHouseId),
      itemId: item.itemId,
      // ...
    })),
  )
  .onConflictDoUpdate({
    target: [items.itemId, items.auctionHouseId],
    set: {
      yourColumnName: sql`excluded.yourColumnName`
    },
  });

But this results in an error:

error: ON CONFLICT DO UPDATE command cannot affect row a second time
{
  length: 239,
  severity: 'ERROR',
  code: '21000',
  detail: undefined,
  hint: 'Ensure that no rows proposed for insertion within the same command have duplicate constrained values.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'nodeModifyTable.c',
  line: '2564',
  routine: 'ExecOnConflictUpdate'
}

error: ON CONFLICT DO UPDATE command cannot affect row a second time
{
  length: 239,
  severity: 'ERROR',
  code: '21000',
  detail: undefined,
  hint: 'Ensure that no rows proposed for insertion within the same command have duplicate constrained values.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'nodeModifyTable.c',
  line: '2564',
  routine: 'ExecOnConflictUpdate'
}