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