I have an orders table. For simplicity sake, I’ll just include the columns that matters.

The number column is unique but it is not auto-generated by the database, although I hope it could. And it has to be easy to read.

Currently, I’m running a recursive function to look up the table for a number before inserting.

const generateOrderNumber = async(length) => {
  const number = generateKey(length).toUpperCase(); // random readable string with length of 6

  const exists = await pg.query(`SELECT * FROM orders WHERE number = $1`, [number]);

  if (exists.rows.length) {
    return await generateOrderNumber(length + 1);
  }

  return number;
);

What this function does is it will generate a string of the specified length, look up the orders number column with that string, if exists, rerun the function again with length + 1 until none returns.

Is this an optimal way to tackle this problem? Are there better solutions?