[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 Launchpad
Trader Launchpad12mo 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
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 ...
Want results from more Discord servers?
Add your server