next.jsdatabase-designpgdrizzle

Recommended column type for id field


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.


Solution

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