Recommended column type for id field

Question

I am using Drizzle, Next.js 14 app router and Neon serverless. What is the best column type for id field in a table? I think a solution that is auto generating, random, yet not very large (like UUID) is best.

Most tutorials show a type of either text or serial. But text does not automatically set the Id, so I can't use insert without providing an id field. Serial is auto incrementing, but I read that is bad for security (id's being predictable). UUID is another option but they take up a lot of space ans have other downsides. So which one should I use? I would love if you could share a short example with your answer. I need random short auto generating Id field.

Answer 1

I ran into this same issue.

What I am doing now is to save the number of inserts for each table in a json file.

Read this number from the json file and pad it with a random string of any length (as you want) which returns a string that is bound to be unique for each table.

Here is tables.json

 //table.json
    {
      "users": 0,
      "sections": 0,
      "purchases": 0,
      "orders": 2,
      "payments": 0,
    }

Here is the createId module

// createId
import jsonfile from "jsonfile";
import path from "path";
const file = path.join(__dirname, "tables.json"); //get the tables.json file path

export const createId = (table: string): string => {
   let tables = jsonfile.readFileSync(file);
   const tableId = tables[table] + 1;
   tables[table] = tableId;
   jsonfile.writeFile(file, tables, (err) => {
     if (err) {
        console.log(err);
     }
   });
   const chars = "abcdefghijklmnopqrstuvwxyz0123456789";
   const salt = shuffle(chars, 2, 5); //you can create this function to get any random string of any length as you want. Here, my shuffle is getting a random string of length between 2 and 5. just to add some extra unpredictability to it
   return tableId + salt;
}

Here is the drizzle schema. take note of the createId argument. ('orders') referencing the table name in the tables.json where it will read the last incremented id, add 1 to it and write it back to the tables.json file. You may decide to use the synchronous read and write for the json file. But this is serving my purpose currently.

import { createId } from '../schemas/create-id.ts

export const orders = pgTable("orders", {
 id: varchar("id", { length: 128 }).$defaultFn(() => createId("orders")).primaryKey(),
 ......
})

Please, if you are using nodemon, remember to us the nodemon --ignore flag to ignore the tables.json file. Otherwise, nodemon will keep restarting on every write to the tables.json.

I hope that helps.