DT
Drizzle Team•9mo ago
nr7751

Can someone direct me to a UUID implementation as PK with Drizzle ORM?

Hello eveyone. I'm trying to create a user table in a Vercel Postgres db, but keep running into errors. Could anyone kindly provide guidance,? An example would be perfect. I'm looking for a schema of sorts. Any help will be greatly appreciated!
15 Replies
Blixkreeg
Blixkreeg•9mo ago
Try something like this: 
import { pgTable, uuid } from "drizzle-orm/pg-core"

export const user = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
})
import { pgTable, uuid } from "drizzle-orm/pg-core"

export const user = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
})
nr7751
nr7751OP•9mo ago
@Blixkreeg , thanks. It's appreciated. Bbut now I'm having basic configuration problems. I have psoted a new question to see if I can get any help. @Blixkreeg , your schema update gets me this error: error: column "id" cannot be cast automatically to type uuid (...) length: 169, severity: 'ERROR', code: '42804', detail: undefined, hint: 'You might need to specify "USING id::uuid".', position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'tablecmds.c', line: '12336', routine: 'ATPrepAlterColumnType' }
Blixkreeg
Blixkreeg•9mo ago
you're using a postgres database? And have you been able to run any migrations at all?
nr7751
nr7751OP•9mo ago
@Blixkreeg , Yes, I have now been able to run migrations sucessfully. The problem seems to b restricted to UUIDs, at least for now
Blixkreeg
Blixkreeg•9mo ago
ok i see. is this error coming from running a query? Have you made sure you generated your migrations and migrated after every change?
nr7751
nr7751OP•9mo ago
IIRC, this showed up after genereate and migrate
Peter Boling
Peter Boling•9mo ago
I am having the same issue. I am able to run generate:pg and push:pg without issue using code like you gave @Blixkreeg . However, when I use the example code from the running migrations docs I get an error, same as @nr7751 did.
Drizzle ORM - Overview
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Peter Boling
Peter Boling•9mo ago
❯ tsx src/lib/db/db_migrate.server.ts

> [email protected] db:migrate /Users/pboling/src/fisecal/monolith
> tsx src/lib/db/db_migrate.server.ts

node:internal/process/esm_loader:34
      internalBinding('errors').triggerUncaughtException(
                                ^

PostgresError: column "id" cannot be cast automatically to type uuid
    at ErrorResponse (file:///Users/pboling/src/fisecal/monolith/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:790:26)
    at handle (file:///Users/pboling/src/fisecal/monolith/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:476:6)
    at Socket.data (file:///Users/pboling/src/fisecal/monolith/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:518:28)
    at Socket.emit (node:domain:488:12)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42804',
  hint: 'You might need to specify "USING id::uuid".',
  file: 'tablecmds.c',
  line: '12318',
  routine: 'ATPrepAlterColumnType'
}
❯ tsx src/lib/db/db_migrate.server.ts

> [email protected] db:migrate /Users/pboling/src/fisecal/monolith
> tsx src/lib/db/db_migrate.server.ts

node:internal/process/esm_loader:34
      internalBinding('errors').triggerUncaughtException(
                                ^

PostgresError: column "id" cannot be cast automatically to type uuid
    at ErrorResponse (file:///Users/pboling/src/fisecal/monolith/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:790:26)
    at handle (file:///Users/pboling/src/fisecal/monolith/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:476:6)
    at Socket.data (file:///Users/pboling/src/fisecal/monolith/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:518:28)
    at Socket.emit (node:domain:488:12)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42804',
  hint: 'You might need to specify "USING id::uuid".',
  file: 'tablecmds.c',
  line: '12318',
  routine: 'ATPrepAlterColumnType'
}
And this is the migrate script (src/lib/db/db_migrate.server.ts): 
import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import postgres from "postgres";
// We can't use svelte env vars here because migrations run without loading svelte or vite.
import * as dotenv from 'dotenv';
dotenv.config();
const DATABASE_NAME = process.env.DATABASE_NAME ?? 'demo-db';
const DATABASE_URL = process.env.DATABASE_URL ?? `postgres://localhost:5432/${DATABASE_NAME}`;
if (!DATABASE_URL) {
    throw new Error('db_migrate: No url for Scripting SQL');
}
// No output on presence of DATABASE_URL because this will run in production deploys

const sql = postgres(DATABASE_URL, { max: 1 })
const db = drizzle(sql);
await migrate(db, { migrationsFolder: "migrations" });
await sql.end();
import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import postgres from "postgres";
// We can't use svelte env vars here because migrations run without loading svelte or vite.
import * as dotenv from 'dotenv';
dotenv.config();
const DATABASE_NAME = process.env.DATABASE_NAME ?? 'demo-db';
const DATABASE_URL = process.env.DATABASE_URL ?? `postgres://localhost:5432/${DATABASE_NAME}`;
if (!DATABASE_URL) {
    throw new Error('db_migrate: No url for Scripting SQL');
}
// No output on presence of DATABASE_URL because this will run in production deploys

const sql = postgres(DATABASE_URL, { max: 1 })
const db = drizzle(sql);
await migrate(db, { migrationsFolder: "migrations" });
await sql.end();
nr7751
nr7751OP•9mo ago
@Peter Boling , I solved it uisng cuid2 import { createId } from "@paralleldrive/cuid2"; and in the table: export const users = pgTable( "users", { id: text("id") .$defaultFn(() => createId()) .primaryKey(), ... Hope this helps
Peter Boling
Peter Boling•9mo ago
id: text("id")
id: text("id")
I specifically am attempting to avoid using a text field as that bloats the size of the key immensely. I want it to be binary.
nr7751
nr7751OP•9mo ago
@Peter Boling , but then you can't use any form of UUID , can you?
Peter Boling
Peter Boling•9mo ago
uuid is a native binary type with the postgresql extension uuid-ossp, but it only ships with a generator for UUID v1, v3, v4, and v5 I want to use either ulid or uuidv7 uuidv7 is binary format compatible with the older uuid formats, so I can store a uuidv7 inside a uuid field from uuid-ossp The issue is that Drizzle + Lucia doesn't allow me to override the type of the session id (they only just added the ability to override the type of the user id). I did end up getting everything working, except for the session id. All my other IDs are now uuid, and they are v7 when generated by Javascript clients, and v4 when generated by the database.
nr7751
nr7751OP•9mo ago
Intersting. I will be implementing auth with Lucia as well. Any considerations I should eb aware of? WOuld you say my implementation might cause problems with Lucia?
Peter Boling
Peter Boling•9mo ago
No, if you are using text it will just work 🙂 Probably best to start there, the performance difference won't matter until you hit millions of users! @nr7751 I described my solution over on the Lucia discord: https://discord.com/channels/1004048134218981416/1219330483796574270/1219342383918092318
Startup Spells 🪄 Newsletter Guy
Startup Spells 🪄 Newsletter Guy•9mo ago
i use it like this: 
import { ulid } from "ulidx"

id: text("id").primaryKey().$defaultFn(() => ulid()),
import { ulid } from "ulidx"

id: text("id").primaryKey().$defaultFn(() => ulid()),
i also tried using sqlite extension using ulid but it wasn't working for some reason. https://github.com/asg017/sqlite-ulid/issues/9
Want results from more Discord servers?
Add your server