SQLite - how to handle a column that contains XML data

[rhscjohn-dev]

I have an SQLite3 database and I am using better-sqlite3. I have multiple tables that have columns that contain XML.

Example. Table: CHANNEL has a Column: epg-mapping type: varchar(50). A sample value is

 <epg>
    <source>Schedules Direct</source>
    <lineup>USA-OTA-29730</lineup>
    <station>19603</station>
</epg>

I tried defining a Custom Type but it looks like module 'drizzle-orm/better-sqlite3' has no exported member 'customType'.

Using Zod, I was able to convert the XML to an object:

const channelSchema = z.object({
  oid: z.number().int(),
  name: z.string(),
  epgSource: z.string(),
  epgMapping: z.preprocess((xml) => {
    // @ts-ignore
    const parseResult = xmlParser.parse(xml)
    return parseResult.epg
  }, z.object({ source: z.string(), lineup: z.string(), station: z.number().int() })
  )
})

Here is the Drizzle statement.

 const selectResult = db.select(
      {
        oid: channel.oid,
        name: channel.name,
        epgSource: channel.epgSource,
        epgMapping: channel.epgMapping,
      }
    ).from(channel).all()
const zodResult = channelsSchema.parse(selectResult)

Is there a more Drizzle like way to do this?

Thanks

[Angelelz]

customType should not be imported from 'drizzle-orm/better-sqlite3' but from 'drizzle-orm/sqlite-core'.
I think customType is a better approach.