Drizzle Team•15mo ago

Is it good to use UUID as primary key ?

Hi, i want to define manually an id for my entry to be able to create at tame time a folder with same id. So using uuid can be a problem ? or maybe i have to get a normal id used for everything, and a uuid used to identify my entry in folders (for images) and for client supports ?

  id_store: int("id_store").primaryKey().autoincrement(),
  uuid: uuid("uuid").notNull(),
  id_currency: int("id_currency").references(() => currencies.id_currency),
  name: varchar("name", { length: 256 }),

  id_store: int("id_store").primaryKey().autoincrement(),
  uuid: uuid("uuid").notNull(),
  id_currency: int("id_currency").references(() => currencies.id_currency),
  name: varchar("name", { length: 256 }),

24 Replies

Angelelz•15mo ago

That's a tough question, there's blog posts written about it. It will ultimately be up the systems architect to decide if the implications are worth it. UUID v7 seem to be better but ULID seems even better still.

Luxaritas•15mo ago

From what I’m reading the main drawbacks are size (including size in indexes) and they won’t perform well in a clustered index. There may also be some examples of some performance limitations but I’m unsure how much of an impact that winds up being and it may well depend on context You do lose an automatic ordering mechanism, so there’s that Might also be interested in looking at CUID

HugoOP•15mo ago

yeah that's what i will still keep an autoincrement id The fact is that i would like a short (10 to 15 chars) id that users can use to comunicate with support and use it as folder label

Luxaritas•15mo ago

Look at CUID

HugoOP•15mo ago

because i donwant user have id like "14" or " 3"

Luxaritas•15mo ago

Or even NanoID UUIDs are long

HugoOP•15mo ago

NanoID is 25 Digit to much

Luxaritas•15mo ago

Oh is it?

HugoOP•15mo ago

and have special chars

Luxaritas•15mo ago

Then CUID might fit the bill

HugoOP•15mo ago

oh no no special chars but 21 symbols Short IDs. It uses a larger alphabet than UUID (A-Za-z0-9_-). So ID size was reduced from 36 to 21 symbols.

HugoOP•15mo ago

https://github.com/paralleldrive/cuid2 may do the job verry whel

GitHub

GitHub - paralleldrive/cuid2: Next generation guids. Secure, collis...

Next generation guids. Secure, collision-resistant ids optimized for horizontal scaling and performance. - GitHub - paralleldrive/cuid2: Next generation guids. Secure, collision-resistant ids optim...

HugoOP•15mo ago

can configure the length

Luxaritas•15mo ago

I’m not sure if you’re gaining much by using an autoincrement column Any perf issues feels like something that won’t be an issue in most situations and you’d actually need to profile Though admittedly this is a bit out of my wheelhouse

HugoOP•15mo ago

if i don't have autoincrement i cant sort my entries by id so? only with creation date

Luxaritas•15mo ago

Right, that’s true

HugoOP•15mo ago

so now when i create my store, i want to assign a user to it in an another table My table have this colums : id_store, id_customer, status. the id_store is an autoincrement so i can't get the id before the store is fully created. How can i get the id of the first insert ? Maybe i can do :

    const createStore = async (store: NewStore) => {
      await db.transaction(async (tx) => {
        const theStore = await tx.insert(stores).values(store);
        await tx.insert(stores_team).values({id_store:[theStore.id_store]})
      })
    };

    const createStore = async (store: NewStore) => {
      await db.transaction(async (tx) => {
        const theStore = await tx.insert(stores).values(store);
        await tx.insert(stores_team).values({id_store:[theStore.id_store]})
      })
    };

HugoOP•15mo ago

but it doesn't seems to work

Luxaritas•15mo ago

What dialect are you using? Oh, MySQL I see it in the tag The result of the insert provides a value that comes from mysql2, and I believe res[0].insertId should include the ID of the last insert

kheruc•15mo ago

I recently switched from UUIDs to Nanoids for my project's refactor. I never liked the length of UUIDs. It's possible to set a custom size for Nanoids, I chose 12. This article from Planetscale was helpful in my decision: https://planetscale.com/blog/why-we-chose-nanoids-for-planetscales-api

Why we chose NanoIDs for PlanetScale’s API | PlanetScale

Learn why PlanetScale used NanoID to generate obscure and URL friendly identifiers.

DiamondDragon•15mo ago

Yeah I am using uuidv7 for pk and I have a publicId slug with 13 char nanoid. If I ever run into performance issues then it means I should be making money to hire someone smarter to figure out a better path lol

HugoOP•15mo ago

Yeah i saw NanoId, i switched to CUID, i also can choose the length I will try, i'm not shure it can works because it'is ina transaction instance so i think the the insert will be executed at the verry end so res[0] may be empty ?

h•15mo ago

Hi Im using nanoid as my primary key (which is varchar null in supabase by default) thats how it looks after drizzle-kit introspect

export const todos = pgTable("todos", {
    id: varchar("id").primaryKey().notNull(),
    title: varchar("title", { length: 256 }),
    description: varchar("description", { length: 256 }),
    isDone: boolean("isDone").default(false),
});

export const todos = pgTable("todos", {
    id: varchar("id").primaryKey().notNull(),
    title: varchar("title", { length: 256 }),
    description: varchar("description", { length: 256 }),
    isDone: boolean("isDone").default(false),
});

and in code I populate it

export const addTodo = async (db: Context['db'], input: InsertTodoInput) => {
  const { title, description, isDone } = input

  const id = generatePublicId()

  return db
    .insert(todos)
    .values({ id, title, description, isDone })
    .returning({ insertedId: todos.id })
}

export const addTodo = async (db: Context['db'], input: InsertTodoInput) => {
  const { title, description, isDone } = input

  const id = generatePublicId()

  return db
    .insert(todos)
    .values({ id, title, description, isDone })
    .returning({ insertedId: todos.id })
}

import { customAlphabet } from 'nanoid'

const alphabet =
  '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
const length = 12

const nanoid = customAlphabet(alphabet, length)

export function generatePublicId() {
  return nanoid()
}

import { customAlphabet } from 'nanoid'

const alphabet =
  '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
const length = 12

const nanoid = customAlphabet(alphabet, length)

export function generatePublicId() {
  return nanoid()
}

pandareaper•15mo ago

I'm also using V7 UUIDs as primary keys, which mimic the monotonically increasing behaviour of auto incrementing IDs. I use this library https://github.com/jetpack-io/typeid-js to Base32 encode our UUIDs improves the usability of IDs (shorter, URL friendly, double clickable) I would be weary of using nanoids as a primary key, they would suffer the same problems that V4 UUIDs have. They are suitable in the context of planetscale because it is a sharded database and having a good random key means a nice distribution across your shards. This is not the case for most database

GitHub

GitHub - jetpack-io/typeid-js: TypeScript implementation of TypeIDs...

TypeScript implementation of TypeIDs: type-safe, K-sortable, and globally unique identifiers inspired by Stripe IDs - GitHub - jetpack-io/typeid-js: TypeScript implementation of TypeIDs: type-safe,...

Gaming

Programming

Is it good to use UUID as primary key ?