Theo's Typesafe Cult•13mo ago

[How To?] Generate uuids properly on cloudflare d1 sqlite...

I am running an app with Drizzle, TRPC, and Cloudflare D1 sqlite database: My initial project databasse schema looks like:

export const CarTable = sqliteTable('Car', {
  id: text('id').primaryKey(),
  make: text('make').notNull(),
  model: text('model').notNull(),
  year: integer('year').notNull(),
  color: text('color').notNull(),
  price: real('price').notNull(),
  mileage: integer('mileage').notNull(),
  fuelType: text('fuelType').notNull(),
  transmission: text('transmission').notNull(),
})

export const CarTable = sqliteTable('Car', {
  id: text('id').primaryKey(),
  make: text('make').notNull(),
  model: text('model').notNull(),
  year: integer('year').notNull(),
  color: text('color').notNull(),
  price: real('price').notNull(),
  mileage: integer('mileage').notNull(),
  fuelType: text('fuelType').notNull(),
  transmission: text('transmission').notNull(),
})

I am trying to add a uuid column to the table in addition to autoincrementing the id column and making the other columns not required. First I am trying to ad a uuid column. I have researched this in a couple places, cant seem to find what I am looking for. I want a uuid column that automatically generates a uuid when i insert a record. Seems to be possible with mysql but not sqlite? Here is the uuid column I have been able to push through drizzle to the database:

uuid: binary('uuid', { length: 16 }).default(`hex(randomblob(16))`), // Generate a random 16-byte binary string

uuid: binary('uuid', { length: 16 }).default(`hex(randomblob(16))`), // Generate a random 16-byte binary string

I have been able to generate the migration (drizzle-kit generate:sqlite --schema=./src/db/schema.ts --out=./migrations) This gets pushed to the database via wrangler with a wrangler d1 migrations apply production --local command.

1 Reply

Trader LaunchpadOP•13mo ago

I can seed the database with data, that does not include a uuid column and it generates the same UUID for each record, not what I want. The uuid itself looks correct, but is not unique. When i try and add a record via trpc/frontend form, with or without a uuid value supplied i get an error: TRPCClientError: Invalid hex string I have tried deleting the uuid field from the drizzle schema, running a migration to overwrite the table, and using the frontend form again. Records populate properly i found this, which I am trying next, but probably would have to pass these commands manually through a drizzle/wrangler migration file rather than through the schema and drizzle generate? https://www.kittell.net/code/auto-increment-auto-generate-guid/ Is anyone else using uuids with cloudflare d1 database? I see there are extension i could load for sqlite that allow for automatic uuids but not sure if i can execute those against cloud d1, even if i were able to get it working with local dev.

David Kittell

SQLite - Auto-Increment / Auto Generate GUID | David Kittell

Recently was asked if it's possible to create an auto-incrementing GUID in SQLite. Here is one approach: In Firefox there is an add-on called SQLite Ma ...

Gaming

Programming

[How To?] Generate uuids properly on cloudflare d1 sqlite...