Drizzle Team•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.

2 Replies

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 ...

Trader LaunchpadOP•13mo ago

thanks...appreciate the boot from the t4 server :/ for...trying to figure why xcode simulator was not working? in your own discord servers help channel? I even gave you the solution, you thanked me this will help new members for sure then gave me the boot. Yeah, great support and community from you over there.

Gaming

Programming

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